Table 10.1
Payroll Component	Formula	Comments
Regular pay	(Hours worked *Hourly wage)
Retirement Contribution	(Vlookup function)*Regular Pay	Vlookup is based on the Retirement Scale assumption box 2. Use absolute reference for the Retirement Contribution scale.
Pay after pretax deductions	Regular Pay-Retirement Contribution
Social Security	Pay after pretax deductions*Social Security Tax	Use absolute reference for the Social Security Tax
Federal Tax	If Statement=Logical test: Pay after pretax deduction*52>Annual Federal income tax baseline (assumption box 1) True: Pay after pretax deduction*Assumption box 1, Tax bracket 1 False: Pay after pretax deduction * Assumption box 1, Tax Bracket 2	52 weeks in the year determines the employees approximate annual salary
Net Pay	(Pay after deductions)-(Social Security + Federal tax)
Total, Max, Min, Average		Insert the formulas for Total, Average, Maximum, Minimum, statistics for Hours Worked, Regular Pay, and Net Pay in the worksheet

Up the Hill Ba Payroll Report Date Figure 10.1 Pay After Pre-Tax Social Job Position Worked Wage Chef Chef Hours Hourly Federal Regular Pay Retirement Deductions Tax Net Pay 6 Jack Gr 7 Susan Gr s #1 Tax Information 45 S 15.00 675.00 45 S 15.00 S 675.00 Ass 6.20% Social Security Tax Federal Tax Annual Income Baseline Income Tax Bracket #1 Income Tax Bracket #2 35 S 10.00 $ 350.00 33 S 10.00 $ 330.00 32 $ 10.00$ 320.00 23 S 7.00 161.00 30 S 7.00 210.00 341 S 7.00 238.00 $ 34,500.00 8 Mary Wright 9 John Berry 10 Collyn Bear 11 |Chris Hill 12 Laura Godwin 13 Samantha Collins Wait Staff Chef Cashier Cashier Wait Staff Wait Staff Ass ion Box #2 Retirement Contribution Scale Retirement Percentage S 7.00 | $ 175.00 28 S8.00$ 224.00 42 S 8.00 336.00 36 S 15.00 540.00 50 S 15.00 $ 750.00 Regular Pa 14 IEd L 15 Steve Little 16 Mike Nitsch 17 Gwen ManchesterMana 18 Stephanie Baker Mana 19 20 Total 21 Average 22 Maximum 23 Minimum Wait Staff 400.00 800.00 Driver 3%