Illustrated Excel 2016 | Module 12: SAM Project 1b Backyard Supply Company ANALYZING DATA WITH PIVOTTABLES GETTING STARTED Open the file IL_EX16_12b_FirstLastName_1

Subject:MS ExcelPrice: Bought3

Share With

Illustrated Excel 2016 | Module 12: SAM Project 1b

Backyard Supply Company

ANALYZING DATA WITH PIVOTTABLES

GETTING STARTED

• Open the file IL_EX16_12b_FirstLastName_1.xlsx, available for download from the SAM website.
• Save the file as IL_EX16_12b_FirstLastName_2.xlsx by changing the “1” to a “2”.
  • If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
• With the file IL_EX16_12b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
  • If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

 

PROJECT STEPS

1. Travis Hollman is the sales manager for Backyard Supply Company, a wholesaler that provides supplies to gardening stores in the midwest. As an office assistant, you are helping him develop an Excel workbook that tracks and analyzes sales of the company’s new sustainability products, which include composting containers and rain barrels. Travis wants you to create PivotTables and PivotCharts to provide an overview of the company’s customers, sales, pricing, and product line.

Switch to the Orders worksheet. Travis wants to display the products each customer ordered and the total price. To provide this information, create and format a PivotTable as follows:

1. 1. Insert a PivotTable on a new worksheet based on the Orders table.
   2. Use Customers Pivot as the name of the worksheet.
   3. Add the Customer field to the Rows area of the PivotTable.
   4. Add the Product field to the Columns area of the PivotTable.
   5. Add the Total field to the Values area of the PivotTable. (Hint: The field appears as Sum of Total.)
   6. Change the Value Field Settings for the Sum of Total values to apply the Currency number format with 2 decimal places and the $ symbol.
   7. Apply the Pivot Style Medium 3 PivotTable style to the PivotTable to use a design visually similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Green, Pivot Style Medium 3.)

Refer to Final Figure 1 to confirm that the PivotTable has the correct settings and format.

2. Return to the Orders worksheet and add the record shown in Table 1 below as the last record in the Orders table. (Hint: The values for the Discounted Price and Total columns are calculated automatically.)

Table 1: New Record for the Orders Table

3. Switch to the Products worksheet and refresh the data source so it displays accurate data.
4. Modify the Products PivotTable so it is easier to interpret as follows:
   1. Turn off the Field Headers in the PivotTable.
   2. Change the Report Layout of the PivotTable to Compact Form.
   3. Turn off the Grand Totals for rows and columns.
   4. Show all Subtotals at the top of the group.
5. Modify the Sum of Discount values so they display the average discount for each product as follows:
   1. Change the Value Field Settings for the Sum of Discount values to determine the Average discounts.
   2. Use Average Discount as the custom field name, which appears as the column heading.
   3. Apply the Percentage number format with 1 decimal place.
6. Modify the Average Discounted Price values by applying the Currency number format with 2 decimal places and the $ symbol so these values are easier to understand.
7. Switch to the Monthly Sales worksheet. Provide another way to visualize the data in this worksheet by creating a PivotChart as follows:
   1. Create a Clustered Column PivotChart based on the MonthlySales PivotTable.
   2. Move and resize the PivotChart so that its upper-left corner is in cell A21 and its lower-right corner is in cell G36.
   3. Add the title Monthly Sales by State to the PivotChart.
   4. Place the title above the chart.
8. Switch to the State PivotChart worksheet. Because Travis wants to compare all of the product order amounts, change the PivotChart filter to display data for all dates, not just for May and June.
9. Switch to the Discounted Price worksheet. Because Travis wants this PivotTable to focus on customers, reorder the fields in the Rows area so the PivotTable displays data first by Customer and then by Order No.
10. Travis is interested in analyzing sales in Wisconsin. Filter the PivotTable as follows to display orders from Wisconsin in March and August, which were the most popular months for sustainable product orders:
    1. Add a State slicer to the worksheet and then move and resize it so it covers the range I3:J12.
    2. Use the State slicer to filter the PivotTable and display Wisconsin orders only.
    3. Add a second slicer based on the Date field.
    4. Move the Date slicer below the State slicer so that it covers the range I13:J21.
    5. Use the Date slicer to filter the PivotTable and display March and August orders only.
11. Switch to the Product Pricing worksheet. For each product and customer, Travis wants to compare the unit price with the average discounted price, starting with products that have the most orders. To provide this information, modify the Product Pricing PivotTable as follows:
    1. Sort the PivotTable based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom.
    2. Add the Discounted Price field to the bottom of the Values area of the PivotTable.
    3. Change the Value Field Settings for the Sum of Discounted Price values to determine the Average discounted prices.
    4. Change the number format of this field to Currency with 2 decimal places and the $ symbol.
12. Return to the Orders worksheet. Complete the Quantity per Region section as follows:
    1. In cell M9, enter a formula that uses the GETPIVOTDATA function.
    2. Using “Quantity” as the data field, extract data from the PivotTable starting in cell A5 on the Monthly Sales worksheet. Use an absolute reference to the cell.
    3. Select the grand total quantity amount for Wisconsin by using “State” as the field1 argument and “Wisconsin” as the item1 argument.

Your workbook should look like the Final Figures below. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

Final Figure 1: Customers Pivot Worksheet

Final Figure 2: Orders Worksheet

Final Figure 3: Products Worksheet

Final Figure 4: Monthly Sales Worksheet

Final Figure 5: State PivotChart Worksheet

Final Figure 6: States Worksheet

Final Figure 7: Discounted Price Worksheet

Final Figure 8: Product Pricing Worksheet