Question

The president of the retailer Prime Products has just approached the company's bank with a request for a $47,000, 90-day loan. The purpose of the loan is to assist the company in acquiring inventories. Because the company has had some difficulty in paying off its loans in the past, the loan officer has asked for a cash budget to help determine whether the loan should be made. The following data are available for the months April through June, during which the loan will be used: a. On April 1, the start of the loan period, the cash balance will be $40,000. Accounts receivable on April 1 will total $145,600, of which $124,800 will be collected during April and $16,640 will be collected during May. The remainder will be uncollectible. b. Past experience shows that 30% of a month's sales are collected in the month of sale, 60% in the month following sale, and 8% in the second month following sale. The other 2% is bad debts that are never collected. Budgeted sales and expenses for the three- month period follow: Sales (all on account) Merchandise purchases Payroll Lease payments Advertising Equipment purchases Depreciation April May $ 336,000 $ 514,000 $ 240,000 $ 145,500 $ 16,000 $ 16,000 $ 22,600 $ 22,600 $ 73,400 $ 73,400 - $ 19,800 $ 19,800 June $285,000 $ 158,000 $ 27,000 $ 22,600 $ 40,800 $ 102,500 $ 19,800 c. Merchandise purchases are paid in full during the month following purchase. Accounts payable for merchandise purchases during March, which will be paid in April, total $187,000. d. In preparing the cash budget, assume that the $47,000 loan will be made in April and repaid in June. Interest on the loan will total $820. Required: 1. Calculate the expected cash collections for April, May, and June, and for the three months in total. 2. Prepare a cash budget, by month and in total, for the three-month period.

Answer 1

Expected Cash collections
	April	May	June	Quarter
Sales	336,000	514,000	285,000	1,135,000
Collections:
30% in same month	100,800	154,200	85,500	340,500
60% in following month	124,800	201,600	308,400	634,800
8% in second month following sale		16,640	26,880	43,520
Total collections	225,600	372,440	420,780	1,018,820
2.Cash Budget
	April	May	June	Total
Beginning Cash Balance	40,000	13,600	250,040	40,000
Add: Collections	225,600	372,440	420,780	1,018,820
Cash available	265,600	386,040	670,820	1,058,820
Less: Payments
Purchases	187,000	24,000	145,500	356,500
Payroll	16,000	16,000	27,000	59,000
Lease	22,600	22,600	22,600	67,800
Advertising	73,400	73,400	40,800	187,600
Equipment Purchase			102,500	102,500
Total Disbursements	299,000	136,000	338,400	773,400
Excess/Deficiency	-33,400	250,040	332,420	285,420
Financing
Borrowing	47,000			47,000
Repayment			-47,000	-47,000
Interest			-820	-820
Total Financing	47,000		-47,820	-820
Ending Cash Balance	13,600	250,040	284,600	284,600
Note: Depreciation is a non-cash expense and hence is not taken into cash budget