New Perspectives Excel 2019 | Modules 1-4: SAM Critical Thinking Capstone Project 1c Tallridge Regional Medical Center

GETTING STARTED

1. Open the file NP_EX19_CT_CS1-4c_FirstLastName_1.xlsx, available for download from the SAM website.

2. Save the file as NP_EX19_CT_CS1-4c_FirstLastName_2.xlsx by changing the “1” to a “2”.

a. 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.

3. With the file NP_EX19_CT_CS1-4c_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

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

PROJECT STEPS

1. Carmelo Miraglia is an executive consultant at Tallridge Regional Medical Center. Carmelo is building a workbook that summarizes income and expenses for the organization. Change the theme of the workbook to Office.

2. Switch to the Departments worksheet, which contains a header and several tables that need to be completed and formatted. Columns C through H could use a bit more room. Change the width of columns C through H to 12.00 characters.

3. Change the height of the first row to 21 points.

4. Merge and center the contents of the second row to match the merged range in the first row.

5. Format the newly merged range using the 40% - Accent 5 cell style, Bold formatting, and a 14 point font size.

6. The first few headings of the Staff table need to be entered. Enter the data shown in Table 1 in the appropriate cells.

Table 1: Data for the Range A5:E5

| A | B | C | D | E 5 | Abbreviation | Department | MD | RN | Support

7. Format the headings in the Staff table as follows:

a. Center the headers and change the font size to 11 piont.

b. Change the background color to one shade darker than the subtitle in the second row, and then wrap the text.

8. From the CCU abbreviation to the end of the table (including the total row), add a White, Background 1 border to all sides of each cell. Be sure to include the total row.

9. Add a thin top border to the total row (including the abbreviation column) using the Automatic color.

10. Format the last three columns of the staff table data (not including the total row).

a. Display the turnover percentage data as a percentage with no decimal places.

b. Use a conditional formatting rule to highlight departments with a turnover rate greater than 24 percent using the default formatting.

11. Use a conditional formatting rule to add solid orange data bars to the data for the 2021 budget. Don't include the total row.

12. Finish by adding conditional formatting rules to the change from 2020 data that highlights the top and bottom 10% of values. Show the top 10% of values as green fill with dark green text and the bottom 10% of values as light red fill with dark red text.

13. The Department summary table should look up the department abbreviation and return all of the data listed. It needs to be filled out. To begin, next to the cell listed "Department", use a function to search the staff table data (not including the row of headings and the total row) and return the department name based on the abbreviation above. The formula will be copied, so make sure it always references the abbreviation.

14. Copy the formula down to complete the Department summary table, and edit the copied formulas to return the values from the headings listed in the left-hand column.

15. In the appropriate cell below the staff table, use a function to display the current date without displaying the time.

16. The averages will not be needed, so delete column O.

17. The date for the next update isn't certain, so hide row 18.

18. Switch to the Budget worksheet, which contains the department budgets from 2018 to 2022. There's already a pie chart showing 2018 budget information, and Carmelo wants another one to compare. Create a 2-D pie chart based on the data in the Department and 2022 columns. Move the chart so that it covers the range H4:O22. Use the third chart style, and use 2022 Budget by Department as the chart title.

19. The pie chart for 2018 now needs to be changed. Format this chart the same way as the 2022 chart.

20. The Departmental Budget Comparison 2018-2022 combo chart needs to be updated as well.

a. Change the minimum bounds of the right vertical axis to 40,000,000, and then add axis titles to the chart.

b. Use Department Budgets as the left vertical title, use Total Budget as the right vertical title, and remove the horizontal axis title.

c. Apply a shape fill to the chart using the Blue, Accent 5, Lighter 80% fill color.

21. The Hiring worksheet won't be needed, so delete it from the workbook.

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.

Final Figure 1: Departments Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2018 Cengage Learning. All Rights Reserved.

Final Figure 2: Budget Worksheet

Microsoft product screenshot reprinted with permission from Microsoft Incorporated. Copyright © 2018 Cengage Learning. All Rights Reserved.