You work in the human resources department of your company helping new employees fill out the necessary paperwork to get their first paycheck. There are a number of decisions that employees must make when they complete this paperwork including: (1) which health insurance package to buy (this impacts how much money will be deducted each pay period to pay for the premium), (2) how much money to put in a flexible spending account annually to cover medical and childcare expenses, and (3) the percentage of their regular paychecks to invest in the company's 401k retirement savings plan. The expenses paid for these three items are not subject to Federal or State income taxes. Each new employee must also complete a W4 form where they claim their marital status and number of allowances for deductions for tax purposes. Often these employees will ask you to project their first paycheck so that they know how much money to plan on for their personal budget. You have decided to create a spreadsheet model that will make these projections given the information the new employee provides on their paperwork. Task # Points Task Description Enter the hours worked and pay rate for the employee paycheck calculations section of the model. Reference the appropriate cells in the "Employee Information" area of the spreadsheet model. 1 2 Calculate the regular pay. The regular pay will be the hours worked times the pay rate unless the employee works overtime (more than the # of regular hours in the 3 pay period - described in the model assumptions). If they work overtime, the reqular pay is the rate times the # of regular hours worked in the pay period. 3 3.5 Calculate the overtime pay. The employee is paid 1.5 times their reqular pay rate for any time they work more than # of reqular hours worked in the pay period. 4 2.5 Calculate total gross pay. The total gross pay is the sum of the regular pay and the overtime pay. Calculate the insurance premium deduction by referencing the appropriate cell in the employee information section of the model. Notice the amount in this section is already calculated for each paycheck 5 2.5 Calculate the flexible spending deduction by referencing the appropriate cell in the employee information section of the model. Notice that the flexible spending deduction is an annual rate. You will need to divide this by the number of paychecks per year in the assumptions section of the model. 6 2.5 2.5 Calculate the retirement savings deduction. The retirement savings deduction is the total gross pay times the retirement savings % for the employee. 8 2.5 Calculate the total deductions. The total deductions equal the sum of the insurance, flexible spending, and retirement savings deductions. 9 2.5 Calculate the adjusted income. The adjusted income is the difference between the total gross income and the total deductions. 4 2.5 Calculate total gross pay. The total gross pay is the sum of the regular pay and the overtime pay. Calculate the insurance premium deduction by referencing the appropriate cell in the employee information section of the model. Notice the amount in this section is already calculated for each paycheck 5 2.5 Calculate the flexible spending deduction by referencing the appropriate cell in the employee information section of the model. Notice that the flexible spending deduction is an annual rate. You will need to divide this by the number of paychecks per year in the assumptions section of the model. 2.5 7 2.5 Calculate the retirement savings deduction. The retirement savings deduction is the total gross pay times the retirement savings % for the employee. 8 2.5 Calculate the total deductions. The total deductions equal the sum of the insurance, flexible spending, and retirement savings deductions. 2.5 Calculate the adjusted income. The adjusted income is the difference between the total gross income and the total deductions. Calculate the federal tax rate. The federal tax rate is a function of the adjusted income and the employee's marital status. Reference the tax tables in the model assumptions and the marital status in the employee information section to construct a formula (or set of nested formulas) to calculate the tax rate. 10 7 11 2.5 Calculate the federal income tax. The federal income tax is the adjusted income times the federal income tax rate you calculated in task 10. 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. 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 tax rate (calculated in task 10). 12 2.5 Calculate the net federal income tax. The net federal income tax is the difference between the federal income tax and the adjustment for allowances. If the adjustment for allowances is greater than the federal income tax then the net federal income tax is zero (0). 13 4.5 14 2.5 Calculate the state income tax. The state tax is the adjusted income times the state tax rate in the model assumptions. 15 2.5 Calculate the medicare tax. The medicare tax is the total gross income times the medicare tax rate in the model assumptions 16 2.5 Calculate the social security tax. The social security tax is the total gross income times the social security tax rate in the model assumptions. 17 2.5 Calculate total taxes. Total taxes are the sum of the net federal income tax, state income tax, medicare, and social security taxes. B E F G H I J K M 2 Employee Information Model Assumptions Hours Worked Pay Rate Marital Status # of Allowances Retirement Savings % Health Insurance (every paychecl $125. 00 Flexible Spending (Annual) 85 $30. 00 # regular hours in Pay Per # of paychecks per year 4 80 Federal Tax Rate Tables 5 Married 26 "Single" Tax Rate Married" Tax Rat 2 Income Rate Income Rate $0 $0 $1, 450 $5, 892 $11, 892 7 5% Payroll Tax information 10% 10% Medicare Rate Social Security Rate 8 1. 45% $725 15% 15% $2, 946 $7, 138 9 #### 4. 2% 25% 25% 10 28% 28% 11 Paycheck Summary Income Tax Information $14, 888 33% $18, 121 33% Gross Pay Total Deductions Ad justed Income Total Taxes $0. 00 $0. 00 12 Allowance Deduction $146 $32, 363 35% $32, 363 35% 13 State Tax Rate 5% $0. 00 $0. 00 14 15 Paycheck Calculator 16 complete the necessary paperwork to get their first paycheck. There are a number of decisions that employees must make when they complete this paperwork including: (1) which health insurance package to buy (this impacts how much money will be deducted each pay period to pay for the premium), (2) how much money to put in a flexible spending account annually to cover medical and childcare expenses, and (3) the percentage of their regular paychecks to invest in the company's 401k retirement savings plan. The expenses paid for these three items are not subject to Federal or State income taxes. Each new employee must also complete a W4 form where they claim their marital status and number of allowances for deductions for tax purposes. Often these employees will ask you to project their first paycheck so that they know how much money to plan on for their 17 Net Pay $0. 00 18 19 Paycheck Calculations 20 Income 21 Hours Worked Pay Rate Regular Pay 22 23 Overtime Pay Total Gross Pay 24 25 26 27 Deductions (Items not subject to income tax) personal budget. You have decided to create a spreadsheet model that will make 28 Insurance these projections given the information the new employee provides on their Deductions (Items not subject to income tax) personal budget. You have decided to create a spreadsheet model that will make these projections given the information the new employee provides on their 27 28 Insurance Flexible Spending Retirement 29 Total Deductions Adjusted Income 34 |es Federal Income Tax Rate Federal Income Tax Adjustment for Allowances Net Federal Income Tax State Income Tax Medicare Tax Social Security Tax 35 36 37 39 39 40 41 42 43 Total Taxes