Question

You have been assigned the task of putting together a statement for the ACME Com- pany 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 firm 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 miscella- neous 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.

TO DO

a. Prepare a cash budget for July 2016 through December 2016 by creating a com- bined spreadsheet that incorporates spreadsheets similar to those in Tables 4.8, 4.9, and 4.10. Divide your spreadsheet into three sections:

(1) Total cash receipts (2) Total cash disbursements (3) Cash budget covering the period of July through December

The cash budget should reflect the following: (1) Beginning and ending monthly cash balances (2) The required total financing in each month required (3) The excess cash balance in each month with excess

b. Based on your analysis, briefly 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?

Answer 1

ACME Company
	May	June	July	Aug.	Sep.	Oct.	Nov.	Dec.
Gross Sales	300000	290000	425000	500000	600000	625000	650000	700000
Collections:
Current mth sales-12% *(1-3%)collections	34920	33756	49470	58200	69840	72750	75660	81480
Prev. mth.*75% collections		225000	217500	318750	375000	450000	468750	487500
Mth. Before prev. mth.*13% coll.			39000	37700	55250	65000	78000	81250
Total sales collections			305970	414650	500090	587750	622410	650230

Raw mat. Purch.sales*60%	180000	174000	255000	300000	360000	375000	390000	420000
Purchases		180000	174000	255000	300000	360000	375000	390000

Cash Budget									Total
Beginning balance			15000	15000	15000	15000	15000	15000	15000
Total sales collections			305970	414650	500090	587750	622410	650230	3081100
Total cash available			320970	429650	515090	602750	637410	665230	3096100
Cash disbursements for :
Purchases		180000	174000	255000	300000	360000	375000	390000	1854000
Wages and salaries(6%*Sales)	18000	17400	25500	30000	36000	37500	39000	42000	210000
Lease payments(2%*sales)	6000	5800	8500	10000	12000	12500	13000	14000	70000
Advertising expense(Sales*3%)	9000	8700	12750	15000	18000	18750	19500	21000	105000
R&D(sales*12%)				60000	72000	75000			207000
Insurance prepaid								24000	24000
Misc. exp.			15000	20000	25000	30000	35000	40000	165000
Taxes					40000			45000	85000
Total cash disbursements			235750	390000	503000	533750	481500	576000	2720000
Surplus/(Deficit)			85220	39650	12090	69000	155910	89230	376100
Add Borrowings:					2910				2910
Less: Int. on borrowings									0
Less: Investments			70220	24650		54000	140910	74230	364010
Ending Balance			15000	15000	15000	15000	15000	15000	15000

b. Other than regular trade payments like purchases, the firm has specific commitments such as R&D & Insurance payments.
The firm has borrowed when there was a deficit , ie below target balance of $ 15000
In all the other months it has invested the excess balance over $ 15000