Excel Module 8 SAM Project 1b | Deal Depot | ANALYZE DATA WITH PIVOTTABLES #illustratedexcel

Excel Module 8 SAM Project 1b | Deal Depot | ANALYZE DATA WITH PIVOTTABLES #illustratedexcel

If you directly want to get the project from us then contact us on our Whatsapp. Link is given here,

Whatsapp Contact Link:
https://api.whatsapp.com/message/4B6NMKKBKUFYN1?autoload=1&app_absent=0

Whatsapp Number:
+919116641093
+918005564456

Gmail Id:
singhal.agrawal.bharati@gmail.com

We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English.

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:

Видео Excel Module 8 SAM Project 1b | Deal Depot | ANALYZE DATA WITH PIVOTTABLES #illustratedexcel канала Homework Help