is $920. Utilities average $288 per month, except in May and June when they average only $173. The ending cash balance in December 2016 was $12,000. a. Create a cash budget for January to June 2017, 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. b. Camp and Fevurly are thinking of obtaining a line of credit from their bank. Based on their forecast 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 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? April and May sales were $186,875 and $219,375, respectively. The firm collects 15% of its sales during the month, 75% the following month, and 10% two months after the sale. Each month it purchases inventory equal to 55% of the next month's expected sales. The company pays for 40% of its inventory purchases in the same month and 60% in the following month. However, the firm's suppliers give it a 2% discount if it pays during the same month as the purchase. A minimum cash balance of $25,000 must be maintained each month, and the firm pays 4% annually for short-term borrowing from its bank. a. Create a cash budget for June to October 2017. The cash budget should account for short-term borrowing and payback of outstanding loans as well as the interest expense. The firm ended May with a $30,000 unadjusted cash balance. b. Bob Loblaw, the president, is considering stretching out its inventory payments. He believes that it may be less expensive to borrow from suppliers than from the bank. He has asked you to use the Scenario Manager to see what the total interest cost for this time period would be if the company paid for 0%, 10%, 30%, or 40% of its inventory purchases in the same month. The remainder would be paid in the following month. Create a scenario summary and describe whether the results support Bob's beliefs. 3. Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2017, as shown in the following table. Month Revenue Month Revenue November 2016 $44.160 March $27,600 December 41,400 April 34,960 January 2017 23,000 May 36,800 February 24,840 June 41,400 The firm collects 70% of its sales immediately, 29% 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 $8,050 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 $4,025 per month, and lease expense for office equipment is $920. Utilities average $288 per month, except in May and June when they average only $173. The ending cash balance in December 2016 was $12,000. a. Create a cash budget for January to June 2017, 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. b. Camp and Fevurly are thinking of obtaining a line of credit from their bank. Based on their forecast 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 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? April and May sales were $186,875 and $219,375, respectively. The firm collects 15% of its sales during the month, 75% the following month, and 10% two months after the sale. Each month it purchases inventory equal to 55% of the next month's expected sales. The company pays for 40% of its inventory purchases in the same month and 60% in the following month. However, the firm's suppliers give it a 2% discount if it pays during the same month as the purchase. A minimum cash balance of $25,000 must be maintained each month, and the firm pays 4% annually for short-term borrowing from its bank. a. Create a cash budget for June to October 2017. The cash budget should account for short-term borrowing and payback of outstanding loans as well as the interest expense. The firm ended May with a $30,000 unadjusted cash balance. b. Bob Loblaw, the president, is considering stretching out its inventory payments. He believes that it may be less expensive to borrow from suppliers than from the bank. He has asked you to use the Scenario Manager to see what the total interest cost for this time period would be if the company paid for 0%, 10%, 30%, or 40% of its inventory purchases in the same month. The remainder would be paid in the following month. Create a scenario summary and describe whether the results support Bob's beliefs. 3. Camp and Fevurly Financial Planners have forecasted revenues for the first six months of 2017, as shown in the following table. Month Revenue Month Revenue November 2016 $44.160 March $27,600 December 41,400 April 34,960 January 2017 23,000 May 36,800 February 24,840 June 41,400 The firm collects 70% of its sales immediately, 29% 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 $8,050 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 $4,025 per month, and lease expense for office equipment