1 Booknark Show all steps: ON Chapter 3, Problem 3P Press esc to exit full screen > Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2012 as shown in the following table. Month Revenue Month Revenue November 2011 $48.000 March 30,000 December 45,000 pril 38,000 25,000 January 2012 ay 40,000 February 27,000 June 45,000 The firm collects 60% of its sales immediately, 39% one month after the sale, and 1 % are written off as bad debis 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 assoclates 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. a. Create a cash budget for January to June 2012, and detemine the firm's ending cash balance in each manth assuming that the partners wish to maintain a minimum cash balance of $10,000. b. Camp and Fevurly are thinking of obtaining a line of credit from their 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. (Hint: Look up the ROUNDUP function in the online help.) c. Create three scenarios (best case, base case, and worst case) assuming that revenues are 10% better than expected, exactly as expacted, 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? Step-by-step solution 1 Booknark Show all steps: ON Chapter 3, Problem 3P Press esc to exit full screen > Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2012 as shown in the following table. Month Revenue Month Revenue November 2011 $48.000 March 30,000 December 45,000 pril 38,000 25,000 January 2012 ay 40,000 February 27,000 June 45,000 The firm collects 60% of its sales immediately, 39% one month after the sale, and 1 % are written off as bad debis 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 assoclates 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. a. Create a cash budget for January to June 2012, and detemine the firm's ending cash balance in each manth assuming that the partners wish to maintain a minimum cash balance of $10,000. b. Camp and Fevurly are thinking of obtaining a line of credit from their 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. (Hint: Look up the ROUNDUP function in the online help.) c. Create three scenarios (best case, base case, and worst case) assuming that revenues are 10% better than expected, exactly as expacted, 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? Step-by-step solution