Illustrated Excel 2019 | Module 8: SAM Project 1a

M&K Merchants Bank
ANALYZE DATA WITH PIVOTTABLES

 

      GETTING STARTED

  • Open the file IL_EX19_8a_FirstLastNamexlsx, available for download from the SAM website.
  • Save the file as IL_EX19_8a_FirstLastNamexlsx 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_EX19_8a_FirstLastNamexlsx 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. Sandra Russo is an account manager at M&K Merchants Bank, where she consults with businesses of all sizes to help them be successful and profitable. Sandra is using an Excel workbook to analyze the financial performance of Home Chef, the market leader in producing midrange kitchen utensils. She asks for your help in creating PivotTables and PivotCharts to provide an overview of Home Chef’s customers, regional sales, pricing, and product line.
    Go to the Orders worksheet, which contains a table of data named Orders.
    Add a new order as the last record in the Orders table using the data shown in Table 1.

Table 1: New Record for the Orders Table
 

Customer CS20
Region Atlantic
Order Number 1525
Date 8/17/2021
Product Tongs
Quantity 10
Unit Price $15.75
Discount 2.5%

 

  1. Sandra wants to create a separate table displaying the products each customer ordered and the total price.
    To provide this information, create and format a PivotTable as follows:

    1. Insert a PivotTable on a new worksheet based on the Orders
    2. Use Orders and Price 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, where it appears as Sum of Total.
    6. Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 0 decimal places and the $
  2. Change the appearance of the PivotTable as follows to make it more attractive:
    1. Apply Dark Teal, Pivot Style Dark 4 (1st row, 4th column in the PivotTable Styles gallery) to the PivotTable to use a design similar to the one applied to other PivotTables in the workbook.
    2. Hide the field headers in the PivotTable to make it more compact.
  3. Return to the Orders Sandra wants to display the order totals by region. Insert a recommended PivotTable based on the Orders table as follows:
    1. Insert the Sum of Total by Region recommended PivotTable.
    2. Use Totals by Region Pivot as the name of the new worksheet.
    3. Change the number format of the Sum of Total field to Accounting with 2 decimal places and the $
  4. Sandra decides a chart would help to analyze the order data. On the Totals by Region Pivot worksheet, insert a Stacked Column Resize and position the PivotChart so that its upper-left corner is within cell D3 and its lower-right corner is within cell K17.
  5. Home Chef sales representatives earn a 10 percent dividend on each order. Add the dividend data to the PivotChart as follows so that Sandra can compare the totals and dividends:
    1. Add a calculated field to the PivotChart.
    2. Use Dividends as the name of the calculated field (Hint: The Dividends field will appear as Sum of Dividends).
    3. The formula should multiply the Total field value by 1 to calculate the commission amount.
  6. Go to the Products worksheet, which contains a PivotTable named Products. Modify the Products PivotTable so that it is easier to interpret as follows:
    1. Remove the Customer field from the Rows area to focus on product and region data.
    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.
  7. Refresh the data source so that it displays accurate data, including the new record you added to the Orders table.
  8. Sandra wants to display the average discount for each product rather than the sum of discount values in the Products PivotTable.
    Modify the Products PivotTable as follows:

    1. Change the Value Field Settings for the Sum of Discount values to determine the Average
    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.
  9. Sandra wants to change the format of Average Discounted Price values, which are difficult to interpret.
    Modify the Average Discounted Price values by applying the Currency number format with 2 decimal places and the $ symbol so that these values are easier to understand.
  10. Go to the Monthly Sales worksheet, which contains a PivotTable named MonthlySales. Sandra wants to provide another way to visualize this data.
    Create 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 within cell A20 and its lower-right corner is within cell G38.
    3. Add the title Monthly Sales to the PivotChart in the above position.
    4. Apply Layout 3 to the PivotChart to display the legend at the bottom of the chart area.
  11. Add a slicer to the PivotChart as follows to make it easy for Sandra to filter the data:
    1. Add a slicer to the PivotChart based on the Region
    2. Position the slicer so that its upper-left corner is within cell H20 and its lower-right corner is within cell J33.
  12. Sandra thinks the Midwest quantity amount for September seems high. Examine the details of this amount as follows:
    1. Drill down into the Midwest quantity amount for September to display the details of the sales on a new worksheet. (Hint: Double-click the amount.)
    2. Use Sep Midwest as the name of the new worksheet.
  13. Go to the Region PivotChart worksheet, which shows the May product orders in the Pacific region based on the data in the Regions Sandra wants to compare all the product order amounts, not just those for May.
    Modify and format the PivotChart as follows to show all months:

    1. Refresh the PivotChart to make sure it contains updated order information.
    2. Change the PivotChart filter to compare data for all months. [Mac Hint: Deselect the date filter to perform this step.]
    3. Apply Style 10 to the PivotChart to display the data more clearly.
  14. Go to the Discounted Price Sandra wants this PivotTable to focus on customers, not order numbers.
    Reorder the fields in the Rows area so that the PivotTable displays data first by Customer and then by Order Number.
  15. Sandra is interested in the performance of the Pacific region during the months of September and October, which were the months of the most sales for kitchen utensils.
    Filter the PivotTable as follows to show the data that Sandra requests:

    1. Use the Region slicer to filter the PivotTable and display Pacific region orders only.
    2. Add a second slicer based on the Date
    3. Move the Date slicer below the Region slicer so that its upper-left corner is within cell J18 and the lower-right corner is within cell K28.
    4. Use the Date slicer to filter the PivotTable and display September and October orders only.
  16. Go to the Product Pricing For each product and customer, Sandra wants to compare the unit price with the average discounted price, starting with products that have the most orders. She also wants to display the product pricing data by month.
    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 Accounting with 2 decimal places and the $
    5. Add the Date field to the Filters area of the PivotTable.
    6. Filter the Product Pricing PivotTable to display January order data only.
  17. Return to the Orders Sandra wants to display the quantity of the orders in the Southeast region.
    Complete the Quantity per Region section as follows:

    1. In cell M8, enter a formula that uses the GETPIVOTDATA
    2. Using “Quantity” as the data field, extract data from the PivotTable starting in cell A5 on the Monthly Sales Use an absolute reference to the cell.
    3. Select the grand total quantity amount for the Southeast region by using “Region” as the field1 argument and “Southeast” as the item1 argument.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project.
 
 
Final Figure 1: Orders and Price Pivot Worksheet
 
 
Final Figure 2: Totals by Region Pivot Worksheet
 
 
Final Figure 3: Orders Worksheet
 
 
Final Figure 4: Products Worksheet
 
 
Final Figure 5: Sep Midwest Worksheet
 
 
Final Figure 6: Monthly Sales Worksheet
 
 
Final Figure 7: Region PivotChart Worksheet
 
 
Final Figure 8: Discounted Price Worksheet
 
 
Final Figure 9: Product Pricing Worksheet
 
 

Excel
Tagged on:     
We have updated our contact contact information. Text Us Or WhatsApp Us+1-(309) 295-6991