O Sea WBPayroll.xlsx - Protected View - Ex... AutoSave OH Review View Formulas Data Insert Draw Page Layout File Home PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safert The Waterfront Bistro A1 G F H E A B D The Waterfront Bistro 1 Payroll Summary 2 3 4 Total Overtime Hours Hours Gross Pay 5 Employee 6 Lou Cortez + Jasmine Hill 8 Heather Kiley 9 Dayna McGuire 10 Carla Modano 11 Tyler Santini 12 Pat Soulliere 13 Moira Su-Lin 14 Toni Williams 15 Total 16 17 18 19 20 21 22 Summary Week 1 Week2 Week 3 Week4 Type here to search AutoSave OOR WBPayroll.xlsx . Protected View - Ex... e Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Ec A1 fr The Waterfront Bistro A B C D 1 K M N o 1 2 2 3 For the week ended: 4 Date of payment: E F G H The Waterfront Bistro Payroll Summary 11/9/2018 11/12/2018 Fri Total Hours Overtime Hours Sun Gross Pay 4 48 39 8 8 5 8 8 5 Employee Mon Tele Wed Thu 6 Lou Cortez 8 8 8 8 4 lasmine Hill 7 0 0 8 8 Heather kiley 8 8 6 5 9 Dayna McGuire 6 0 0 6 8 8 To Carla Modang 0 0 7 7 11 Tyler Santini 4 4 8 8 12 12. Pat Soulliere 0 0 8 8 13 Moira Su-Lin 8 6 7 0 14 Toni Williams 8 0 0 0 8 8 15 Total 49 26 50 60 16 17 Pay Rate $8.25 Sat 4. 4 8 0 8 8 7 8 8 6 8 0 8 0 8 53 8 8. 8 59 8 5 9 4 60 40 42 38 39 37 38 36 357 21 23 Summary Weckt Wert Week Week Type here to search 2 * do 7 + 2 # 3 199 $ 4 % DDI AutoSave On - - WBPayroll.xlsx - Protected View - Ex... Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A1 The Waterfront Bistro A B C D 1 K M E F GHI The Waterfront Bistro Payroll Summary N 2 3 For the week ended: 4 Date of payment: 11/16/2018 11/19/2018 Thu Fri Total Hours Overtime Hours Gross Sun . 8 Pay 8 0 Sat 6 8 0 0 44 41 40 8 5 Employee Mon Tu Wed 6 Lou Cortez 0 7 9 Jasmine Hill 8 6 4 B Heather Riley 8 0 8 9 Dayna McGuire 7 5 0 10 Carla Modano 6 0 8 11 Tyler Santini 0 8 O 12. Pat Soulliere 8 0 0 13 Moira su-lin 0 8 8 14 Toni Williams 0 7 7. 15 Total 37 41 44 16 17 Pay Rate. 58.25 18 8 8 7 8 -8 8 8 9 8 40 6 8 8. 8 4 0 8 8 0 8 8 8. 7 6 0 8 8 8 8 8 8 72 38 41 39 38 36 357 51 6 48 20 21 Summary Weekt Week 2 Week Week Type here to search la 2 ** 7 2. # 3 $ + + 18 % 199 4. D AutoSave OH B 8 WBPayroll.xlsx - Protected View - Ex... O Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Ed A1 The Waterfront Bistro X fx co A B 1 K M N 1 E F G H The Waterfront Bistro Payroll Summary O 2 3 For the week ended: 4 Date of payment: 11/23/2018 11/26/2018 Sun Gross Pay Fri 8 7 8 8 8 9 8 0 8 8 4 5 Employee Man Te Wed Thu 6 Lou Cortez 0 7 9 9 8 7 Jasmine Hill 8 6 4 0 8 Heather Kiley 8 0 8 8 9 Dayna McGuire 7 5 5 8 10 Carla Modano 6 0 8 8 8 11 Tyler Santini 0 8 0 8 12 Pat Soulliere 8 0 0 8 13 Moira Su-Lin 0 8 8 8 8. 14 Toni 0 7 7 15 Total 8 37 41 49 64 16 17 Pay Rate: $8.25 18 19 20 21 22 23 Sat 6 8 o 8 8 8 8 7 6 4 55 Total Overtime Hours Hours 38 41 40 45 38 41 39 38 40 360 0 8 8 8 8 Williams 8 8 72 6 6 42 Summary Week 1 Week 2 Week 3 Week 4 Type here to search * is 7 @ 2 # 3. $ + 199 4 % 5 A D# 6 & AutoSave oe CB WBPayroll.xlsx - Protected View - Ex... Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. En AL B C D E G H 1 K 2 15 10 21 Summary Welt Week2 Week Week e Type here to search 7 a 2. # 3 $ References Mailings Review View Help 1G11 Skills Review Note: If you submit your work in hard copy, check with your instructor before completing these reviews to find out if you need to print two copies of each worksheet with one of the copies showing formulas in cells instead of the calculated results. Review 1 Managing and Formatting Worksheets; Using 3-D References; Printing Multiple Worksheets 1. Open WBPayroll.xlsx and then save it with the name 4-WBPayroll. 2. Delete the Week4 worksheet. 3. Copy the Week3 worksheet and position the new worksheet after Week3. 4. Rename the Week3 (2) worksheet as Week4. 5. Make the Week4 worksheet active and then edit the following cells: Change cell C3 from 11/23/2018 to 11/30/18. Change cell CII from 0 to 8. Change cell from 9 to 0. Change cell 114 from 6 to 9. 6. Apply the standard dark blue tab color to the Weekl through Week4 worksheet tabs and the standard dark red tab color to the Summary worksheet tab. 7. With the Summary worksheet active, create a SUM formula with a 3-D reference in cell C6 that sums the hours for Lou Cortez for all four weeks. 8. Drag the fill handle from cell C6 to cell C14. 9. Make the Weekl worksheet active. In cell K6, type the formula if(6>40.j6-40,0). drag the fill handle from cell Ko to cell K14, and then calculate the total in cell K15. (This IF formula says that if the amount in cell J6 is greater than 40, subtract the amount in the cell from 40 and return the result. If the amount is less than 40, the formula will insert a zero in the cell.) 10. In cell L6, type the formula =(6 $b$17)+(k6 $b$17.5). drag the fill handle from cell L6 to cell L14 and then calculate the total in cell Li5. If necessary, increase the width of column L to display all cell entries. The formula multiplies the total number of hours by the pay rate in cell B17 and then adds that amount to the number of overtime hours multiplied by the pay rate in cell B17 multiplied by 0.5 since overtime pay is one-and-a-half times the pay rate.) 11. Copy and paste the formulas in columns K and L to complete the Overtime Hours and Gross Pay column entries in the Week2 through Week 4 worksheets. 12. Make the Summary worksheet active and then enter the 3-D reference formulas in cell D6 and cell E6 to sum the overtime hours and gross pay for Lou Cortez from all four worksheets. 13. Copy the 3-D formulas in the range D6:56 and paste to the range D7:E14. 14. Calculate the totals in the range CIS:E15 and then apply the Accounting format to the cells in the Gross Pay column. 15. Group and print all five worksheets. Note: If you submit your work in hard copy, check with your instructor to see if you need to print two copies of the worksheets with one of the copies showing the cell formulas instead of the calculated results. 16. Save and then close 4-WB Payroll.xlsx a W O Sea WBPayroll.xlsx - Protected View - Ex... AutoSave OH Review View Formulas Data Insert Draw Page Layout File Home PROTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safert The Waterfront Bistro A1 G F H E A B D The Waterfront Bistro 1 Payroll Summary 2 3 4 Total Overtime Hours Hours Gross Pay 5 Employee 6 Lou Cortez + Jasmine Hill 8 Heather Kiley 9 Dayna McGuire 10 Carla Modano 11 Tyler Santini 12 Pat Soulliere 13 Moira Su-Lin 14 Toni Williams 15 Total 16 17 18 19 20 21 22 Summary Week 1 Week2 Week 3 Week4 Type here to search AutoSave OOR WBPayroll.xlsx . Protected View - Ex... e Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Ec A1 fr The Waterfront Bistro A B C D 1 K M N o 1 2 2 3 For the week ended: 4 Date of payment: E F G H The Waterfront Bistro Payroll Summary 11/9/2018 11/12/2018 Fri Total Hours Overtime Hours Sun Gross Pay 4 48 39 8 8 5 8 8 5 Employee Mon Tele Wed Thu 6 Lou Cortez 8 8 8 8 4 lasmine Hill 7 0 0 8 8 Heather kiley 8 8 6 5 9 Dayna McGuire 6 0 0 6 8 8 To Carla Modang 0 0 7 7 11 Tyler Santini 4 4 8 8 12 12. Pat Soulliere 0 0 8 8 13 Moira Su-Lin 8 6 7 0 14 Toni Williams 8 0 0 0 8 8 15 Total 49 26 50 60 16 17 Pay Rate $8.25 Sat 4. 4 8 0 8 8 7 8 8 6 8 0 8 0 8 53 8 8. 8 59 8 5 9 4 60 40 42 38 39 37 38 36 357 21 23 Summary Weckt Wert Week Week Type here to search 2 * do 7 + 2 # 3 199 $ 4 % DDI AutoSave On - - WBPayroll.xlsx - Protected View - Ex... Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. A1 The Waterfront Bistro A B C D 1 K M E F GHI The Waterfront Bistro Payroll Summary N 2 3 For the week ended: 4 Date of payment: 11/16/2018 11/19/2018 Thu Fri Total Hours Overtime Hours Gross Sun . 8 Pay 8 0 Sat 6 8 0 0 44 41 40 8 5 Employee Mon Tu Wed 6 Lou Cortez 0 7 9 Jasmine Hill 8 6 4 B Heather Riley 8 0 8 9 Dayna McGuire 7 5 0 10 Carla Modano 6 0 8 11 Tyler Santini 0 8 O 12. Pat Soulliere 8 0 0 13 Moira su-lin 0 8 8 14 Toni Williams 0 7 7. 15 Total 37 41 44 16 17 Pay Rate. 58.25 18 8 8 7 8 -8 8 8 9 8 40 6 8 8. 8 4 0 8 8 0 8 8 8. 7 6 0 8 8 8 8 8 8 72 38 41 39 38 36 357 51 6 48 20 21 Summary Weekt Week 2 Week Week Type here to search la 2 ** 7 2. # 3 $ + + 18 % 199 4. D AutoSave OH B 8 WBPayroll.xlsx - Protected View - Ex... O Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Ed A1 The Waterfront Bistro X fx co A B 1 K M N 1 E F G H The Waterfront Bistro Payroll Summary O 2 3 For the week ended: 4 Date of payment: 11/23/2018 11/26/2018 Sun Gross Pay Fri 8 7 8 8 8 9 8 0 8 8 4 5 Employee Man Te Wed Thu 6 Lou Cortez 0 7 9 9 8 7 Jasmine Hill 8 6 4 0 8 Heather Kiley 8 0 8 8 9 Dayna McGuire 7 5 5 8 10 Carla Modano 6 0 8 8 8 11 Tyler Santini 0 8 0 8 12 Pat Soulliere 8 0 0 8 13 Moira Su-Lin 0 8 8 8 8. 14 Toni 0 7 7 15 Total 8 37 41 49 64 16 17 Pay Rate: $8.25 18 19 20 21 22 23 Sat 6 8 o 8 8 8 8 7 6 4 55 Total Overtime Hours Hours 38 41 40 45 38 41 39 38 40 360 0 8 8 8 8 Williams 8 8 72 6 6 42 Summary Week 1 Week 2 Week 3 Week 4 Type here to search * is 7 @ 2 # 3. $ + 199 4 % 5 A D# 6 & AutoSave oe CB WBPayroll.xlsx - Protected View - Ex... Search File Home Insert Draw Page Layout Formulas Data Review View Help PROTECTED VIEW Be careful--files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View. En AL B C D E G H 1 K 2 15 10 21 Summary Welt Week2 Week Week e Type here to search 7 a 2. # 3 $ References Mailings Review View Help 1G11 Skills Review Note: If you submit your work in hard copy, check with your instructor before completing these reviews to find out if you need to print two copies of each worksheet with one of the copies showing formulas in cells instead of the calculated results. Review 1 Managing and Formatting Worksheets; Using 3-D References; Printing Multiple Worksheets 1. Open WBPayroll.xlsx and then save it with the name 4-WBPayroll. 2. Delete the Week4 worksheet. 3. Copy the Week3 worksheet and position the new worksheet after Week3. 4. Rename the Week3 (2) worksheet as Week4. 5. Make the Week4 worksheet active and then edit the following cells: Change cell C3 from 11/23/2018 to 11/30/18. Change cell CII from 0 to 8. Change cell from 9 to 0. Change cell 114 from 6 to 9. 6. Apply the standard dark blue tab color to the Weekl through Week4 worksheet tabs and the standard dark red tab color to the Summary worksheet tab. 7. With the Summary worksheet active, create a SUM formula with a 3-D reference in cell C6 that sums the hours for Lou Cortez for all four weeks. 8. Drag the fill handle from cell C6 to cell C14. 9. Make the Weekl worksheet active. In cell K6, type the formula if(6>40.j6-40,0). drag the fill handle from cell Ko to cell K14, and then calculate the total in cell K15. (This IF formula says that if the amount in cell J6 is greater than 40, subtract the amount in the cell from 40 and return the result. If the amount is less than 40, the formula will insert a zero in the cell.) 10. In cell L6, type the formula =(6 $b$17)+(k6 $b$17.5). drag the fill handle from cell L6 to cell L14 and then calculate the total in cell Li5. If necessary, increase the width of column L to display all cell entries. The formula multiplies the total number of hours by the pay rate in cell B17 and then adds that amount to the number of overtime hours multiplied by the pay rate in cell B17 multiplied by 0.5 since overtime pay is one-and-a-half times the pay rate.) 11. Copy and paste the formulas in columns K and L to complete the Overtime Hours and Gross Pay column entries in the Week2 through Week 4 worksheets. 12. Make the Summary worksheet active and then enter the 3-D reference formulas in cell D6 and cell E6 to sum the overtime hours and gross pay for Lou Cortez from all four worksheets. 13. Copy the 3-D formulas in the range D6:56 and paste to the range D7:E14. 14. Calculate the totals in the range CIS:E15 and then apply the Accounting format to the cells in the Gross Pay column. 15. Group and print all five worksheets. Note: If you submit your work in hard copy, check with your instructor to see if you need to print two copies of the worksheets with one of the copies showing the cell formulas instead of the calculated results. 16. Save and then close 4-WB Payroll.xlsx a W