Excel Chart Assignment Pivot Charts and Tables

User Generated

Nyon_2017

Business Finance

Description

I have an Excel assignment due my the end of the day. I have attached the assignment from my book in order labeled Hospital_1, Hospital_2, and so forth. There is also an Excel data chart that is needed for the assignment that is titled" Patients" that I have attached as well. Here are the instructions...

Complete the following Lesson 7 assignment with the modifications listed.

  1. Complete the Chapter 5 Practice Exercise 2: "Suburbia Regional Hospital" on pages 386-389. Complete Steps A-M.
    • Step M: Add your name, MEID, and course section number in the footer.
    • Save your file as "Hospital_MEID.xlsx." Note: Where indicated, replace "MEID" with your MEID number.
  2. Place the following Excel file in a folder labeled "Lesson7_File."
    • Hospital_MEID.xlsx

Unformatted Attachment Preview

Weekday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Day 4/1/2018 4/1/2018 4/2/2018 4/2/2018 4/3/2018 4/3/2018 4/4/2018 4/4/2018 4/5/2018 4/5/2018 4/6/2018 4/6/2018 4/7/2018 4/7/2018 4/8/2018 4/8/2018 4/9/2018 4/9/2018 4/10/2018 4/10/2018 4/11/2018 4/11/2018 4/12/2018 4/12/2018 4/13/2018 4/13/2018 4/14/2018 4/14/2018 4/15/2018 4/15/2018 4/16/2018 4/16/2018 4/17/2018 4/17/2018 4/18/2018 4/18/2018 4/19/2018 4/19/2018 4/20/2018 4/20/2018 4/21/2018 4/21/2018 4/22/2018 4/22/2018 Shift Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave PatientStart 24 22 25 31 32 29 32 34 33 28 33 26 27 27 27 29 30 29 30 31 31 30 31 31 31 33 34 33 32 34 34 37 38 30 29 28 29 31 32 31 30 33 33 35 PatientAdmit 2 3 11 2 5 3 7 0 10 5 7 1 3 0 3 1 9 1 4 0 3 1 4 0 6 2 2 0 2 0 10 2 4 0 8 1 11 2 10 1 4 0 2 1 PatientDischarge 4 0 5 1 8 0 5 1 15 0 14 0 3 0 1 0 10 0 3 0 4 0 4 0 4 1 3 1 0 0 7 1 12 1 9 0 9 1 11 2 1 0 0 1 PatientEnd 22 25 31 32 29 32 34 33 28 33 26 27 27 27 29 30 29 30 31 31 30 31 31 31 33 34 33 32 34 34 37 38 30 29 28 29 31 32 31 30 33 33 35 35 Nurses 6 6 8 7 8 7 8 7 7 6 8 6 7 6 6 6 8 7 8 7 8 7 7 6 8 6 7 6 6 6 8 8 6 6 7 7 8 8 8 7 8 8 7 6 Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday 4/23/2018 4/23/2018 4/24/2018 4/24/2018 4/25/2018 4/25/2018 4/26/2018 4/26/2018 4/27/2018 4/27/2018 4/28/2018 4/28/2018 4/29/2018 4/29/2018 4/30/2018 4/30/2018 Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave 35 31 34 32 33 31 35 34 35 32 33 34 34 35 35 29 14 3 4 2 9 4 5 1 13 2 3 0 1 0 12 3 18 0 6 1 11 0 6 0 16 1 2 0 0 0 18 1 31 34 32 33 31 35 34 35 32 33 34 34 35 35 29 31 6 7 8 7 7 8 8 8 6 6 6 8 8 8 6 7 Weekday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday Day 4/1/2018 4/1/2018 4/2/2018 4/2/2018 4/3/2018 4/3/2018 4/4/2018 4/4/2018 4/5/2018 4/5/2018 4/6/2018 4/6/2018 4/7/2018 4/7/2018 4/8/2018 4/8/2018 4/9/2018 4/9/2018 4/10/2018 4/10/2018 4/11/2018 4/11/2018 4/12/2018 4/12/2018 4/13/2018 4/13/2018 4/14/2018 4/14/2018 4/15/2018 4/15/2018 4/16/2018 4/16/2018 4/17/2018 4/17/2018 4/18/2018 4/18/2018 4/19/2018 4/19/2018 4/20/2018 4/20/2018 4/21/2018 4/21/2018 4/22/2018 4/22/2018 4/23/2018 4/23/2018 Shift Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Patient-Start Patient-Admit 24 22 25 31 32 29 32 34 33 28 33 26 27 27 27 29 30 29 30 31 31 30 31 31 31 33 34 33 32 34 34 37 38 30 29 28 29 31 32 31 30 33 33 35 35 31 Patient-Discharge 2 3 11 2 5 3 7 0 10 5 7 1 3 0 3 1 9 1 4 0 3 1 4 0 6 2 2 0 2 0 10 2 4 0 8 1 11 2 10 1 4 0 2 1 14 3 4 0 5 1 8 0 5 1 15 0 14 0 3 0 1 0 10 0 3 0 4 0 4 0 4 1 3 1 0 0 7 1 12 1 9 0 9 1 11 2 1 0 0 1 18 0 Tuesday Tuesday Wednesday Wednesday Thursday Thursday Friday Friday Saturday Saturday Sunday Sunday Monday Monday 4/24/2018 4/24/2018 4/25/2018 4/25/2018 4/26/2018 4/26/2018 4/27/2018 4/27/2018 4/28/2018 4/28/2018 4/29/2018 4/29/2018 4/30/2018 4/30/2018 Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave Day Grave 34 32 33 31 35 34 35 32 33 34 34 35 35 29 4 2 9 4 5 1 13 2 3 0 1 0 12 3 6 1 11 0 6 0 16 1 2 0 0 0 18 1 Patient-End Nurses 22 25 31 32 29 32 34 33 28 33 26 27 27 27 29 30 29 30 31 31 30 31 31 31 33 34 33 32 34 34 37 38 30 29 28 29 31 32 31 30 33 33 35 35 31 34 6 6 8 7 8 7 8 7 7 6 8 6 7 6 6 6 8 7 8 7 8 7 7 6 8 6 7 6 6 6 8 8 6 6 7 7 8 8 8 7 8 8 7 6 6 7 32 33 31 35 34 35 32 33 34 34 35 35 29 31 8 7 7 8 8 8 6 6 6 8 8 8 6 7 Alesha Rogers is the Nurse Manager for the Neuro Acute Care Division at Suburbia Regional Hospital. The 12-hour nursing shifts are divided into day and graveyard. Alesha collects data on the number of patients that start each shift, the number of patients admitted, the number of patients discharged, and the number of patients at the end of each shift. She also notes how many nurses worked each shift. Because of your expertise in using Excel to analyze data, she has pro- vided you a basic spreadsheet and asked you to help consolidate the data for her to analyze. She is interested in the number of nurses per daily shift (such as all Sunday graveyard shifts) for the month of April. Refer to Figure 5.47 as you complete this exercise. month of April. Refer to Fig PivotTable Fields B Choose fields to add to report: 1 H G Search Patient Erd Nurses 22 6 6 Average of Patient-Start Average of Patient-End 30.5 31.4 31.6 32.2 32 30.875 31 31.375 31.5 31.875 31.625 30.75 31.375 31.625 31.35 31.46666667 25 27 0 0 0 0 0 2 3 Row Labels 4 Sunday 5 Monday 6 Tuesday 7 Wednesday 8 Thursday 9 Friday 10 Saturday 11 Grand Total 12 13 Weekday Day Shift Patient-Start Patient-Admit Patient-Discharge Patient-End Nurses Start Patients per Nurse 28 30 30 32 6 7 32 6 33 8 Average Number of Patients by Weekday 4 4 MORE TABLES... 33 8 6.5 15 16 29.2 39 Saturday Drag fields between areas below 8 7 40 7 Y FILTERS I COLUMNS 18 Shift 1 Wack 2 Sunday 4/1/2018 Day 3 Sunday 4/1/2018 Grave 4 Sunday 4/8/2018 Day 5 Sunday 4/8/2018 Grave 6 Sunday 4/15/2018 Day 7 Sunday 4/15/2018 Grave 8 Sunday 4/22/2018 Day 9 Sunday 4/22/2018 Grave 10 Sunday 4/29/2018 Day 11 Sunday 4/29/2018 Grave 12 Sunday Average 13 Monday 4/2/2018 Day 14 Monday 4/2/2018 Grave 15 Monday 4/9/2018 Day 16 Monday 4/9/2018 Grave 17 Monday 4/16/2018 Day 18 Monday 4/16/2018 Grave 19 Monday 4/23/2018 Day 20 Monday 4/23/2018 Grave 21 Monday 4/30/2018 Day 22 Monday 4/30/2018 Grave 23 Monday Average 24 Tuesday 4/3/2018 Day 25 Tuesday 4/3/2018 Grave 26 Tuesday 4/10/2018 Day April Data PivotTable (2) 39 8 Thursday 2 Values 40 7 Average of Patient-End 8 20 Wednesday 21 Tuesday 22 43 44 40 Average of Patient-Start 8 2016. Windows 10. Microsoft Corporation 6 23 Monday ROWS VALUES 6 24 25 Sunday Weekday 43 37 39 40.4 36 7 Average of Pa. Average of P... 29.5 30 30.5 32 32.5 7.2 B 26 27 28 39 7 29 . 8 1 Pivo ... Defer Layout Update April Data Pivot Table (2) 06 Ready E URE 5.47 Suburbia Regional Hospital . . a. Open e05p2 Patients and save it as e05p2Patients LastFirst. b. Ensure that the April Data worksheet is active. Complete the following steps to sort the data: Click the Data tab and click Sort in the Sort & Filter group. Click the Sort by arrow and select Weekday. Click the Order arrow and select Custom List to open the Custom Lists dialog box. Select Sunday, Monday, Tuesday in the Custom lists section and click OK. Click OK in the Sort dialog box. c. Click Subtotal in the Outline group. Complete the following steps in the Subtotal dialog box: Click the At each change in arrow and select Weekday. Click the Use function arrow and select Average. Click the Patient-End check box to select it, keep the Nurses check box selected, and then click OK. d. Click the Group arrow in the Outline group on the Data tab, select Auto Outline, and then click OK. Click the collapse button above column G to collapse the outline. e. Click the April Table sheet tab, click the Insert tab, click Recommended PivotTables in the Tables group, click the Sum of Patient-Start by Weekday and Shift thumbnail, and then click OK. f. Complete the following steps to modify the Pivot Table: Click the Day check box in the PivotTable Fields List to add that field to the ROWS area. Move the Shift field from the COLUMNS area to the FILTERS area in the PivotTable Fields . . . . . List. Click the Shift arrow in cell B1, select Day, and click OK to filter the records to show totals for only the day shifts. Click the PivotTable Name box in the PivotTable group on the Analyze tab, type Patients- Nurses, and then press Enter. Rename Sheetl as PivotTable. g. Complete the following steps to create a calculated field: Click the Analyze tab, click Fields, Items, & Sets in the Calculations group, and then select Calculated field. Type Start Patients per Nurse in the Name box in the Insert Calculated field dialog box. Double-click Patient-Start in the Fields list, type /, and double-click Nurses in the Fields list. Click OK. Click cell C3, click Field Settings in the Active Field group, click Number Format, click Number in the Category list, click the Decimal places setting to 0, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. h. Click Insert Slicer in the Filter group, click the Weekday check box in the Insert Slicers dialog box, and then click OK. i. Complete the following steps to customize the filter: • Move the filter to start in cell F3. Click More in the Slicer Styles group on the Options tab and click Slicer Style Dark 5. Change the Width to 1.5 in the Buttons group on the Options tab. Press Ctrl and click Sunday and Saturday in the Weekday slicer to filter out these two days. j. Copy the PivotTable sheet tab and place the PivotTable (2) sheet tab between the April Data and Pivot Table sheet tabs. Ensure that the Pivot Table (2) sheet is active and complete the fol- lowing steps: • Click the Shift check box in the PivotTable Fields List to remove it from the FILTERS area. • Click the Day check box in the PivotTable Fields List to remove it from the ROWS area. Click Multi-Select in the Weekday slicer window, click Sunday, and then click Saturday . so that all seven days will display. . k. Click in the Pivot Table, click the Analyze tab, click PivotChart in the Tools group, and then complete the following steps: Click Bar in the Insert Chart dialog box and click OK. Click the Sum of Start Patients per Nurse arrow in the VALUES area of the Pivot Table Fields List and select Remove Field. Click the Sum of Patients Start arrow in the VALUES area of the PivotTable Fields List, click Value Field Settings, select Average, click Number Format, select Number in the Category list, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box, Click the Sum of Patients End arrow in the VALUES area of the Pivot Table Fields List, click Value Field Settings, select Average, click Number Format, select Number in the Category list, click OK in the Format Cells dialog box, and then click OK in the Value Field Settings dialog box. Click the Analyze tab, click the Field Buttons arrow in the Show/Hide group, and then select Hide All. Click the Chart Title placeholder, type Average Number of Patients by Weekday. and then press Enter. Move the chart so that the top-left corner starts in cell A13. 1. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on each worksheet. m. Save and close the file. Based on your instructor's directions, submit e05p2Patients_LastFirst. .
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hi! Here is your...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags