NP_EX19_6a_FirstLastName_1

 NP_EX19_6a 

 NP_EX19_6a_FirstLastName_1 

  New Perspectives Excel 2019 | Module 6: SAM

  Project 1a

                    Oval Lake Hospital

MANAGING YOUR DATA WITH DATA TOOLS

GETTING STARTED

· Open the file NP_EX19_6a_FirstLastName_1.xlsx, available for download from the SAM website.

· Save the file as NP_EX19_6a_FirstLastName_2.xlsx by changing the “1” to a “2”.

o 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_6a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o 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 data in the CancerCenter table first in ascending order by the Admission field and then in ascending order by the Service field.

3. Insert a Total Row in the CancerCenter table, and then use the Total row to calculate the total of the values in the 2021, 2022, and 2023 fields. 

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 the range C4:C11 that uses Light Red Fill with Dark Red Text for duplicate values.

5. Switch to the

Cardiac Care

worksheet. Freeze the first column of the worksheet.

6. Format the range A3:F10 as an Excel table with headers using the Gold, Table Style Medium 5 table style. 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 end of the CardiacCare table.

Table 1: Data for the Range A11:F11

   
A
B
C
D
E
F
 
11
Cardiac Care

Inpatient

Other

2855

3540

3752

8. The Maternity department has more admission criteria than 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:

a. Sort the table by the Admission field in ascending order.

b. Convert the table to a normal range.

c. Insert subtotals into the range A3:F13, with the subtotals appearing at each change in the Admission column value. The subtotals should use the SUM function and include subtotals for the 2021, 2022, and 2023 fields.

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. Change the Data Bars conditional formatting for the range D4:F12 to use the Solid Fill Green Data Bar format.

11. Donnie wants to calculate total admissions for the hospital for each year. Switch to the All Departments worksheet, then create the following formulas:

a. In cell D40 use the SUM function with a structured reference to total the values in the 2021 column of the AllDepartments table.  

b. In cell E40 use the SUM function with a structured reference to total the values in the 2022 column of the AllDepartments table.  

c. In cell F40 use the SUM function with a structured reference to total the values in the 2023 column of the AllDepartments table.  

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.

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. 

Documentation

Cancer Center

Oval Lake Hospital

–

2021

2023

4

81
92

Cardiac Care

Oval Lake Hospital

Department
Admission
Service
2021
2022
2023

525

8

48

Maternity

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

298

Psychiatric

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

Psychiatric
Outpatient
Other
6,256
5,881
6,587

All Departments

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

Cancer Center
Inpatient
Diagnosis
2,252
1,914
1,550

Cancer Center
Inpatient
Hospice
81
92
107

Cancer Center
Inpatient
Other

704
866

Cancer Center
Inpatient
Surgery
7,894
6,631
6,565

Cancer Center
Outpatient
Consultation
1,288
1,649

Cancer Center
Outpatient
Imaging
3,923
3,335
2,935

Cancer Center
Outpatient
Other
841
891
1,149

Cancer Center
Outpatient
Surgery
653
627
558

Cardiac Care
Inpatient
Diagnosis
7,803
9,910
12,189

Cardiac Care
Inpatient
Hospice
44
51
48

Cardiac Care
Inpatient
Surgery
18,904
16,636
18,632

Cardiac Care
Outpatient
Consultation
3,190
3,350
4,121

Cardiac Care
Outpatient
Imaging
4,502
5,673
5,786

Cardiac Care
Outpatient
Other
1,788
1,842
2,247

Cardiac Care
Outpatient
Surgery
525
525

Maternity
Delivery
Natural
3,396
4,279
4,921

Maternity
Delivery
Surgical
5,084
5,033
5,234

Maternity
Inpatient
Non-maternity
1,322
1,408
1,296

Maternity
Inpatient
Other
226
287
339

Maternity
Inpatient
Pre-labor
556
428
496

Maternity
Inpatient
Procedure
1,252
1,189
1,213

Maternity
Outpatient
Consultation
5,208
5,781
6,128

Maternity
Outpatient
Imaging
7,795
8,964
7,440

Maternity
Outpatient
Other
2,253

2,873

Maternity
Outpatient
Surgery
285
259
298

Psychiatric
Inpatient
3-day hold
796
812
918

Psychiatric
Inpatient
Diagnosis
1,803
1,857

Psychiatric
Inpatient
Other
628
754
618

Psychiatric
Inpatient
Substance
884
972
902

Psychiatric
Inpatient
Surgery
51
44
44

Psychiatric
Outpatient
Imaging
523
450
518

Psychiatric
Outpatient
Labs
3,893
3,620
4,018

Psychiatric
Outpatient
Other
6,256
5,881
6,587

Psychiatric
Outpatient
Therapy
10,051
9,548
10,789

image1

Graded Summary Report

0.0-rc0000 1.

