This need financial calculations and analysis in Excel

Assessment:

Yamada Company is an office supplies speciality store in Melbourne. Recently, Peter Marker, the manager of the store, has been concerned about cash flow shortages, which arose quite unexpectedly in the last three months of the past year. The store bank account went into overdraft and incurred interest charges. Peter believes that the main source of the cash flow difficulties is a lack of attention to outstanding customer accounts and the practice of purchasing office supplies in large quantities at irregular intervals.

Actual sales for the last two months of 2015 and budgeted sales for 2016 follow (all sales are credit sales)

Experience has shown that of total sales, 45 per cent are collected in the month of sale, 25 per cent are collected in the following month, 20 per cent are collected two months after sale, and 10 per cent are uncollectible. In the budget year, new credit policies are expected to result in collections of 55 per cent in the month of sale, 25 per cent in the next month, 10 per cent are collected two months after sale and 10 per cent uncollectible. .

The office supplies purchased in December 2015 for $50,000 were used in 2015. The cost of supplies to be purchased for the first half of the 2016 will be $440,000. These office supplies will be purchased in three lots-January ($80,000), March ($220,000) and May ($140,000).

Half of suppliers' accounts are paid in the month they are incurred and half in the following month. Monthly salaries of $50,000 (includes $5,000 depreciation), selling and administrative expense of $20,000 are paid in the month they are incurred. Bank interest calculated on the overdraft balance at the end of month is paid on the first day of the following month. Bank overdraft as at 30 December 2015 was $250,000. The maximum credit limit of this overdraft facility is $500,000 and the applicable interest 19% per annum.

You are required to prepare a report to the manager of Yamada Company showing the following:

(a) The effect on the cash flows for the period from 1st January to 30th June

(b) The total amount to be collected from the customers (Account Receivables) as at 30 June 2016.

(c) Net profit before tax for the period from January to June, assuming that there was no beginning inventory and the ending inventories amounts to $40,000. Ignore tax.

(d) The steps the manager could take to ensure that the cash balance of the store remains positive and the ways in which budgeting may assist the company.

Note: You are expected to use MS Excel spreadsheet for your analysis and to show the detailed results of your analysis in Appendices.