Illustrated Excel 2019 | Module 8: SAM Project 1b Deal Depot ANALYZE DATA WITH PIVOTTABLES GETTING STARTED   Open the file IL_EX19_8b_ FirstLastName _1

Subject:MS ExcelPrice:19.87 Bought11

Share With

Illustrated Excel 2019 | Module 8: SAM Project 1b

Deal Depot

ANALYZE DATA WITH PIVOTTABLES

GETTING STARTED

 

Open the file

IL_EX19_8b_

FirstLastName

_1.xlsx

, available for download from the SAM

website.

?

Save the file as

IL_EX19_8b_

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_EX19_8b_

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.

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.

Illustrated Excel 2019 | Module 8: SAM Project 1b

 

Illustrated Excel 2019 | Module 8: SAM Project 1b

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.

 

Illustrated Excel 2019 | Module 8: SAM Project 1b

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.

 

Illustrated Excel 2019 | Module 8: SAM Project 1b

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.

 

Illustrated Excel 2019 | Module 8: SAM Project 1b

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