Design and create a workbook to keep track of the payroll for hourly employees.

Question

Design and create a workbook to keep track of the payroll for hourly employees. Each worksheet will correspond to a different week. You have six employees. List them in a column. Each employee makes a different amount per hour. List the hourly pay for each employee in a new column. Now add a column for each of the seven days of the week. In these columns enter the number of hours worked by each employee each day that week. To the right add columns that give the total number of hours worked by each employee and the gross pay for each employee. The federal government asks you to deduct 20% of gross pay for federal income taxes. Your state or province asks you to deduct 5% of income taxes. Add columns for these. Social Security deduction is 62% of gross pay. The deduction for medical insurance is $30 per week. Add columns for these. Each employee's take home pay is the gross pay minus the deductions. Add a column for take home pay. Add a row to total all of the columns. Add appropriate cells with headings for the worksheet. gross pay for state Now use the worksheet you just created for one week's payroll to create a workbook for a month (four weeks) You should have an initial worksh information. There should be one workshect for each week. Employees may work different hours from week to week. The final worksheet should be the totals for the four weeks. There should be six worksheets in all. eet that gives a title and on.

Explanation / Answer

I AM NOT ABLE TO UPLOAD THE SHEETS ..

BUT DETAILS OF EACH OF SIX SHEETS ARE GIVEN BELOW ..

IT HAS WRITTEN PROPERLY AS PER THE QUESTIONS GUIDELINES..

DATA ABOUT EACH OF THE EMPLOYEES HAVE BEEN DISPLAYED ..

THE EMPLOYEES HAVE HAVE RESPECTIVELY MATCHED WITH HOURS OF PAY..

AFTER THAT HOURS WORKED EACH DAY HAS BEEN INPUTED

THEN TOTAL NO. OF HOURS IS CALCULATED..

THEN TOTAL PAY IS DEFINED AS THE THE PAY PER HOURS INTO THE NO. OF HOURS..

THEN THE DEDUCTION IS CALCULATED AND ALL THE DEDUCTIONS ARE SUMED UP...

ACTUAL PAY IS DEFINED AS THE TOTAL PAY MINUS THE DEDUCTIONS ..

THIS DONE FOUR TIMES FOR EACH OF THE WEEKS..

PAYROLL STRUCTURE FOR EMPLOYEES(WEEK 1)

THEN TOTAL NO. OF HOURS IS CALCULATED..

THEN TOTAL PAY IS DEFINED AS THE THE PAY PER HOURS INTO THE NO. OF HOURS..

THEN THE DEDUCTION IS CALCULATED AND ALL THE DEDUCTIONS ARE SUMED UP...

ACTUAL PAY IS DEFINED AS THE TOTAL PAY MINUS THE DEDUCTIONS ..

THIS DONE FOUR TIMES FOR EACH OF THE WEEKS..

PAYROLL STRUCTURE FOR EMPLOYEES(WEEK 1)

