Finn's Fish Tales has sent you an Excel file with data on recent sales. The company would like you to help them gain some insight from the data by answering the questions provided. Excel Learning Objectives When you have finished this assignment, you should be able to: 1. Copy and move a worksheet 2. Change worksheet order 3. Create a pivot table 4. Insert a pivot chart 5. Resize a pivot chart 6. Add a slicer to a pivot table 7. Use paste special to paste values 8. Add a calculated field to a pivot table 9. Create a table 10. Apply styles to a table 11. Insert a total row in a table 12. Use VLOOKUP 13. Wrap text in a cell 14. Use conditional formatting 15. Fill cells using autofill 1. Open the file Sales_Order_Data. 2. Review the data to become familiar with it. (There are a number of worksheets.) 3. Make a copy of the Salesdata worksheet and name it Salesdata2. 4. Select all of the data on Salesdata2 and insert a table. On the design tab, change the table style to one of your choice. 5. In Salesdata2, click on Design and add a total row. How much is the average shipping cost? Be sure your answer is in dollars and cents. sales rep (use sales amount, not invoice total) for each of the three years. Sort the results to find out which sales rep had the most and least sales in 2018, and what were the amounts? 7. Go to the SalesPrice worksheet. Make a copy of the worksheet, and call it GrossProfit. 8. Working on GrossProfit: next to SalesPrice, create a column for Item Cost. Use VLOOKUP to bring the costs from the ItemCost worksheet. Next to item cost, create a column for gross profit in $, then one for gross profit %. Wrap the text in the headings so the columns are not to wide. Create calculations and copy them down to all of the rows. Sort the data by Gross Profit \$. Which product number has the highest gross profit? What do you notice about the gross profit percentages? 9. Finn would like an analysis of shipping costs. Put both on the same worksheet and label it ShippingCosts. Round the answers to dollars and cents. 2. Sort the data in both pivot tables from largest to smallest. 3. Which region has the highest and lowest average shipping cost? 4. Which customer has the highest and lowest average shipping cost? 5. From Salesdata insert a third pivot table in the same ShippingCosts worksheet that shows average shipping costs based on the number of units sold. Add a calculated field that shows how much shipping cost is per unit sold. What did you find? Based on this, what recommendation might you make to Finn? 10. Next Finn would like to know about how seasonality affects his sales. Insert a pivot table to show sales per month, and label it SalesBym. Add a pivot chart, and label it Sales by Month. Increase the size of the chart to make it easier to read. Do you notice any seasonality? Why do you think calecin har jn17 ara en Iniwe? Ian Jh19? 10. Next Finn would like to know about how seasonality affects his sales. Insert a pivot table to show sales per month, and label it SalesByMonth. Add a pivot chart, and label it Sales by Month. Increase the size of the chart to make it easier to read. Do you notice any seasonality? Why do you think sales in Dec. 2017 are so low? Jan. 2019? 11. Finally, Finn would like to know which sales reps met their annual goals. On SalesRep insert a pivot table to show sales by sales rep below current data. Add a slicer for years, and select only 2018, since we have data for the full year. Copy the data from the pivot table and paste the values next to the pivot table. Label this table Sales Rep performance. Next to 2018 sales, add the annual goal for each rep by referencing the data given, then a third column showing the amount over/under goal. To make it even better, try adding conditional formatting (icon sets work well here) to highlight who is over and under their goal. Which sales rep(s) is over his or her goal(s)? 12. When you have finished, put your worksheets in the following order: 1. Salesdata 2. Salesdata2 3. SalesByRep 4. GrossProfit 5. ShippingCosts 6. SalesByMonth 7. SalesRep 8. SalesPrice 9. Itemcost 10. Region 13. Save your file as Sales_Analysis_YourName. 14. Answer all of the related questions, and then upload your file here. Name it Sales_Analysis_YourName. Grading Rubric ( 30 points): Grading Rubric ( 30 points): \begin{tabular}{|l|c|} \hline Item & Points \\ \hline Multiple choice questions & 8.5 \\ \hline All worksheets included and in order & 1.5 \\ \hline Calculations and numbers are all correct & 4 \\ \hline All numbers are properly formatted & 4 \\ \hline Salesdata2 table created & 1 \\ \hline Salesdata2 taqble design is changed & 1 \\ \hline Salesbyrep pivot table created & 1 \\ \hline Salesbyrep pivot table sorted & 1 \\ \hline Gross profit is calculated correctly & 1 \\ \hline VLOOKUP used to add item costs to Grossprofit & 1 \\ \hline Shippingcosts has three pivot tables & 1 \\ \hline Calculated field added to pivot table on & 1 \\ \hline Shippingcosts & 1 \\ \hline Salesbymonth pivot table added & 1 \\ \hline Salesbymonth pivot chart added & 1 \\ \hline Salesrep includes a slicer & 1 \\ \hline Sales rep Performance shows over/under goal & 1 \\ \hline \end{tabular} Finn's Fish Tales has sent you an Excel file with data on recent sales. The company would like you to help them gain some insight from the data by answering the questions provided. Excel Learning Objectives When you have finished this assignment, you should be able to: 1. Copy and move a worksheet 2. Change worksheet order 3. Create a pivot table 4. Insert a pivot chart 5. Resize a pivot chart 6. Add a slicer to a pivot table 7. Use paste special to paste values 8. Add a calculated field to a pivot table 9. Create a table 10. Apply styles to a table 11. Insert a total row in a table 12. Use VLOOKUP 13. Wrap text in a cell 14. Use conditional formatting 15. Fill cells using autofill 1. Open the file Sales_Order_Data. 2. Review the data to become familiar with it. (There are a number of worksheets.) 3. Make a copy of the Salesdata worksheet and name it Salesdata2. 4. Select all of the data on Salesdata2 and insert a table. On the design tab, change the table style to one of your choice. 5. In Salesdata2, click on Design and add a total row. How much is the average shipping cost? Be sure your answer is in dollars and cents. sales rep (use sales amount, not invoice total) for each of the three years. Sort the results to find out which sales rep had the most and least sales in 2018, and what were the amounts? 7. Go to the SalesPrice worksheet. Make a copy of the worksheet, and call it GrossProfit. 8. Working on GrossProfit: next to SalesPrice, create a column for Item Cost. Use VLOOKUP to bring the costs from the ItemCost worksheet. Next to item cost, create a column for gross profit in $, then one for gross profit %. Wrap the text in the headings so the columns are not to wide. Create calculations and copy them down to all of the rows. Sort the data by Gross Profit \$. Which product number has the highest gross profit? What do you notice about the gross profit percentages? 9. Finn would like an analysis of shipping costs. Put both on the same worksheet and label it ShippingCosts. Round the answers to dollars and cents. 2. Sort the data in both pivot tables from largest to smallest. 3. Which region has the highest and lowest average shipping cost? 4. Which customer has the highest and lowest average shipping cost? 5. From Salesdata insert a third pivot table in the same ShippingCosts worksheet that shows average shipping costs based on the number of units sold. Add a calculated field that shows how much shipping cost is per unit sold. What did you find? Based on this, what recommendation might you make to Finn? 10. Next Finn would like to know about how seasonality affects his sales. Insert a pivot table to show sales per month, and label it SalesBym. Add a pivot chart, and label it Sales by Month. Increase the size of the chart to make it easier to read. Do you notice any seasonality? Why do you think calecin har jn17 ara en Iniwe? Ian Jh19? 10. Next Finn would like to know about how seasonality affects his sales. Insert a pivot table to show sales per month, and label it SalesByMonth. Add a pivot chart, and label it Sales by Month. Increase the size of the chart to make it easier to read. Do you notice any seasonality? Why do you think sales in Dec. 2017 are so low? Jan. 2019? 11. Finally, Finn would like to know which sales reps met their annual goals. On SalesRep insert a pivot table to show sales by sales rep below current data. Add a slicer for years, and select only 2018, since we have data for the full year. Copy the data from the pivot table and paste the values next to the pivot table. Label this table Sales Rep performance. Next to 2018 sales, add the annual goal for each rep by referencing the data given, then a third column showing the amount over/under goal. To make it even better, try adding conditional formatting (icon sets work well here) to highlight who is over and under their goal. Which sales rep(s) is over his or her goal(s)? 12. When you have finished, put your worksheets in the following order: 1. Salesdata 2. Salesdata2 3. SalesByRep 4. GrossProfit 5. ShippingCosts 6. SalesByMonth 7. SalesRep 8. SalesPrice 9. Itemcost 10. Region 13. Save your file as Sales_Analysis_YourName. 14. Answer all of the related questions, and then upload your file here. Name it Sales_Analysis_YourName. Grading Rubric ( 30 points): Grading Rubric ( 30 points): \begin{tabular}{|l|c|} \hline Item & Points \\ \hline Multiple choice questions & 8.5 \\ \hline All worksheets included and in order & 1.5 \\ \hline Calculations and numbers are all correct & 4 \\ \hline All numbers are properly formatted & 4 \\ \hline Salesdata2 table created & 1 \\ \hline Salesdata2 taqble design is changed & 1 \\ \hline Salesbyrep pivot table created & 1 \\ \hline Salesbyrep pivot table sorted & 1 \\ \hline Gross profit is calculated correctly & 1 \\ \hline VLOOKUP used to add item costs to Grossprofit & 1 \\ \hline Shippingcosts has three pivot tables & 1 \\ \hline Calculated field added to pivot table on & 1 \\ \hline Shippingcosts & 1 \\ \hline Salesbymonth pivot table added & 1 \\ \hline Salesbymonth pivot chart added & 1 \\ \hline Salesrep includes a slicer & 1 \\ \hline Sales rep Performance shows over/under goal & 1 \\ \hline \end{tabular}