Excel Assignment 2
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.
Grader – Instructions Excel 2019 Project
Exp19_Excel_Ch04_ML1_Pet_Supplies
Project Description:
You are the inventory manager for Crafton’s Pet Supplies. You are currently preforming analysis to determine inventory levels, as well as the total value of inventory on hand. Your last steps will be to check the report for duplicate entries and format for printing.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Excel. Download and open the file named EXP19_Excel_Ch04_ML1_HW_PetSupplies.xlsx. Grader has automatically added your last name to the beginning of the filename.
0
2
Freeze the top row so that the column labels do not scroll off screen.
5
3
Convert the data to a table and name the table Inventory2021.
10
4
Apply Table Style Medium 3 to the table.
Note, depending on the version of Office used, the table style name may be Orange, Table Style Medium 3.
5
5
Sort the table by Warehouse (A to Z), then by Unit Price (smallest to largest). Create a custom sort order for Department so that it appears in this sequence: Food & Health, Collars & Leashes, Toys, Clothes, Training, and Grooming.
Note, Mac users to create a custom order list, on the Excel menu, click Preferences, and then under Formulas and Lists, click Custom Lists.
Click Add.
10
6
Remove duplicate records from the table. Excel should find and remove one duplicate record.
5
7
Create an unqualified structured reference in column G to determine the value of the inventory on hand and apply Accounting Number Format. To calculate the inventory on hand, multiply the Unit Price and the Amount on Hand.
10
8
Apply a Total Row to the Inventory2021 table, set the Inventory Value to Sum, and the Amount on Hand to Average. Format the results to display with two decimal points.
16
9
Create a new conditional formatting rule that displays any Inventory Value in column G for the Food & Health department with a value of $30,000 or more as Red fill color (second column, last row). There will be two qualifying entries.
Note, Mac users select the range G2:G78. Then in the New Formatting Rule dialog box, select the style Classic.
14
10
Ensure the warehouse information is not broken up between pages when printed. Add a page break to make sure that each warehouse prints on its own consecutive page.
10
11
Set the worksheet to Landscape orientation, and repeat row 1 labels on all pages.
10
12
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 sheet. Display the Inventory sheet in Page Break Preview.
5
13
Save and close EXP19_Excel_CH04_ML1_HW_PetSupplies.xlsx. Exit Excel. Submit the file as directed.
0
Total Points
100
Created On: 06/18/2020 1 Exp19_Excel_Ch04_ML1 – Pet Supplies 1.1
Inventory
Inventory ID | Warehouse | Supplier ID | Department | Amount on Hand | Unit Price | Inventory Value | ||||||||||||||||||||||||||||||||||||||
11178 | Memphis | 24- | 38 | Clothes | 654 | $ 9.00 | ||||||||||||||||||||||||||||||||||||||
1 | 129 | San Diego | 43-5660 | Collars & Leashes | 82 | $ 42.00 | ||||||||||||||||||||||||||||||||||||||
11320 | 40-1780 | Grooming | 470 | $ 16.00 | ||||||||||||||||||||||||||||||||||||||||
11 | 48 | Potomac | 62-2793 | Toys | 23 | $ 26.00 | ||||||||||||||||||||||||||||||||||||||
11590 | 62-9040 | 440 | $ 37.00 | |||||||||||||||||||||||||||||||||||||||||
18621 | Denver | 67-1881 | 483 | $ 6.00 | ||||||||||||||||||||||||||||||||||||||||
11685 | 57-4516 | Food & Health | 238 | |||||||||||||||||||||||||||||||||||||||||
15639 | 12-6662 | 140 | $ 8.00 | |||||||||||||||||||||||||||||||||||||||||
11724 | 76- | 916 | 485 | $ 55.00 | ||||||||||||||||||||||||||||||||||||||||
16828 | 99-6880 | 41 | $ 10.00 | |||||||||||||||||||||||||||||||||||||||||
118 | 55-2458 | 448 | $ 31.00 | |||||||||||||||||||||||||||||||||||||||||
12015 | 63-6657 | Training | 565 | $ 3.00 | ||||||||||||||||||||||||||||||||||||||||
19801 | 32-3003 | 651 | ||||||||||||||||||||||||||||||||||||||||||
18790 | 51-8095 | 985 | $ 11.00 | |||||||||||||||||||||||||||||||||||||||||
19074 | 83-1 | 147 | $ 12.00 | |||||||||||||||||||||||||||||||||||||||||
1 | 213 | 77-9113 | 46 | $ 40.00 | ||||||||||||||||||||||||||||||||||||||||
15320 | 43-2702 | 918 | $ 14.00 | |||||||||||||||||||||||||||||||||||||||||
16432 | 41-2655 | $ 15.00 | ||||||||||||||||||||||||||||||||||||||||||
12258 | 11-4558 | 713 | $ 21.00 | |||||||||||||||||||||||||||||||||||||||||
12672 | 15-8803 | 196 | $ 22.00 | |||||||||||||||||||||||||||||||||||||||||
12806 | 58-4302 | 553 | $ 28.00 | |||||||||||||||||||||||||||||||||||||||||
12832 | 13-9209 | 294 | ||||||||||||||||||||||||||||||||||||||||||
19007 | 7-5363 | 543 | ||||||||||||||||||||||||||||||||||||||||||
12843 | 2-9636 | 593 | $ 47.00 | |||||||||||||||||||||||||||||||||||||||||
16342 | 9-6725 | 498 | $ 19.00 | |||||||||||||||||||||||||||||||||||||||||
12 | 948 | 34-7020 | $ 49.00 | |||||||||||||||||||||||||||||||||||||||||
19473 | 96-1948 | 712 | $ 20.00 | |||||||||||||||||||||||||||||||||||||||||
13075 | 53-8846 | 692 | ||||||||||||||||||||||||||||||||||||||||||
13404 | 81-7230 | 808 | ||||||||||||||||||||||||||||||||||||||||||
13440 | 4-7732 | 547 | $ 5.00 | |||||||||||||||||||||||||||||||||||||||||
13505 | 53-4573 | 818 | ||||||||||||||||||||||||||||||||||||||||||
13705 | 21- | 632 | 445 | $ 39.00 | ||||||||||||||||||||||||||||||||||||||||
13822 | 43-5490 | $ 51.00 | ||||||||||||||||||||||||||||||||||||||||||
14016 | 23-6766 | |||||||||||||||||||||||||||||||||||||||||||
17146 | 84-9406 | $ 23.00 | ||||||||||||||||||||||||||||||||||||||||||
14184 | 72-7913 | 805 | $ 50.00 | |||||||||||||||||||||||||||||||||||||||||
18797 | 32- | 680 | 437 | $ 24.00 | ||||||||||||||||||||||||||||||||||||||||
14448 | 53-8404 | 962 | ||||||||||||||||||||||||||||||||||||||||||
14636 | 74-3227 | 677 | $ 44.00 | |||||||||||||||||||||||||||||||||||||||||
15531 | 66-6754 | 709 | ||||||||||||||||||||||||||||||||||||||||||
11711 | 24-3362 | 329 | ||||||||||||||||||||||||||||||||||||||||||
15687 | 7-6023 | 387 | ||||||||||||||||||||||||||||||||||||||||||
11485 | 31-6267 | 696 | $ 29.00 | |||||||||||||||||||||||||||||||||||||||||
17007 | 43-6210 | 927 | ||||||||||||||||||||||||||||||||||||||||||
15472 | 54-7909 | 408 | $ 30.00 | |||||||||||||||||||||||||||||||||||||||||
15930 | 6-7306 | 569 | ||||||||||||||||||||||||||||||||||||||||||
16146 | 66-6102 | 380 | $ 41.00 | |||||||||||||||||||||||||||||||||||||||||
19487 | 92-8378 | 236 | $ 32.00 | |||||||||||||||||||||||||||||||||||||||||
14261 | 80-8283 | $ 33.00 | ||||||||||||||||||||||||||||||||||||||||||
16437 | 89-3177 | 836 | $ 18.00 | |||||||||||||||||||||||||||||||||||||||||
18638 | 2-1553 | 584 | ||||||||||||||||||||||||||||||||||||||||||
16745 | 57-2430 | $ 36.00 | ||||||||||||||||||||||||||||||||||||||||||
16849 | 80-7774 | 416 | $ 7.00 | |||||||||||||||||||||||||||||||||||||||||
16946 | 97-7267 | 748 | ||||||||||||||||||||||||||||||||||||||||||
17020 | 89-8450 | 999 | ||||||||||||||||||||||||||||||||||||||||||
17513 | 66-4757 | |||||||||||||||||||||||||||||||||||||||||||
17973 | 32-1822 | 760 | ||||||||||||||||||||||||||||||||||||||||||
18113 | 95-8142 | 452 | ||||||||||||||||||||||||||||||||||||||||||
18254 | 33-9096 | 721 | ||||||||||||||||||||||||||||||||||||||||||
18455 | 90-8867 | 202 | $ 38.00 | |||||||||||||||||||||||||||||||||||||||||
18554 | 43-1802 | 734 | ||||||||||||||||||||||||||||||||||||||||||
16840 | 42-9356 | 386 | $ 43.00 | |||||||||||||||||||||||||||||||||||||||||
18698 | 43-2218 | 989 | ||||||||||||||||||||||||||||||||||||||||||
17308 | 43-3691 | 994 | ||||||||||||||||||||||||||||||||||||||||||
18781 | 2-2476 | |||||||||||||||||||||||||||||||||||||||||||
18861 | 83-4623 | |||||||||||||||||||||||||||||||||||||||||||
19160 | 81-2610 | |||||||||||||||||||||||||||||||||||||||||||
13136 | 63-2238 | $ 48.00 | ||||||||||||||||||||||||||||||||||||||||||
15651 | 15-6231 | |||||||||||||||||||||||||||||||||||||||||||
19483 | 15- | 589 | 392 | |||||||||||||||||||||||||||||||||||||||||
19744 | 10-5462 | 914 | ||||||||||||||||||||||||||||||||||||||||||
19754 | 23-1749 | |||||||||||||||||||||||||||||||||||||||||||
19784 | 69-3905 | 673 | $ 45.00 | |||||||||||||||||||||||||||||||||||||||||
19838 | 26-5867 | $ 53.00 | ||||||||||||||||||||||||||||||||||||||||||
19859 | 84-3864 | 393 | $ 13.00 |
Submission
– MarkUp
of all points deducted in the submitted document are listed on this sheet.
747
00
1
19 March, 2021
Worksheet: ‘
Inventory
‘ (
: 31.833)
-In the Sheet ‘Inventory’ the ‘Freeze Panes’ were not applied at ‘Row 1’.
1
point(s) deducted in instruction #10:
-In the Sheet ‘Inventory’ the ‘Page Break set before Row’ was not set to ’28’.
-In the Sheet ‘Inventory’ the ‘Center Footer Text Case’ was not set as per instruction.
-In the Sheet ‘Inventory’ the ‘Center Footer Content’ was not set to ‘&[Tab]’.
-In the Sheet ‘Inventory’ the ‘Right Footer Text Case’ was not set as per instruction.
-In the Sheet ‘Inventory’ the ‘Right Footer Content’ was not set to ‘&[File]’.
-In the ‘Inventory2021 [A1:G79]’ on Sheet ‘Inventory’ the ‘Table Name’ was not set to ‘Inventory2021’.
-In the Conditional formatting ‘expression’ on range ‘G2:G78’ of sheet ‘Inventory’, the ‘Range’ was not set to ‘G2:G78’.
-In the Conditional formatting ‘expression’ on range ‘G2:G78’ of sheet ‘Inventory’, the ‘Rule Type’ was not set to ‘Expression’.
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.
Inventory
Inventory ID | Warehouse | Supplier ID | Department | Amount on Hand | Unit Price | Inventory Value | ||||||||||||||||||||||||||||||||||||||
18621 | Denver | 67-1881 | Grooming | 483 | $ 6.00 | $ 2,898.00 | ||||||||||||||||||||||||||||||||||||||
15639 | 12-6662 | Clothes | 140 | $ 8.00 | $ 1,120.00 | |||||||||||||||||||||||||||||||||||||||
19801 | 32-3003 | Toys | 651 | $ 10.00 | $ 6,510.00 | |||||||||||||||||||||||||||||||||||||||
16828 | 99-6880 | 41 | $ 410.00 | |||||||||||||||||||||||||||||||||||||||||
18790 | 51-8095 | 985 | $ 11.00 | $ 10,835.00 | ||||||||||||||||||||||||||||||||||||||||
19074 | 83-1 | 147 | 916 | $ 12.00 | $ 10,992.00 | |||||||||||||||||||||||||||||||||||||||
15320 | 43-2702 | 918 | $ 14.00 | $ 12,852.00 | ||||||||||||||||||||||||||||||||||||||||
16432 | 41-2655 | Food & Health | $ 15.00 | $ 2,100.00 | ||||||||||||||||||||||||||||||||||||||||
19007 | 7-5363 | 543 | $ 16.00 | $ 8,688.00 | ||||||||||||||||||||||||||||||||||||||||
16342 | 9-6725 | 498 | $ 19.00 | $ 9, | 46 | |||||||||||||||||||||||||||||||||||||||
19473 | 96-1 | 948 | Collars & Leashes | 712 | $ 20.00 | $ 14,240.00 | ||||||||||||||||||||||||||||||||||||||
17146 | 84-9406 | Training | 129 | $ | 23 | $ 2,967.00 | ||||||||||||||||||||||||||||||||||||||
18797 | 32- | 680 | 437 | $ 24.00 | $ 10,488.00 | |||||||||||||||||||||||||||||||||||||||
808 | ||||||||||||||||||||||||||||||||||||||||||||
11711 | 24-3362 | 329 | $ 28.00 | $ 9,212.00 | ||||||||||||||||||||||||||||||||||||||||
11 | 485 | 31-6267 | 696 | $ 29.00 | $ 20,184.00 | |||||||||||||||||||||||||||||||||||||||
17007 | 43-6210 | 927 | $ 26,883.00 | |||||||||||||||||||||||||||||||||||||||||
1 | 547 | 54-7909 | 408 | $ 30.00 | $ 12,240.00 | |||||||||||||||||||||||||||||||||||||||
19487 | 92-8378 | 236 | $ 32.00 | $ 7,552.00 | ||||||||||||||||||||||||||||||||||||||||
14261 | 80-8283 | 118 | $ 33.00 | $ 3,894.00 | ||||||||||||||||||||||||||||||||||||||||
186 | 38 | 2-1553 | 584 | $ 19,272.00 | ||||||||||||||||||||||||||||||||||||||||
16745 | 57-2430 | 632 | $ 36.00 | $ 22,752.00 | ||||||||||||||||||||||||||||||||||||||||
16840 | 42-9356 | 386 | $ 43.00 | $ 16,598.00 | ||||||||||||||||||||||||||||||||||||||||
17308 | 43-3691 | 994 | $ 44.00 | $ 43,736.00 | ||||||||||||||||||||||||||||||||||||||||
13136 | 63-2 | 238 | $ 48.00 | $ 1,104.00 | ||||||||||||||||||||||||||||||||||||||||
1 | 565 | 15-6 | 231 | $ 32,640.00 | ||||||||||||||||||||||||||||||||||||||||
13505 | Memphis | 53-4573 | 818 | $ 3.00 | $ 2,454.00 | |||||||||||||||||||||||||||||||||||||||
13404 | 81-7230 | $ 4,848.00 | ||||||||||||||||||||||||||||||||||||||||||
16849 | 80-7774 | 416 | $ 7.00 | $ 2,912.00 | ||||||||||||||||||||||||||||||||||||||||
11178 | 24-3810 | 654 | $ 9.00 | $ 5,886.00 | ||||||||||||||||||||||||||||||||||||||||
17973 | 32-1822 | 760 | $ 7,600.00 | |||||||||||||||||||||||||||||||||||||||||
16437 | 89-3177 | 836 | $ 18.00 | $ 15,048.00 | ||||||||||||||||||||||||||||||||||||||||
19160 | 81-2610 | $ 760.00 | ||||||||||||||||||||||||||||||||||||||||||
12258 | 11-4558 | 713 | $ 21.00 | $ 14,973.00 | ||||||||||||||||||||||||||||||||||||||||
18861 | 83-4623 | $ 22.00 | $ 1,056.00 | |||||||||||||||||||||||||||||||||||||||||
12672 | 15-8803 | 196 | $ 4,312.00 | |||||||||||||||||||||||||||||||||||||||||
16946 | 97-7267 | 748 | $ 31.00 | $ 23,188.00 | ||||||||||||||||||||||||||||||||||||||||
18554 | 43-1802 | 734 | $ 24,222.00 | |||||||||||||||||||||||||||||||||||||||||
18455 | 90-8867 | $ 38.00 | $ 7,676.00 | |||||||||||||||||||||||||||||||||||||||||
13705 | 21-6324 | 445 | $ 39.00 | $ 17,355.00 | ||||||||||||||||||||||||||||||||||||||||
1 | 213 | 77-9113 | $ 40.00 | $ 1,840.00 | ||||||||||||||||||||||||||||||||||||||||
1 | 294 | 34-7020 | $ 49.00 | $ 46, | 452 | |||||||||||||||||||||||||||||||||||||||
1 | 593 | 6-7306 | 569 | $ 50.00 | $ 28,450.00 | |||||||||||||||||||||||||||||||||||||||
11724 | 76-9160 | $ 55.00 | $ 26,675.00 | |||||||||||||||||||||||||||||||||||||||||
12015 | Potomac | 63-6657 | $ 1,695.00 | |||||||||||||||||||||||||||||||||||||||||
13 | 440 | 4-7732 | $ 5.00 | $ 2,735.00 | ||||||||||||||||||||||||||||||||||||||||
18113 | 95-8142 | $ 4,520.00 | ||||||||||||||||||||||||||||||||||||||||||
19859 | 84-3864 | 393 | $ 13.00 | $ 5,109.00 | ||||||||||||||||||||||||||||||||||||||||
11685 | 57-4516 | $ 3,808.00 | ||||||||||||||||||||||||||||||||||||||||||
18781 | 2-2476 | $ 10,112.00 | ||||||||||||||||||||||||||||||||||||||||||
17020 | 89-8450 | 999 | $ 21,978.00 | |||||||||||||||||||||||||||||||||||||||||
19744 | 10-5462 | 914 | $ 20,108.00 | |||||||||||||||||||||||||||||||||||||||||
14 | 448 | 53-8404 | 962 | $ 23.00 | $ 22,126.00 | |||||||||||||||||||||||||||||||||||||||
19754 | 23-1749 | $ 5,112.00 | ||||||||||||||||||||||||||||||||||||||||||
11482 | 62-2793 | $ 26.00 | $ 6,006.00 | |||||||||||||||||||||||||||||||||||||||||
15531 | 66-6754 | 709 | $ 18,434.00 | |||||||||||||||||||||||||||||||||||||||||
11590 | 62-9040 | $ 37.00 | $ 16,280.00 | |||||||||||||||||||||||||||||||||||||||||
16146 | 66-6102 | 380 | $ 41.00 | $ 15,580.00 | ||||||||||||||||||||||||||||||||||||||||
18254 | 33-9096 | 721 | $ 31,724.00 | |||||||||||||||||||||||||||||||||||||||||
19784 | 69-3905 | 673 | $ 45.00 | $ 30,285.00 | ||||||||||||||||||||||||||||||||||||||||
12843 | 2-9636 | $ 47.00 | $ 27,871.00 | |||||||||||||||||||||||||||||||||||||||||
13822 | 43-5490 | $ 51.00 | $ 28,815.00 | |||||||||||||||||||||||||||||||||||||||||
19838 | 26-5867 | 589 | $ 53.00 | $ 31,217.00 | ||||||||||||||||||||||||||||||||||||||||
19483 | San Diego | 15-5897 | 392 | $ 1,960.00 | ||||||||||||||||||||||||||||||||||||||||
13075 | 53-8846 | 692 | $ 5,536.00 | |||||||||||||||||||||||||||||||||||||||||
12832 | 13-9209 | $ 4,704.00 | ||||||||||||||||||||||||||||||||||||||||||
11320 | 40-1780 | 470 | $ 7,520.00 | |||||||||||||||||||||||||||||||||||||||||
14016 | 23-6766 | $ 836.00 | ||||||||||||||||||||||||||||||||||||||||||
17513 | 66-4757 | $ 3,528.00 | ||||||||||||||||||||||||||||||||||||||||||
12806 | 58-4302 | $ 15,484.00 | ||||||||||||||||||||||||||||||||||||||||||
11867 | 55-2458 | $ 13,888.00 | ||||||||||||||||||||||||||||||||||||||||||
18698 | 43-2218 | 989 | $ 39,560.00 | |||||||||||||||||||||||||||||||||||||||||
11292 | 43-5660 | $ 42.00 | $ 3,444.00 | |||||||||||||||||||||||||||||||||||||||||
15687 | 7-6023 | 387 | $ 16,254.00 | |||||||||||||||||||||||||||||||||||||||||
14636 | 74-3227 | 677 | $ 29,788.00 | |||||||||||||||||||||||||||||||||||||||||
14184 | 72-7913 | 805 | $ 40,250.00 | |||||||||||||||||||||||||||||||||||||||||
Total | 514.17 | $ 1,046,795.00 | ||||||||||||||||||||||||||||||||||||||||||
Taubasey
>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
of all points deducted in the submitted document are listed on this sheet.
75920500
1
20 March, 2021 Worksheet: ‘ ‘ ( : 19.667) Inventory Denver Denver 8
Denver Clothes $ 10.00 Denver Toys Denver Grooming Denver Toys Denver 140 Denver Clothes Denver Grooming 2.00
Denver Denver .00
Denver 8
Food & Health Grooming 437 $ 24.00 $ 10,488.00 Denver Grooming Denver Toys Denver Training $ 29.00 2
Denver Toys Denver Toys Denver Collars & Leashes Denver Training $ 33.00 Denver Grooming Denver Food & Health Denver Training Denver Food & Health 23 1
Denver Food & Health 680 $ 48.00 Clothes Memphis Collars & Leashes 808 $ 6.00 Memphis Collars & Leashes Memphis Clothes Memphis Clothes $ 10.00 Memphis Collars & Leashes Memphis Grooming 38 $ 20.00 Memphis Training Memphis Food & Health 48 Memphis Training $ 22.00 Memphis Training Memphis Training $ 33.00 Memphis Toys 202 Memphis Toys 6
Memphis Clothes 46 8
Memphis Collars & Leashes 948 .00
0
Memphis Toys Memphis Clothes 485 Training 565 $ 3.00 Potomac Toys 547 Potomac Toys 452 $ 10.00 Potomac Training Potomac Food & Health 238 $ 16.00 Potomac Toys 632 $ 16.00 Potomac Food & Health $ 22.00 Potomac Grooming $ 22.00 Potomac Grooming Potomac Clothes 213 $ 24.00 Potomac Toys 231 Potomac Clothes $ 26.00 Potomac Clothes 440 Potomac Collars & Leashes Potomac Food & Health $ 44.00 Potomac Toys Potomac Food & Health 593 Potomac Toys 565 Potomac Training Toys $ 5.00 San Diego Collars & Leashes $ 8.00 San Diego Collars & Leashes 294 $ 16.00 San Diego Grooming $ 16.00 San Diego Toys 38 $ 22.00 San Diego Collars & Leashes 147 $ 24.00 San Diego Toys 553 $ 28.00 San Diego Food & Health 448 $ 31.00 San Diego Collars & Leashes $ 40.00 San Diego Collars & Leashes 82 San Diego Collars & Leashes $ 42.00 San Diego Grooming $ 44.00 San Diego Grooming $ 50.00 toubasey ML1 pet supplies
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.
Submission – MarkUp
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 4 Mid-Level 1 – Pet Supplies (PC and Mac)
Submission Date
20 March,
202
Report Generated on
Points Obtained
80.333/100 (80.333%)
Inventory
Points Deducted
Points Deducted Details
Error Description
10.000
Incorrect Actions: Sheet (Inventory)
10.000 point(s) deducted in instruction #10:
-In the Sheet ‘Inventory’ the ‘Page Break set before Row’ was not set to ’28’.
5.000
Incorrect Actions: Header(s)/Footer(s)
5.000 point(s) deducted in instruction #12:
-In the Sheet ‘Inventory’ the ‘Center Footer Text Case’ was not set as per instruction.
-In the Sheet ‘Inventory’ the ‘Center Footer Content’ was not set to ‘&[Tab]’.
-In the Sheet ‘Inventory’ the ‘Right Footer Text Case’ was not set as per instruction.
-In the Sheet ‘Inventory’ the ‘Right Footer Content’ was not set to ‘&[File]’.
4.667
Incorrect Actions: Conditional Format(s)
4.667 point(s) deducted in instruction #9:
-In the Conditional formatting ‘expression’ on range ‘G2:G78’ of sheet ‘Inventory’, the ‘Background Color’ was not set to ‘Red’.
Inventory ID
Warehouse
Supplier ID
Department
Amount on Hand
Unit Price
Inventory Value
18621
Denver
67-1881
Grooming
483
$ 6.00
$ 2,898.00
15639
12-6662
Clothes
140
$ 8.00
$ 1,120.00
19801
32-3003
Toys
651
$ 10.00
$ 6,510.00
16
82
99-6880
41
$ 410.00
18790
51-8095
985
$ 11.00
$ 10,835.00
19074
83-1
147
916
$ 12.00
$ 10,992.00
15320
43-2702
918
$ 14.00
$ 12,852.00
16432
41-2655
Food & Health
$ 15.00
$ 2,100.00
19007
7-5363
543
$ 16.00
$ 8,688.00
16342
9-6725
498
$ 19.00
$ 9,
46
19473
96-1
948
Collars & Leashes
712
$ 20.00
$ 14,240.00
17146
84-9406
Training
129
$
23
$ 2,967.00
18797
32-
680
437
$ 24.00
$ 10,488.00
18797 Denver 32-6
808
11711
24-3362
329
$ 28.00
$ 9,212.00
11
485
31-6267
696
$ 29.00
$ 20,184.00
17007
43-6210
927
$ 26,883.00
1
547
54-7909
408
$ 30.00
$ 12,240.00
19487
92-8378
236
$ 32.00
$ 7,552.00
14261
80-8283
118
$ 33.00
$ 3,894.00
186
38
2-1
553
584
$ 19,272.00
16745
57-2430
632
$ 36.00
$ 22,752.00
16840
42-9356
386
$ 43.00
$ 16,598.00
17308
43-3691
994
$ 44.00
$ 43,736.00
13136
63-2
238
$ 48.00
$ 1,104.00
1
565
15-6
231
$ 32,640.00
13505
Memphis
53-4573
818
$ 3.00
$ 2,454.00
13404
81-7230
$ 4,848.00
16849
80-7774
416
$ 7.00
$ 2,912.00
11178
24-3810
654
$ 9.00
$ 5,886.00
17973
32-1822
760
$ 7,600.00
16437
89-3177
836
$ 18.00
$ 15,048.00
19160
81-2610
$ 760.00
12258
11-4558
713
$ 21.00
$ 14,973.00
18861
83-4623
$ 22.00
$ 1,056.00
12672
15-8803
196
$ 4,312.00
16946
97-7267
748
$ 31.00
$ 23,188.00
18554
43-1802
734
$ 24,222.00
18455
90-8867
$ 38.00
$ 7,676.00
13705
21-6324
445
$ 39.00
$ 17,355.00
1
213
77-9113
$ 40.00
$ 1,840.00
1
294
34-7020
$ 49.00
$ 46,
452
1
593
6-7306
569
$ 50.00
$ 28,450.00
11724
76-9160
$ 55.00
$ 26,675.00
12015
Potomac
63-6657
$ 1,695.00
13
440
4-7732
$ 5.00
$ 2,735.00
18113
95-8142
$ 4,520.00
19859
84-3864
393
$ 13.00
$ 5,109.00
11685
57-4516
$ 3,808.00
18781
2-2476
$ 10,112.00
17020
89-8450
999
$ 21,978.00
19744
10-5462
914
$ 20,108.00
14
448
53-8404
962
$ 23.00
$ 22,126.00
19754
23-1749
$ 5,112.00
11482
62-2793
$ 26.00
$ 6,006.00
15531
66-6754
709
$ 18,434.00
11590
62-9040
$ 37.00
$ 16,280.00
16146
66-6102
380
$ 41.00
$ 15,580.00
18254
33-9096
721
$ 31,724.00
19784
69-3905
673
$ 45.00
$ 30,285.00
12843
2-9636
$ 47.00
$ 27,871.00
13822
43-5490
$ 51.00
$ 28,815.00
19838
26-5867
589
$ 53.00
$ 31,217.00
19483
San Diego
15-5897
392
$ 1,960.00
13075
53-8846
692
$ 5,536.00
12832
13-9209
$ 4,704.00
11320
40-1780
470
$ 7,520.00
12832 San Diego 13-9209 Grooming 294 $ 16.00 $ 4,704.00
14016
23-6766
$ 836.00
17513
66-4757
$ 3,528.00
12806
58-4302
$ 15,484.00
11867
55-2458
$ 13,888.00
18698
43-2218
989
$ 39,560.00
11292
43-5660
$ 42.00
$ 3,444.00
15687
7-6023
387
$ 16,254.00
14636
74-3227
677
$ 29,788.00
14184
72-7913
805
$ 40,250.00
Total
514.17
$ 1,046,795.00