Question

The president of the retailer Prime Products has just approached the company’s bank with a request for a $71,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:

The president of the retailer Prime Products has just approached the company's bank with a request for a $71,000, 90-day oan. 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 oan 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 $43,800. Accounts receivable on April 1 will total $179,200, of which $153,600 will be collected during April and $20,480 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 June $ 235,000 $ 652,000 $ 342,000 $ 197,000 $213,500 $ 151,000 $ 33,600 $ 33,600 $ 23, 100 $ 39,600 $ 39,600 $ 39,600 $ 70,200 $ 70,200 $ 83, 280 $ 70,000 $ 31,200 $ 31,200 $ 31,200 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 $145,500. d. In preparing the cash budget, assume that the $71,000 loan will be made in April and repaid in June. Interest on the loan will total $1,060.

Answer 1

To see the formulas, please see the below images:

Solution Requirement 1 Schedule of expected cash collection Particulars April May June Quarter $174,080 Cash collection from accounts receivables $153,600 $20,480 Cash collection from Sales: |- April month sales - May month sales |- June month sales Total Cash collections $230,300 $70,500 $141,000 $18,800 $195,600 $391,200 $102,600 $512,600 $586,800 $102,600 $224,100 $357,080 $1,093,780 Requirement 2: Prime Products Cash Budget April $43,800 May June Quarter Beginning cash balance Add: receipts: $66,680 $50,000 $43,800 $357,080 $512,600 Collections from customers $224,100 $1,093,780 Total cash available Less: Cash disbursements: Merchandise purchases $1,137,580 $267,900 $407,080 $579,280 $56,000 $90,300 $118,800 $213,500 $145,500 $197,000 $33,600 $39,600 $70,200 $33,600 Payroll Lease payments $23,100 $39,600 $70,200 $39,600 $83,280 Advertising $223,680 Equipment purchases Total cash disbursements Excess (deficiency) of cash available over disbursements Financing $70,000 $70,000 $1,058,780 $78,800 $429,480 $149,800 $288,900 $340,400 ($21,000) $66,680 $71,000 (S71,000) ($1,060) ($1,060) $77,740 $71,000 Borrowings Repayments ($71,000) $0 $0 $0 ($1,060) Interest Total financing Ending cash balance $0 ($72,060) $77,740 $71,000 $66,680 $50,000

C Е F A Solution 2 Requirement 1: 4 Schedule of expected cash collection April 6 Particulars May June Quarter 7 |-SUM(C8:E8) 8 Cash collection from accounts receivables 153600 20480 0 Cash collection from Sales: -April month sales -May month sales 13 10 |-SUMIC11:E11) -652000 60 %-SUM(C12: E12) -342000 30 %6SUMIC13:E13) -SUMIC7:C13)-SUMID7:D13 )-SUMIE7:E13)-SUMIF7:F13) -235000 8 % - 235000 30 % 11 -235000 60 % - 652000 30 % 12 0 -June month sales 0 Total Cash collections 14 15

A C E F Requirement 2: 16 17 18 Prime Products Cash Budget 19 20 April 43800 May June Quarter 21 -С38 ED38 -C21 Beginning cash balance Add: receipts: 22 SUM(C23:E23) 23 Collections from customers -C14 |-SUM(C21:C23)-SUM(D21:D23 )-SUM(E21:E23)-SUM(F21:F23) =D14 E14 Total cash available Less: Cash disbursements: Merchandise purchases Payroll Lease payments Advertising Equipment purchases Total cash disbursements Excess (deficiency) of cash available over disbursements-C24-C31 Financing: Borrowings Repayments 24 25 145500 33600 39600 -SUM(C26:E26) SUM(C27:E27) -SUMIC28:E28) |-SUMIC29:E29) |-SUM(C30:E30) -SUM(C26:C30)SUM(D26:D30) SUM(E26:E30) SUM(F26: F30) =F24-F31 26 197000 33600 213500 23100 39600 27 28 39600 29 70200 70200 83280 30 70000 0 31 -D24-D31 32 - E24-E31 33 |=SUM(C34:E34) -SUM(C35:E35) SUM(C36:E36) |-SUM(C34:C36)-SUM(D34:D36 )-SUM(E34:E36)-SUM(F34:F36) -F32+F37 0 0 34 71000 35 -C34 0 0 36 0 |-1060 Interest 0 Total financing Ending cash balance 39 37 - E32+ E37 38 -С32+С37 D32+D37