Advanced Project 10-7

These instructions are only compatible with the Microsoft Windows operating system.

Wilson Home Entertainment Systems uses Power Pivot to combine sales and representatives’ data. You create named sets to separate the odd- and even-numbered transactions that result from different locations.

[Student Learning Outcomes 10.5, 10.6, 10.8]

 DO NOT use the files below if you are completing an autograded SIMnet Project. Using the linked files below will prevent you from uploading and submitting your work in SIMnet. The files for all autograded SIMnet Projects MUST be downloaded from your class assignment.

File Needed: WilsonHome-10.xlsx and Database-10.accdb (Student data files are available in the Library of your SIMnet account.)

Completed Project File Name: [your initials] Excel 10-7.xlsx

Skills Covered in This Project

• Get data in Power Pivot.
• Add a calculated column in Power Pivot.
• Create a PivotTable from the data model.
• Edit fields in a PivotTable.
• Copy a PivotTable.
• Create named sets in a PivotTable.
• Pivot fields in a PivotTable.
• Use GETPIVOTDATA in a worksheet.

1. Create a new workbook and save it as [your initials] Excel 10-7.
2. Get data in Power Pivot.
   1. Launch Power Pivot.
   2. Get the representative’s data from the WilsonHome-10 Excel workbook in your student data files . The first row is a header row (first Import Wizard). There are 40 rows.
   3. Get tblWilsonHome from the Access Database-10. There are 40 rows in this table.
   4. Create the relationship between the tables.
3. Manipulate data in Power Pivot.
   1. Build a formula to add a column to tblWilsonHome to determine the total transaction amount. Change the label to Amount.
   2. Add a column to tblWilsonHome that displays a 4% commission amount based on the amount. You must use the decimal equivalent. Change the column label to Commission.
   3. Add a column named Average Sale and use the AVERAGE function with the Amount column. The average is the same for all rows.
   4. Set the data type for the Price, Amount, Commission, and Average Sale fields as Currency.
   5. Add a Sum measure for the Amount column.
   6. Add a Sum measure for the Average Sale column (Figure 10-99).
4. Build a PivotTable from the data model in Power Pivot.
   1. Select the first data cell in tblWilsonHome and create a PivotTable in your existing worksheet.
   2. Show the transaction ID, the sales representative, and the style in that order as Rows entries. Add the price and the amount as values. (You may need to edit the relationship [PivotTable Analyze tab] to show tblWilsonHome as the table and Reps as the related table.)
   3. Format the PivotTable report layout as tabular form.
   4. Edit the labels in columns D and E to remove “Sum of.”
   5. AutoFit the value columns.
   6. Rename the sheet as All.
5. Copy a PivotTable.
   1. Copy the All sheet to the end and name the copy EvenID.
   2. Copy either sheet to the end and name the copy as OddID.
   3. Select a cell in the PivotTable on the OddID sheet.
   4. Remove the style, price, and amount fields and show the commission field. Arrange the Rows group so that the transaction ID is first.
   5. Delete “Sum of” in the label row and AutoFit the columns.
6. Create named sets in PivotTables.
   1. Create a set based on row items named Odd.
   2. Deselect the Replace the fields currently in the row area with the new set box.
   3. Select and delete each even-numbered transaction and the row that displays All (Figure 10-100).
   4. Display the Odd set in the PivotTable and remove the existing fields.
   5. Select the EvenID sheet tab.
   6. Remove the style, price, and amount fields and show the commission field. Arrange the Rows field so that the transaction ID is first.
   7. Delete “Sum of” and AutoFit the columns.
   8. Create a set based on row items named Even for all the even-number rows.
   9. Display the Even set in the PivotTable.
7. Use GETPIVOTDATA to display product sales for the period.
   1. Select the All sheet tab and make a copy at the end named GetPivot.
   2. Remove all fields and display the Style and Sales Representative fields as rows in that order and the Quantity field as a value.
   3. Show subtotals at the bottom of each group.
   4. Open WilsonHome-10 from your student data files and copy the Products sheet as the last sheet in your workbook.
   5. Close WilsonHome-10 and select the Products sheet in your workbook.
   6. Select cell C6, type =, and get the Soundbar total on the GetPivot sheet.
   7. Complete the Products sheet to get the totals for two additional products.
   8. Select the GetPivot sheet and place the Color field in the Columns area to rearrange the PivotTable.
   9. Return to the Products sheet to verify the totals. The GETPIVOT function displays the result if the field displays in the PivotTable.
8. Build a Key Performance Indicator for the PivotTable.
   1. Select the All sheet tab. (If the Power Pivot tab does not display, show it from the Excel Options dialog box.)
   2. Click the KPIs button [Power Pivot tab, Calculations group] and create a new KPI.
   3. Use the Sum of Amount measure as the base field and Sum of Average Sale for the measure.
   4. Define the low end at 60% and the high end at 90%.
   5. Select the diamond, triangle, and ellipse icon set.
   6. Show the Status of the Sum of Amount KPI in the PivotTable. If the column first displays values, deselect the box in the PivotTable Fields pane and then re-select it.
   7. Change the label for the Status column to Amt/Average.
   8. Select and center the icons and AutoFit the column.
   9. Do not show totals in the PivotTable.
9. Save and close the workbook (Figure 10-101).