Ch.4 Spreadsheet Exercise - Principals of Managerial Finance You have been assig
ID: 2383167 • Letter: C
Question
Ch.4 Spreadsheet Exercise - Principals of Managerial Finance
You have been assigned the task of putting together a statement for the ACME Company that shows its expected inflows and outflows of cash over the months of July 2016 through December 2016.
You have been given the following data for ACME company:
(1)Expected gross sales for May through December, respectively, are $300,000, $290,000, $425,000, $500,000, $600,000, $625,000, $650,000, and $700,000.
(2)12% of the sales in any given month are collected during that month. However, the firm has a credit policy of 3/10 net 30, so factor a 3% discount into the current month's sales collection.
(3)75% of the sales in any given month are collected during the following month after the sale.
(4)13% of the sales in any given month are collected during the second month following the sale.
(5) The expected purchases of raw materials in any given month are based on 60% of the expected sales during the following month.
(6)The firms pays 100% of its current month's raw materials purchases in the following month.
(7)Wages and salaries are paid on a monthly basis and are based on 6% of the current month's expected sales.
(8) Monthly lease payments are 2% of the current month's expected sales.
(9)The monthly advertising expense amounts to 3% of sales.
(10)R&D expenditures are expected to be allocated to August, September, and October at the rate of 12% of sales in those months.
(11) During December a prepayment of insurance for the following year will be made in the amount of $24,000.
(12) During the months of July through December, the firm expects to have miscellanous expenditures of $15,000, $20,000, $25,000, $30,000, $35,000, and $40,000, respectively.
(13)Taxes will be paid in September in the amount of $40,000 and in December in the amount of $45,000.
(14)The beginning cash balance in July is $15,000.
(15) The target cash balance is $15,000.
A. Prepare a cash budget for July 2016 through December 2016 by creating a combined spreadsheet that incorporates spreadsheets similar to those in tables 4.8, 4.9, and 4.10. Divide you spreadsheet into three sections:
a)Total Cash receipts
b) Total cash disbursements
c) Cash budget covering the period of July through December.
The cash budget should reflect the following:
a)Beginning and ending monthly cash balances
b)The required total financing in each month required
c)The excess cash balance in each month with excess.
B. Based on your analysis, breifly describe the outlook for this company over the next 6 months. Discuss its specific obligations and the funds available to meet them. What could the firm do in the case of a cash deficit? (Where could it get the money?) What should the firm do if it has a cash surplus?
Explanation / Answer
Cash Receipts
Cash Disbursements
Cash Budget
The company seems to be in good shape as it progresses from July to December as the cash collections go up on account of improving sales. In the event of cash deficit, it needs to take overdraft or cash credit facility from the bank for a limited period. In case of surplus, it may invest the funds in debt or equity of other companies depending on its risk taking capacity.
May June July August September October November December Total Sales 300,000 290,000 425,000 500,000 600,000 625,000 650,000 700,000 Collection within Month 36000 34800 51000 60000 72000 75000 78000 84000 Collection After Discount 34920 33756 49470 58200 69840 72750 75660 81480 Collection After 1 Month 225000 217500 318750 375000 450000 468750 487500 Collection After 2 Months 39000 37700 55250 65000 78000 81250 Total 34920 258756 305970 414650 500090 587750 622410 650230