Can someone do this test ?
MGT 210 – Modeling
Test #2
1. In cell D6, calculate the Bonus using an “IF” and “Vlookup” -- Bonus – if hours are more than 40, lookup hours Bonus in Table (abs) and return the amount.
2. In Cell E6, Calculate Gross Pay – PayRate * HRSWK
3. In Cell F6, Calculate Taxes – Gross Pay * Tax Rate
4. In Cell G6, Calculate the Net Pay – Gross Pay less Taxes – use the Abs cell reference for the Tax rate.
5. Highlight Cells D6, E6, F6, G6 and fill cells down for the calculated values.
6. In Cells D10, E10, F10, G10, calculate the totals using the Sum Function
7. Copy Worksheet 4 and Create a Monthly Totals Chart; delete the amounts for each employee;
a. Use the Reference Cells to add the amounts for each column, i.e.,
b. =’Week 1’!C10+’Week 2’!C10 etc. to add the totals for HRS WKD, Bonus, Gross Pay, Taxes, and Net
c. Create a chart using the Column Headings HRSWKD, Bonus, GrossPay, Taxes and Net
d. Select Chart – Style 5’
e. Place the chart on the worksheet with the data between Cell B14 and F14 as shown.
f. Save the worksheet and upload this file.
Completed Model
|
|
|
|
|
|
|
|
|
Tax Rate |
7% |
|
|
|
|
|
|
|
|
|
|
|
|
Monthly Totals |
|
Bonus |
|
||||||
|
Employee |
Pay Rate |
HRS WK |
Bonus |
Gross Pay |
Taxes |
Net |
|
Hours |
Amount |
|
A3121 |
14.15 |
|
|
|
|
|
|
40 |
100 |
|
B4325 |
12.75 |
|
|
|
|
|
|
45 |
200 |
|
H9891 |
21.95 |
|
|
|
|
|
|
50 |
300 |
|
K7198 |
13.6 |
|
|
|
|
|
|
55 |
400 |
|
Total |
|
696.20 |
$2,500.00 |
$13,098.40 |
$916.89 |
$12,181.51 |
|
60 |
500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax Rate |
7% |
|
|
|
|
|
|
|
|
|
|
|
|
Week 4 Payroll Totals |
|
Bonus |
|
||||||
|
Employee |
Pay Rate |
HRS WK |
Bonus |
Gross Pay |
Taxes |
Net |
|
Hours |
Amount |
|
A3121 |
14.15 |
52.00 |
300.00 |
1,035.80 |
72.51 |
963.29 |
|
40 |
100 |
|
B4325 |
12.75 |
32.50 |
0.00 |
414.38 |
29.01 |
385.37 |
|
45 |
200 |
|
H9891 |
21.95 |
40.00 |
100.00 |
978.00 |
68.46 |
909.54 |
|
50 |
300 |
|
K7198 |
13.6 |
62.50 |
500.00 |
1,350.00 |
94.50 |
1,255.50 |
|
55 |
400 |
|
Total |
|
187.00 |
$900.00 |
$3,778.18 |
$264.47 |
$3,513.70 |
|
60 |
500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax Rate |
7% |
|
|
|
|
|
|
|
|
|
|
|
|
Week 3 Payroll Totals |
|
Bonus |
|
||||||
|
Employee |
Pay Rate |
HRS WK |
Bonus |
Gross Pay |
Taxes |
Net |
|
Hours |
Amount |
|
A3121 |
14.15 |
40.00 |
100.00 |
666.00 |
46.62 |
619.38 |
|
40 |
100 |
|
B4325 |
12.75 |
48.50 |
200.00 |
818.38 |
57.29 |
761.09 |
|
45 |
200 |
|
H9891 |
21.95 |
23.75 |
0.00 |
521.31 |
36.49 |
484.82 |
|
50 |
300 |
|
K7198 |
13.6 |
39.50 |
0.00 |
537.20 |
37.60 |
499.60 |
|
55 |
400 |
|
Total |
|
151.75 |
$300.00 |
$2,542.89 |
$178.00 |
$2,364.89 |
|
60 |
500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax Rate |
7% |
|
|
|
|
|
|
|
|
|
|
|
|
Week 2 Payroll Totals |
|
Bonus |
|
||||||
|
Employee |
Pay Rate |
HRS WK |
Bonus |
Gross Pay |
Taxes |
Net |
|
Hours |
Amount |
|
A3121 |
14.15 |
34.75 |
0.00 |
491.71 |
34.42 |
457.29 |
|
40 |
100 |
|
B4325 |
12.75 |
45.10 |
200.00 |
775.03 |
54.25 |
720.77 |
|
45 |
200 |
|
H9891 |
21.95 |
40.00 |
100.00 |
978.00 |
68.46 |
909.54 |
|
50 |
300 |
|
K7198 |
13.6 |
37.85 |
0.00 |
514.76 |
36.03 |
478.73 |
|
55 |
400 |
|
Total |
|
157.70 |
$300.00 |
$2,759.50 |
$193.16 |
$2,566.33 |
|
60 |
500 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax Rate |
7% |
|
|
|
|
|
|
|
|
|
|
|
|
Week 1 Payroll Totals |
|
Bonus |
|
||||||
|
Employee |
Pay Rate |
HRS WK |
Bonus |
Gross Pay |
Taxes |
Net |
|
Hours |
Amount |
|
A3121 |
14.15 |
56.00 |
400.00 |
1,192.40 |
83.47 |
1,108.93 |
|
40 |
100 |
|
B4325 |
12.75 |
62.50 |
500.00 |
1,296.88 |
90.78 |
1,206.09 |
|
45 |
200 |
|
H9891 |
21.95 |
38.75 |
0.00 |
850.56 |
59.54 |
791.02 |
|
50 |
300 |
|
K7198 |
13.6 |
42.50 |
100.00 |
678.00 |
47.46 |
630.54 |
|
55 |
400 |
|
Total |
|
199.75 |
$1,000.00 |
$4,017.84 |
$281.25 |
$3,736.59 |
|
60 |
500 |
|
|
|
|
|
|
|
|
|
|
|
1