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: April $ 336,000 514,000 $285,000 $240,000 $145,500 $ 158,000 May June Sales (all on account) Merchandise purchases Payroll Lease payments Advertising Equipment purchases Depreciation $ 16,000 16,000 27,000 $ 22,600 22,600 22,600 $ 73,400 $ 73,400$ 40,800 $102,500 $ 19,800 19,800 $ 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	Total
March Sales	124,800	16,640		141,440
April Sales	100,800	201,600	26,880	329,280
May Sales		154,200	308,400	462,600
June Sales			85,500	85,500
Cash Collections	225,600	372,440	420,780	1,018,820

2.Cash Budget

	April	May	June	Total
Beginning Cash Balance	40,000	13,600	34,040	40,000
Add: Collections	225,600	372,440	420,780	1,018,820
Cash available	265,600	386,040	454,820	1,058,820
Less: Payments
Purchases	187,000	240,000	145,000	572,000
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	352,000	337,900	988,900
Excess/Deficiency	(33,400)	34,040	116,920	69,920
Loan	47,000			47,000
Repayment			47,000	47,000
Interest			820	820
Ending Cash Balance	13,600	34,040	69,100	69,100