support@essayby.com Chat Now Available 24/7
Log in
+1 (602) 704-8962 Chat Now
Log in
Order Now

Posted: March 12th, 2023

NP_EX19_6a_FirstLastName_1

Instructions_NP_EX19_6a xNP_EX19_6a_Smith_1.xlsxNP_EX19_6a_JumahSaleh_Report_1.xlsx

 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

New Perspectives Excel 2019 | Module 6: SAM Project 1a
Oval Lake Hospital
MANAGING YOUR DATA WITH DATA TOOLS
Author: Jumah Saleh
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.

Cancer Center

Oval Lake Hospital - 2021 2023 Cancer Center Cancer Center Cancer Center Inpatient Other Cancer Center Outpatient Cancer Center Outpatient Cancer Center Inpatient Surgery Cancer Center Outpatient 4 Cancer Center Inpatient 81 92
Patients Admitted 20212023
Department Admission Service2022
Inpatient Diagnosis 2,252 1,914 1,550
Outpatient Other 841 891 1,149
592 704 866
Imaging 3,923 3,335 2,935
Surgery 653 627 558
7,894 6,631 6,565
Consultation 1,288 1,649 1,81
Hospice107

Cardiac Care

Oval Lake Hospital Department Admission Service 2021 2022 2023 Cardiac Care Inpatient Surgery Cardiac Care Outpatient Consultation Cardiac Care Inpatient Diagnosis Cardiac Care Outpatient Other Cardiac Care Outpatient Imaging Cardiac Care Outpatient Surgery 525 8 Cardiac Care Inpatient Hospice 48
Patients Admitted 2021-2023
18,904 16,636 18,632
3,190 3,350 4,121
7,803 9,910 12,189
1,788 1,842 2,247
4,502 5,673 5,786
52548
44 51

Maternity

Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 Maternity Maternity Inpatient Maternity Inpatient Other Maternity Outpatient Imaging Maternity Outpatient Consultation Maternity Outpatient Other Maternity Inpatient Maternity Outpatient Surgery 298 Maternity Delivery Maternity Inpatient
Delivery Natural 3,396 4,279 4,921
Non-maternity 1,322 1,408 1,296
226 287 339
7,795 8,964 7,440
5,208 5,781 6,128
2,253 2,298 2,873
Procedure 1,252 1,189 1,213
285 259
Surgical 5,084 5,033 5,234
Pre-labor 556 428 496

Psychiatric

Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 Psychiatric Outpatient Other Psychiatric Inpatient Diagnosis Psychiatric Inpatient Other Psychiatric Inpatient Psychiatric Outpatient Psychiatric Outpatient Other 6,256 5,881 6,587 Psychiatric Outpatient Psychiatric Inpatient Surgery 51 44 44 Psychiatric Outpatient Imaging 523 Psychiatric Inpatient
6,256 5,881 6,587
1,803 1,857 1,523
628 754 618
Substance 884 972 902
Labs 3,893 3,620 4,018
Therapy 10,051 9,548 10,789
450 518
3-day hold 796 812 918

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 Other 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
5921,814
2,855 3,540 3,752
4882,2981,523
Total Admissions

image1

Graded Summary Report

New Perspectives Excel 2019 | Module 6: SAM Project 1a Jumah Saleh SUBMISSION #1 | SCORE IS: 100 OUT OF 100 GE ver. 15.0.0-rc0000 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. 8/8 Unfreeze a row. 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. 8/8 Sort a table based on multiple ranges. 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. 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 Freeze a column. 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. 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. Convert a table to a range. Insert a subtotal row in a range. Set options for subtotaling data. 9. Switch to the

Psychiatric

worksheet and remove the duplicate record based on the values in the Admission and Service columns. 9/9 Delete a record from a table. 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. 9/9 Format a conditional formatting rule. 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.  9/9 Create a formula using a function. Create a formula using a function. Create a formula using a function. 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. 9/9 Add a column to a table.

Documentation

New Perspectives Excel 2019 | Module 6: SAM Project 1a Oval Lake Hospital Jumah Saleh
MANAGING YOUR DATA WITH DATA TOOLS
Author:
Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website.
Cancer Center Oval Lake Hospital Department Admission Service 2021 2022 2023 Cancer Center Cancer Center Inpatient Cancer Center Inpatient Cancer Center Inpatient Cancer Center Cancer Center Outpatient Cancer Center Outpatient Other Cancer Center Outpatient Surgery Total
Patients Admitted 2021-2023
Inpatient Diagnosis 2,252 1,914 1,550
Hospice 81 92 107
Other 592 704 866
Surgery 7,894 6,631 6,565
Outpatient Consultation 1,288 1,649 1,814
Imaging 3,923 3,335 2,935
841 891 1,149
653 627 558
17,524 15,843 15,544
Cardiac Care Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 Cardiac Care Inpatient Surgery Cardiac Care Outpatient Consultation Cardiac Care Inpatient Diagnosis Cardiac Care Outpatient Other Cardiac Care Outpatient Imaging Cardiac Care Outpatient Surgery 525 8 Cardiac Care Inpatient Hospice 44 48 Cardiac Care Inpatient Other
18,904 16,636 18,632
3,190 3,350 4,121
7,803 9,910 12,189
1,788 1,842 2,247
4,502 5,673 5,786
52548
51
2,855 3,540 3,752
Maternity Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 Maternity Maternity Delivery Maternity Inpatient Maternity Inpatient Other Maternity Inpatient Maternity Inpatient Maternity Outpatient Imaging Maternity Outpatient Consultation Maternity Outpatient Other Maternity Outpatient Surgery 298
Delivery Natural 3,396 4,279 4,921
Surgical 5,084 5,033 5,234
Delivery Total 8,480 9,312 10,155
Non-maternity 1,322 1,408 1,296
226 287 339
Procedure 1,252 1,189 1,213
Pre-labor 556 428 496
Inpatient Total 3,356 3,312 3,344
7,795 8,964 7,440
5,208 5,781 6,128
2,253 2,298 2,873
285 259
Outpatient Total 15,541 17,302 16,739
Grand Total 27,377 29,926 30,238
Psychiatric Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 Psychiatric Outpatient Other Psychiatric Inpatient Diagnosis Psychiatric Inpatient Other Psychiatric Inpatient Psychiatric Outpatient Psychiatric Outpatient Psychiatric Inpatient Surgery 51 44 44 Psychiatric Outpatient Imaging 523 Psychiatric Inpatient
6,256 5,881 6,587
1,803 1,857 1,523
628 754 618
Substance 884 972 902
Labs 3,893 3,620 4,018
Therapy 10,051 9,548 10,789
450 518
3-day hold 796 812 918
All Departments Oval Lake Hospital Patients Admitted 2021-2023 Department Admission Service 2021 2022 2023 2024 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
4882,2981,523
Total Admissions 109,397 111,234 118,962

image2

image3

image4

image1

Expert paper writers are just a few clicks away

Place an order in 3 easy steps. Takes less than 5 mins.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00