Homework answers / question archive / New Perspectives Excel 2019 | Module 11: SAM Critical Thinking Project 1c Hempstead University Foundation CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS GETTING STARTED Open the file NP_EX19_CT11c_FirstLastName_1

New Perspectives Excel 2019 | Module 11: SAM Critical Thinking Project 1c Hempstead University Foundation CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS GETTING STARTED Open the file NP_EX19_CT11c_FirstLastName_1

MS Excel

Share With

New Perspectives Excel 2019 | Module 11: SAM Critical Thinking Project 1c

Hempstead University Foundation

CREATE ADVANCED PIVOTTABLES AND USE DATABASE FUNCTIONS

GETTING STARTED

• Open the file NP_EX19_CT11c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT11c_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file NP_EX19_CT11c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

• PROJECT STEPS

1. Camille Darbonne is the director of the Office of Capital Projects for the Hempstead University Foundation. She uses an Excel workbook to track building projects on three Hempstead  University campuses in New York State. She asks for your help in summarizing project data as she prepares for a presentation to the foundation's board. To do so, you will use database functions and advanced PivotTable features.
   Go to the Capital Projects worksheet, which contains a table named Projects listing details about the university's building projects. To its right, Camille wants you to summarize project information.
   Start by calculating the number of each project type.
   1. Use a function to count the number of expansion projects in the Project Type column of the Projects table. Refer to the Project Type listed in column J.
   2. Use the same function to count the remaining project types in the rows beneath the number of expansion projects.
2. In column L, Camille wants you to calculate the total cost of each project type.
   1. Use a function to calculate the total cost for expansion projects in the Project Type column of the Projects table. Refer to the Project Type listed in column J.
   2. Use the same function to return the total cost of the remaining project types in the rows beneath the total cost for expansion projects.
3. In column M, Camille wants you to calculate the average cost of each project type.
   1. Use a function to calculate the average cost for expansion projects in the Project Type column of the Projects table. Refer to the Project Type listed in column J.
   2. Use the same function to return the average cost of the remaining project types in the rows beneath the average cost for expansion projects.
4. Camille needs you to identify the number of projects that have a final cost of more than $100,000 and those that were completed in 2019.
   Create formulas that provide this information using database functions.
   1. In the next section down, next to the cell labeled "Number of Projects:", use a function to count the number of projects with final costs totaling more than $100,000, using the data in the entire Projects table and counting the values in the column of final costs that are equal to the values in the range J9:J10.
   2. Next, use a function to count the number of projects completed in 2019, using the data in the entire Projects table and counting the values in the column of completion dates that are equal to the values in the range J13:J14.
5. Camille also needs you to calculate the total cost of the building projects on the Newburgh campus and the average cost of the projects on the Hempstead campus.
   Create formulas that provide this information using database functions.
   1. In the next section down, use a function to calculate the total cost of the Newburgh campus projects, using the data in the entire Projects table and totaling the values in the column of final costs that are equal to the values in the range J17:J18.
   2. Next, use a function to calculate the average cost of the Hempstead campus projects, using the data in the entire Projects table and averaging the values in the column of final costs that are equal to the values in the range J21:J22.
6. Go to the Cost by Green Feature worksheet. Camille has created a PivotTable on this worksheet to list the final costs of the projects by green feature, years, and campus. She grouped the year data into two-year spans but wants them listed as separate years.
   Ungroup the year data in the PivotTable.
7. Camille thinks the PivotTable looks crowded in the default Compact layout.
   Change the report layout to show the PivotTable in Outline Form.
8. Go to the Cost by Campus worksheet. Camille created a PivotTable that lists each project by ID and campus, and then displays its final cost. The data is sorted in alphabetic order by project ID, but Camille wants you to sort the data from the smallest to largest total amount. She also wants to focus on renovation projects only, which have an ID that begins with "RE".
   Change the display of the PivotTable as follows:
   1. Sort the data in ascending order by the Grand Total values.
   2. Apply a label filter that displays Project ID values that begin with RE.
9. Go to the Project Budgets worksheet. The PivotTable on this worksheet lists the final cost totals and the budgeted amounts by project type. Camille wants you to include the number of projects for each project type in the second column of the PivotTable.
   Make this change for Camille as follows:
   1. Add the ID field to the Values area of the PivotTable, making it the first field in the Values area.
   2. Change the number format of the Count of ID values to Number format with zero decimal places.
10. Camille wants to know the monetary difference and the percentage of difference between the final costs and the budgeted amounts.
    Provide this information for Camille as follows:
    1. Insert a calculated field named Difference that subtracts the Budget field amount from the Final Cost field amount.
    2. Insert another calculated field named % Difference that subtracts the Budget field amount from the Final Cost field amount and then divides the result by the Budget field amount.
    3. Change the number format of the Sum of % Difference amounts to Percentage format with two decimal points.
11. Camille also wants you to include the average budget for each project type as the last column in the PivotTable.
    Add this information to the PivotTable as follows:
    1. Add the Budget field to the Values area of the PivotTable, making it the last field in the Values area.
    2. Use the Average calculation to summarize the Budget field data.
12. Go to the Project Types by Year worksheet. The PivotTable on this worksheet lists the final costs of the building projects by year and type. Camille wants you to show the names of each project and display the information about the two project types with the highest total final cost.
    Provide this information for Camille as follows:
    1. Expand the Project Type field to show the name of each project.
    2. Apply a Value Filter to the Project Type field that displays the top two items by the sum of final cost.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.