Question

Lakewood Laser SkinCare's ending cash balance as of January 31, 2015 (the end of its fiscal year 2014) was $15,000. Its expected cash collections and payments for the next six months are given in the following table. Month Collections Payments February $24,750 $29,100 March 27,450 31,200 April 34,050 32,400 May 43,650 36,450 June 48,750 40,050 July 54,000 40,800 a. Calculate the firm's expected ending cash balance for each month. b. Assuming that the firm must maintain an ending cash balance of at least $12,000, how much must they borrow during each month? c. If the firm must pay 5% annual interest on its short-term borrowing, what are your ending cash balances for each month? d. What are the ending cash balances if the firm uses any cash in excess of the minimum to pay off its short-term borrowing in each month?

Answer 1

Opening Collections A A a. Calculation of the firm's expected cash balance at the end of each month. Ending S.No Month | Payments Cash Balance Cash Balance February 15,000 24,750 29,100 10,650 March 10,650 27,450 31,200 6,900 April 6,900 34,050 32,400 8,550 May 8,550 43,650 36,450 15,750 June 15,750 48,750 40,050 24,450 July 24,450 $ 54,000 $ 40,800 $ 37,650 A A A A A A au + WNH A LA LA LA LA LA A A A A A A A A A A b. Calculation of amount to be borrowed each month to maintain a cash balance of $ 12,000 each month. Opening Balance S.No Month Collections Payments Loan Amount Cash Balance (Before Loan) February 15,000 24,750 29,100 10,650 1,350 March 12,000 27,450 31,200 $ 8,250 3,750 April 12,000 34,050 32,400 13,650 May 13,650 43,650 36,450 20,850 June 20,850 48,750 40,050 $ 29,550 July 29,550 $ 54,000 $ 40,800 $ 42,750 $ WNA A A A A A Ending Cash Balance 12,000 12,000 13,650 20,850 29,550 42,750 Ending Loan Balance 1,350 5,100 5,100 5,100 5,100 5,100 A A A A A LA LA LA LA LA A A A A A A A A A A A A A A A c. Calculation of the Ending Cash Balance after paying an Interest Amount @ 5% per Annum. Interest Balance Opening S.No Month Collections Payments Cash Balance | @ 5% p.a (Before Loan) 15,000 24,750 29,100 $ 10,650 March 12,000 27,450 31,200 8,244 April 12,000 $ 34,050 32,400 13,629 13,629 43,650 36,450 20,807 June 20,807 48,750 40,050 29,486 July 29,486 $ 54,000 40,800 $ 42,665 A A A A 3,756 A Loan Amount 1,350 $ $ $ $ $ Ending Cash Balance 12,000 12,000 13,629 20,807 29,486 42,665 than 1 Ending Loan Balance 1,350 5,106 5,106 5,106 5,106 5,106 A ou WNA A A A htt May A A A A $ A A A A A A A A |

A A A A A A d. Calculation of the Ending Cash Balance after paying the Loan Amount with excess of Minimum Cash Balance. Opening Interest Balance S.No Month Collections Payments Loan Amount Cash Balance @ 5% p.a (Before Loan) February 15,000 24,750 29,100 - $ 10,650 1,350 March 12,000 27,450 31,200 6 $ 8,244 3,756 April 12,000 34,050 32,400 21 $ 13,629 -1,629 May 12,000 43,650 36,450 14 $ 19,186 -3,477 June 15,709 $ 48,750 40,050 24,409 $ July 24,409 $ 54,000 $ 40,800 $ - $ 37,609 $ Ending Cash Balance 1,350 5,106 3,477 A A A A A A AWN Ending Cash Balance 12,000 12,000 12,000 15,709 24,409 37,609 A A A A A A A LA LA LA LA LA A LA LA LA LA LA A A A A A A A A A A A A A A A A A Note : Interest is calculated on the Previous Month's Ending Cash Balance @ 5% * 1/12 months.