EMPLOYEES PAY/HOUR($) DAYS OF THE WEEK (HRS WORKED)                       TOTAL NO. OF HRS WORKED GROSS PAY($) DEDUCTIONS TAKE HOME PAY MONDAY TUESDAY WEDNENSDAY THURSDAY FRIDAY SATURDAY SUNDAY FED. GOV (20%) STATE GOV.(5%) SOCIAL SECURITY(6.2%) MEDICAL(30$) (GR.PAY-DEDUCTION) EMP A 10 2 5 6 3 9 7 1 33 330 66 16.5 20.46 30 197.04 EMP B 20 9 10 13 11 12 5 2 62 1240 248 62 76.88 30 823.12 EMP C 25 8 4 14 13 20 8 1 68 1700 340 85 105.4 30 1139.6 EMP D 30 9 3 2 15 7 9 2 47 1410 282 70.5 87.42 30 940.08 EMP E 40 5 5 7 8 5 7 3 40 1600 320 80 99.2 30 1070.8 EMP F 45 6 2 9 8 7 5 1 38 1710 342 85.5 106.02 30 1146.48 PAYROLL STRUCTURE FOR EMPLOYEES(WEEK 2) EMPLOYEES PAY/HOUR($) DAYS OF THE WEEK (HRS WORKED)                       TOTAL NO. OF HRS WORKED GROSS PAY($) DEDUCTIONS TAKE HOME PAY MONDAY TUESDAY WEDNENSDAY THURSDAY FRIDAY SATURDAY SUNDAY FED. GOV (20%) STATE GOV.(5%) SOCIAL SECURITY(6.2%) MEDICAL(30$) (GR.PAY-DEDUCTION) EMP A 11 4 5 2 13 3 7 1 35 385 77 19.25 23.87 30 234.88 EMP B 22 9 10 13 11 12 4 2 61 1342 268.4 67.1 83.204 30 893.296 EMP C 32 7 4 14 22 20 4 1 72 2304 460.8 115.2 142.848 30 1555.152 EMP D 42 7 5 2 2 7 4 4 31 1302 260.4 65.1 80.724 30 865.776 EMP E 34 8 5 6 8 5 7 3 42 1428 285.6 71.4 88.536 30 952.464 EMP F 67 6 9 4 8 7 5 1 40 2680 536 134 166.16 30 1813.84 PAYROLL STRUCTURE FOR EMPLOYEES(WEEK 3) EMPLOYEES PAY/HOUR($) DAYS OF THE WEEK (HRS WORKED)                       TOTAL NO. OF HRS WORKED GROSS PAY($) DEDUCTIONS TAKE HOME PAY MONDAY TUESDAY WEDNENSDAY THURSDAY FRIDAY SATURDAY SUNDAY FED. GOV (20%) STATE GOV.(5%) SOCIAL SECURITY(6.2%) MEDICAL(30$) (GR.PAY-DEDUCTION) EMP A 12 11 5 6 3 9 7 1 42 504 100.8 25.2 31.248 30 316.752 EMP B 23 9 7 22 11 12 7 2 70 1610 322 80.5 99.82 30 1077.68 EMP C 34 22 3 6 13 8 8 1 61 2074 414.8 103.7 128.588 30 1396.912 EMP D 32 9 3 2 15 7 9 2 47 1504 300.8 75.2 93.248 30 1004.752 EMP E 11 5 2 7 19 5 7 3 48 528 105.6 26.4 32.736 30 333.264 EMP F 45 6 2 9 8 7 5 1 38 1710 342 85.5 106.02 30 1146.48 PAYROLL STRUCTURE FOR EMPLOYEES(WEEK 4) EMPLOYEES PAY/HOUR($) DAYS OF THE WEEK (HRS WORKED)                       TOTAL NO. OF HRS WORKED GROSS PAY($) DEDUCTIONS TAKE HOME PAY MONDAY TUESDAY WEDNENSDAY THURSDAY FRIDAY SATURDAY SUNDAY FED. GOV (20%) STATE GOV.(5%) SOCIAL SECURITY(6.2%) MEDICAL(30$) (GR.PAY-DEDUCTION) EMP A 13 4 4 5 3 9 7 1 33 429 85.8 21.45 26.598 30 265.152 EMP B 14 9 10 13 5 4 5 2 48 672 134.4 33.6 41.664 30 432.336 EMP C 25 8 4 6 3 20 8 4 53 1325 265 66.25 82.15 30 881.6 EMP D 16 4 3 22 15 7 6 2 59 944 188.8 47.2 58.528 30 619.472 EMP E 11 5 22 7 8 5 6 4 57 627 125.4 31.35 38.874 30 401.376 EMP F 45 4 2 9 8 7 5 1 36 1620 324 81 100.44 30 1084.56 PAYROLL STRUCTURE FOR EMPLOYEES(ALL 4 WEEKS) EMPLOYEES AVG. PAY/HOUR($) DAYS OF THE WEEK (AVG .HRS WORKED)                       TOTAL NO. OF HRS WORKED GROSS PAY($)(AVG.) DEDUCTIONS TAKE HOME PAY(AVG) MONDAY TUESDAY WEDNENSDAY THURSDAY FRIDAY SATURDAY SUNDAY FED. GOV (20%) STATE GOV.(5%) SOCIAL SECURITY(6.2%) MEDICAL(30$) (GR.PAY-DEDUCTION) EMP A 46 21 19 19 22 30 28 22 161 7406 1481.2 370.3 459.172 30 5065.328 EMP B 79 36 37 61 38 40 21 38 271 21409 4281.8 1070.45 1327.358 30 14699.392 EMP C 116 45 15 40 51 68 28 51 298 34568 6913.6 1728.4 2143.216 30 23752.784 EMP D 120 29 14 28 47 28 28 47 221 26520 5304 1326 1644.24 30 18215.76 EMP E 96 23 34 27 43 20 27 43 217 20832 4166.4 1041.6 1291.584 30 14302.416 EMP F 202 22 15 31 32 28 20 32 180 36360 7272 1818 2254.32 30 24985.68

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.