Unit J Independent Challenge 1 Grading Criteria
Document
J-Midwest 1.
Q1 Sales
2.
3.
4.
5.
Grade Criterion
The “J-3” Excel spreadsheet is saved as “J-Midwest Q1 Sales”.
Points
3
The worksheet orientation is landscape.
The worksheet is scaled so all columns fit on one sheet.
The range A4:F17 is defined as a table.
A table style of the student’s choosing is applied to the range
A4:F17.
6. The Total Row table option is selected.
7. The data in the table is sorted by the Sales Rep column (column
B) in ascending order.
8. The data in the table has a second sort level on the Q1 Sales vs.
Prior Year Q1 ($) (column E) in descending order.
9. Conditional formatting is applied to the range C5:C16 with the 3
Stars icon set.
10. Data Bars conditional formatting is applied to the range E5:E16
using a Data Bars style of the student’s choosing.
11. A formula is entered in cell E5 that subtracts the Q1 Sales Prior
Year in cell D5 from the Q1 Sales in cell C5.
12. The formula in cell E5 is auto-filled to cells E6:E16.
13. A formula is entered in cell F5 that calculates the percentage
that Q1 Sales increased over Q1 Sales in the prior year.
14. The formula in cell F5 is auto-filled to cells F6:F16.
15. A sum formula is selected for cell E17.
16. The sum formula in cell E17 is auto-filled to cells C17:D17.
17. The formula in cell F17 is deleted.
18. A formula is entered in cell B19 to calculate the average sales
for all states in Q1.
19. A formula is entered in cell B20 to calculate the maximum Q1
sales for all states.
20. A formula is entered in cell B21 to calculate the minimum Q1
sales for all states.
21. A formula is entered in cell B23 to calculate the average Q1
increase for Jonathan Stephens’ states.
22. A formula is entered in cell B24 to calculate the average Q1
increase for Patricia Morley’s states.
23. A formula is entered in cell B25 to calculate the average Q1
increase for Ricardo Juarez’s states.
24. A formula is entered in cell F23 that calculates Jonathan
Stephens’ bonus amount.
25. Since Patricia Morley’s average Q1 increase in cell B24 of -31%
does not exceed 10%, no formula is entered in cell F24.
26. A formula is entered in cell F25 that calculates Ricardo Juarez’s
bonus amount.
Total Points:
3
3
3
3
2
3
3
3
3
3
2
3
2
3
2
2
3
3
3
3
3
3
4
3
4
75
Unit J Independent Challenge 2 Grading Criteria
Document
J-‐August Sales
Rep Report
Grade Criterion
Points
1. The “J-‐4” Excel spreadsheet is saved as “J-‐August Sales
3
Rep Report”.
2. The worksheet is scaled so all columns fit on one sheet.
3
3. The student’s name is entered in cell A3.
5
4. The range A6:F40 is defined as a table.
3
5. A table style of the student’s choosing is applied to the
3
range A6:F40.
6. The Total Row table option is selected.
2
7. The table is sorted by sales (column D) in descending
3
order.
8. Data Bars conditional formatting is applied to the range
3
D7:D39 using a Data Bars style of the student’s choosing.
9. A formula is entered in cell E7 that calculates the
4
commission owed to the rep, =D7*$B$4.
10. The formula in cell E7 is auto-‐filled to cells E8:E39.
2
11. A formula is entered in cell F7 that subtracts the rep’s
3
commission in cell E7 from the Sales amount in cell D7,
=D7-‐E7.
12. The formula in cell F7 is auto-‐filled to cells F8:F39.
2
13. The sum formula in cell F40 is auto-‐filled to cells
2
D40:E40.
14. “Nelson, Deborah” is entered in cell B42, the name of the
2
rep with the largest sales amount.
15. A formula is entered in cell B43 that calculates the
3
highest individual sale in the month,
=MAX(Table1[Sales]).
16. Cell B43 is formatted with the Accounting number
2
format and no decimal places.
17. A formula is entered in cell B44 that calculates the
3
average for all sales, =AVERAGE(Table1[Sales]).
18. Cell B44 is formatted with the Accounting number
2
format and no decimal places.
Total Points:
50
Plushkin Fine Linens and Towels
First Quarter Sales-‐-‐Midwest Region
State
Minnesota
Kansas
North Dakota
South Dakota
Nebraska
Iowa
Wisconsin
Missouri
Michigan
Illinois
Indiana
Ohio
Total
Average Q1 sales for a state
Highest Q1 sales for a state
Lowest Q1 sales for a state
Stephens average Q1 increase
Morley average Q1 increase
Juarez average Q1 increase
Your Name
Sales Rep
Jonathan Stephens
Jonathan Stephens
Jonathan Stephens
Jonathan Stephens
Jonathan Stephens
Patricia Morley
Patricia Morley
Patricia Morley
Patricia Morley
Ricardo Juarez
Ricardo Juarez
Ricardo Juarez
Q1 Sales
$ 55,898
$ 67,534
$ 40,967
$ 31,098
$ 29,856
$ 45,877
$ 14,678
$ 38,764
$ 46,756
$ 58,978
$ 78,564
$ 64,533
$ 573,503
Q1 Sales Prior Year
$ 46,287
$ 60,046
$ 35,098
$ 26,543
$ 25,978
$ 56,787
$ 27,809
$ 55,342
$ 65,234
$ 43,267
$ 66,345
$ 58,745
$ 567,481
Q1 Sales vs. Prior Year Q1 ($)
Q1 Sales vs. Prior Year Q1 (%)
$ 9,611
21%
$ 7,488
12%
$ 5,869
17%
$ 4,555
17%
$ 3,878
15%
$ (10,910)
-‐19%
$ (13,131)
-‐47%
$ (16,578)
-‐30%
$ (18,478)
-‐28%
$ 15,711
36%
$ 12,219
18%
$ 5,788
10%
$ 6,022
$ 47,792
$ 78,564
$ 14,678
Bonus multiplier (% of rep's total sales):
16%
-‐31%
22%
Stephens bonus
Morley bonus
Juarez bonus
3%
$ 6,760.59
$ 6,062.25
Wexler Organics, Inc.
August Sales Rep Report
John Student
Sales rep commission:
Sales Rep
Nelson, Deborah
Nelson, Deborah
Jose, Martinez
Parisi, Maria
Nelson, Deborah
Tyson, Amelia
Martinez, Jose
Parisi, Maria
Lee, Schuyler
Jose, Martinez
Jose, Martinez
Nelson, Deborah
Lee, Schuyler
Emery, Richard
Lee, Schuyler
Parisi, Maria
Jose, Martinez
Lee, Schuyler
Parisi, Maria
Nelson, Deborah
Nelson, Deborah
Tyson, Amelia
Nelson, Deborah
Tyson, Amelia
Emery, Richard
Nelson, Deborah
Tyson, Amelia
Parisi, Maria
Lee, Schuyler
Emery, Richard
Tyson, Amelia
Jose, Martinez
Emery, Richard
Total
5%
Region
Midwest
Midwest
West
South
Midwest
Northeast
West
South
East
West
West
Midwest
East
Midwest
East
South
West
East
South
Midwest
Midwest
Northeast
Midwest
Northeast
Midwest
Midwest
Northeast
South
East
Midwest
Northeast
West
Midwest
Rep with highest sale:
Nelson, Deborah
Highest sales transaction: $
7,245
Average sale:
$
3,061
Date
13-Mar
18-Mar
17-Mar
10-Mar
13-Mar
4-Mar
14-Mar
1-Mar
10-Mar
25-Mar
30-Mar
3-Mar
9-Mar
1-Mar
18-Mar
17-Mar
20-Mar
20-Mar
16-Mar
11-Mar
11-Mar
10-Mar
6-Mar
20-Mar
5-Mar
29-Mar
7-Mar
5-Mar
28-Mar
13-Mar
28-Mar
22-Mar
5-Mar
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Sales
7,245
6,798
6,743
6,538
6,527
6,513
6,512
5,643
5,423
4,526
3,872
3,871
3,768
3,654
3,067
2,905
2,798
2,765
2,567
1,876
1,764
764
654
598
542
534
487
435
435
425
287
247
243
101,026
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Commission
362
340
337
327
326
326
326
282
271
226
194
194
188
183
153
145
140
138
128
94
88
38
33
30
27
27
24
22
22
21
14
12
12
5,051
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
Sales Less Commission
6,883
6,458
6,406
6,211
6,201
6,187
6,186
5,361
5,152
4,300
3,678
3,677
3,580
3,471
2,914
2,760
2,658
2,627
2,439
1,782
1,676
726
621
568
515
507
463
413
413
404
273
235
231
95,975
Purchase answer to see full
attachment