Скачать или смотреть Illustrated Excel 2019 | Module 8: SAM Project 1b

Please contact us if you need this project or any other project in any subject to be completed.
Whatsapp: +1-343-321-3276
Email: [email protected]
WhatsApp Link: https://wa.me/13433213276

#IllustratedExcel2019 #Module8SAMProject1b
#DealDepot
Illustrated Excel 2019 | Module 8: SAM Project 1b
Deal Depot
ANALYZE DATA WITH PIVOTTABLES
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:
3. Change the appearance of the PivotTable as follows to make it more attractive:
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:
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:
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:
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:
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:
12. Add a slicer to the PivotChart as follows to make it easy for Christine to filter the data:
13. Christine thinks the August Auction amount seems high. Examine the details of this amount as follows:
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:
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:
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:
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.