Review View Normal Normal IndentStrong As manager of the gift shop at the Metrap

Question

Review View Normal Normal IndentStrong As manager of the gift shop at the Metrapolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to indude total pay! columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas For the purpose of grading the project you are required to perform the following tasks: Points Step 0.000 Use IF functions to calculate the regular pay and overtime pay based on a regular 40-hour workoweek in cells E5 and F5. Pay overtime only for overtime hours. In cell G5, cakulate the gross pay based on the regular and overtime pay. Abram's regular pay is $398. With8 20.000 overtime hours, Abram's overtime pay s $119.40 Create a formula in cell HS to calculate the taxable pay. Multiply the number of dependents two dependents, Abram's tamable pay is $417.40 Use a VLOOKUP function in cell IS to identbity and cakculate the federal withholding tax. Use which you must then multiply by the taxable pay 3 (column B) by the deduction per dependent (B24) and subtract that from the gross pay. With 10.000 4 the tax rates from the range D21:E25. The VLOOKUP function returns the applicable tax rate,20.000 Calculate FICA in cell J5 based on gross pay and the FICA rate (cell 823), and calculate the net pay in cell K5.Copy all formulas down their respective columns to row 16 10.000 With the rape ESK?6 overtime pay, gross pay, taxable pay, withholding tax,FICA, and net pay on row 17 Analysis tools to calculate the total regular pay 10.000 Note, Mac users, with the range selected, on the Home tab, in the Editing group, click Accounting Number Format to the range C5:C16. Apply Accounting Number Format to the first row of monetary data and to the total row. Apply the Comma style to the monetary 10.000 values for the other employees, Underline the last employee's monetary values and use the Format Cels dalog box to apply Top and Double Bottom border for the totals Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (the range 121:23) of the worksheet. Format the e of hours calculations as General number format with one decimal and the remaining calculations with Accounting number formet. 15.000 9 Insert a footer with your name on the left side, the sheet name code in the center, and the le name code on the right side of the worksheet 5.000 Points 10 |Save and doe ?e worito k, sanit the fie as anded. 0.000 Tetal Points 100.000 MacBook Pro

Explanation / Answer

Names were not clearly visible so sorry for that. Column no. and row no. are written in the tables and the formulas used are written in the last. Just drag the formula used till the last employee. I has created a excel file too but not able to share that with you.

357.30

Formulas Used:

E5: IF(D5<=40,D5*C5,40*C5) = Regular pay

F5: IF(D5>40,(D5-40)*(1.5*C5),0) = Overtime Pay

G5: E5+F5 = Gross Pay

H5: G5-(B5*$B$24) = Taxable Pay

I5: IF(H5<250,0.22,IF(H5<300,0.25,IF(H5<450,0.28,0.31)))*H5 = Federal Withholding Tax

J5: G5*7.65% = FICA

K5: G5-I5-J5 = Net Pay

Name(A) No. of Dependents(B) Hourly Wage(C) Hours Worked(D) Regular Pay(E) Overtime Pay(F) Gross Pay(G) Taxable Pay(H) Federal Withholding Tax(I) FICA(J) Net Pay(K) Abram(1) 2 9.95 48 398 119.4 517.4 417.4 116.87 39.58 360.95 Acoqla(2) 1 9.55 48 382 114.6 496.4 446.4 125.05 37.99 333.56 Bond(3) 3 11.75 50 470 176.25 646.25 496.25 153.84 49.44 442.97 Higinbotham(4) 1 11.75 35 411.25 0 411.25 361.25 101.15 31.46 278.64 Ianzil(5) 1 10 40 400 0 400 350 98 30.6 271.4 Jausal(6) 1 9.55 44 382 57.30 439.30 389.30 109 33.61 296.69 Ling(7) 3 15.75 40 630 0 630 480 148.80 48.20 433.01 Mumoz(8) 2 12.25 20 245 0 245 145 31.90 18.74 194.36 Riltry(9) 3 13.35 38 507.30 0 507.30

357.30

100.04 38.81 368.45 Sabey(10) 2 10 45 400 75 475 375 105 36.34 333.66 Trujiio(11) 1 9.95 15 149.25 0 149.25 99.25 21.84 11.42 116 Wieston(12) 2 11.75 41 470 17.63 487.63 387.63 108.54 37.30 341.79 Totals(13) 4844.80 560.18 5404.98 4304.98 1220.03 413.48 3771.47

Related Questions

Navigate

• Browse (All)
• Browse R
• Subjects

---

---

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