Prepare a cash budget, by month and in total, for the three-month period.
Assignment
Schedule of Expected Cash Collections; Cash Budget [LO8–2, LO8–8] The president of the retailer Prime Products has just approached the company’s bank with a request for a $30,000, 90-day loan. The purpose of the loan is to assist the company in acquiring invento-ries. Because the company has had some difficulty in paying off its loans in the past, the loan offi-cer 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 $24,000. Accounts receivable on April 1 will total $140,000, of which $120,000 will be collected during April and $16,000 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% rep-resents bad debts that are never collected. Budgeted sales and expenses for the three-month period follow: April May June Sales (all on account) . . . . . . . . . . . . . $300,000 $400,000 $250,000 Merchandise purchases . . . . . . . . . . . $210,000 $160,000 $130,000 Payroll . . . . . . . . . . . . . . . . . . . . . . . . . $20,000 $20,000 $18,000 Lease payments . . . . . . . . . . . . . . . . . $22,000 $22,000 $22,000 Advertising . . . . . . . . . . . . . . . . . . . . . . $60,000 $60,000 $50,000 Equipment purchases . . . . . . . . . . . . . — — $65,000 Depreciation . . . . . . . . . . . . . . . . . . . . $15,000 $15,000 $15,000 c. Merchandise purchases are paid in full during the month following purchase. Accounts payable for merchandise purchases during March, which will be paid during April, total $140,000. d. In preparing the cash budget, assume that the $30,000 loan will be made in April and repaid in June. Interest on the loan will total $1,200.
Required: 1. Prepare a schedule of 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.
3. If the company needs a minimum cash balance of $20,000 to start each month, can the loan be repaid as planned? Explain.
Accounts receivable April May June Quarter
April Sales
30% X 300,000
60% X 300,000 $120,000.00 $16,000.00 $136,000.00
8% X 300,000 $90,000.00 $90,000.00
$180,000.00 $180,000.00
May Sales $24,000.00 $24,000.00
30% X 400,000 $120,000.00 $120,000.00
60% X 400,000 $240,000.00 $240,000.00
$75,000.00 $75,000.00
Junes Sales
30% X 250,000
Total Cash Collections $210,000.00 $316,000.00 $334,000.00 $850,000.00
Cash balance (beginning) $24,000.00 $22,000.00 $26,000.00 $24,000.00
Receipts (collections) $210,000.00 $316,000.00 $339,000.00 $865,000.00
Total available $234,000.00 $338,000.00 $365,000.00 $889,000.00
Disbursements
Merchandise purchases $140,000.00 $210,000.00 $160,000.00 $510,000.00
Payroll $20,000.00 $20,000.00 $18,000.00 $58,000.00
Lease Payments $22,000.00 $20,000.00 $22,000.00 $66,000.00
Advertising $60,000.00 $60,000.00 $50,000.00 $170,000.00
Equipment Purchases – – $65,000.00 $65,000.00
Total disbursements $242,000.00 $312,000.00 $315,000.00 $869,000.00
Receipt – disbursements $8,000.00 $26,000.00 $50,000.00 $20,000.00
Financing
Borrowing $30,000.00 – – $30,000.00
Repayments – – -$30,000.00 -$30,000.00
Interest – – -$1,200.00 -$1,200.00
Total Financing $30,000.00 – $31,200.00 -$1,200.00
Ending Cash Balance $22,000.00 $26,000.00 $18,800.00 $18,800.00
