New Perspectives Excel 2019 | Module 6: SAM Critical Thinking Project 1c Oval Lake Hospital MANAGING YOUR DATA WITH DATA TOOLS GETTING STARTED Open the file NP_EX19_CT6c_FirstLastName_1

Subject:MS ExcelPrice:19.99 Bought4

Share With

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

Oval Lake Hospital

MANAGING YOUR DATA WITH DATA TOOLS

GETTING STARTED

• Open the file NP_EX19_CT6c_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as NP_EX19_CT6c_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_CT6c_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. Donnie Cruz is a finance assistant for Oval Lake Hospital in south Florida. His current project is to format departmental data for the years 2021-2023 to make it easier to analyze.
   Switch to the Cancer Center worksheet. Unfreeze the top row of the worksheet.
2. Sort the table data first in ascending order by the Admission column and then in ascending order by the Service column.
3. Insert a total row in the table, and then calculate the totals for the 2021, 2022, and 2023 columns.
4. The administrator overseeing the cancer center is considering mandating that all inpatient service names be different from outpatient service names, and wants to see which duplicate names are currently in use.
   Apply a conditional formatting rule to service names that uses light red fill with dark red text for duplicate values. Don't include the total row since there's no value there.
5. Switch to the Cardiac Care worksheet. Freeze the first column of the worksheet.
6. The data on this worksheet is not formatted like the other worksheets.
   Format the headings and the data as an Excel table with headers using the same style as the table on the Cancer Center worksheet. Enter CardiacCare as the name of the table.
7. Donnie notices that the CardiacCare table is missing a record. Add the record shown in Table 1 to the bottom of the CardiacCare table.

8. The Maternity department has more admission criteria than the other major divisions of the hospital. Donnie wants to summarize maternity admissions data using subtotals to show the relationships between admission type and number of patients. Switch to the Maternity worksheet and complete the following steps:
   1. Sort the table by the Admission field in ascending order.
   2. Convert the table to a normal range.
   3. Insert subtotals at each change in the Admission column value. The subtotals should display total values and include subtotals for the 2021, 2022, and 2023 columns.
9. Switch to the Psychiatric worksheet and remove the duplicate record based on the values in the Admission and Service columns.
10. The data bars in the last three columns make some of the numbers hard to read. Replace the existing conditional formatting rule using solid green data bars.
11. Donnie wants to calculate total admissions for the hospital for each year. Switch to the All Departments worksheet, and then create the following formulas:
    1. Below the table, in the row labeled "Total Admissions", use a function and a structured reference to total the values in the 2021 column of the AllDepartments table.
    2. Use the same function to calculate the 2022 and 2023 totals in the same row.
12. Donnie wants to prepare the AllDepartments table for 2024 data when it becomes available.
    Add a table column to the end of the table. Be sure that the column shows the appropriate column header.

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.