New Perspectives Excel 2019 | Module 11: SAM Project 1a Nekeisha Champagne 1

Subject:MS ExcelPrice:20.01 Bought9

Share With

New Perspectives Excel 2019 | Module 11: SAM Project 1a

Nekeisha Champagne

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 the state of New York. 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. In the range J2:M7, Camille wants to summarize project information.

Start by calculating the number of each project type as follows:

a. In cell K3, enter a formula using the COUNTIF Create a formula using a function. Copy a formula into a range.

Create a formula using a function.

Copy a formula into a range.

2.  In column L, Camille wants to calculate the total cost of each project type. Determine the total costs as follows:

a. In cell L3, enter a formula using the SUMIF function that totals the cost for Expansion projects, checking that the column of project types in the Projects table ( Projects[Project Type] ) is equal to the value in cell J3 and that the formula totals all the final costs ( Projects[Final Cost]).

b. Fill the range L4:L7 with the formula in cell L3.

Create a formula using a function.

Copy a formula into a range.

3. In column M, Camille wants to calculate the average cost of each project type. Determine the average costs as follows:

a. In cell M3, enter a formula using the AVERAGEIF function that averages the cost for Expansion projects, checking that the column of project types in the Projects table ( Projects[Project Type] ) is equal to the value in cell J3 , and that the formula averages all the final costs ( Projects[Final Cost] ).

b. Fill the range M4:M7 with the formula in cell M3.

Create a formula using a function.

Copy a formula into a range.

4. Camille needs 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 as follows:

a. In cell K11, create a formula using the DCOUNT function to count the number of projects with final costs of more than $100,000, using the data in the entire Projects table ( Projects[#All] ) and counting the values in the column of final costs ( "Final Cost" ) that are equal to the values in the range J9:J10

.

b. In cell K15, create a formula using the DCOUNTA function to count the number of projects completed in 2019, using the data in the entire Projects table ( Projects[#All] ) and counting the values in the column of completion dates ( "Completed" ) that are equal to the values in the range J13:J14 In the Capital Projects worksheet, the formula in cell K15 should use the text ''Completed'' as the field argument.

Create a formula using a function.

Create a formula using a function.

5. Camille also needs 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 as follows:

a. In cell K19, create a formula using the DSUM function to calculate the total cost of the Newburgh campus projects, using the data in the entire Projects table ( Projects[#All] ) and totaling the values in the column of final costs ( "Final Cost" ) that are equal to the values in the range J17:J18

.

b. In cell K23, create a formula using the DAVERAGE function to calculate the average cost of the Hempstead campus projects, using the data in the entire Projects table ( Projects[#All] ) and averaging the values in the column of final costs ( "Final Cost" ) that are equal to the values in the range J21:J22

Create a formula using a function.

Create a formula using a function.

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.

Ungroup data in a PivotTable.

7. Camille thinks the PivotTable looks crowded in its default Compact layout. Change the report layout to show the PivotTable in Outline Form

.

Change the layout of a PivotTable.

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 to sort the data from 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:

a. Sort the data in ascending order by Grand Total.

b. Apply a Label Filter that displays Project ID values that begin with RE

.

Sort a table on multiple ranges.

Filter a PivotTable.

 

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 to include the number of projects for each project type in the second column of the PivotTable. Make this change for Camille as follows:

a. Add the ID field to the Values area of the PivotTable, making it the first field in the Values area.

b. Change the number format of the Count of ID amounts to Number with 0 decimal places.

Add values to a PivotTable.

Modify the number format of a PivotTable field.

 

10. Camille wants to know the difference and the percentage of difference between the final costs and the budgeted amounts. Provide this information for Camille as follows:

a. Insert a calculated field named Difference that subtracts the Budget field amount from the Final Cost field amount.

b. Insert another calculated field named % Difference that subtracts the Budget field amounts from the Final Cost field amounts, and then divides the result by the Budget field amount.

c. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points. In the Project Budgets worksheet, the % Difference field in the Values area of the PivotTable should be formatted using the Percentage number format with two decimal places.

Add a calculated field to a PivotTable.

Add a calculated field to a PivotTable.

Modify the number format of a PivotTable field.

11. Camille also wants to include the average budget for each project type as the last column in the PivotTable.

Add this information to the PivotTable as follows:

a. Add the Budget field to the Values area of the PivotTable, making it the last field in the Values area.

b. Use the Average calculation to summarize the Budget field data.

Add values to a PivotTable.

Summarize field values in a PivotTable.

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 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:

a. Expand the Project Type field to show the names of each project.

b. Apply a Value Filter to the Project Type field that displays the top 2 items by the sum of final cost.

Add a record to a table.

Filter a PivotTable.