Excel Assignment
An Excel assignment with a few steps.
I’ve attached 3 files, one for the instructions, one for the file that needs to be edited, one for the way the file is supposed to look at the end.
The instructor is very specific so please make sure you read everything very carefully.
>Payroll Data
2 48 1 11.75 1 1 9.55 3 .75
40 2 3 2 10 1 9.95 15 2 11.75 Taxable Pay Gross Pay Net Pay 40 Grader – Instructions Excel 2019 Project As manager of the gift shop at the Metropolitan Zoo, you are responsible for managing the weekly payroll. Your assistant developed a partial worksheet, but you need to enter the formulas to calculate the regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay. In addition, you want to include total pay columns and calculate some basic statistics. As you construct formulas, make sure you use absolute and relative cell references correctly in formulas. Step Instructions Points Possible 1 Start Excel. Download and open the file named Exp19_Excel_Ch02_ML1_Payroll.xlsx. Grader has automatically added your last name to the beginning of the filename. 0 2 Use IF functions to calculate the regular pay and overtime pay based on a regular 40-hour workweek in cells E5 and F5. Pay overtime only for overtime hours. In cell G5, calculate the gross pay based on the regular and overtime pay. Abram’s regular pay is $398. With 8 overtime hours, Abram’s overtime pay is $119.40. 20 3 Create a formula in cell H5 to calculate the taxable pay. Multiply the number of dependents (column B) by the deduction per dependent (B24) and subtract that from the gross pay. With two dependents, Abram’s taxable pay is $417.40. 10 4 Use a VLOOKUP function in cell I5 to identify and calculate the federal withholding tax. Use the tax rates from the range D21:E25. The VLOOKUP function returns the applicable tax rate, which you must then multiply by the taxable pay. 20 5 Calculate FICA in cell J5 based on gross pay and the FICA rate (cell B23), and calculate the net pay in cell K5. Copy all formulas down their respective columns to row 16. 10 6 With the range E5:K16 selected, use Quick Analysis tools to calculate the total regular pay, overtime pay, gross pay, taxable pay, withholding tax, FICA, and net pay on row 17. 10 7 Apply Accounting Number Format to the range C5:C16. Apply Accounting Number Format to the first row of monetary data and to the total row. Apply the Comma style to the monetary values for the other employees. Apply the Total cell styles format to the range E17:K17. 10 8 Insert appropriate functions to calculate the average, highest, and lowest values in the Summary Statistics area (the range I21:K23) of the worksheet. Format the # of hours calculations as Number format with one decimal and the remaining calculations with Accounting number format. 15 9 Insert a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of the worksheet. 5 10 Save and close the workbook. Submit the file as directed. 0 Total Points 100 Created On: 11/09/2020 1 Exp19_Excel_Ch02_ML1 – Metropolitan Zoo Gift Shop Weekly Payroll 1.5 >Submission – MarkUp
of all points deducted in the submitted document are listed on this sheet.
66280700
– (PC and Mac)
21
: )
Worksheet: ‘ ‘ (Points Deducted: 1 ) This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. This action did not result in any loss of points. Payroll Data 2 48 7.40
1 48 2.00
6.60
3 7.86
1 $ 11.75 35 411.25 1 40 Project Error:: Project Error: – 0 400.00 0
1 $ 9.55 44 382.00 3 40 – 0 630.00 2 20 – 0 245.00 3 38 – 0 507.30 2 $ 10.00 45 400.00 1 $ 9.95 15 – 0 149.25 2 $ 11.75 41 470.00 Taxable Pay Gross Pay Net Pay 40 50.0 toubasey Exp_Excel_Ch02_ML1_Payroll 2019 Project
2
Metropolitan Zoo
Gift Shop Department
Name
No. of Dependents
Hourly Wage
Hours Worked
Regular Pay
Overtime Pay
Gross Pay
Taxable Pay
Federal Withholding Tax
FICA
Net Pay
Abram
9.95
48
Acosta
1
9.55
Bordeaux
3
11.75
50
Higinbotham
35
Ianziti
10
40
Jaussi
44
Ling
15
Munoz
12.25
20
Riley
13.35
38
Sabey
45
Trujillo
Weston
41
Totals
Assumptions
Tax Rate
Summary Statistics
# of Hours
Base Work Hours
$0
15%
Average
Overtime rate
1.5
$250
22%
Highest
FICA rate
7.65%
$300
25%
Lowest
Deduct per Depend
$ 50.00
$450
28%
$525
31%
Notes
1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40.
2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate.
3. The gross pay is the total of the regular pay and overtime pay.
4. The taxable pay is the difference between the gross pay
and the product of the number of dependents and deduction per dependent.
5. Federal withholding tax is calculated on the taxable pay based on the tax table.
6. FICA is calculated on the employee’s gross pay.
7. The net pay is based on the gross pay, federal withholding tax, and FICA.
Exp19_Excel_Ch02_ML1_Metropolitan_Zoo_Gift_Shop_Weekly_Payroll
Project Description:
Steps to Perform:
Note, Mac users, with the range selected, on the Home tab, in the Editing group, click AutoSum.
2
Submission MarkUp
This is a machine generated report of your submission document containing error details as annotations.
Details
Assignment Details
Submission ID
48
Assignment
Name
Excel Chapter 2 Mid-Level
1
Metropolitan Zoo
Submission Date
12 March,
20
Report Generated on
18 March, 2021
Points Obtained
85/100 (85%)
Worksheet: Extra/Missing information (
Points Deducted
0.000
Points Deducted Details
Error Description
0.000
Missing: Sheet (Overtime)
This action did not result in any loss of points.
Payroll Data
5.000
Points Deducted Details Error Description
10.000
Cell level Incorrect Actions
11 cell(s) commented inplace. Please check the sheet.
5.000
Incorrect Actions: Header(s)/Footer(s)
5.000 point(s) deducted in instruction #9:
-In the Sheet ‘Payroll Data’ the ‘Center Footer Content’ was not set to ‘&[Tab]’.
-In the Sheet ‘Payroll Data’ the ‘Right Footer Content’ was not set to ‘&[File]’. 0.000
Missing: Cell M1
0.000
Missing: Cell N1
0.000
Missing: Cell M2
0.000
Missing: Cell N2
0.000
Missing: Cell M
3
0.000
Missing: Cell N3
0.000
Missing: Cell M4
0.000
Missing: Cell N4
0.000
Missing: Cell M5
0.000
Missing: Cell N5
0.000
Missing: Cell M6
0.000
Missing: Cell N6
0.000
Missing: Cell M7
0.000
Missing: Cell N7
0.000
Missing: Cell M8
0.000
Missing: Cell N8
0.000
Missing: Cell M9
0.000
Missing: Cell N9
0.000
Missing: Cell M10
0.000
Missing: Cell N10
0.000
Missing: Cell M11
0.000
Missing: Cell N11
0.000
Missing: Cell M12
0.000
Missing: Cell N12
0.000
Missing: Cell M13
0.000
Missing: Cell N13
0.000
Missing: Cell M14
0.000
Missing: Cell N14
0.000
Missing: Cell M
15
0.000
Missing: Cell N15
0.000
Missing: Cell M16
0.000
Missing: Cell N16
0.000
Missing: Cell M17
0.000
Missing: Cell N17
0.000
Missing: Cell M18
0.000
Missing: Cell N18
0.000
Missing: Cell M19
0.000
Missing: Cell N19
0.000
Missing: Cell M20
0.000
Missing: Cell N20
0.000
Missing: Cell M21
0.000
Missing: Cell N21
0.000
Missing: Cell M22
0.000
Missing: Cell N22
0.000
Missing: Cell M23
0.000
Missing: Cell N23
0.000
Missing: Cell M24
0.000
Missing: Cell N24
0.000
Missing: Cell M25
0.000
Missing: Cell N25
0.000
Missing: Cell M26
0.000
Missing: Cell N26
0.000
Missing: Cell M27
0.000
Missing: Cell N27
0.000
Missing: Cell M28
0.000
Missing: Cell N28
0.000
Missing: Cell M29
0.000
Missing: Cell N29
0.000
Missing: Cell M30
0.000
Missing: Cell N30
0.000
Missing: Cell M31
0.000
Missing: Cell N31
0.000
Missing: Cell M32
0.000
Missing: Cell N32
0.000
Missing: Cell M33
0.000
Missing: Cell N33
0.000
Missing: Cell M34
0.000
Missing: Cell N34
Metropolitan Zoo
Gift Shop Department
Name
No. of Dependents
Hourly Wage
Hours Worked
Regular Pay
Overtime Pay
Gross Pay
Taxable Pay
Federal Withholding Tax
FICA
Net Pay
Abram
$ 9.95
$ 398.00
$ 119.
40
$ 517.40
Project Error:: Project Error:
2 point(s) deducted in instruction #2:
– In the Cell ‘G5’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘E5+F5’.$
41
$ 104.
35
$ 39.58
$ 373.47
Acosta
$ 9.55
38
114.60
496.60
44
111.65
37.99
346.96
Bordeaux
$ 11.75
50
470.00
176.25
Project Error:: Project Error:
1 point(s) deducted in instruction #5:
– In the Cell ‘F7’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘IF(D7>$B$21,(D7-$B$21)*C7*$B$22,0)’.646.25
496.25
138.95
49.44
45
Higinbotham
411.25
– 0
361.25
90.31
31.46
289.48
Ianziti
$ 10.00
400.00
1 point(s) deducted in instruction #5:
– In the Cell ‘E9’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘IF(D9<=$B$21,D9,$B$21)*C9'.
3
50.0
87.50
30.60
281.90
Jaussi
57.30
439.30
389.30
97.33
33.61
308.37
Ling
$ 15.75
630.00
480.00
134.40
48.20
447.41
Munoz
$ 12.25
245.00
145.00
21.75
18.74
204.51
Riley
$ 13.35
507.30
357.30
89.33
38.81
379.17
Sabey
75.00
475.00
375.00
93.75
36.34
344.91
Trujillo
149.25
99.25
14.89
11.42
122.94
Weston
17.63
Project Error:: Project Error:
1 point(s) deducted in instruction #5:
– In the Cell ‘F16’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘IF(D16>$B$21,(D16-$B$21)*C16*$B$22,0)’.487.63
387.63
96.91
37.30
353.42
Totals
$ 4,844.80
Project Error:: Project Error:
0.714 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.$ 560.18
Project Error:: Project Error:
0.714 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.$ 5,404.98
Project Error:: Project Error:
0.714 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.$ 4,304.98
Project Error:: Project Error:
0.714 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.$ 1,081.11
Project Error:: Project Error:
0.715 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.$ 413.48
Project Error:: Project Error:
0.714 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.
Project Error:: Project Error:
2 point(s) deducted in instruction #2:
– In the Cell ‘G5’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘E5+F5’.$ 3,910.39
Project Error:: Project Error:
0.715 point(s) deducted in instruction #7:
– In the Cell(s) ‘E17’, ‘F17’, ‘G17’, ‘H17’, ‘I17’, ‘J17’, ‘K17’ of Sheet ‘Payroll Data’, the ‘Cell Style Name’ was not set to ‘Total’.
Project Error:: Project Error:
1 point(s) deducted in instruction #5:
– In the Cell ‘F7’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘IF(D7>$B$21,(D7-$B$21)*C7*$B$22,0)’.
Project Error:: Project Error:
1 point(s) deducted in instruction #5:
– In the Cell ‘E9’ of Sheet ‘Payroll Data’, the ‘Formula’ was not set to ‘IF(D9<=$B$21,D9,$B$21)*C9'.Assumptions
Tax Rate
Summary Statistics
# of Hours
Base Work Hours
$0
15%
Average
38.7
$ 450.41
$ 325.87
Overtime rate
1.5
$250
22%
Highest
$ 646.25
$ 457.86
FICA rate
7.65%
$300
25%
Lowest
15.0
$ 149.25
$ 122.94
Deduct per Depend
$ 50.00
$450
28%
$525
31%
Notes
1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40.
2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate.
3. The gross pay is the total of the regular pay and overtime pay.
4. The taxable pay is the difference between the gross pay
and the product of the number of dependents and deduction per dependent.
5. Federal withholding tax is calculated on the taxable pay based on the tax table.
6. FICA is calculated on the employee’s gross pay.
7. The net pay is based on the gross pay, federal withholding tax, and FICA.