The Tasks Below Should All Be Completed On The Project 1 Worksheet

Download as xlsx, pdf, or txt
Download as xlsx, pdf, or txt
You are on page 1of 14

The tasks below should all be completed on the Project 1 w

1 Create a Clustered Column Chart in A13 that displays each Department, the Hours
2 Add a Linear Forecast Trendline that forecasts 3 periods for Gross Pay.
3 Create a secondary Axis that displays the Gross Pay as a Line Chart.

H+ Sports
Department Comparison for November 2016

DEPARTMENT HR Marketing Receiving Social Media Sales


HOURS 430 603 525 800 500
HOURLY RATE $32.38 $32.38 $31.37 $32.89 $35.10
GROSS PAY $13,921.79 $19,522.88 $16,469.95 $26,312.00 $17,550.63
mpleted on the Project 1 worksheet.
each Department, the Hours worked and the Gross Pay.
for Gross Pay.
a Line Chart.

Shipping Sparklines
557
$29.66
$16,518.39
The tasks below should all be completed on the Project 2 worksheet
Create a PivotTable on this worksheet beginning in G10 that includes all fields in the table. D
1 SKU#), Price as Values, Date as Columns. Group by Date by Days, Months and Quarters

Create a calculated field to calculate the Total Sales of each Order. Name the Field "Total Sa
2
Create an Area PivotChart. Position the PivotChart so the upper-left corner of the chart is in c
Using the Slicers, filter the data to show only Green Items sold.
3
4 In cell H7, use the GETPIVOTDATA function to return the total sales of Green Clothing sold in

H+ Sport
Monthly sales report - Store #18 - Chicago, IL
Women's Clothing Sales - 2016

DATE Item SKU# COLOR QTY SOLD PRICE


