COMPUTER 6
You work for a local construction firm “
Bass Engineering Group
” and your supervisor wants to test your knowledge and skills with Microsoft Excel and has instructed you to develop a spreadsheet to calculate weekly payroll. To do this, follow the steps below.
For row 2, you will enter labels. For example:
In cell A2, input “
EFN
” (Reference: EFN stands for Employee First Name)
In cell B2, input “
ELN
” (Reference: EFN stands for Employee Last Name)
In cell C2, input “HR” (Reference: HR stands for Hourly Rate)
In cell D2, input “
OHR
” (Reference: OHR stands for Overtime Hourly Rate)
In cell E2, input “
THW
” (Reference: THW stands for Total Hours Worked)
In cell F2, input “
OHW
” (Reference: OHW stands for Overtime Hours Worked)
In cell G2, input “GP” (Reference: GP stands for Gross Pay)
In cell H2, input “
SST
” (Reference: SST stands for Social Security Tax)
In cell I2, input “
FWT
” (Reference: FWT stands for Federal Withholding Tax)
In cell J2, input “
SWT
” (Reference: SWT stands for State Withholding Tax)
In cell K2, input “NP” (Reference: NP stands for Net Pay)
Bass Engineering Group
EFN
ELN
HR
OHR
THW
OHW
GP
SST
FWT
SWT
NP
John
Doe
Sally
Doe
Ferris
Doe
Peter
Doe
Jessica
Doe
Tammy
Doe
Julie
Doe
Michael
Doe
Jason
Doe
Buck
Doe
Tula
Doe
Heather
Doe
Gary
Doe
Under the “OHR” label beginning with cell D3 and ending with D15, use a formula to calculate the overtime hourly rate of Time and Half. For example, in cell D3, you will enter “
=C3*1.5
” and in cell D4, you will enter “
=C4*1.5
” and in cell D5, you will enter “
=C5*1.5
” and follow the same pattern entering the rest of the formulas through cell D15 and pay close attention to the cell references. (Hint: Once the first 3 formulas above are entered, select these cells holding these formulas using your mouse and then in cell D5, use the fill handle in the bottom right corner of this cell to drag and fill the rest of the formulas automatically through D15.)
Under the “OHW” label beginning with cell F3 and ending with cell F15, you will use the IF function to extract any overtime hours from the total hours worked entered from step 16. For example, in cell F3, you will enter the following formula: “
=IF(E3>40, E3-40,0)
”. (Note: An “IF” function is a conditional formula that is looking for a condition that is true or false based on parameters set. In this case, the condition is E3>40 and if cell E3 has value greater than 40, then the condition is true thus the nested formula E3-40 will execute to output the remaining hours as overtime hours. If cell E3 is below 40, then the condition is false and no overtime hours are outputted thus 0 will be the output.)
For step 17, pay close attention to cell references as well. For example, in cell F4, you will enter the following formula: “
=IF(E4>40, E4-40,0)
” and in cell F5, you will enter the following formula: “
=IF(E5>40, E5-40,0)
”. See the illustration below for visual guidance and follow the pattern to enter the remaining “IF” functions through cell F15. (Hint: Using the fill handle learned from step 15 will work in this case as well.)
EFN
ELN
HR
OHR
THW
OHW
John
Doe
15
=C3*1.5
33
=IF(E3>40, E3-40,0)
Sally
Doe
17
=C4*1.5
35
=IF(E4>40, E4-40,0)
Ferris
Doe
19
=C5*1.5
37
=IF(E5>40, E5-40,0)
Peter
Doe
21
=C6*1.5
39
=IF(E6>40, E6-40,0)
Jessica
Doe
23
=C7*1.5
41
=IF(E7>40, E7-40,0)
Tammy
Doe
25
=C8*1.5
43
=IF(E8>40, E8-40,0)
Julie
Doe
27
=C9*1.5
45
=IF(E9>40, E9-40,0)
Michael
Doe
29
=C10*1.5
12
=IF(E10>40, E10-40,0)
Jason
Doe
31
=C11*1.5
25
=IF(E11>40, E11-40,0)
Buck
Doe
33
=C12*1.5
77
=IF(E12>40, E12-40,0)
Tula
Doe
35
=C13*1.5
41
=IF(E13>40, E13-40,0)
Heather
Doe
37
=C14*1.5
40
=IF(E14>40, E14-40,0)
Gary
Doe
39
=C15*1.5
40
=IF(E15>40, E15-40,0)
Now you are going to enter formulas to calculate the answers to the questions below:
Calculate each employee’s gross pay (GP) for the week. For example, the gross pay for cell G3 can be calculated with the following formula: “
=((E3-F3)*C3)+(D3*F3)
”. Notice how the formula references cells and follow this pattern for all other employees listed. For example, the formula for G4 is
=((E4-F4)*C4)+(D4*F4)
and G5 is
=((E5-F5)*C5)+(D5*F5)
. (Hint: Using the fill handle learned from step 15 will work in this case as well). For some information on what this formula is doing, the first part of the formula “=((E3-F3)*C3” using cell G3 as an example takes Total Hours Worked and subtracts Overtime Hours Worked and then multiplies the result by the Hourly Rate. The remaining part of the formula “+(D3*F3)” calculates overtime pay by multiplying the Overtime Hourly Rate by Overtime Hours Worked.
Next, calculate each employee’s SST (Social Security Tax Rate, 7.65%). For example, the SST for cell H3 can be calculated with the following formula: “
=G3*7.65%
.” Notice how the formula references cells and follow this pattern for all other employees listed, for example, cell H4 will have a formula “
=G4*7.65%
” and cell H5 will have a formula “
=G5*7.65%
“. (Hint: Using the fill handle learned from step 15 will work in this case as well.)
Next, calculate each employee’s FWT (Federal Withholding Tax. 18%). For example, the FWT for cell I3 can be calculated with the following formula: “
=G3*18%
“. Notice how the formula references cells and follow this pattern for all other employees listed, for example, cell I4 will have a formula “
=G4*18%
” and cell I5 will have a formula “
=G5*18%
“. (Hint: Using the fill handle learned from step 15 will work in this case as well).
Next, calculate each employee’s SWT (State Withholding Tax, 5%). For example, the SWT for cell J3 can be calculated with the following formula: “
=G3*5%
“. Notice how the formula references cells and follow this pattern for all other employees listed, for example, cell J4 will have a formula “
=G4*5%
” and cell J5 will have a formula “
=G5*5%
“. (Hint: Using the fill handle learned from step 15 will work in this case as well.)
Next, calculate each employee’s NP (Net Pay). For example, the NP for cell K3 can be calculated with the following formula: “
=G3-(H3+I3+J3)
“. Notice how the formula references cells and follow this pattern for all other employees listed, for example, cell K4 will have a formula “
=G4-(H4+I4+J4)
” and cell K5 will have a formula “
=G5-(H5+I5+J5)
“. (Hint: Using the fill handle learned from step 15 will work in this case as well.)
GP
SST
FWT
SWT
NP
=((E3-F3)*C3)+(D3*F3)
=G3*7.65%
=G3*18%
=G3*5%
=G3-(H3+I3+J3)
=((E4-F4)*C4)+(D4*F4)
=G4*7.65%
=G4*18%
=G4*5%
=G4-(H4+I4+J4)
=((E5-F5)*C5)+(D5*F5)
=G5*7.65%
=G5*18%
=G5*5%
=G5-(H5+I5+J5)
=((E6-F6)*C6)+(D6*F6)
=G6*7.65%
=G6*18%
=G6*5%
=G6-(H6+I6+J6)
=((E7-F7)*C7)+(D7*F7)
=G7*7.65%
=G7*18%
=G7*5%
=G7-(H7+I7+J7)
=((E8-F8)*C8)+(D8*F8)
=G8*7.65%
=G8*18%
=G8*5%
=G8-(H8+I8+J8)
=((E9-F9)*C9)+(D9*F9)
=G9*7.65%
=G9*18%
=G9*5%
=G9-(H9+I9+J9)
=((E10-F10)*C10)+(D10*F10)
=G10*7.65%
=G10*18%
=G10*5%
=G10-(H10+I10+J10)
=((E11-F11)*C11)+(D11*F11)
=G11*7.65%
=G11*18%
=G11*5%
=G11-(H11+I11+J11)
=((E12-F12)*C12)+(D12*F12)
=G12*7.65%
=G12*18%
=G12*5%
=G12-(H12+I12+J12)
=((E13-F13)*C13)+(D13*F13)
=G13*7.65%
=G13*18%
=G13*5%
=G13-(H13+I13+J13)
=((E14-F14)*C14)+(D14*F14)
=G14*7.65%
=G14*18%
=G14*5%
=G14-(H14+I14+J14)
=((E15-F15)*C15)+(D15*F15)
=G15*7.65%
=G15*18%
=G15*5%
=G15-(H15+I15+J15)
In cell E16, the formula “=SUM(E3:E15)” will calculate the total THW for the week.
In cell F16, the formula “=SUM(F3:F15)” will calculate the total OHW for the week.
Next, type the label
Totals
in cell A16 and then merge and center this label across cells A16 through D16.
Below is a general presentation to give you an idea of how your final data on the Weekly Payroll worksheet should look and do not forget that you will have 2 other worksheets holding the 2 required charts:
Bass Engineering Group
EFN
ELN
HR
OHR
THW
OHW
GP
SST
FWT
SWT
NP
John
Doe
15
40
33
0
$ 495.00
$ 37.87
$89.10
$ 24.75
$ 343.28
Sally
Doe
17
40
35
0
$ 595.00
$ 45.52
$ 107.10
$ 29.75
$412.63
Ferris
Doe
55
82.5
37
0
$ 2,035.00
$ 155.68
$ 366.30
$ 101.75
$ 1,411.27
Peter
Doe
21
31.5
39
0
$ 819.00
$ 62.65
$147.42
$40.95
$ 567.98
Jessica
Doe
23
34.5
41
1
$ 954.50
$ 73.02
$171.81
$ 47.73
$ 661.95
Tammy
Doe
55
82.5
43
3
$ 2,447.50
$ 187.23
$ 4
40.5
5
$ 122.38
$ 1,697.34
Julie
Doe
27
40.5
45
5
$ 1,282.50
$ 98.11
$230.85
$64.13
$889.41
Michael
Doe
26
45
12
0
$ 312.00
$ 23.87
$56.16
$15.60
$216.37
Jason
Doe
31
46.5
25
0
$ 775.00
$ 59.29
$139.50
$38.75
$537.46
Buck
Doe
33
49.5
77
37
$ 3,151.50
$ 241.09
$567.27
$157.58
$2,185.57
Tula
Doe
35
55
41
1
$ 1,455.00
$ 111.31
$261.90
$72.75
$1,009.04
Heather
Doe
37
55.5
40
0
$ 1,480.00
$ 113.22
$266.40
$74.00
$1,026.38
Gary
Doe
39
58.5
40
0
$ 1,560.00
$ 119.34
$280.80
$78.00
$1,081.86
Totals
508
47
$ 17,362.00
$ 1,328.19
$ 3,125.16
$ 868.10
$12,040.55
Assignment Expectations
Structure and Format: Follow all 29 steps using ideally Microsoft Excel.
File name: Name your saved file according to your first initial, last name, and the assignment number (for example, “RHall Assignment 1.xlsx”)