Final Part 1 (Excel) Atwood Country Club 1. Open the file Final Part 1.xlsx attached to this assignment in the Final Exam Lessons folder. 2. Create the range names as seen below: Cell Address C5 C6 C7 C8 D5 D6 Range Name Initial_Members Initial_Fee Initial_Monthly Overhead Initial_Monthly Payroll Growth_Factor_Members Growth_Factor_Fee Growth Factor Overhead Growth Factor Payroll Profit_Year_1 Profit Year 2 Profit_Year_3 Profit_Year_4 Profit_Year_5 D7 D8 C20 D20 E20 F20 G20 3. In cell C11, enter the formula to reference cell C5 using a range name. 4. In cell C12, enter the formula to reference cell C6 using a range name. 5. In cell C13, enter the formula to multiply cell C11 by cell C12 by 12 months in a year. Copy the formula in cell C13 through the range D13:G13. 6. Enter the appropriate formula in cell C16 to multiply the initial monthly overhead value by 12 months in a year. Use a range name in the formula. 7. Enter the appropriate formula in cell C17 to multiply the initial monthly payroll value by 12 months in a year. Use a range name in the formula. 8. Enter the appropriate formula in cell C18 to add up the two expenses. Copy the formula from cell C18 through the range D18:18. 9. Enter the appropriate formula in cell C20 to calculate Net Profit Before Taxes, the difference between Annual Member Revenue in cell C13 and Total Expenses in cell C18. Copy the formula from cell C20 through the range D20:620. 10. Enter the appropriate formula in cell D11 to increase the Year 1 Members value in cell C11 by the appropriate annual growth factor using a range name from the growth factor. Copy the formula from cell D11 through the range E11:611. 11. Enter the appropriate formula in cell D12 to increase the Year 1 Monthly Membership Fee value in cell C12 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula from cell D12 through the range E12:G12. 12. Enter the appropriate formula in cell D16 to increase the Year 1 Overhead value in cell C16 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula in cell D16 through the range E16:G16. 13. Enter the appropriate formula in cell 017 to increase the Year 1 Payroll value in cell C17 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula in cell D17 through the range E17:617. 14. Format cells C10:10 with a Heading 4 style. 15. Format cells C13:513 and C18:18 with a Currency(0) cell style and a 40% - Accents style. 16. Format cells C20:20 with a Currency(0) cell style and a 40% - Accent6 style. 17. At this point, the cash flow statement should look like the figure below: F G Atwood Country Club Five Year Cash Flow Projection Members Monthly Membership Fee Monthly Overhead Monthly Payroll Annual Initial Growth Factors: 500 10% $50 1% 15,000 10% 10,000 20% REVENUE: Members Monthly Membership Fee Annual Member Revenue: $ Year 1 500 50 300,000 $ Year 2 550 51 333,300 $ Year 3 605 51 370,296 $ Year 4 666 52 411,399 $ Year 5 732 52 457,064 EXPENSES: Overhead Payroll Total Expenses: $ 180,000 120,000 300,000 $ 198,000 144,000 342,000 $ 217,800 172,800 390,600 $ 239,580 207,360 446,940 $ 263,538 248,832 512,370 Net Profit Before Taxes: $ (8,700) $ (20,304) $ (35,541) $ (55,306) 18. Create three scenarios from this cash flow statement with the scenario names and Changing Cells values shown below: Scenario Name Changing Cells Initial Fast Growth No Growth Growth_Factor_Members 10% 20% 0% Growth_Factor_Fee 1% 20% 0% Growth_Factor_Overhead 10% 10% 20% Growth_Factor_Payroll 20% 20% 20% 19. Create the Scenario Summary report shown below on a separate worksheet within the workbook and change the worksheet name to Scenario Summary. Note that the Result Cells are Profit_Year_1 through Profit_Year_5. Also note the Current Values column has been deleted. 112 IX fx 2 A + Initial Fast Growth No Growth 2 3 5 6 7 8 9 Scenario Summary -87 Changing Cells: Growth_Factor_Members Growth_Factor_Fee Growth_Factor_Overhead Growth_Factor_Payroll Result Cells: Profit_Year_1 Profit_Year_2 Profit_Year_3 Profit_Year_4 Profit_Year 5 10% 1% 10% 20% 20% 20% 10% 20% 0% 0% 20% 20% - 10 11 12 13 14 (8,700) (20,304) (35,541) (55,306) 90,000 231,480 448,855 777,575 (60,000) (132,000) (218,400) (322,080) 15 16 17 18 19 20 21 22 Scenario Summary Sheet1 Sheetz Sheet3 20. Enter your name in the left section of the footer, and enter the current date in the right section using the Insert Date button in the footer of both the Sheet1 and Scenario Summary worksheets. 21. Switch the orientation of both worksheets to Landscape. 22. Save and close the workbook, and then exit Excel. Final Part 1 (Excel) Atwood Country Club 1. Open the file Final Part 1.xlsx attached to this assignment in the Final Exam Lessons folder. 2. Create the range names as seen below: Cell Address C5 C6 C7 C8 D5 D6 Range Name Initial_Members Initial_Fee Initial_Monthly Overhead Initial_Monthly Payroll Growth_Factor_Members Growth_Factor_Fee Growth Factor Overhead Growth Factor Payroll Profit_Year_1 Profit Year 2 Profit_Year_3 Profit_Year_4 Profit_Year_5 D7 D8 C20 D20 E20 F20 G20 3. In cell C11, enter the formula to reference cell C5 using a range name. 4. In cell C12, enter the formula to reference cell C6 using a range name. 5. In cell C13, enter the formula to multiply cell C11 by cell C12 by 12 months in a year. Copy the formula in cell C13 through the range D13:G13. 6. Enter the appropriate formula in cell C16 to multiply the initial monthly overhead value by 12 months in a year. Use a range name in the formula. 7. Enter the appropriate formula in cell C17 to multiply the initial monthly payroll value by 12 months in a year. Use a range name in the formula. 8. Enter the appropriate formula in cell C18 to add up the two expenses. Copy the formula from cell C18 through the range D18:18. 9. Enter the appropriate formula in cell C20 to calculate Net Profit Before Taxes, the difference between Annual Member Revenue in cell C13 and Total Expenses in cell C18. Copy the formula from cell C20 through the range D20:620. 10. Enter the appropriate formula in cell D11 to increase the Year 1 Members value in cell C11 by the appropriate annual growth factor using a range name from the growth factor. Copy the formula from cell D11 through the range E11:611. 11. Enter the appropriate formula in cell D12 to increase the Year 1 Monthly Membership Fee value in cell C12 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula from cell D12 through the range E12:G12. 12. Enter the appropriate formula in cell D16 to increase the Year 1 Overhead value in cell C16 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula in cell D16 through the range E16:G16. 13. Enter the appropriate formula in cell 017 to increase the Year 1 Payroll value in cell C17 by the appropriate annual growth factor using a range name for the growth factor. Copy the formula in cell D17 through the range E17:617. 14. Format cells C10:10 with a Heading 4 style. 15. Format cells C13:513 and C18:18 with a Currency(0) cell style and a 40% - Accents style. 16. Format cells C20:20 with a Currency(0) cell style and a 40% - Accent6 style. 17. At this point, the cash flow statement should look like the figure below: F G Atwood Country Club Five Year Cash Flow Projection Members Monthly Membership Fee Monthly Overhead Monthly Payroll Annual Initial Growth Factors: 500 10% $50 1% 15,000 10% 10,000 20% REVENUE: Members Monthly Membership Fee Annual Member Revenue: $ Year 1 500 50 300,000 $ Year 2 550 51 333,300 $ Year 3 605 51 370,296 $ Year 4 666 52 411,399 $ Year 5 732 52 457,064 EXPENSES: Overhead Payroll Total Expenses: $ 180,000 120,000 300,000 $ 198,000 144,000 342,000 $ 217,800 172,800 390,600 $ 239,580 207,360 446,940 $ 263,538 248,832 512,370 Net Profit Before Taxes: $ (8,700) $ (20,304) $ (35,541) $ (55,306) 18. Create three scenarios from this cash flow statement with the scenario names and Changing Cells values shown below: Scenario Name Changing Cells Initial Fast Growth No Growth Growth_Factor_Members 10% 20% 0% Growth_Factor_Fee 1% 20% 0% Growth_Factor_Overhead 10% 10% 20% Growth_Factor_Payroll 20% 20% 20% 19. Create the Scenario Summary report shown below on a separate worksheet within the workbook and change the worksheet name to Scenario Summary. Note that the Result Cells are Profit_Year_1 through Profit_Year_5. Also note the Current Values column has been deleted. 112 IX fx 2 A + Initial Fast Growth No Growth 2 3 5 6 7 8 9 Scenario Summary -87 Changing Cells: Growth_Factor_Members Growth_Factor_Fee Growth_Factor_Overhead Growth_Factor_Payroll Result Cells: Profit_Year_1 Profit_Year_2 Profit_Year_3 Profit_Year_4 Profit_Year 5 10% 1% 10% 20% 20% 20% 10% 20% 0% 0% 20% 20% - 10 11 12 13 14 (8,700) (20,304) (35,541) (55,306) 90,000 231,480 448,855 777,575 (60,000) (132,000) (218,400) (322,080) 15 16 17 18 19 20 21 22 Scenario Summary Sheet1 Sheetz Sheet3 20. Enter your name in the left section of the footer, and enter the current date in the right section using the Insert Date button in the footer of both the Sheet1 and Scenario Summary worksheets. 21. Switch the orientation of both worksheets to Landscape. 22. Save and close the workbook, and then exit Excel