Illustrated Excel 2013 Unit L: SAM Project 1a Tyler’s Toys ANALYZING DATA WITH PIVOTTABLES PROJECT DESCRIPTION Tyler’s Toys is a small company that crafts specialty toys from sustainable products

Subject:MS ExcelPrice:19.87 Bought3

Share With

Illustrated Excel 2013 Unit L: SAM Project 1a

Tyler’s Toys

ANALYZING DATA WITH PIVOTTABLES

PROJECT DESCRIPTION

Tyler’s Toys is a small company that crafts specialty toys from sustainable products. Although the company has only been in existence a short time, business is beginning to pick up, and the CFO has asked you to analyze the company’s Order Database to try to spot trends and opportunities. He wants you to create PivotTables and PivotCharts using the data from the Order Database to create a multi-faceted picture of the company’s customers, regional presence, pricing, and product line. 

GETTING STARTED

Download the following file from the SAM website:

IL_Excel2013_UL_P1a_FirstLastName_1.xlsx

Open the file you just downloaded and save it with the name:With the file IL_Excel2013_UL_P1a_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.

IL_Excel2013_UL_P1a_FirstLastName_2.xlsx

Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically.

PROJECT STEPS

1.             Go to the Orders worksheet. Create a Blank PivotTable on a new worksheet based on table named CustomerOrder and then format it as described below:

a.Add the Customer field to the PivotTable, so that it appears in the ROWS section of the PivotTable diagram.

b.Add the Product field to the PivotTable and, if necessary, move the Product field from the ROWS area to the COLUMNS area of the PivotTable Diagram.

c.  Add the Order Total field to the PivotTable, so that it appears in the VALUES section of the PivotTable Diagram (Hint: In the PivotTable diagram, this field will be named Sum of Order Total).

d.Apply the Accounting number format, with no decimal places, to the data in columns B:H (Hint: You only need to click on one cell in this region to modify the Value Field settings of the Order Total field). 

e.Apply Pivot Style Medium 2 to the PivotTable.

f.   Rename the worksheet OrdersPivot.

Confirm the PivotTable matches Figure 1. (Hint: Figure 1shows the values in this PivotTable after completing only Step 1. Later steps in the project will change the values displayed in this PivotTable.)

2.             Go to the Orders worksheet and add the record shown below (in Table 1)as the last record in the CustomerOrder table. The values for the Discounted Price and Order Total columns will automatically be calculated and those values are not listed below

Table 1: New Record for CustomerOrder Table

---

Column Title	Value
Customer	ID3
Region	Southwest
Order Number	239
Date	9/1/2016
Product	WildWagon
Quantity	750
Unit Price	$15.75
Discount	7.5%

 

3.             Go to the ProductAverage worksheet and refresh the data source.

4.             Turn off the Field Headers in the PivotTable, so that they do not appear in the PivotTable.

5.             Modify the existing PivotTable as described below:

a.Change the Layout of the PivotTable to Compact Form.

b.Turn off the Grand Totals for Rows and Columns.

c.  Show all Subtotals at the Top of Group.

d.Apply Pivot Style Medium 2 to the PivotTable.

6.             Modify the Sum of Discount column as described below:

a.Change the column calculation to determine the Average discount, instead of the SUM of all discount data (Hint: The title of the column will update to Average of Discount).

b.Modify the number format of this column to display as Percentage with 1 decimal places.

7.             Modify the Average of Discounted Price column, so that the values display with the Accounting number format with 2 decimal places.

8.             Go to the MonthPivot worksheet. Create a Clustered Column PivotChart based on the existing PivotTable. Format the PivotChart as described below:

a.Move the PivotChart to a new worksheet, and name the worksheet UnitPivotChart.

b.Add the Chart Title Unit Sales by Month to the PivotChart. Place the title above the chart.

9.             Go to the RegionPivotChart worksheet. Change the PivotChart filter, so that it displays data for all dates, not just for June.

10.         Modify the Sum of Discounted Price field, so that the data is displayed as the average of the Discounted Price field.

11.         Go to the PricePivot worksheet. Reorder the fields in the ROWS area of the PivotTable diagram, so first the PivotTable displays data by the Customer field and then by Order Number field.

12.         Use the slicer based on the Region field to filter the PivotTable, so that only data from the Southwest and New England regions are displayed.

13.         Insert a second slicer into the worksheet, based on the Date field (Hint: If necessary, move the Date slicer so that it doesn’t overlap the PivotTable or Region slicer). Use the Date slicer to filter the PivotTable to display only data for the months August, September, and November.

14.         Go to the ProductPivot worksheet. Sort the PivotTable based on the Sum of Quantity field, sorting from Largest to Smallest and Top to Bottom.

15.         Add the Discounted Price field to the PivotTable in the Values area of the PivotTable diagram and then update the field as described below:

a.    Modify the field to calculate the average Discount Price, instead of summing the Discount Price field values.

b.    Change the number format of this field to Accounting with 2 decimal places.

16.         Return to the Orders worksheet. In cell L3, enter a formula that uses the GETPIVOTDATA function to determine the total number of products sold in New England. This data will be extracted from the Quantity field in the PivotTable in range A3:F17 on the MonthPivot worksheet (Hint: Remember that you are retrieving data where the Region field is equal to New England).

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