Practical Laboratory on MS-Excel Hours, plus their Overtime Hours at a rate that is 1.5 times their Pay Rate. What is Lyle O'Toole's Subtotal? To begin the Practice Lab Class Work, first download Type the following data into MS-Excel 6. Jo cells C30 and C31 are listed the Income Tax rates for employees earning less than $450.00 and greater than or equal to $450.00. In column F, create an IF function, which evaluates which tax bracket an employee falls into and uses Absolute References to return the amount of their total taxes paid. How much does John Abbot pay in Income tax? G PAYROLL RECORDS FOR JANUARY 4, 2021 7. Lo cells C30 and C31 are listed the Income Tax rates for employees earning less than $450.00 and greater than or equal to $450.00. In column F, create an IF function, which evaluates which tax bracket an employee falls into and uses Absolute References to return the amount of their total taxes paid. How much does Fred Gretchen Ray in Income Tax? Employee 5 John Abhol 5 Terry Burke 7 Linda Carlson Krista Carter John Dole 10 James Erickson 11 Fred Gretches 12 Sally Hertz 13 Melanie Jackson 14 Peter Lore 15 Mike McDonald 16 Lyle O'Toole 17 Samantha Reimer 18 William Peterson 10 Randy Wilkes 20 Alex Zimmerman Hours Overtime Hours Pay Rate (hour) Subtotal income Tax Pay After Taxes 35.00 $1200 40.00 200 $15.00 22200 $1200 25.00 $1500 3900 $9.00 40.00 9.00 $12.00 39.00 $2000 13700 $12.00 35.00 $12.00 40.00 $12.00 19.00 $9.00 40.00 5.00 $15.00 25.00 $1200 $9.00 29.00 $15.00 40.00 11.00 $1200 3300 & Pay.After Taxes is equal to the Subtotal minus Income Tax How much does Alex Zimmerman make after taxes? 9. In cell B25, use a function to determine the highest Pay After Taxes value. 23 Total Hours (Regular and OT) 24 Average Pay Rate 25 Highest Pay After Taxes 26 Lowest Pay Aner Taxes 27 Average Pay After Taxes 10.do cell B27, use a function to determine the average Pay After Taxes value. 30 Income tax rate brackets 31 2476 31.00 Once you have finish typing the datas, saved the file to your Documents, open it and you may begin. Use the spreadsheet to answer the following questions: 1. What is the correct way of referring to the range of cells which record both Regular and Overtime Hours for the employees? A5.820 BB5:20 65B:20C B5..20 2. In cell B23, use a function to calculate the total hours worked (both Regular and Overtime). How many hours were xocked total? 3. In cell B24, use a function to calculate the Average Pay Rate for employees. What is the Average Pay Rate? ? 4. Subtotal is equal to an employee's Pay Rate multiplied by their Hours, plus their Overtime Hours at a rate that is 1.5 times their Pay Rate. What is John Dole's Subtotal? 5. Subtotal is equal to an employee's Pay Rate multiplied by their
