Paycheck Calculator

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 $401$k retirement savings plan. The expenses paid for these three items are not subject to Federal or State income taxes.

New employees must also complete a W$4$ 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 the paperwork.

Note: Because the employee information will change as you work with different employees, your solution should be designed to handle changing employee information. For example, the current employee has worked overtime $($the employee has worked $85$ hours, and a regular work period has $80$ hours$).$ Your solution should calculate this employee's pay information and correctly handle an employee who does not work overtime.

Enter the hours worked and the pay rate for the employee into the Paycheck Calculations section of the model.

Reference the hours worked $($C$3)$ and pay rate $($C$4)$ values in the "Employee Information" area of the spreadsheet model.

$2$

Calculate the regular pay.

Reference cell C$21$ for the "Hours Worked".

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$).$

If the employee works overtime, the regular pay is the rate times the number regular hours in the pay period.

$3$

Calculate the overtime pay.

Reference cell C$21$ for the "Hours Worked".

The employee is paid $1\mathrm{.}5$ times the regular pay rate for any time the employee works more than the number regular hours in pay period.

$3\mathrm{.}5$

Calculate the total gross pay.

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

$2\mathrm{.}5$

Reference the health insurance deduction.

Reference the appropriate cell in the employee information section of the model for the health insurance deduction.

Notice the amount in this section is already calculated for each paycheck.

$2\mathrm{.}5$

Calculate the flexible spending deduction.

Reference 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 Model Assumptions section of the model.