New Perspectives Excel 365 | Module 11: SAM Project A Lepage Medical Center

GETTING STARTED

1. Save the file NP_EX365_11A_FirstLastName_1.xlsx as NP_EX365_11A_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

2. With the file NP_EX365_11A_FirstLastName_2.xlsx open, ensure that your first and last name is displayed in cell B6 of the Documentation worksheet.

a. If cell B6 does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Brenda Espinoza is the director of development for Lepage Medical Center in Kansas City, Missouri. She uses an Excel workbook to track development campaigns for the hospital and asks for your help in summarizing campaign data. To do so, you will use database functions and advanced PivotTable features. Go to the Campaigns worksheet, which contains a table named Campaigns listing details about the hospital's development campaigns. In column K, Brenda asks you to enter formulas for analyzing campaign data. Start by counting the number of campaigns that raised more than $150,000 as follows:

a. In cell K5, create a formula using the DCOUNT function.

b. Use the data in the entire Campaigns table (Campaigns[#All]).

c. Count the values in the Total Raised column ("Total Raised") that are equal to the values in the range J3:J4.

2. Based on the formula in cell K5, count the number of campaigns completed in 2029 as follows:

a. In cell K9, create a formula using the DCOUNTA function.

b. Use the data in the entire Campaigns table.

c. Count the values in the Completed column that are equal to the values in the range J7:J8.

3. Calculate the total amount raised for the Orthopedics Department as follows:

a. In cell K13, create a formula using the DSUM function.

b. Use the data in the entire Campaigns table.

c. Total the values in the Total Raised column for departments equal to the values in the range J11:J12.

4. Calculate the average amount raised for the Orthopedics Department as follows:

a. In cell K17, create a formula using the DAVERAGE function.

b. Use the data in the entire Campaigns table.

c. Average the values in the Total Raised column for departments equal to the values in the range J15:J16.

5. Determine the maximum amount raised for annual giving campaigns as follows:

a. In cell K21, create a formula using the DMAX function.

b. Use the data in the entire Campaigns table.

c. Determine the maximum of the values in the Total Raised column for campaign types equal to the values in the range J19:J20.

6. Determine the minimum amount raised for major gift campaigns as follows:

a. In cell K25, create a formula using the DMIN function.

b. Use the data in the entire Campaigns table.

c. Determine the minimum of the values in the Total Raised column for campaign types equal to the values in the range J23:J24.

7. Go to the Departments worksheet. Brenda has created a PivotTable named Departments on this worksheet to list the total raised by hospital department, beneficiary, and year. She grouped the year data into two-year spans, but asks you to list them as separate years. Ungroup the year data in the PivotTable.

8. Brenda asks you to make the PivotTable look less crowded. She also wants you to draw attention to grand totals of $500,000 or more and those less than $100,000. Adjust the PivotTable as follows:

a. Change the report layout to show the PivotTable in Outline Form.

b. Turn off the display of the subtotals.

c. For the grand totals in the range H6:H24, apply the 3 Triangles icon set.

d. Edit the icon set rule to display a green triangle when the value is greater than or equal to 500,000 using the Number type.

e. Display a yellow rectangle when the value is greater than or equal to 100,000 using the Number type.

f. Resize column H to its best fit to display all the data in the column.

9. Return to the Campaigns worksheet to complete another type of campaign analysis. Include the total amount raised for each department as follows:

a. In cell L28, insert a formula using the GETPIVOTDATA function to display the grand total for the ER department in cell C25 on the Departments worksheet.

b. In cell L29, insert a formula using the GETPIVOTDATA function to display the grand total for the ICU department in cell D25 on the Departments worksheet.

c. In cell L30, insert a formula using the GETPIVOTDATA function to display the grand total for the ONC department in cell E25 on the Departments worksheet.

d. In cell L31, insert a formula using the GETPIVOTDATA function to display the grand total for the OR department in cell F25 on the Departments worksheet.

e. In cell L32, insert a formula using the GETPIVOTDATA function to display the grand total for the ORTHO department in cell G25 on the Departments worksheet.

10. Go to the Campaign Totals worksheet. Brenda created a PivotTable named Totals that displays the total raised by campaign ID and department. The data is sorted in alphabetic order by campaign ID, but Brenda wants you to sort the data from smallest to largest total amount. She also wants to focus on events only, which have an ID that begins with "EV". 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 Campaign ID values that begin with EV.

11. Go to the Campaign Goals worksheet, which contains a PivotTable named Goals that lists the total amount raised and the goal for each campaign type. Brenda wants you to include the number of campaigns for each campaign type in the second column of the PivotTable. Make this change for Brenda 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.

12. Brenda asks you to include the difference and the percentage of difference between the total raised and the goal for each campaign type in the Goals PivotTable. Provide this information for Brenda as follows:

a. Insert a calculated field named Difference that subtracts the Goal field amount from the Total Raised field amount.

b. Use +/- Difference as the custom name for the Sum of Difference field.

c. Insert another calculated field named % Difference that subtracts the Goal field amounts from the Total Raised field amounts, and then divides the result by the Goal field amount.

d. Use Percent Difference as the custom name for the Sum of % Difference field.

e. Change the number format of the Percent Difference amounts to Percentage with 2 decimal points.

13. Brenda also wants you to include the average goal for each campaign type as the last column in the PivotTable. Add this information to the PivotTable as follows:

a. Add the Goal 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 Goal field data.

14. Go to the Campaign Types by Year worksheet, which includes a PivotTable named Years that lists the totals raised by campaign type and year. Brenda wants you to show the names of each campaign and display the information about the two campaign types with the highest total raised. Provide this information for Brenda as follows:

a. Expand the Campaign Type field to show the names of each campaign.

b. Apply a Top 10 Filter to the Campaign Type field that displays the top 2 items by Final Totals.

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 website to submit your completed project.

Final Figure 1: Campaigns Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Departments Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 3: Campaign Totals Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 4: Campaign Goals Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 5: Campaign Types by Year Worksheet

Windows, Access, Excel, Word, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.