Homework answers / question archive / Final Part 1 (Excel) – Atwood Country Club 1

Final Part 1 (Excel) – Atwood Country Club 1

Computer Science

Share With

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 D7 D8 C20 D20 E20 F20 G20 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 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:G18. 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:G20. 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:G11. 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 D17 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:G17. 14. Format cells C10:G10 with a Heading 4 style. 15. Format cells C13:G13 and C18:G18 with a Currency(0) cell style and a 40% - Accent5 style. 16. Format cells C20:G20 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: 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 Growth_Factor_Fee Growth_Factor_Overhead Growth_Factor_Payroll 10% 1% 10% 20% 20% 20% 10% 20% 0% 0% 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. 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 2 (Excel) – Combatting Childhood Poverty Fund 1. Open the file Final Part 2.xlsx attached to this assignment in the Final Exam Lessons folder. 2. Delete row 5 to create a well-formed list. 3. Format the Pledge values in column E with a Currency(0) cell style. 4. Create a custom sort to sort the document in ascending order on the Zip Code, Last Name, and First Name. 5. Enter your name in the left section of the footer, and then enter the current date using the Insert Date button in the right section of the footer. 6. Create a PivotTable and PivotChart in a new worksheet like the figure shown below. Rename the worksheet name as “PivotTable & PivotChart”. Note that the PivotTable does not have a field in the Column Labels position, and that the data is filtered for only those zip codes that start with 77. Also note that the donation amounts in the Sum of Pledge column are formatted with a Currency(0) cell style. 7. Enter your name in the left section of the footer, and then enter the current date using the Insert Date button in the right section of the footer. 8. Save and close the workbook, and then exit Excel. Final Part 3 (Access) – Employee Evaluation Form 1. Open the file Final Part 3.accdb attached to this assignment in the Final Exam Lessons folder. 2. Write a query that will retrieve the ID, ScholarshipName, DueDate, and Major from the Scholarships table where they have the word “Scholarship” (hint…you’ll have to use the special character) in some capacity in the Scholarship Name and the Major is Business. Sort the query by DueDate in ascending order. Save the query as Business Scholarships. 3. Create new form Scholarships in 2014 using the Form Wizard to include all columns from the Scholarship table. The form will be done in columnar format and will have a title of Scholarships in 2014. Change the Theme of the form to Ion Boardroom. 4. Add a date control without the time to the top of your form and italicize the date. 5. Create new report Scholarships by Major using the Report Wizard. This report should be done in Stepped layout, and it will be grouped on Major and then sorted by the DueDate. The report will contain the Major, ScholarshipName, DueDate, and Amount. The report should be changed to Landscape orientation. Make sure you set the report to fit all fields on the page. Change the Theme of the report to Ion Boardroom (see just a sample of report below). 6. Save and close the database, and exit Access.