Question

​(Preparation of a cash budget​) Lewis Printing has projected its sales for the first 8 months of 2016 as​ follows:

January	​$140,000	April	​$300,000	July	​$200,000
February	$100,000	May	$255,000	August	$200,000
March	$170,000	June	$180,000

Lewis collects 30 percent of its sales in the month of the​sale, 40 percent in the month following the​ sale, and the remaining 30 percent 2 months following the sale. During November and December of​ 2015, Lewis's sales were $200,000 and $165,000​, respectively. Lewis purchases raw materials 2 months in advance of its sales. These purchases are equal to 70 percent of its final sales. The supplier is paid 1 month after delivery.​ Thus, purchases for April sales are made in February and payment is made in March. In​ addition, Lewis pays $12,000 per month for rent and $20,000 each month for other expenditures. Tax prepayments of $23,500 are made each quarter beginning in March. The​ company's cash balance as of December​ 31, 2015, was $28,000​; a minimum balance of ​$15,000 must be maintained at all times to satisfy the​firm's bank line of credit agreement. Lewis has arranged with its bank for​ short-term credit at an interest rate of 15 percent per annum (1.25 percent per​ month) to be paid monthly. Borrowing to meet estimated monthly cash needs takes place at the end of the​month, and interest is not paid until the end of the following month.​ Consequently, if the firm needed to borrow​ $50,000 during​April, then it would pay $625 (=0.0125 × ​$50,000) in interest during May.​ Finally, Lewis follows a policy of repaying its outstanding​ short-term debt in any month in which its cash balance exceeds the minimum desired balance of $15,000.

a. Lewis needs to know what its cash requirements will be for the next 6 months so that it can renegotiate the terms of its​short-term credit agreement with its​ bank, if necessary. To evaluate this​ problem, the firm plans to evaluate the impact of a​±20 percent variation in its monthly sales efforts. Prepare a​6-month cash budget for Lewis and use it to evaluate the​ firm's cash needs.

b. Lewis has a $20,000 note due in June. Will the firm have sufficient cash to repay the​ loan?

Prepare a cash budget for Lewis Printing covering the first 6 months of

$ 23 comma 500$23,500.

Fill in the Collections for the month of​ January:  ​(Round to the nearest​ dollar.)

	Nov	Dec	Jan	Feb	Mar	Apr	May	June
Sales	​$200,000	​$165,000	​$140,000	​$100,000	​$170,000	​$300,000	​$255,000	​$180,000
​Collections:
Month of sale ​(30​%)
First month ​(40​%)
Second month ​(30​%)
Total Collections			​

Answer 1

Please hit LIKE button if this helped. For any further explanation, please put your query in comment, will get back to you. Part b DATA Minimm Cash Balance Beginning Cash Balance 15,000 28,000 Historical Sales and Base Case Sales Predictions for Future Sales January February 140,000 100,000 May 255,000 June 180,000 200,000 March 170,000 July 200,000 April 300,000 August Annual Interest Purchases as a % Sales = 70% Rate 15.00% Collections: Current Mo. 1 Mo. Later 2 Mo. Later 30% 30% 40% Cash Budget for January thru June Nov Dec Jan Feb Mar Apт May June July August 200,000 165,000 140,000 100,000 170,000 300,000 255,000 180,000 200,000 200,000 Collections Month of sales 42,000 30,000 51,000 90,000 76,500 54,000 66,000 60,000 First month 56,000 40,000 68,000 120,000 102,000 42,000 30,000 51,000 90,000 Second month 49,500 135.500 133.000 188,000 247,500 246,000 Total collections 168,000 Purchases 119,000 70,000 98,000 70,000 210,000 178,500 126,000 140,000 140,000 Payments 98,000 119,000 210,000 178,500 126,000 140,000 Cash Receipts 168,000 135,500 133,000 188,000 247,500 246,000 (collections) Cash Disbursements Payments for Purchases 70,000 12,000 20,000 119,000 210,000 178,500 126,000 140,000 12,000 20,000 12,000 20,000 12,000 20,000 20,000 23,500 Rent 12,000 20,000 23,500 12,000 Other expenditures Tax Deposits Interest on S-T 863 1,155 50 borrowing Total Disbursements 102,000 151,000 265,500 211,363 159,155 195,550 Net Monthly Change 66,000 (15,500) (132,500) (23,363) 88,345 50,450 Analysis of Borrowing Needs Beginning Cash Balance Ending Cash (No Borrowing) Needed Borrowing) Loan Repayment 28,000 94,000 78.500 15,000 15,000 15,000 94,000 78,500 (54,000 (8.363) 103.345 65.450 0 69.000 23.363 0 0 0 0 0 0 88,345 4,017 15,000 15,000 69,000 92,363 Ending Cash Balance 94,000 78,500 15,000 61.433 Cumulative Borowing 4,017 0 0 Part a Projection of Lewis are given in above table. Firm expects to Borrow $69,000 in March and additional $23,363 in April. But by end of the year, firm will retire all its borrowing and wll have cash balance of S65,450. If sals is higher by 20% , then Lewis will be in much better position But if it is less than 20% , firm will be able to retire its borrowing but not before June end