help solve step by step n excel ( DUE IN 2 hours) thank you

Calculate the total gross pay.

• The total gross pay is the sum of the regular pay and the overtime pay.

2.5

1.5

Reference the health insurance deduction.

1. Reference the appropriate cell in the employee information section of the model for the health insurance deduction.
2. Notice the amount in this section is already calculated for each paycheck.

2.5

Prior submission results: [-1.5] The value in cell C28 is not correct [-1] The formula in cell C28 does not reference C8

1.6

Calculate the flexible spending deduction.

1. Reference the appropriate cell in the employee information section of the model.
2. Notice that the flexible spending deduction is an annual rate.
3. You will need to divide this by the number of paychecks per year in the Model Assumptions section of the model.

2.5

1.7

Calculate the retirement savings deduction.

• The retirement savings deduction is the total gross pay times the retirement savings percentage for the employee.

2.5

1.8

Calculate the total deductions.

• The total deductions equals the sum of the insurance, flexible spending, and retirement savings deductions.

2.5

1.9

Calculate the adjusted income.

• The adjusted income is the difference between the total gross pay and the total deductions.

2.5

1.10

Use an IF function with nested VLOOKUP functions to calculate the Federal Income Tax Rate.

1. The federal tax rate is a function of the adjusted income and the employee's marital status.
2. 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.
3. 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 income tax rate calculated in cell C36.

2.5

1.12

Calculate the tax adjustment for allowances. An employee will have less federal income tax withheld for every allowance that they claim. Allowances account for the number of dependents they will claim on their taxes and other factors that will ultimately reduce the amount of federal taxes they will pay.

1. The adjustment for allowances is calculated as the product of the number of allowances (in the employee information section), the allowance deduction amount (in the model assumptions), and the employee Federal Income Tax rate (calculated in task 10).

2.5

1.13

Calculate the net federal income tax.

1. The net federal income tax is the difference between the federal income tax and the adjustment for allowances.
2. If the adjustment for allowances is greater than the federal income tax, then the net federal income tax is zero (0).

4.5

1.14

Calculate the state income tax.

• The state tax is the adjusted income times the state tax rate in the Model Assumptions section.

2.5

1.15

Calculate the medicare tax rate.

• The medicare tax rate is the total gross pay times the medicare tax rate in the Model Assumptions section.

2.5

1.16

Calculate the social security tax.

• The social security tax is the total gross pay times the social security tax rate in the Model Assumptions section.

2.5

1.17Calculate the total taxes.

• The total taxes is the sum of the net federal income tax, the state income tax, the medicare tax, and social security tax.

SECURITY RISK Microsoft has blocked macros from running beca B C D $0.00 Paycheck Calculations Deductions (Items not subject to income tax) Insurance Flexible Spending Retirement Total Deductions Adjusted Income Taxes Federal income Tax Rate Federal Income Tax Adjustment for Allowances Net Federal Income Tax State Income Tax Medicare Tax Social Security Tax Total Taxes SECURITY RISK Microsoft has blocked macros from running beca B C D $0.00 Paycheck Calculations Deductions (Items not subject to income tax) Insurance Flexible Spending Retirement Total Deductions Adjusted Income Taxes Federal income Tax Rate Federal Income Tax Adjustment for Allowances Net Federal Income Tax State Income Tax Medicare Tax Social Security Tax Total Taxes