C. Home Insert Draw Page Layout X n AutoSave Paste C21 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 A Hours Worked Pay Rate Marital Status # of Allowances Calibri (Body) I U Net Pay B Gross Pay Total Deductions Adjusted Income Total Taxes Income Hours Worked Pay Rate Retirement Savings % Health Insurance (every paycheck) Flexible Spending (Annual) Regular Pay Overtime Pay Total Gross Pay fx Adjusted Income B Pricing Employee Information Paycheck Summary Paycheck Calculations Deductions (Items not subject to income tax) Insurance Flexible Spending Retirement Total Deductions Taxes Federal Income Tax Rate Federal Income Tax Adjustment for Allowances Net Federal Income Tax State Income Tax Medicare Tax Paycheck V Ready Accessibility: Investigate 12 + Formulas V V C A. V 85 $30.00 Married 2 5% $125.00 $1,500.00 $0.00 $0.00 $0.00 $0.00 $0.00 D Data |||| Review E ab # regular hours in Pay Period # of paychecks per year Medicare Rate Social Security Rate View Automate Payroll Tax information Income Tax Information Allowance Deduction State Tax Rate TI F 80 26 1.45% 4.2% ab $146 5% G MyEducator-Add-in.xlsx Model Assumptions General Tell me $ % 9 V H I J "Single" Tax Rate Income Rate $0 10% $725 15% $2,945 25% $7,137 28% $14,887 33% $32,362 35% K .00 0 Federal Tax Rate Tables Read-Only L V Conditional Format Formatting as Table "Married" Tax Rate Income Rate $0 10% $1,450 15% $5,891 25% $11,891 28% $18,120 33% $32,362 35% V M Cell Styles N O Insert Delete Format P WE V V 28.0 V Sort & Filter Find & Select Comments 2.5 888 Share MyEducator Tasks (2.5) 1.9 Calculate the adjusted income. The adjusted income is the difference between the total gross pay and the total deductions. me Sensitivity Analyze Tasks Data B Font Size 1.10 Use an IF function with nested VLOOKUP functions to calculate the Federal Income Tax Rate. a. The federal tax rate is a function of the adjusted income and the employee's marital status. b. Reference the tax tables in the Model Assumptions and the marital status in the Employee Information section to construct a formula (set of nested formulas) to calculate the tax rate. c. For example, a single employee who earned $15,000 would pay a 33% marginal tax rate. 7 1.11) Calculate the federal income tax. The federal income tax is the adjusted income times the federal B 100%