Camp and Fevurly Financial Planning have forecasted revenues for the first six months of 2021, as shown in the following table. Month November 2020 December January 2021 February Revenue $44,160 41,400 32,200 32,788 Month March April May June Revenue 34,224 40,553 40,755 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 $9,125 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. Com- missions and partner salaries are paid one month after the revenue is earned. Rent expense for their office space is $4,750 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 2020 was $12,000. a. Create a cash budget for January to June 2021, and determine the firm's end- ing 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.) 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? C. 1 Read Problem 3 of Chapter 4 in the textbook. 2 You will need to build a spreadsheet from scratch on the Problem 3' tab provided in this workbook. Also, be sure to answer any questions posed in the problem and label your answers clearly. 3 Before you begin building your spreadsheet, be sure to refer to the hints provided on the second tab. 4 Review Exhibits 4-1 through 4-6 about Bithlo Barbecues provided in the textbook for some general guidance on building your Cash Budget and decision tree for Current Borrowings. 5 Note that Problem 3 ignores Interest. This particular problem does not offer Interest expense values and does not require you to calculate Interest expense. 6 When you create and run the Scenario Manager, Excel will automatically create a new tab for the results. 4 5 B Hints Cell B5 This value is for the scenario analysis and is multiplied by the expected sales for each month. Note that November and December sales are assumed to be fixed since they are in the past. 2 3 c D E Camp and Fevurly Financi Cash Budget For the Period January 2021 November December January $ 44.160 $ 41,400 S 32.200 6 100% 7 5 Revenues 6 Collections: 7 Cash 8 First Month 9 Bad Debt 10 Net Collections 11 Disbursements: Cell B9 Bad debts are simply not collected, there is no cash flow so this row is blank. It has been included simply to show the full picture (100%) of the collections assumptions. Cell B9 This number is also relevant when estimating collectable sales for the previous month. This is then used to calculate sales commissions and partner salaries. 22.540 12.006 70% 29% 1% 34,546 Camp and Fevurly Financial Planning have forecasted revenues for the first six months of 2021, as shown in the following table. Month November 2020 December January 2021 February Revenue $44,160 41,400 32,200 32,788 Month March April May June Revenue 34,224 40,553 40,755 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 $9,125 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. Com- missions and partner salaries are paid one month after the revenue is earned. Rent expense for their office space is $4,750 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 2020 was $12,000. a. Create a cash budget for January to June 2021, and determine the firm's end- ing 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.) 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? C. 1 Read Problem 3 of Chapter 4 in the textbook. 2 You will need to build a spreadsheet from scratch on the Problem 3' tab provided in this workbook. Also, be sure to answer any questions posed in the problem and label your answers clearly. 3 Before you begin building your spreadsheet, be sure to refer to the hints provided on the second tab. 4 Review Exhibits 4-1 through 4-6 about Bithlo Barbecues provided in the textbook for some general guidance on building your Cash Budget and decision tree for Current Borrowings. 5 Note that Problem 3 ignores Interest. This particular problem does not offer Interest expense values and does not require you to calculate Interest expense. 6 When you create and run the Scenario Manager, Excel will automatically create a new tab for the results. 4 5 B Hints Cell B5 This value is for the scenario analysis and is multiplied by the expected sales for each month. Note that November and December sales are assumed to be fixed since they are in the past. 2 3 c D E Camp and Fevurly Financi Cash Budget For the Period January 2021 November December January $ 44.160 $ 41,400 S 32.200 6 100% 7 5 Revenues 6 Collections: 7 Cash 8 First Month 9 Bad Debt 10 Net Collections 11 Disbursements: Cell B9 Bad debts are simply not collected, there is no cash flow so this row is blank. It has been included simply to show the full picture (100%) of the collections assumptions. Cell B9 This number is also relevant when estimating collectable sales for the previous month. This is then used to calculate sales commissions and partner salaries. 22.540 12.006 70% 29% 1% 34,546