Illustrated Excel 2019 | Module 8: SAM Project 1b Deal Depot
ANALYZE DATA WITH PIVOTTABLES
· Excel Projects Help
GETTING STARTED
1. Open the file IL_EX19_8b_FirstLastName_1.xlsx, available for download from the SAM website.
2. Save the file as IL_EX19_8b_FirstLastName_2.xlsx by changing the “1” to a “2”.
a. 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.
3. With the file IL_EX19_8b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.
a. If cell B6 does not display your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. Christine Tan has a small business called Deal Depot that buys and sells items using online auction sites. She is using an Excel workbook to analyze her sales, and asks for your help in creating PivotTables and PivotCharts to provide an overview of her buyers, sales, and pricing. Go to the Sales worksheet, which contains a table of data named Sales. Add a new sale as the last record in the Sales table using the data shown in Table 1.
Table 1: New Record for the Sales Table
Buyer | B1-11 Sales Type | Auction Sales ID | 3701 Date | 12/19/2021 Item Category | Technology Quantity | 1 Unit Price | $95.00 Shipping | 3.0% Total | $97.85
2. Christine wants to create a separate table displaying the types of products each buyer purchased and the total price. To provide this information, create and format a PivotTable as follows:
a. Insert a PivotTable on a new worksheet based on the Sales table.
b. Use Sales Category Pivot as the name of the worksheet.
c. Add the Buyer field to the Rows area of the PivotTable.
d. Add the Item Category field to the Columns area of the PivotTable.
e. Add the Total field to the Values area, where it appears as Sum of Total.
f. Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with 0 decimal places and the $ symbol.
3. Change the appearance of the PivotTable as follows to make it more attractive:
a. Apply Light Green, Pivot Style Medium 11 (2nd row, 4th column in the Medium section of the PivotTable Styles gallery) to the PivotTable to use a design similar to the one applied to other PivotTables in the workbook.
b. Hide the field headers in the PivotTable to make it more compact.
4. Return to the Sales worksheet. Christine wants to display the sales totals by sales type. Insert a recommended PivotTable based on the Sales table as follows:
a. Insert the Sum of Total by Sales Type recommended PivotTable.
b. Use Totals by Type Pivot as the name of the new worksheet.
c. Change the number format of the Sum of Total field to Accounting with 2 decimal places and the $ symbol.
5. Christine decides a chart would help to analyze the sales data. On the Totals by Type Pivot worksheet, insert a Stacked Column PivotChart. Resize and position the PivotChart so that its upper-left corner is within cell A10 and its lower-right corner is within cell F23.
6. Sellers on the Deal Depot website provide 3 percent of their sales to Christine, which she considers a service charge. Add the service charge data to the PivotChart as follows so that Christine can compare the totals and charges:
a. Add a calculated field to the PivotChart.
b. Use Service Charge as the name of the calculated field.
c. The formula should multiply the Total field value by 0.03 to calculate the service charge amount.
7. Go to the Items worksheet, which contains a PivotTable named Items. Modify the Items PivotTable so that it is easier to interpret as follows:
a. Remove the Buyer field from the Rows area to focus on sales and category data.
b. Change the Report Layout of the PivotTable to Compact Form.
c. Turn off the Grand Totals for rows and columns.
d. Show all Subtotals at the bottom of the group.
8. Christine wants to display the average shipping for each sales type rather than the sum of shipping rates in the Items PivotTable. Modify the Items PivotTable as follows:
a. Change the Value Field Settings for the Sum of Shipping values to determine the Average shipping rates.
b. Use Average Shipping as the custom field name, which appears as the column heading.
c. Apply the Percentage number format with 1 decimal place.
9. Refresh the data source so that it displays accurate data, including the new record you added to the Sales table.
10. Christine wants to change the format of Average of Total values, which are difficult to interpret. Modify the Average of Total values by applying the Currency number format with 2 decimal places and the $ symbol so that these values are easier to understand.
11. Go to the Monthly Sales worksheet, which contains a PivotTable named MonthlySales. Christine wants to provide another way to visualize this data. Create a PivotChart as follows:
a. Create a Clustered Column PivotChart based on the MonthlySales PivotTable.
b. Move and resize the PivotChart so that its upper-left corner is in cell A20 and its lower-right corner is in cell G34.
c. Add the title Sales per Month to the PivotChart.
d. Apply Layout 2 to the PivotChart to display the values above the columns.
12. Add a slicer to the PivotChart as follows to make it easy for Christine to filter the data:
a. Add a slicer to the PivotChart based on the Sales Type field.
b. Position the slicer so that its upper-left corner is in cell H20 and its lower-right corner is in cell J33.
13. Christine thinks the August Auction amount seems high. Examine the details of this amount as follows:
a. Drill down into the August Auction amount in the PivotTable to display the details of the sales on a new worksheet. (Hint: Double-click the amount.)
b. Use Aug Auctions as the name of the new worksheet.
14. Go to the Sales Type PivotChart worksheet, which shows the March sales by category based on the data in the Sales Types worksheet. Christine wants to compare all of the sales, not just those for March. Modify and format the PivotChart as follows to show all months:
a. Change the PivotChart filter to compare data for all months.
b. Refresh the PivotChart to make sure it contains updated sales information.
c. Apply Style 14 to the PivotChart to remove some of the clutter.
15. Go to the Final Prices worksheet. Christine wants this PivotTable to focus on buyers, not dates. Reorder the fields in the Rows area so that the PivotTable displays data first by Buyer and then by Date.
16. Christine is interested in the performance of Direct sales during the months of April and September, which were good months for sales overall. Filter the PivotTable as follows to show the data that Christine requests:
a. Hide the PivotTable Field list so that you can work with the Sales Type slicer in the range I3:J15, and then use the Sales Type slicer to filter the PivotTable and display Direct sales only.
b. Add a second slicer based on the Date field.
c. Move the Date slicer below the Sales Type slicer and resize it so that its upper-left corner is in cell I17 and the lower-right corner is in cell J30.
d. Use the Date slicer to filter the PivotTable and display April and September orders only.
17. Go to the Price Analysis worksheet. For each item category and buyer, Christine wants to compare the average unit price with the total unit price, starting with buyers that have the most sales in each category. She also wants to display the price data by month. To provide this information, modify the PriceAnalysis PivotTable as follows:
a. Sort the PivotTable based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom.
b. Add the Unit Price field to the bottom of the Values area of the PivotTable.
c. Change the Value Field Settings for the Sum of Unit Price2 values to determine the Average discounted prices.
d. Change the number format of this field to Accounting with 2 decimal places and the $ symbol.
e. Add the Date field to the Filters area of the PivotTable.
f. Filter the PriceAnalysis PivotTable to display May order data only.
18. Return to the Sales worksheet. Christine wants to display the quantity of the Offer sales. Complete the Quantity per Sales Type section as follows:
a. In cell L7, enter a formula that uses the GETPIVOTDATA function.
b. 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.
c. Select the grand total quantity amount for the Offer sales type by using "Sales Type" as the field1 argument and "Offer" 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: Sales Category Pivot Worksheet
Final Figure 2: Totals by Type Pivot Worksheet
Final Figure 3: Sales Worksheet
Final Figure 4: Items Worksheet
Final Figure 5: Aug Auctions Worksheet
Final Figure 6: Monthly Sales Worksheet
Final Figure 7: Sales Type PivotChart Worksheet
Final Figure 8: Sales Types Worksheet
Final Figure 9: Final Prices Worksheet
Final Figure 10: Price Analysis Worksheet