(1.1) Enter the hours worked and the pay rate for the employee into the Paycheck Calculations section of the model. a. Reference the hours worked ( C3) and pay rate ( (4) ) values in the "Employee Information" area of the spreadsheet model. 1.2 Calculate the regular pay. a. Reference cell C21) for the "Hours Worked". b. The regular pay will be the hours worked times the pay rate unless the employee works overtime (more than the number of regular hours in the pay period - described in the model assumptions). c. If the employee works overtime, the regular pay is the rate times the number regular hours in the pay period. (1.3) Calculate the overtime pay. a. Reference cell C21) for the "Hours Worked". b. The employee is paid 1.5 times the regular pay rate for any time the employee works more than the number regular hours in pay period. (1.4) Calculate the total gross pay. - The total gross pay is the sum of the regular pay and the overtime pay. (1.5) Reference the health insurance deduction. a. Reference the appropriate cell in the employee information section of the model for the health insurance deduction. b. Notice the amount in this section is already calculated for each paycheck. (1.6) Calculate the flexible spending deduction. a. Reference the appropriate cell in the employee information section of the model. b. Notice that the flexible spending deduction is an annual rate. c. You will need to divide this by the number of paychecks per year in the Model Assumptions section of the model. (1.7) Calculate the retirement savings deduction. - The retirement savings deduction is the total gross pay times the retirement savings percentage for the employee. (1.8) Calculate the total deductions. - The total deductions equals the sum of the insurance, flexible spending, and retirement savings deductions. (1.9) Calculate the adjusted income. - The adjusted income is the difference between the total gross pay and the total deductions. (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. (1.11) Calculate the federal income tax. - The federal income tax is the adjusted income times the federal income tax rate calculated in cell (36). (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. a. 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). (1.13) Calculate the net federal income tax. a. The net federal income tax is the difference between the federal income tax and the adjustment for allowances. b. If the adjustment for allowances is greater than the federal income tax, then the net federal income tax is zero (0). 1.14 Calculate the state income tax. - The state tax is the adjusted income times the state tax rate in the Model Assumptions section. (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. 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. 1.17 Calculate 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. (1.1) Enter the hours worked and the pay rate for the employee into the Paycheck Calculations section of the model. a. Reference the hours worked ( C3) and pay rate ( (4) ) values in the "Employee Information" area of the spreadsheet model. 1.2 Calculate the regular pay. a. Reference cell C21) for the "Hours Worked". b. The regular pay will be the hours worked times the pay rate unless the employee works overtime (more than the number of regular hours in the pay period - described in the model assumptions). c. If the employee works overtime, the regular pay is the rate times the number regular hours in the pay period. (1.3) Calculate the overtime pay. a. Reference cell C21) for the "Hours Worked". b. The employee is paid 1.5 times the regular pay rate for any time the employee works more than the number regular hours in pay period. (1.4) Calculate the total gross pay. - The total gross pay is the sum of the regular pay and the overtime pay. (1.5) Reference the health insurance deduction. a. Reference the appropriate cell in the employee information section of the model for the health insurance deduction. b. Notice the amount in this section is already calculated for each paycheck. (1.6) Calculate the flexible spending deduction. a. Reference the appropriate cell in the employee information section of the model. b. Notice that the flexible spending deduction is an annual rate. c. You will need to divide this by the number of paychecks per year in the Model Assumptions section of the model. (1.7) Calculate the retirement savings deduction. - The retirement savings deduction is the total gross pay times the retirement savings percentage for the employee. (1.8) Calculate the total deductions. - The total deductions equals the sum of the insurance, flexible spending, and retirement savings deductions. (1.9) Calculate the adjusted income. - The adjusted income is the difference between the total gross pay and the total deductions. (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. (1.11) Calculate the federal income tax. - The federal income tax is the adjusted income times the federal income tax rate calculated in cell (36). (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. a. 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). (1.13) Calculate the net federal income tax. a. The net federal income tax is the difference between the federal income tax and the adjustment for allowances. b. If the adjustment for allowances is greater than the federal income tax, then the net federal income tax is zero (0). 1.14 Calculate the state income tax. - The state tax is the adjusted income times the state tax rate in the Model Assumptions section. (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. 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. 1.17 Calculate 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