Camp and Fevurly Financial Planners have forecasted revenues for the first six m

Question

Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2012, as shown in the following table. The firm collects 60% of its sales immediately, 39% one month after the sale, and 1% are written off as bad debts two months after the sale. The firm assumes that wages and benefits paid to clerical personnel will be $7,000 per month while commissions to sales associates average 25% of collectable sales. Each of the two partners is paid $5,000 per month or 20% of net sales, whichever is greater. Commissions and partner salaries are paid one month after the revenue is earned. Rent expense for their office space is $3,500 per month, and lease expense for office equipment is $800. Utilities average $250 per month, except in May and June when they average only $150. The ending cash balance in December 2011 was $12,000. Create a cash budget for January to June 2012, and determine the firm's ending cash balance in each month assuming that the partners wish to maintain a minimum cash balance of $10,000. Camp and Fevurly are thinking of obtaining a line of credit from then-bank. Based on their expectations for the first six months of the year, what is the minimum amount that would be necessary? Round your answer to the next highest $1,000 and ignore interest charges on short-term debt. Create three scenarios (best case, base case, and worst case) assuming that revenues are 10% better than expected, exactly as expected, or 10% worse than expected. What is the maximum that the firm would need to borrow to maintain its minimum cash balance in all three cases? Use the Scenario Manager and create a summary of your results. Would this change your answer in part b?

Explanation / Answer

Question a. Cash Budget Month Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Total Beginning Cash 12000 14862.5 11975 10000 10000 11665 12000 Collection: December Sales 17550 17550 January 15000 9750 24750 February 16200 10530 26730 March 18000 11700 29700 April 22800 14820 37620 May 24000 15600 39600 June 27000 27000 Total Collection 32550 25950 28530 34500 38820 42600 202950 Cash Payment: Wages and Salaries 7000 7000 7000 7000 7000 7000 42000 Sales Commission 8137.5 6487.5 7132.5 8625 9705 10650 50737.5 Partnars Salaries 10000 10800 12000 15200 16000 18000 82000 Rent Expenses 3500 3500 3500 3500 3500 3500 21000 Utilities 250 250 250 250 150 150 1300 lease Expenses 800 800 800 800 800 800 4800 Total Cash Disbursement 29687.5 28837.5 30682.5 35375 37155 40100 201837.5 Initial Cash 14862.5 11975 9822.5 9125 11665 14165 13112.5 Loan taken /Loab Payment 177.5 875 1052.5 Ending Cash 14862.5 11975 10000 10000 11665 14165 14165 Question b. Shortage amount 1052.5 Minimum Loal required = 2000 Question c. If Revenues are 10%better than expected: Cash Budget Month Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Total Beginning Cash 12000 15987.5 15046.25 15033.5 16746 21322.5 12000 Collection: December Sales 17550 17550 January 16500 10725 27225 February 17820 11583 29403 March 19800 12870 32670 April 25080 16302 41382 May 26400 17160 43560 June 29700 29700 Total Collection 34050 28545 31383 37950 42702 46860 221490 Cash Payment: Wages and Salaries 7000 7000 7000 7000 7000 7000 42000 Sales Commission 8512.5 7136.25 7845.75 9487.5 10675.5 11715 55372.5 Partnars Salaries 10000 10800 12000 15200 16000 18000 82000 Rent Expenses 3500 3500 3500 3500 3500 3500 21000 Utilities 250 250 250 250 150 150 1300 lease Expenses 800 800 800 800 800 800 4800 Total Cash Disbursement 30062.5 29486.25 31395.75 36237.5 38125.5 41165 206472.5 Initial Cash 15987.5 15046.25 15033.5 16746 21322.5 27017.5 27017.5 Loan taken /Loab Payment 0 Ending Cash 15987.5 15046.25 15033.5 16746 21322.5 27017.5 27017.5 If Revenues are 10%worse than expected: Cash Budget Month Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Total Beginning Cash 12000 13737.5 10000 10000 10000 10000 12000 Collection: December Sales 17550 17550 January 13500 8775 22275 February 14580 9477 24057 March 16200 10530 26730 April 20520 13338 33858 May 21600 14040 35640 June 24300 24300 Total Collection 31050 23355 25677 31050 34938 38340 184410 Cash Payment: Wages and Salaries 7000 7000 7000 7000 7000 7000 42000 Sales Commission 7762.5 5838.75 6419.25 7762.5 8734.5 9585 46102.5 Partnars Salaries 10000 10800 12000 15200 16000 18000 82000 Rent Expenses 3500 3500 3500 3500 3500 3500 21000 Utilities 250 250 250 250 150 150 1300 lease Expenses 800 800 800 800 800 800 4800 Total Cash Disbursement 29312.5 28188.75 29969.25 34512.5 36184.5 39035 197202.5 Initial Cash 13737.5 8903.75 5707.75 6537.5 8753.5 9305 -792.5 Loan taken /Loab Payment 1096.25 4292.25 3462.5 1246.5 695 10792.5 Ending Cash 13737.5 10000 10000 10000 10000 10000 10000 Maximum amount to borrow is 10,792.50

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.