Illustrated Excel 2016 | Module 12: SAM Project 1a The Spice Market ANALYZING DATA WITH PIVOTTABLES   GETTING STARTED Open the file IL_EX16_12a_FirstLastName_1

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2016 | Module 12: SAM Project 1a

The Spice Market

ANALYZING DATA WITH PIVOTTABLES

 

GETTING STARTED

Open the file IL_EX16_12a_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as IL_EX16_12a_FirstLastName_2.xlsx by changing the “1” to a “2”.

0. 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_EX16_12a_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

Anita Rossman is the sales manager for The Spice Market, a national wholesaler of spices and dried herbs. As Anita’s assistant, you are helping her develop an Excel workbook that tracks and analyzes sales of their new spice packs, which include coordinated sets of spices. Anita wants you to create PivotTables and PivotCharts to provide an overview of the company’s customers, regional sales, pricing, and product line.

Switch to the Orders worksheet. Anita wants to display the products each customer ordered 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 Orders table.

b. Use Orders Pivot as the name of the worksheet.

c. Add the Customer field to the Rows area of the PivotTable.

d. Add the Product field to the Columns area of the PivotTable.

e. Add the Total field to the Values area of the PivotTable. (Hint: The field appears as Sum of Total.)

f. Change the Value Field Settings for the Sum of Total values to apply the Accounting number format with zero decimal places and the $ symbol.

g. Apply Pivot Style Medium 10 to the PivotTable to use a design similar to the one applied to the Orders table. (Hint: Depending on your version of Office, the PivotTable style may appear as Light Orange, Pivot Style Medium 10.)

Refer to Final Figure 1 to confirm that the PivotTable has the correct settings and format.

Return to the Orders worksheet and add the record shown in Table 1 below as the last record in the Orders table. (Hint: The values for the Discounted Price and Total columns are calculated automatically.)

Table 1: New Record for the Orders Table

 

Column Title	Value
Customer	SP9
Region	Northeast
Order Number	559
Date	8/15/2018
Product	Grill Set
Quantity	40
Unit Price	$19.75
Discount	2.5%

 

 

Switch to the Products worksheet and refresh the data source so it displays accurate data.

Modify the Products PivotTable so it is easier to interpret as follows:

h. Turn off the Field Headers in the PivotTable.

i. Change the Report Layout of the PivotTable to Compact Form.

j. Turn off the Grand Totals for rows and columns.

k. Show all Subtotals at the top of the group.

Modify the Sum of Discount values so they display the average discount for each product as follows:

l. Change the Value Field Settings for the Sum of Discount values to determine the Average discounts.

m. Use Average Discount as the custom field name, which appears as the column heading.

n. Apply the Percentage number format with one decimal place.

Modify the Average Discounted Price values by applying the Currency number format with two decimal places and the $ symbol so these values are easier to understand.

Switch to the Monthly Sales worksheet. Provide another way to visualize the data in this worksheet by creating a PivotChart as follows:

o. Create a Clustered Column PivotChart based on the MonthlySales PivotTable.

p. Move and resize the PivotChart so that its upper-left corner is in cell A21 and its lower-right corner is in cell G34.

q. Add the title Sales by Month to the PivotChart.

r. Place the title above the chart.

Switch to the Region PivotChart worksheet. Because Anita wants to compare all of the product order amounts, change the PivotChart filter to display data for all dates, not just for March.

Switch to the Discounted Price worksheet. Because Anita wants this PivotTable to focus on customers, reorder the fields in the Rows area so the PivotTable displays data first by Customer and then by Order Number.

Anita is interested in the performance of the Midwest region. Filter the PivotTable to display orders from the Midwest in June and August, which were the most popular months for spice pack orders, as follows:

s. Use the Region slicer in the range I3:K15 to filter the PivotTable and display Midwest orders only.

t. Add a second slicer based on the Date field.

u. Move the Date slicer below the Region slicer so that its upper-left corner is in cell I16 and the lower-right corner is in cell K28.

v. Use the Date slicer to filter the PivotTable and display June and August orders only.

Switch to the Product Pricing worksheet. For each product and customer, Anita wants to compare the unit price with the average discounted price, starting with products that have the most orders. To provide this information, modify the Product Pricing PivotTable as follows:

w. Sort the PivotTable based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom.

x. Add the Discounted Price field to the bottom of the Values area of the PivotTable.

y. Change the Value Field Settings for the Sum of Discounted Price values to determine the Average discounted prices.

z. Change the number format of this field to Accounting with two decimal places and the $ symbol.

Return to the Orders worksheet. Complete the Quantity per Region section as follows:

aa. In cell M9, enter a formula that uses the GETPIVOTDATA function.

ab. 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.

ac. Select the grand total quantity amount for the Southwest region by using “Region” as the field1 argument and “Southwest” as the item1 argument.

Your workbook should look like the Final Figure below. Save your changes, close the workbook, then exit Excel. Follow the directions on the SAM website to submit your completed project.

 

Final Figure 1: Orders Pivot Worksheet

 

Final Figure 2: Orders Worksheet

 

Final Figure 3: Products Worksheet

 

Final Figure 4: Monthly Sales Worksheet

Final Figure 5: Region PivotChart Worksheet

 

Final Figure 6: Regions Worksheet

 

Final Figure 7: Discounted Price Worksheet

 

Final Figure 8: Product Pricing Worksheet