Question

The Excel file Payroll Data provides hourly salaries for a group of employees. Create an Excel template that allows the user to select an employee by employee ID, enter the number of regular hours and overtime hours worked, and display a payroll summary with the employee name, gross pay, federal tax, state tax, Social Security, Medicare withholding deductions, and net pay. Assume that the federal tax rate is 11%, the state tax rate is 2.385%, Social Security withholding is 6.2%, and Medicare withholding is 1.45%. Use a form control to select the employee ID.

Here what the excel workbook look like:

.

Rate Model Parameters Federal Tax State Tax Social Security deduction Medicare deduction 11.00% 2.39% 6.20% 1.457 Model lapets Employee ID Regular Hours Overtime Hours sters Hodel Results and Outputs Nams Regular Pay Overtime Pay Grogo Pay Federal Tax State Tax Social Security deduction Medicare deduction Het Par Overtime Rate $18.00 $15.75 $22.88 $17.25 Model Data Employee ID Name Hourly Rat 1001 John Smit $12.00 1002 Craig Joh $10.50 1003 Jessica M 15.25 1004 Emily Mai $11.50 1005 Tom Wile $12.00 1006 Meliaga E $8.25 1007 Elisabeth $14.60 1008 Jacob D $13.55 1009 Austin An 19.20 1010 Angie Cla $14.65 1011 Alison Les $8.80 1012 Gary Hall $10.20 1013 David Ro $13.65 1014 Lec Collin $12.30 $18.00 $1238 $21.30 $20.33 $13.80 $21.98 $13.20 $15.30 $20.48 $18.45

I just don't know where to start on this problem. Any help would be appreicated

Answer 1

This require use of simple basic formula of addition of cell, Multiplication of cells, subtract & most important formula is Vlookup.

Vlookup function can be used to value from table on the base of one common value. Parameter have employee ID as a input whereas data table also have employee ID, hence through vlookup we can bring name, regular hourly rate etc. (For detail on how to use Vlookup you might have to refer online information available)

Below is the solution where i have provided brief explanation of excel formula used :

Model Parameter	Rate
Federal Tax	11%
State Tax	2.39%
Social Security Deduction	6.20%
Medicate deduction	1.45%
Model Inputs	Values
Employee ID	1,007
Regular Hours	40.00
Overtime Hours	10.00
Model Result & Output	Values	Explanation of Excel Formula / basis
Name	Elizabeth	Use Vlookup considering employee ID and bring Name from table
Regular Pay	584.00	Use Vlookup considering employee ID to fetch Regular Hourly rate & Multiply with Regular Hours input
Overtime Pay	876.00	Use Vlookup considering employee ID to fetch Overtime Hourly rate & Multiply with Overtime Hours input
Gross Pay	1,460.00	Use excel formula to add Regular Pay & Gross Pay
Federal Tax	160.60	Use base i.e. Gross Pay and Multiply with respective rate to get deduction
State Tax	34.89
Social Security Deduction	90.52
Medicate deduction	21.17
Net Pay	1,152.82	Reduce all deduction from Gross Pay

Below is excel worksheet along with Formula :

Model Parameter	Rate
Federal Tax	0.11
State Tax	0.0239
Social Security Deduction	0.062
Medicate deduction	0.0145
Model Inputs	Values
Employee ID	1007
Regular Hours	40
Overtime Hours	10
Model Result & Output	Values
Name	=VLOOKUP(C10,A29:E33,2,0)
Regular Pay	=VLOOKUP(C10,A29:E32,3,0)*C11
Overtime Pay	=VLOOKUP(C10,A29:E32,4,0)*C11
Gross Pay	=SUM(C16:C17)
Federal Tax	=C18*C4
State Tax	=C18*C5
Social Security Deduction	=C18*C6
Medicate deduction	=C18*C7
Net Pay	=C18-SUM(C19:C22)

Data Table

Employee ID	Name	Hourly Rate	Overtime Rate
1001	John Smith	12	18
1007	Elizabeth	14.6	21.9

The Model parameter value is appearing form Cell B3:C7, Model Input at B9:C12 & Model Result from B14:C23. In the excel worksheet the data table appears at A28:D30.