50.1K

Verified Solution

Link Copied!

I Uploaded A Full image of the work schedule and but it Doesn't clear so thats why i uploaded it in two parts. but it is Just a One Case problem .

So, What we Have Do is Just Follow the Steps showing in the 4th image to solve this Case study

hope you understand.

File Home Insert Page Layout Formulas Review View Developer Help Calibri =- 11-AA a-A- 22 Wap Tout Merge Center Number S -% - insert - delete- El forma Paide Ideas BY Conditional Formats Cell Formatting Table Styles Sort & Find Film Select Editing Clipboard Aligne Font Number SA Meas cra Sentity G6 0 Capshaw Family Dentistry Estimate of Take Home Pay Personalition Working Schedule Federal Tere 6 SOFT 0 1 RO SOM 52 1 DES int 253 ring Awarcon TER-10 2DIA SOL 03 YOL 50 OX F10 Sot TI BOAR En C LE RE 1600 BRE 150 21 172 TR Oh OC 60 Total Home 2017-0-105A Social Security More 1 SS * Take Home Pay Zoom out Documentation Work Schedule 6:36 AM Type here to search OD ENG O @ Clipboard Font Alignment Numbe G6 B E F G H K > 1 Capshaw Family Dentistry 2 Estimate of Take Home Pay 3 Overtime Payment 4 Personal Information 5 Name Carol Lemke 6 Hourly Rate $16.25 7 Federal Marital Status Single 8 State Marital Status Single 9 Withholding Allowances 1 10 11 Total Straight Time Hours 12 Total Overtime Hours 13 Total Hours 14 Total Pay 15 16 Deductions 17 Federal Income Tax 18 Social Security 19 Medicare 20 Georgia Income Tax 21 22 Total Deductions 23 Total Withholding Rate 24 Take Home Pay 25 26 Date 2017-10-04 2017-10-05 2017-10-06 2017-10-09 2017-10-10 2017-10-04 2017-10-05 2017-10-06 2017-10-09 2017-10-10 Clock In 8:00 AM 8:00 AM 8:30 AM 8.00 AM 10:00 AM 8:00 AM 11:30 AM 8:00 AM 8:00 AM 10:45 AM Working Schedule Clock Out Total Hours Straight Time 3:30 PM 100PM 5:00 PM 10:30 AM 7:00 PM 3:00 PM 5:45 PM 6:30 PM 2:30 PM 3:30 PM 27 M P @ S oc w O X 2 Federal Tax Rate 7 00 Withholding Single Married Supplemental 2 10.92%. 7.88% 4.85% 5.65% 3.62% 1.60% 25.00% 25.00% 25.00% 2.764 0% 25% 0.74% 0% 25% 5 07 0% 25% 6 074 0%. 25% 0% 07 25%. 0% 074 25% 9 07 0% 25% 10 0%. 0% 25%. 5 DN Withholding Single Marted 11-working Martied 2-working 8 0%. 0 4.3474 3.77% 4.43%. State & Local Tax Rate 3 4 1.61% 0.77% 0.22% 1.15% 0.52% 0.13% 1.70% 0.80%. 0.17% 3.43%. 2.86% 3.52%. 2 2.524 1.95% 2.614 6 0%. 0% 0% 0% 0%. 0%. 9 0% 0% 0% 10 0% 07 0%. 0% 0% Social Security Tax R 16.2022 Medicare 1.45% Zoom Out In cell G6, calculate the total hours worked on the first day, which is equal to the difference between cell F6 and cell E6 multiplied by 24. Carol will get overtime wages when she works more than eight hours in a day. Calculate the non-overtime hours in cell H6 by using the MIN function to return the minimum of the value in cell G6 and the value 8. In cell 16, calculate the amount of overtime hours by using the IF function to test whether cell G6 is greater than 8. If it is, return the value cell G6 minus 8; otherwise, return the value 0. In cell 16, calculate the salary due on the first day. The salary due is equal to the Straight Time worked multiplied by the hourly rate in cell B6 plus the Overtime multiplied by the hourly rate times 1.5 (Carol will receive time-and-a-half for each overtime hour.) Use an absolute reference to cell B6. Select the range G6:36, and then use AutoFill to copy the formulas into the range G7:J15 to calculate the salary for each of the ten days in the table. In cell B11, calculate the total straight time hours worked by summing the values in column H. In cell B12, calculate the total overtime hours by summing the values in column I. In cell B13, calculate the total hours worked by summing the value in column G. In cell B14, calculate the total payments by summing the values in column ). In cell B17, calculate the amount of federal tax by multiplying the Total Pay value in cell B14 by the appropriate federal tax rate for an employee with the marital status in cell B7 and withholding allowances in cell B9. (Hint: Use the VLOOKUP function with an exact match lookup for the lookup table in the range L6:W8. For the Col_index_num argument, use the value of cell B9 plus 2.) In cell B18, calculate the Social Security tax equal to the value of cell B14 multiplied by the tax rate in cell M16. File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do O 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 Editing A1 Capshaw Family Dentistry B D E F H 1 Capshaw Family Dentistry 2 Estimate of Take Home Pay Date Overtime Payment 4 Personal Information 5 Name 6 Hourly Rate 7 Federal Marital Status 8 State Marital Status 9 Withholding Allowances 10 11 Total Straight Time Hours 12 Total Overtime Hours 13 Total Hours 14 Total Pay 15 16 Deductions 17 Federal Income Tax 18 Social Security 19 Medicare 20 Georgia Income Tax 21 22 Total Deductions 23 Total Withholding Rate 24 Take Home Pay 25 Documentation Work Schedule Clock in 8:00 AM 8:00 AM 8:30 AM 8:00 AM 10:00 AM 8:00 AM 11:30 AM 8:00 AM 8:00 AM 10:45 AM Working Schedule Clock Out Total Hours Straight Time 3:30 PM 1:00 PM 5:00 PM 10:30 AM 7:00 PM 3:00 PM 5:45 PM 6:30 PM 2:30 PM 3:30 PM Type here to search ORI L M N o P Q 20 S T U V w Withholding Single Married Supplemental 0 10.92% 5.65% 25.00% 7.88% 3.62% 25.00% 2 4.85% 1.60% 25.00% Federal Tax Rate 3 4 2.76% 0.74% 0% 25% 25% 0% 5 0% 0% 25% 6 0% 0% 25% 7 096 0% 25% 8 0% 0% 25% 9 0% 0% 25% 10 0% 0% 25% 8 9 Withholding Single Married 1-working Married 2-working 0 4.34% 3.77% 4.43% 1 3.43% 2.86% 3.52% 2 2.52% 1.95% 2.61% State & Local Tax Rate 3 5 1.61% 0.7796 0.22% 1.15% 0.52% 0.13% 1.70% 0.80% 0.17% 6 0% 0% 0% 7 0% 0% 0% 0% 0% 0% 0% 0% 0% 10 0% 0% 0% Social Security Tax Rate Medicare 6.20% 1.45% File Home Insert Page Layout Formulas Review View Developer Help Calibri =- 11-AA a-A- 22 Wap Tout Merge Center Number S -% - insert - delete- El forma Paide Ideas BY Conditional Formats Cell Formatting Table Styles Sort & Find Film Select Editing Clipboard Aligne Font Number SA Meas cra Sentity G6 0 Capshaw Family Dentistry Estimate of Take Home Pay Personalition Working Schedule Federal Tere 6 SOFT 0 1 RO SOM 52 1 DES int 253 ring Awarcon TER-10 2DIA SOL 03 YOL 50 OX F10 Sot TI BOAR En C LE RE 1600 BRE 150 21 172 TR Oh OC 60 Total Home 2017-0-105A Social Security More 1 SS * Take Home Pay Zoom out Documentation Work Schedule 6:36 AM Type here to search OD ENG O @ Clipboard Font Alignment Numbe G6 B E F G H K > 1 Capshaw Family Dentistry 2 Estimate of Take Home Pay 3 Overtime Payment 4 Personal Information 5 Name Carol Lemke 6 Hourly Rate $16.25 7 Federal Marital Status Single 8 State Marital Status Single 9 Withholding Allowances 1 10 11 Total Straight Time Hours 12 Total Overtime Hours 13 Total Hours 14 Total Pay 15 16 Deductions 17 Federal Income Tax 18 Social Security 19 Medicare 20 Georgia Income Tax 21 22 Total Deductions 23 Total Withholding Rate 24 Take Home Pay 25 26 Date 2017-10-04 2017-10-05 2017-10-06 2017-10-09 2017-10-10 2017-10-04 2017-10-05 2017-10-06 2017-10-09 2017-10-10 Clock In 8:00 AM 8:00 AM 8:30 AM 8.00 AM 10:00 AM 8:00 AM 11:30 AM 8:00 AM 8:00 AM 10:45 AM Working Schedule Clock Out Total Hours Straight Time 3:30 PM 100PM 5:00 PM 10:30 AM 7:00 PM 3:00 PM 5:45 PM 6:30 PM 2:30 PM 3:30 PM 27 M P @ S oc w O X 2 Federal Tax Rate 7 00 Withholding Single Married Supplemental 2 10.92%. 7.88% 4.85% 5.65% 3.62% 1.60% 25.00% 25.00% 25.00% 2.764 0% 25% 0.74% 0% 25% 5 07 0% 25% 6 074 0%. 25% 0% 07 25%. 0% 074 25% 9 07 0% 25% 10 0%. 0% 25%. 5 DN Withholding Single Marted 11-working Martied 2-working 8 0%. 0 4.3474 3.77% 4.43%. State & Local Tax Rate 3 4 1.61% 0.77% 0.22% 1.15% 0.52% 0.13% 1.70% 0.80%. 0.17% 3.43%. 2.86% 3.52%. 2 2.524 1.95% 2.614 6 0%. 0% 0% 0% 0%. 0%. 9 0% 0% 0% 10 0% 07 0%. 0% 0% Social Security Tax R 16.2022 Medicare 1.45% Zoom Out In cell G6, calculate the total hours worked on the first day, which is equal to the difference between cell F6 and cell E6 multiplied by 24. Carol will get overtime wages when she works more than eight hours in a day. Calculate the non-overtime hours in cell H6 by using the MIN function to return the minimum of the value in cell G6 and the value 8. In cell 16, calculate the amount of overtime hours by using the IF function to test whether cell G6 is greater than 8. If it is, return the value cell G6 minus 8; otherwise, return the value 0. In cell 16, calculate the salary due on the first day. The salary due is equal to the Straight Time worked multiplied by the hourly rate in cell B6 plus the Overtime multiplied by the hourly rate times 1.5 (Carol will receive time-and-a-half for each overtime hour.) Use an absolute reference to cell B6. Select the range G6:36, and then use AutoFill to copy the formulas into the range G7:J15 to calculate the salary for each of the ten days in the table. In cell B11, calculate the total straight time hours worked by summing the values in column H. In cell B12, calculate the total overtime hours by summing the values in column I. In cell B13, calculate the total hours worked by summing the value in column G. In cell B14, calculate the total payments by summing the values in column ). In cell B17, calculate the amount of federal tax by multiplying the Total Pay value in cell B14 by the appropriate federal tax rate for an employee with the marital status in cell B7 and withholding allowances in cell B9. (Hint: Use the VLOOKUP function with an exact match lookup for the lookup table in the range L6:W8. For the Col_index_num argument, use the value of cell B9 plus 2.) In cell B18, calculate the Social Security tax equal to the value of cell B14 multiplied by the tax rate in cell M16. File Home Insert Page Layout Formulas Data Review View Help Tell me what you want to do O 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 Editing A1 Capshaw Family Dentistry B D E F H 1 Capshaw Family Dentistry 2 Estimate of Take Home Pay Date Overtime Payment 4 Personal Information 5 Name 6 Hourly Rate 7 Federal Marital Status 8 State Marital Status 9 Withholding Allowances 10 11 Total Straight Time Hours 12 Total Overtime Hours 13 Total Hours 14 Total Pay 15 16 Deductions 17 Federal Income Tax 18 Social Security 19 Medicare 20 Georgia Income Tax 21 22 Total Deductions 23 Total Withholding Rate 24 Take Home Pay 25 Documentation Work Schedule Clock in 8:00 AM 8:00 AM 8:30 AM 8:00 AM 10:00 AM 8:00 AM 11:30 AM 8:00 AM 8:00 AM 10:45 AM Working Schedule Clock Out Total Hours Straight Time 3:30 PM 1:00 PM 5:00 PM 10:30 AM 7:00 PM 3:00 PM 5:45 PM 6:30 PM 2:30 PM 3:30 PM Type here to search ORI L M N o P Q 20 S T U V w Withholding Single Married Supplemental 0 10.92% 5.65% 25.00% 7.88% 3.62% 25.00% 2 4.85% 1.60% 25.00% Federal Tax Rate 3 4 2.76% 0.74% 0% 25% 25% 0% 5 0% 0% 25% 6 0% 0% 25% 7 096 0% 25% 8 0% 0% 25% 9 0% 0% 25% 10 0% 0% 25% 8 9 Withholding Single Married 1-working Married 2-working 0 4.34% 3.77% 4.43% 1 3.43% 2.86% 3.52% 2 2.52% 1.95% 2.61% State & Local Tax Rate 3 5 1.61% 0.7796 0.22% 1.15% 0.52% 0.13% 1.70% 0.80% 0.17% 6 0% 0% 0% 7 0% 0% 0% 0% 0% 0% 0% 0% 0% 10 0% 0% 0% Social Security Tax Rate Medicare 6.20% 1.45%

Answer & Explanation Solved by verified expert

See Answer