Question

Problem 21(7)-4B Excell Assignments

Instructions Answers are entered in the cells with gray backgrounds. Cells with non-gray backgrounds are protected and cannot be edited. 1 An asterisk (*) will appear to the right of an incorrect entry. The essay answer will not be graded. Enter a zero in cells you would otherwise leave blank. nim ARS August SS Mercury Shoes Inc. Cash Budget For the Three Months Ending August 31 June July Estimated cash receipts from: Cash sales Collections from accounts receivable Total cash receipts Estimated cash payments for Manufacturing costs Selling and administrative expenses Capital expenditures Other purposes: Income tax Pr. 21(7)-4B

Answer 1

Answer 1:

The cash budget and supporting schedules are stated below:

		Percentage	June	July	August
Sales			160000	185000	200000
Cash Sales		10%	16000	18500	20000
On Account Sales		90%	144000	166500	180000
Manufacturing Costs			66000	82000	105000
Less: Depreciation, Insurance and Property tax			12000	12000	12000
Other Manufacturing Costs - On Account			54000	70000	93000
Selling and Administration costs			40000	46000	51000
Capital Expenditures					120000
COLLECTION FROM ACCOUNTS RECEIVABLES
	Sales on account	Percentage	June	July	August
April sales			48000
May Sales	150000
-Collected in june		60%	90000
-Collected in july		40%		60000
June Sales	144000
-Collected in july		60%		86400
-Collected in august		40%			57600
July sales	166500
-Collected in august		60%			99900
			138000	146400	157500
CASH PAYMENTS FOR MANUFACTURING COSTS
	Cost on Account	Percentage	Payments
Paid in June
-Incurred in May			13000
-Incurred in June	54000	80%	43200
			56200
Paid in July
-Incurred in June	54000	20%	10800
-Incurred in July	70000	80%	56000
			66800
Paid in August
-Incurred in July	70000	20%	14000
-Incurred in August	93000	80%	74400
			88400
CASH BUDGET
			June	July	August
Estimated Receipts from
Cash Sales			16000	18500	20000
Collection from Acct Receivable			138000	146400	157500
Total Cash Receipts			154000	164900	177500
Estimated cash payments for
Manufacturing costs			56200	66800	88400
Selling and administrative expenses			40000	46000	51000
Capital Expenditures					120000
Other Purposes:
Income Tax				24000
Dividends					15000
Total Cash Payments			96200	136800	274400
Cash Increase/(Decrease)			57800	28100	-96900
Cash balance at beginning			42000	99800	127900
Cash balance at end			99800	127900	31000
Minimum cash balance			40000	40000	40000
Excess/(Deficiency)			59800	87900	-9000

Answer 2:

On the basis of above budget we can see that there is a deficiency of Cash balance as compared to minimum cash balance in the month of August to the tune of $9000. This can be made up by selling marketable securities to the tune of $9000 out of $25000 which we have readily available.

Again surplus cash balance in previous two months may be invested for short term if suitable market conditions exist.