Figure 3.11 Visual Benchmark 2 Wellington Park Medical Center Nening the rice can be at 2306 EN Average LPN Average Grand Avg 15.96 24.14 Visual Benchmark Demonstrate Your Proficiency A 1. Open WPMCallList.xlsx. 2. Save the workbook and name it EL2-C3-VBI-WPMCallList. 3. Format and apply data tools as required to duplicate the worksheet in Figure 3.10 using the following information: The worksheet has Table Style Medium 5 applied to the table range. Look closely at the sorted order. The table is sorted by three levels using the fields Designation, Hourly Rate, and Hire Date. Shift cost equals the hourly rate times 8 hours. Use Flash Fill to split the names into two columns. Include the Total row and apply the appropriate banded options. 4. Save, print, and then close EL2-C3-VB1-WPMCallList.xlsx 2 USING SUBTOTALS IN A CALL LIST 1. Open EL2-C3-VB1-WPMCallList.xlsx 2. Save the workbook and name it EL2-C3-VB2-WPMCallList. 3. Create subtotals and view the revised worksheet at the appropriate level to display as shown in Figure 3.11 on the next page. 4. Save, print, and then close EL2-C3-VB2-WPMCallList.xlsx. Figure 3.10 Visual Benchmark 1 Wellington Park Medical Center 4100 11 e VE PASTO THEATRE THOUSE Y 100 280 333 SA Ver TH 10 1001 Yer B003 2001- 3:44 200 W 20 COM 1200 1333MILY REN ya 100 130.00 221 TO 23 10 22 220 180.00 220.00 12.00 110.00 14.00 10 SH M 14 Y 154 ONE SH 2222 11.30 12.00 1 16.00 1.00 Payroll No. Name 14586 Alma Fernandez 19658 Paula Sanderson 37944 Fernando Este 38642 Tania Ravi 45968 David Featherstone 46956 Orlando Zambian 48652 Dana Casselman 56983 Amanda Sanchez 58612 Savana Ruiz 68429 Rene Quenneville 68475 Kelly O'Brien 69417 Denis LaPierre 78452 Terry Mason 78647 Jay Bjorg 85412 Kelly Lund 89367 Xiu Zheng 90467 Nadir Abouzeen 95558 Sam Vargas 96523 Lynn Pietre 96721 Noreen Kalir 98364 Lana Bourne 98731 Zail Singh Wellington Park Medical Center Nursing Division Casual Relief Call List Designation Hire Date Telephone OR Exp? Day Shift Only? Night Shift Only? Either Shift? Hourly Rate LPN 8/3/97 555-7412 Yes No No Yes 16.75 RN 4/28/00 555-3485 No No No Yes 38.50 RN 7/18/05 555-4545 No No No Yes 22.50 RN 6/22/02 555-6969 Yes Yes No Weekends only 38.50 RN 9/9/01 555-5961 No No No Yes 35.00 RN 11/10/01 555-1186 No Yes No No 35.00 LPN 10/15/97 555-6325 Yes No No Yes 16.75 RN 4/27/99 555-4896 Yes No Yes No 33.00 RN 4/15/12 555-8457 Yes No Yes Weekends only 22.50 RN 8/15/03 555-4663 Yes Yes No Weekends only 22.50 LPN 1/20/12 555-6344 No Yes No Weekends only 13.75 RN 8/23/03 555-8643 No No Yes No 22.50 RN 10/8/15 555-1279 Yes No Yes No 38.50 RN 5/14/07 555-6598 No No No Yes 22.50 LPN 11/19/98 555-3684 No Yes No Weekends only 15.75 LPN 4/23/06 555-7383 Yes Yes No No 18.75 LPN 8/12/08 555-9023 No No No Yes 14.50 RN 3/2/09 555-4571 No No No Yes 22.50 RN 10/22/98 555-2548 Yes Yes No Weekends only 35.00 4/3/09 555-1876 Yes No No 21.50 LPN 7/15/08 555-9012 Yes No No 15.50 RN 5/6/11 555-3561 Yes Yes No No 22.50 RN Yes Yes Figure 3.11 Visual Benchmark 2 Wellington Park Medical Center Nening the rice can be at 2306 EN Average LPN Average Grand Avg 15.96 24.14 Visual Benchmark Demonstrate Your Proficiency A 1. Open WPMCallList.xlsx. 2. Save the workbook and name it EL2-C3-VBI-WPMCallList. 3. Format and apply data tools as required to duplicate the worksheet in Figure 3.10 using the following information: The worksheet has Table Style Medium 5 applied to the table range. Look closely at the sorted order. The table is sorted by three levels using the fields Designation, Hourly Rate, and Hire Date. Shift cost equals the hourly rate times 8 hours. Use Flash Fill to split the names into two columns. Include the Total row and apply the appropriate banded options. 4. Save, print, and then close EL2-C3-VB1-WPMCallList.xlsx 2 USING SUBTOTALS IN A CALL LIST 1. Open EL2-C3-VB1-WPMCallList.xlsx 2. Save the workbook and name it EL2-C3-VB2-WPMCallList. 3. Create subtotals and view the revised worksheet at the appropriate level to display as shown in Figure 3.11 on the next page. 4. Save, print, and then close EL2-C3-VB2-WPMCallList.xlsx. Figure 3.10 Visual Benchmark 1 Wellington Park Medical Center 4100 11 e VE PASTO THEATRE THOUSE Y 100 280 333 SA Ver TH 10 1001 Yer B003 2001- 3:44 200 W 20 COM 1200 1333MILY REN ya 100 130.00 221 TO 23 10 22 220 180.00 220.00 12.00 110.00 14.00 10 SH M 14 Y 154 ONE SH 2222 11.30 12.00 1 16.00 1.00 Payroll No. Name 14586 Alma Fernandez 19658 Paula Sanderson 37944 Fernando Este 38642 Tania Ravi 45968 David Featherstone 46956 Orlando Zambian 48652 Dana Casselman 56983 Amanda Sanchez 58612 Savana Ruiz 68429 Rene Quenneville 68475 Kelly O'Brien 69417 Denis LaPierre 78452 Terry Mason 78647 Jay Bjorg 85412 Kelly Lund 89367 Xiu Zheng 90467 Nadir Abouzeen 95558 Sam Vargas 96523 Lynn Pietre 96721 Noreen Kalir 98364 Lana Bourne 98731 Zail Singh Wellington Park Medical Center Nursing Division Casual Relief Call List Designation Hire Date Telephone OR Exp? Day Shift Only? Night Shift Only? Either Shift? Hourly Rate LPN 8/3/97 555-7412 Yes No No Yes 16.75 RN 4/28/00 555-3485 No No No Yes 38.50 RN 7/18/05 555-4545 No No No Yes 22.50 RN 6/22/02 555-6969 Yes Yes No Weekends only 38.50 RN 9/9/01 555-5961 No No No Yes 35.00 RN 11/10/01 555-1186 No Yes No No 35.00 LPN 10/15/97 555-6325 Yes No No Yes 16.75 RN 4/27/99 555-4896 Yes No Yes No 33.00 RN 4/15/12 555-8457 Yes No Yes Weekends only 22.50 RN 8/15/03 555-4663 Yes Yes No Weekends only 22.50 LPN 1/20/12 555-6344 No Yes No Weekends only 13.75 RN 8/23/03 555-8643 No No Yes No 22.50 RN 10/8/15 555-1279 Yes No Yes No 38.50 RN 5/14/07 555-6598 No No No Yes 22.50 LPN 11/19/98 555-3684 No Yes No Weekends only 15.75 LPN 4/23/06 555-7383 Yes Yes No No 18.75 LPN 8/12/08 555-9023 No No No Yes 14.50 RN 3/2/09 555-4571 No No No Yes 22.50 RN 10/22/98 555-2548 Yes Yes No Weekends only 35.00 4/3/09 555-1876 Yes No No 21.50 LPN 7/15/08 555-9012 Yes No No 15.50 RN 5/6/11 555-3561 Yes Yes No No 22.50 RN Yes Yes