Question

The president of the retailer Prime Products has just approached the company's bank with a request for a $73,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 $41,800, Accounts receivable on April 1 will total $182,000, of which $156,000 will be collected during April and $20,800 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 $ 316,000 $ 214.009 $ 34.20 $ 31,600 $ 74,000 May $575,000 193,000 34,200 $ 31,600 $ 74,800 June $264,000 151,500 $ 27, 30e $ 31,600 $ 43,000 70,500 $ 31,000 3 $ 31.000 $ 31,000 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 $175.000 d. In preparing the cash budget, assume that the $73,000 loan will be made in April and repaid in June Interest on the loan will total $1,080 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. Complete this question by entering your answers in the tabs below. Required 1 Required 2 Calculate the expected cash collections for April, May, and June, and for the three months in total Schedule of Expected Cash Collections April May June Total cash collections Quarter

Answer 1

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Prime Products					Note
Cash collection schedule	April	May	June	Total
Sales	316,000.00	575,000.00	264,000.00	1,155,000.00	A
30% collected in same month	94,800.00	172,500.00	79,200.00	346,500.00	B=A*30%
60% collected in next month		189,600.00	345,000.00	534,600.00	C=60% of A of previous month
8% collected in second month			25,280.00	25,280.00	D=8% of A of previous to previous month
Collection of March month receivables	156,000.00	20,800.00		176,800.00	E
Expected cash collections	250,800.00	382,900.00	449,480.00	1,083,180.00	F=B+C+D+E
Payment for Purchases	April	May	June	Total
Purchase	214,000.00	193,000.00	151,500.00	558,500.00	G
100% paid in next month		214,000.00	193,000.00	407,000.00	H= 100% of G of previous month
Payment of March month payables	175,000.00			175,000.00	I
Payment for Purchases	175,000.00	214,000.00	193,000.00	582,000.00	J=H+I
Cash budget	April	May	June	Total
Beginning Cash Balance	41,800.00	50,800.00	79,900.00	41,800.00	K
Add: Total cash collections	250,800.00	382,900.00	449,480.00	1,083,180.00	See F
Total cash available	292,600.00	433,700.00	529,380.00	1,124,980.00	L
Less: Cash disbursements
Payment for Purchases	175,000.00	214,000.00	193,000.00	582,000.00	See J
Payroll	34,200.00	34,200.00	27,300.00	95,700.00
Lease payments	31,600.00	31,600.00	31,600.00	94,800.00
Advertising	74,000.00	74,000.00	43,000.00	191,000.00
Equipment purchases			70,500.00	70,500.00
Interest paid			1,080.00	1,080.00
Total Cash disbursements	314,800.00	353,800.00	366,480.00	1,035,080.00
Ending cash balance before financing	(22,200.00)	79,900.00	162,900.00	89,900.00
Financing
Borrowings	73,000.00			73,000.00
Repayment			73,000.00	73,000.00
Ending cash balance	50,800.00	79,900.00	89,900.00	89,900.00