Question

Preparation of a Cash Budget) Lewis Printing has projected its sales for the first 8 $200,000 January $100,000 april 300,000 july 180,000 February120,000 May 275,000 August June 200,000 March 150,000 Lewis collects 20 percent of its sales in the month of the sale, 50 percent in the month following the sale, and the remaining 30 percent 2 months following the sale. Durin November and $175,0 mber of 2017, Lewis's sales were $220,000 and ive res raw materials 2 months in advance of its sales equal to 65 percento 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 $10,000 per month for rent and $20,000 each month for other expenditures. Tax prepayments of $22,500 are made each quarter beginning in March. The company's cash balance as of December 31, 2017, was $28,000; a minimum balance of s25,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 12 percent per annum (1 estimated monthly cash needs takes place at the end of the month, and interest is not until the end of the following month. Consequently, if the firm needed to borrow 000 during April, then it would pay $500 (01x $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 nt per month) to be paid monthly. Borrowing to meet i $25,000. a. Lewis 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 renegotiate the terms of its shori-term cr 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?

Answer 1

Ans:

b. based on the below cash budget company will have $25,000 cash which is the minimum cash they need to maintain hence it will to have borrow $20,000 from the other sources to repay the $20,000 notes due in June.

Cash budget as per the base case:

Base case	Jan	Feb	Mar	Apr	May	June
Credit sales	100,000	120,000	150,000	300,000	275,000	200,000
Cash sales	20,000	74,000	120,000	171,000	250,000	267,500
Suppliers payment (at 65% of credit sales) made in the previous month	-78,000	-97,500	-195,000	-178,750	-130,000	-130,000
Rent	-10,000	-10,000	-10,000	-10,000	-10,000	-10,000
Other expenditure	-20,000	-20,000	-20,000	-20,000	-20,000	-20,000
Tax paid			-22,500			-22,500
Interest paid	0	-850	-1,385	-2,660	-3,038	-2,138
Cash flow (before borrowing)	-88,000	-53,500	-127,500	-37,750	90,000	85,000
Additional borrowing / repayment	85,000	53,500	127,500	37,750	-90,000	-85,000
Net cash flow	-3,000	0	0	0	0	0
Cash balance	25,000	25,000	25,000	25,000	25,000	25,000
Borrowing at start of month	0	85,000	138,500	266,000	303,750	213,750
Borrowing at month end	85,000	138,500	266,000	303,750	213,750	128,750

Excel formula:

10 Base case Feb Mar 11 Credit sales 275000 100000 150000 13 Cash sales :011*20% :E11"20%+D11"50% :F11"20%+E11*50%+D11*30% -G 11"20%+F11*50%+E11"3096-H11"20%+611°50%+F11*30%-111"20%+H11*50%+611*30% Suppliers payment (at 65% of credit sales) E11 65% -611 *65% -111"65% 14 -F11"6596 11"6596 -H11 *6596 15 Rent 10000 10000 10000 10000 16 Other expenditure 20000 17 Tax paid 22500 -1%"626 18 Interest paid -196"D26 --196" H26 SUM(H13:H17) --1%"126 19 Cash flow (before borrowing) SUM(D13:D17) SUM(E13:E17) SUM(F13:F17) SUM(G13:G17) SUM(113:117) -SUM(G19:G20) 21 Net cash flow -SUM(D19:D20 -SUM(E19:E20 SUM(F19:F20) -SUM(H19:H20) -SUM(119:120) -F23+G21 -H23+121 23 Cash balance -C23+D21 -E23+F21 -D23+E21 25 26 Borrowing at start of month 7Borrowing at month end D27 -H27 -C27+D20 -D27+E20 -E27+F20 -F27+G20 -G27+H20 -H27+120

Best case with +20% rise in Credit sales:

Best case	Jan	Feb	Mar	Apr	May	June
Credit sales (+20% increase in credit sales)	120,000	144,000	180,000	360,000	330,000	240,000
Cash sales	24,000	88,800	144,000	205,200	300,000	321,000
Suppliers payment (at 65% of credit sales) made in the previous month	-93,600	-117,000	-234,000	-214,500	-156,000	-156,000
Rent	-10,000	-10,000	-10,000	-10,000	-10,000	-10,000
Other expenditure	-20,000	-20,000	-20,000	-20,000	-20,000	-20,000
Tax paid			-22,500			-22,500
Interest paid	0	-966	-1,548	-2,973	-3,366	-2,226
Cash flow (before borrowing)	-99,600	-58,200	-142,500	-39,300	114,000	112,500
Additional borrwing / repayment	96,600	58,200	142,500	39,300	-114,000	-112,500
Net cash flow	-3,000	0	0	0	0	0
Cash balance	25,000	25,000	25,000	25,000	25,000	25,000
Min cash balance
Borrowing at start of month	0	96,600	154,800	297,300	336,600	222,600
Borrowing at month end	96,600	154,800	297,300	336,600	222,600	110,100

Excel formula:

30 Best case Feb 31 Credit sales (+20% variation in crec-100000*1.2-120000*1.2 -150000 1.2 -300000 1.2 -275000 1.2 -200000 1.2 32 33 Cash sales -E31"20%+D31°509(-F31*20%+E31*50%+D31*30% -G31*20%+F31*50%+E31*309-H31*20%«G31°50%+F31°30%-131*20%+H31*50%+G31*30% -D31"20% Suppliers payment (at 65% of credit sales) made in the previous month 34 --E3 1.65% --F3 1.65% -G31 "65% --H3 1.65% -131.65% -131.65% 35 Rent 10000 10000 10000 10000 10000 10000 6 Other expenditure 20000 20000 20000 20000 20000 20000 37 Tax paid 22500 22500 38 Interest paid -SUM(G33:G37) 39 Cash flow (before borrowing) SUM(D33:D37) SUM(E33:E37)SUM(F33:F37) -SUM(H33:H37) -SUM(U33:137) 41 Net cash flow -SUM(D39:D40-SUM(E39:E40) SUM(F39:F40) -SUM(G39:G40) -SUM(H39:H40) SUM(I39:140) 42 43 Cash balance -C43+D41 -D43+E41 -E43+F41 F43+G41 -G43+H41 H43+141 45 46 Borrowing at start of month -C47 -D47 -E47 -F47 -G47 -H47 H474140 47 Borrowing at month end -C47+D40 -D47+E40 -E47+F40 -F47+G40 -G47+H40

Bear case with -20% decrease in credit sales:

Bear case	Jan	Feb	Mar	Apr	May	June
Credit sales (-20% variation in credit sales)	80,000	96,000	120,000	240,000	220,000	160,000
Cash sales	16,000	59,200	96,000	136,800	200,000	214,000
Suppliers payment (at 65% of credit sales) made in the previous month	-62,400	-78,000	-156,000	-143,000	-104,000	-104,000
Rent	-10,000	-10,000	-10,000	-10,000	-10,000	-10,000
Other expenditure	-20,000	-20,000	-20,000	-20,000	-20,000	-20,000
Tax paid			-22,500			-22,500
Interest paid	0	-734	-1,222	-2,347	-2,709	-2,049
Cash flow (before borrowing)	-76,400	-48,800	-112,500	-36,200	66,000	57,500
Additional borrwing / repayment	73,400	48,800	112,500	36,200	-66,000	-57,500
Net cash flow	-3,000	0	0	0	0	0
Cash balance	25,000	25,000	25,000	25,000	25,000	25,000
Min cash balance
Borrowing at start of month	0	73,400	122,200	234,700	270,900	204,900
Borrowing at month end	73,400	122,200	234,700	270,900	204,900	147,400

Excel formula:

48 49 Bear case Feb Credit sales (-20% variation in cred-100000*0.8-120000*0.8 50 -150000 0.8 .275000*0.8 -200000 0.8 51 52 Cash sales -050" 20% -E50"20%+D50"50%书0"20%-E50"50%+D50"30% -650"20%+50"50%-E50"30%=H50"20%+650"50%+F50"30x450"20%-H50"50%+650"30% suppliers payment (at 65% of credit sales) made in the previous month =-E50*65% --G 50° 65% -H50*65% =150*65% 53 50*65% 54 Rent 10000 10000 10000 10000 10000 10000 55 Other expenditure 20000 -20000 20000 -20000 20000 56 Tax paid 22500 22500 57 Interest paid 58 Cash flow (before borrowing)SUM(DS2:D56) SUM(ES2:ES6 SUM(F52:F56) SUM(G52:G56) SUM(H52:H56) SUM(I52:156) -SUM(G58:G59) 60 Net cash flow =SUM(D58:D59) =SUM(E58:E59) -SUM(F58:F59) -SUM(158:159 SUM(H58:H59) 61 62 Cash balance -C62+D60 -D62+E60 -E62+F60 -F62 G60 :G62+H60 커62+160 64 65 Borrowing at start of month =F66 弍66 D66 -G66 66 Borrowing at month end 66+D59 -D66+E59 E66+F59 =F66-G59 -G66+H59 커66+159