Donnie Cruz is a finance assistant for

in south Florida. His current project is to format departmental data for the years

–

to make it easier to analyze.
Switch to the

Cancer Center

worksheet. Unfreeze the top row of the worksheet.

field and then in ascending order by the

field.

8/8
Sort a table based on multiple ranges. 3. Insert a

Row in the CancerCenter table, and then use the Total row to calculate the total of the values in the 2021,

, and 2023 fields.

8/8
Insert a Total row in a table. Summarize table data using a Total row. 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 the range C4:C11 that uses Light Red Fill with Dark Red Text for duplicate values.

8/8
Create a Find Duplicates conditional formatting rule. Format a conditional formatting rule.

5.
Switch to the

Cardiac Care

worksheet. Freeze the first column of the worksheet.

8/8

8/8
Format a range as a table. Apply a table style. Set the name of a table. 7. Donnie notices that the CardiacCare table is missing a record. Add the record shown in Table 1 to the end of the CardiacCare table.

8/8
Add a record to a table. 8.

The

Maternity

department has more admission criteria than 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:
a. Sort the table by the Admission field in ascending order.
b. Convert the table to a normal range.
c. Insert subtotals into the range A3:F13, with the subtotals appearing at each change in the Admission column value. The subtotals should use the SUM function and include subtotals for the 2021, 2022, and 2023 fields.

8/8

Sort a table based on multiple ranges.

Switch to the

Psychiatric

worksheet and remove the duplicate record based on the values in the Admission and Service columns.

9/9

Format a conditional formatting rule.

Donnie wants to calculate total admissions for the hospital for each year. Switch to the

All

Department

s

worksheet, then create the following formulas:
a. In cell D40 use the SUM function with a structured reference to total the values in the 2021 column of the AllDepartments table. 
b. In cell E40 use the SUM function with a structured reference to total the values in the 2022 column of the AllDepartments table. 
c. In cell F40 use the SUM function with a structured reference to total the values in the 2023 column of the AllDepartments table. 

9/9

Create a formula using a function.

Create a formula using a function.

data when it becomes available.
Add a table column to the end of the table.

9/9
Add a column to a table.

Documentation

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

Jumah Saleh

Cancer Center

Oval Lake Hospital

Cardiac Care

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

525

8

48

Maternity

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

298

Psychiatric

Oval Lake Hospital

Patients Admitted 2021-2023

Department
Admission
Service
2021
2022
2023

All Departments

Oval Lake Hospital

Patients Admitted 2021-2023

Cancer Center
Inpatient
Diagnosis
2,252
1,914
1,550

Cancer Center
Inpatient
Hospice
81
92
107

Cancer Center
Inpatient
Other
592
704
866

Cancer Center
Inpatient
Surgery
7,894
6,631
6,565

Cancer Center
Outpatient
Consultation
1,288
1,649
1,814

Cancer Center
Outpatient
Imaging
3,923
3,335
2,935

Cancer Center
Outpatient
Other
841
891
1,149

Cancer Center
Outpatient
Surgery
653
627
558

Cardiac Care
Inpatient
Diagnosis
7,803
9,910
12,189

Cardiac Care
Inpatient
Hospice
44
51
48

Cardiac Care
Inpatient
Other
2,855
3,540
3,752

Cardiac Care
Inpatient
Surgery
18,904
16,636
18,632

Cardiac Care
Outpatient
Consultation
3,190
3,350
4,121

Cardiac Care
Outpatient
Imaging
4,502
5,673
5,786

Cardiac Care
Outpatient
Other
1,788
1,842
2,247

Cardiac Care
Outpatient
Surgery
525
525

Maternity
Delivery
Natural
3,396
4,279
4,921

Maternity
Delivery
Surgical
5,084
5,033
5,234

Maternity
Inpatient
Non-maternity
1,322
1,408
1,296

Maternity
Inpatient
Other
226
287
339

Maternity
Inpatient
Pre-labor
556
428
496

Maternity
Inpatient
Procedure
1,252
1,189
1,213

Maternity
Outpatient
Consultation
5,208
5,781
6,128

Maternity
Outpatient
Imaging
7,795
8,964
7,440

Maternity
Outpatient
Other
2,253

2,873

Maternity
Outpatient
Surgery
285
259
298

Psychiatric
Inpatient
3-day hold
796
812
918

Psychiatric
Inpatient
Diagnosis
1,803
1,857

Psychiatric
Inpatient
Other
628
754
618

Psychiatric
Inpatient
Substance
884
972
902

Psychiatric
Inpatient
Surgery
51
44
44

Psychiatric
Outpatient
Imaging
523
450
518

Psychiatric
Outpatient
Labs
3,893
3,620
4,018

Psychiatric
Outpatient
Other
6,256
5,881
6,587

Psychiatric
Outpatient
Therapy
10,051
9,548
10,789

image2

image3

image4

image1