8/4/2016 1546013 yellow 9 $15.95
7/1/2016 1546013 red 5 $15.95
10/11/2016 1546013 green 9 $15.95
10/8/2016 1546013 red 5 $15.95
9/6/2016 1546013 violet 5 $15.95
10/4/2016 1546013 green 17 $15.95
7/5/2016 1546013 yellow 1 $15.95
8/8/2016 1546013 violet 11 $15.95
10/10/2016 5408635 red 3 $15.95
10/4/2016 5408635 blue 9 $15.95
9/1/2016 5408635 brown 17 $15.95
9/9/2016 5408635 red 9 $15.95
7/6/2016 5408635 brown 1 $15.95
7/3/2016 5408635 black 11 $15.95
9/4/2016 5408635 black 13 $15.95
9/1/2016 5408635 brown 15 $15.95
8/9/2016 5408635 brown 11 $15.95
7/7/2016 5408635 blue 17 $15.95
9/2/2016 7894579 black 1 $15.95
8/10/2016 7894579 blue 5 $15.95
10/9/2016 7894579 brown 13 $15.95
10/7/2016 7894579 blue 5 $15.95
10/12/2016 7894579 blue 11 $15.95
8/7/2016 7894579 red 11 $15.95
9/5/2016 7894579 brown 15 $15.95
9/3/2016 7894579 blue 11 $15.95
9/9/2016 7894579 red 17 $15.95
7/4/2016 2150233 black 13 $16.75
10/7/2016 2150233 green 17 $16.75
9/1/2016 2150233 blue 17 $16.75
10/6/2016 2150233 yellow 3 $16.75
7/5/2016 2150233 red 7 $16.75
9/7/2016 2150233 green 5 $16.75
8/11/2016 2150233 yellow 1 $16.75
10/4/2016 2150233 yellow 1 $16.75
7/2/2016 2150233 violet 5 $16.75
9/9/2016 7016817 blue 3 $16.75
7/5/2016 7016817 yellow 7 $16.75
8/6/2016 7016817 violet 1 $16.75
8/6/2016 7016817 black 5 $16.75
8/5/2016 7016817 yellow 13 $16.75
10/11/2016 7016817 violet 3 $16.75
10/13/2016 7016817 green 3 $16.75
9/9/2016 7016817 blue 17 $16.75
10/13/2016 7016817 green 5 $16.75
8/2/2016 7016817 yellow 17 $16.75
7/1/2016 7016817 yellow 7 $16.75
9/4/2016 1331839 red 1 $16.94
8/11/2016 1331839 yellow 13 $16.94
7/3/2016 1331839 violet 11 $16.94
9/3/2016 1331839 violet 11 $16.94
8/7/2016 1331839 brown 1 $16.94
8/11/2016 1331839 violet 7 $16.94
7/7/2016 1331839 violet 1 $16.94
9/4/2016 6727427 blue 15 $16.94
8/10/2016 6727427 brown 11 $16.94
8/7/2016 6727427 brown 13 $16.94
7/6/2016 6727427 blue 13 $16.94
10/5/2016 6727427 red 3 $16.94
8/10/2016 6727427 blue 5 $16.94
9/6/2016 6727427 green 7 $16.94
9/3/2016 6727427 violet 9 $16.94
8/10/2016 6727427 green 11 $16.94
8/7/2016 1606430 violet 1 $17.75
8/8/2016 1606430 black 9 $17.75
10/5/2016 1606430 green 13 $17.75
8/6/2016 1606430 yellow 3 $17.75
7/1/2016 1606430 blue 1 $17.75
7/6/2016 1606430 violet 9 $17.75
8/6/2016 1606430 yellow 9 $17.75
9/7/2016 1606430 brown 11 $17.75
10/13/2016 1606430 green 11 $17.75
7/1/2016 1606430 violet 1 $17.75
9/4/2016 9019354 brown 11 $17.75
10/6/2016 9019354 blue 13 $17.75
7/7/2016 9019354 red 1 $17.75
8/8/2016 9019354 blue 5 $17.75
8/11/2016 9019354 violet 13 $17.75
7/6/2016 9019354 yellow 13 $17.75
7/5/2016 1410138 green 13 $18.92
10/4/2016 1410138 green 15 $18.92
10/12/2016 1410138 blue 3 $18.92
8/10/2016 1410138 green 1 $18.92
9/8/2016 1410138 blue 15 $18.92
7/7/2016 1410138 brown 1 $18.92
8/7/2016 1410138 red 9 $18.92
8/3/2016 1410138 brown 7 $18.92
9/2/2016 1410138 black 9 $18.92
10/7/2016 1410138 red 7 $18.92
10/8/2016 5926875 red 11 $18.92
8/6/2016 5926875 green 3 $18.92
7/3/2016 5926875 violet 15 $18.92
7/3/2016 5926875 black 3 $18.92
8/5/2016 5926875 brown 17 $18.92
8/5/2016 5926875 black 15 $18.92
8/2/2016 5926875 orange 13 $18.92
10/9/2016 5926875 brown 11 $18.92
7/5/2016 5926875 green 9 $18.92
10/8/2016 1531586 yellow 9 $18.99
9/1/2016 1531586 violet 3 $18.99
8/3/2016 1531586 blue 13 $18.99
8/2/2016 1531586 black 15 $18.99
7/5/2016 1531586 violet 15 $18.99
7/4/2016 1531586 violet 5 $18.99
8/5/2016 1531586 brown 1 $18.99
10/7/2016 4062706 black 1 $18.99
9/7/2016 4062706 brown 13 $18.99
10/13/2016 4062706 orange 15 $18.99
7/5/2016 4062706 red 7 $18.99
8/10/2016 4062706 black 13 $18.99
7/2/2016 4062706 green 11 $18.99
8/7/2016 4062706 violet 5 $18.99
10/9/2016 4062706 black 5 $18.99
7/3/2016 4062706 yellow 1 $18.99
9/3/2016 4062706 yellow 11 $18.99
7/6/2016 6685295 blue 11 $18.99
7/2/2016 6685295 violet 17 $18.99
8/5/2016 6685295 green 3 $18.99
10/5/2016 6685295 yellow 9 $18.99
8/11/2016 6685295 black 9 $18.99
9/8/2016 6685295 black 15 $18.99
7/5/2016 6685295 green 15 $18.99
7/5/2016 6685295 black 3 $18.99
10/4/2016 6685295 brown 5 $18.99
9/4/2016 6685295 violet 7 $18.99
8/5/2016 6685295 blue 7 $18.99
7/6/2016 6685295 brown 5 $18.99
9/8/2016 1354849 blue 1 $24.99
8/6/2016 1354849 yellow 7 $24.99
7/7/2016 1354849 brown 17 $24.99
7/6/2016 1354849 blue 7 $24.99
10/6/2016 1354849 red 17 $24.99
8/7/2016 1354849 orange 11 $24.99
10/12/2016 1354849 violet 13 $24.99
7/6/2016 1354849 red 3 $24.99
8/5/2016 1354849 blue 13 $24.99
7/6/2016 1354849 black 15 $24.99
8/6/2016 1354849 green 1 $24.99
7/5/2016 5083100 brown 11 $24.99
9/2/2016 5083100 red 13 $24.99
7/6/2016 5083100 black 1 $24.99
8/9/2016 5083100 black 11 $24.99
9/6/2016 5083100 blue 17 $24.99
10/13/2016 5083100 green 13 $24.99
9/3/2016 5083100 brown 11 $24.99
8/10/2016 5083100 blue 15 $24.99
8/6/2016 5083100 blue 13 $24.99
9/5/2016 1118224 green 1 $35.99
8/8/2016 1118224 blue 7 $35.99
9/5/2016 1118224 blue 13 $35.99
10/11/2016 1118224 brown 17 $35.99
9/1/2016 1118224 violet 17 $35.99
8/8/2016 1118224 orange 1 $35.99
9/4/2016 1118224 green 11 $35.99
9/5/2016 1118224 yellow 5 $35.99
7/1/2016 1118224 yellow 15 $35.99
8/5/2016 1118224 green 13 $35.99
9/5/2016 1118224 blue 15 $35.99
8/11/2016 7237406 blue 15 $35.99
7/2/2016 7237406 blue 9 $35.99
8/9/2016 7237406 brown 13 $35.99
7/4/2016 7237406 red 9 $35.99
8/11/2016 7237406 brown 3 $35.99
8/11/2016 7237406 green 7 $35.99
7/6/2016 7237406 red 3 $35.99
10/4/2016 7237406 yellow 11 $35.99
8/2/2016 7237406 black 9 $35.99
10/11/2016 7237406 green 3 $35.99
8/6/2016 10205467 yellow 13 $35.99
7/4/2016 10205467 violet 13 $35.99
7/6/2016 10205467 green 13 $35.99
10/4/2016 10205467 brown 1 $35.99
8/6/2016 10205467 black 11 $35.99
7/2/2016 1683535 red 3 $39.99
9/9/2016 1683535 black 15 $39.99
8/8/2016 1683535 blue 1 $39.99
9/1/2016 1683535 green 15 $39.99
7/2/2016 1683535 red 1 $39.99
8/4/2016 1683535 blue 5 $39.99
7/4/2016 1683535 blue 13 $39.99
8/2/2016 1683535 yellow 5 $39.99
8/9/2016 1683535 black 11 $39.99
9/8/2016 7982210 orange 17 $39.99
7/1/2016 7982210 violet 15 $39.99
10/7/2016 7982210 violet 11 $39.99
10/8/2016 1815816 red 15 $45.99
10/11/2016 1815816 orange 17 $45.99
10/8/2016 1815816 red 5 $45.99
8/9/2016 1815816 red 15 $45.99
9/3/2016 1815816 orange 17 $45.99
8/4/2016 1815816 black 9 $45.99
7/2/2016 9338481 red 13 $45.99
10/9/2016 9338481 yellow 3 $45.99
9/9/2016 9338481 blue 15 $45.99
9/2/2016 9338481 green 1 $45.99
9/1/2016 1670942 yellow 7 $52.99
7/6/2016 1670942 violet 5 $52.99
10/9/2016 1670942 blue 7 $52.99
10/13/2016 1670942 yellow 9 $52.99
10/13/2016 1670942 violet 5 $52.99
9/5/2016 4007846 red 1 $52.99
8/2/2016 4007846 yellow 11 $52.99
10/6/2016 4007846 brown 9 $52.99
9/8/2016 4007846 brown 1 $52.99
10/13/2016 4007846 brown 13 $52.99
8/8/2016 4007846 blue 13 $52.99
10/11/2016 4007846 orange 7 $52.99
9/7/2016 4007846 black 9 $52.99
9/7/2016 8227573 brown 15 $52.99
9/7/2016 8227573 blue 9 $52.99
9/7/2016 8227573 green 13 $52.99
10/10/2016 1639557 yellow 17 $58.95
10/12/2016 1639557 green 11 $58.95
8/4/2016 1639557 blue 11 $58.95
9/6/2016 1639557 blue 11 $58.95
7/4/2016 1639557 blue 11 $58.95
7/6/2016 1639557 brown 1 $58.95
10/7/2016 4974840 red 7 $58.95
7/4/2016 4974840 brown 5 $58.95
7/2/2016 4974840 green 15 $58.95
9/8/2016 4974840 violet 7 $58.95
8/4/2016 4974840 black 3 $58.95
8/4/2016 4974840 violet 17 $58.95
9/5/2016 6650591 brown 9 $58.95
7/3/2016 6650591 brown 7 $58.95
8/3/2016 6650591 blue 11 $58.95
9/8/2016 6650591 black 9 $58.95
10/11/2016 6650591 green 15 $58.95
8/2/2016 6650591 orange 5 $58.95
10/5/2016 6650591 violet 5 $58.95
10/11/2016 6650591 violet 1 $58.95
7/6/2016 6650591 green 17 $58.95
9/2/2016 1427649 black 11 $68.20
7/1/2016 1427649 yellow 3 $68.20
10/12/2016 1427649 brown 11 $68.20
7/4/2016 1427649 orange 1 $68.20
7/7/2016 1427649 brown 15 $68.20
9/8/2016 1427649 brown 1 $68.20
8/7/2016 4038820 red 7 $68.20
10/7/2016 4038820 green 5 $68.20
7/7/2016 4038820 brown 15 $68.20
8/3/2016 4038820 green 15 $68.20
9/3/2016 4038820 black 15 $68.20
9/6/2016 4038820 green 13 $68.20
7/6/2016 4038820 green 5 $68.20
7/7/2016 4038820 black 13 $68.20
10/10/2016 5408731 blue 13 $68.20
10/4/2016 5408731 brown 11 $68.20
9/9/2016 5408731 violet 17 $68.20
8/7/2016 5408731 red 9 $68.20
7/1/2016 5408731 brown 17 $68.20
10/6/2016 5408731 black 7 $68.20
8/8/2016 5408731 violet 3 $68.20
7/7/2016 7724914 yellow 7 $68.20
9/6/2016 7724914 green 7 $68.20
8/10/2016 7724914 brown 15 $68.20
8/9/2016 7724914 yellow 13 $68.20
9/8/2016 7724914 blue 9 $68.20
10/12/2016 7724914 black 9 $68.20
8/4/2016 7724914 green 3 $68.20
10/7/2016 7724914 violet 3 $68.20
10/7/2016 7724914 blue 5 $68.20
8/4/2016 7724914 violet 11 $68.20
9/8/2016 10136282 yellow 11 $68.20
7/5/2016 10136282 green 17 $68.20
8/5/2016 10136282 brown 13 $68.20
9/4/2016 10136282 red 3 $68.20
10/5/2016 10136282 black 11 $68.20
9/1/2016 10136282 black 1 $68.20
7/6/2016 1710265 green 11 $75.95
8/9/2016 1710265 orange 1 $75.95
9/2/2016 1710265 yellow 5 $75.95
10/6/2016 1710265 red 15 $75.95
8/8/2016 1710265 brown 5 $75.95
8/5/2016 1710265 yellow 9 $75.95
7/6/2016 3275473 red 3 $75.95
10/5/2016 3275473 black 1 $75.95
7/2/2016 3275473 violet 13 $75.95
8/2/2016 3275473 black 11 $75.95
10/10/2016 3275473 orange 11 $75.95
9/5/2016 3275473 blue 15 $75.95
8/7/2016 3275473 blue 13 $75.95
7/4/2016 4223234 green 11 $75.95
8/10/2016 4223234 orange 9 $75.95
9/3/2016 4223234 brown 7 $75.95
10/8/2016 4223234 blue 5 $75.95
8/9/2016 4223234 violet 9 $75.95
7/2/2016 4223234 green 7 $75.95
7/2/2016 4223234 black 11 $75.95
10/12/2016 4223234 black 1 $75.95
8/4/2016 5955953 black 15 $75.95
7/4/2016 5955953 brown 17 $75.95
8/4/2016 5955953 orange 1 $75.95
10/11/2016 5955953 brown 13 $75.95
9/2/2016 5955953 violet 9 $75.95
10/7/2016 5955953 brown 15 $75.95
8/7/2016 5955953 orange 15 $75.95
10/5/2016 5955953 brown 9 $75.95
9/1/2016 7813010 black 15 $75.95
9/7/2016 7813010 brown 3 $75.95
10/13/2016 7813010 blue 7 $75.95
8/2/2016 7813010 blue 3 $75.95
10/6/2016 7813010 yellow 17 $75.95
10/4/2016 7813010 violet 9 $75.95
7/7/2016 7813010 blue 9 $75.95
7/3/2016 7813010 brown 15 $75.95
9/2/2016 7813010 green 13 $75.95
10/9/2016 7813010 brown 15 $75.95
8/11/2016 7813010 red 1 $75.95
8/4/2016 9536304 violet 5 $75.95
8/3/2016 9536304 black 5 $75.95
7/4/2016 9536304 red 9 $75.95
8/9/2016 9536304 yellow 5 $75.95
9/1/2016 9536304 brown 9 $75.95
9/6/2016 9536304 yellow 3 $75.95
9/7/2016 9536304 black 15 $75.95
10/10/2016 9536304 violet 15 $75.95
9/4/2016 9536304 violet 7 $75.95
8/11/2016 9536304 red 5 $75.95
8/9/2016 10778313 black 7 $75.95
10/6/2016 10778313 green 15 $75.95
9/9/2016 10778313 violet 13 $75.99
9/9/2016 10778313 green 17 $75.99
10/9/2016 10778313 brown 3 $75.99
7/1/2016 10778313 violet 1 $75.99
8/3/2016 10778313 yellow 5 $75.99
9/3/2016 10778313 blue 13 $75.99
9/7/2016 10778313 brown 5 $75.99
7/6/2016 10778313 orange 9 $75.99
7/3/2016 10778313 orange 9 $75.99
10/6/2016 10778313 violet 1 $75.99
10/5/2016 10778313 yellow 1 $75.99
7/1/2016 10778313 red 1 $75.99
8/6/2016 1325093 blue 7 $88.50
9/6/2016 1325093 yellow 3 $88.50
8/5/2016 1325093 blue 15 $88.50
7/6/2016 1325093 black 5 $88.50
9/4/2016 1325093 blue 5 $88.50
10/12/2016 1325093 brown 17 $88.50
8/2/2016 3882149 violet 3 $88.50
10/10/2016 3882149 orange 5 $88.50
10/9/2016 3882149 red 5 $88.50
10/12/2016 3882149 black 15 $88.50
10/9/2016 3882149 green 13 $88.50
10/10/2016 3882149 black 11 $88.50
8/8/2016 3882149 yellow 1 $88.50
9/6/2016 3882149 violet 7 $88.50
10/5/2016 3882149 brown 17 $88.50
8/2/2016 8230495 brown 13 $88.50
9/4/2016 8230495 brown 7 $88.50
8/3/2016 8230495 red 5 $88.50
9/4/2016 8230495 violet 13 $88.50
8/11/2016 8230495 brown 3 $88.50
7/3/2016 8230495 orange 15 $88.50
9/1/2016 8230495 yellow 17 $88.50
10/13/2016 8230495 green 9 $88.50
8/8/2016 1542241 violet 11 $105.50
10/4/2016 1542241 blue 7 $105.50
10/5/2016 1542241 violet 7 $105.50
8/10/2016 1542241 violet 11 $105.50
9/6/2016 1542241 blue 7 $105.50
7/3/2016 1542241 blue 17 $105.50
8/10/2016 1542241 green 3 $105.50
9/5/2016 5878545 violet 7 $105.50
9/8/2016 5878545 violet 3 $105.50
9/3/2016 5878545 orange 1 $105.50
10/9/2016 5878545 violet 7 $105.50
7/1/2016 5878545 brown 9 $105.50
10/8/2016 5878545 violet 3 $105.50
10/7/2016 5878545 orange 3 $105.50
9/2/2016 5878545 yellow 7 $105.50
9/3/2016 5878545 brown 11 $105.50
8/9/2016 1543191 yellow 3 $110.45
10/12/2016 1543191 red 9 $110.45
8/3/2016 1543191 black 13 $110.45
9/5/2016 1543191 red 1 $110.45
9/9/2016 1543191 brown 13 $110.45
10/8/2016 1543191 violet 15 $110.45
10/6/2016 1543191 blue 5 $110.45
9/2/2016 1543191 yellow 9 $110.45
9/2/2016 1543191 black 9 $110.45
9/6/2016 1543191 yellow 1 $110.45
10/11/2016 1543191 brown 9 $110.45
7/6/2016 1543191 yellow 13 $110.45
10/8/2016 1543191 green 15 $110.45
10/10/2016 1543191 black 9 $110.45
10/4/2016 7198896 brown 15 $110.45
10/5/2016 7198896 brown 11 $110.45
9/5/2016 7198896 red 3 $110.45
7/7/2016 7198896 yellow 17 $110.45
10/8/2016 8604534 yellow 15 $116.99
9/9/2016 8604534 black 13 $116.99
10/6/2016 8604534 yellow 17 $116.99
10/10/2016 8604534 orange 13 $116.99
10/10/2016 8604534 red 9 $116.99
7/3/2016 10947323 violet 5 $116.99
9/7/2016 10947323 blue 1 $116.99
8/3/2016 10947323 red 7 $116.99
8/3/2016 10947323 green 15 $116.99
8/3/2016 10947323 orange 13 $116.99
he Project 2 worksheet.
es all fields in the table. Display Item SKU# and Color as Rows (Under Item
ys, Months and Quarters.

Name the Field "Total Sales". Add QTY Sold to Rows under Color.

ft corner of the chart is in cell A7. Insert a Slicer for Color and QTY Sold.

s of Green Clothing sold in October.

Total Sales of
Green Clothing
sold in Oct.

You might also like