| | Illustrated Excel 2019 | Module 8: SAM Project 1a |
| | Ruby Rawls |
| | SUBMISSION #1 | SCORE IS: 18 OUT OF 100 | | GE ver. 12.0.0-rc0000 |
| 1. | Sandra Russo is an account manager at M&K Merchants Bank, where she consults with businesses of all sizes to help them be successful and profitable. Sandra is using an Excel workbook to analyze the financial performance of Home Chef, the market leader in producing midrange kitchen utensils. She asks for your help in creating PivotTables and PivotCharts to provide an overview of Home Chef's customers, regional sales, pricing, and product line.
Go to the Orders worksheet, which contains a table of data named Orders.
Add a new order as the last record in the Orders table using the data shown in Table 1. | | 0/5 |
| | | Add a record to a table. |
| | | In the Orders worksheet, the range A4:J31 should be formatted as a table. |
| 2. | Sandra wants to create a separate table displaying 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 and Price 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, 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/5 |
| | | Create a PivotTable. |
| | | Add rows to a PivotTable. |
| | | Add fields as columns in a PivotTable. |
| | | In the Orders and Price Pivot worksheet, the Product field should be added to the Columns area of the PivotTable. |
| | | Add values to a PivotTable. |
| | | Modify the number format of a PivotTable field. |
| | | In the PivotTable on the Orders and Price Pivot worksheet, the Sum of Total field in the Values section should be formatted using the Accounting number format with zero decimal places and the $ symbol. |
| 3. | Change the appearance of the PivotTable as follows to make it more attractive:
a. Apply Dark Teal, Pivot Style Dark 4 (1st row, 4th column in 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. | | 0/5 |
| | | Apply a PivotTable style. |
| | | In the Orders and Price Pivot worksheet, the PivotTable should be formatted using the Dark Teal, Pivot Style Dark 4 PivotTable style. |
| | | Hide field headers in a PivotTable. |
| | | In the Orders and Price Pivot worksheet, the PivotTable should be formatted to hide field headers. |
| 4. | Return to the Orders worksheet. Sandra wants to display the order totals by region. Insert a recommended PivotTable based on the Orders table as follows:
a. Insert the Sum of Total by Region recommended PivotTable.
b. Use Totals by Region 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. | | 0/5 |
| | | Create a PivotTable. |
| | | In the Totals by Region Pivot worksheet, the Sum of Total by Region recommended PivotTable has not been added. |
| | | Modify the number format of a PivotTable field. |
| | | In the PivotTable on the Totals by Region Pivot worksheet, the Total field in the Values section should be formatted using the Accounting number format with two decimal places and the $ symbol. |
| 5. | Sandra decides a chart would help to analyze the order data. On the Totals by Region Pivot worksheet, insert a Stacked Column PivotChart. Resize and position the PivotChart so that its upper-left corner is within cell D3 and its lower-right corner is within cell K17. | | 0/5 |
| | | Create a PivotChart. |
| | | The Totals by Region Pivot worksheet should contain a stacked column PivotChart based on the PivotTable beginning in cell A3. |
| | | Resize a PivotChart. |
| | | The Totals by Region Pivot worksheet should contain a stacked column PivotChart based on the PivotTable beginning in cell A3. |
| 6. | Home Chef sales representatives earn a 10 percent dividend on each order. Add the dividend data to the PivotChart as follows so that Sandra can compare the totals and dividends:
a. Add a calculated field to the PivotChart.
b. Use Dividends as the name of the calculated field (Hint: The Dividends field will appear as Sum of Dividends).
c. The formula should multiply the Total field value by 0.1 to calculate the commission amount. | | 0/5 |
| | | Add a calculated field to a PivotTable. |
| | | In the Totals by Region Pivot worksheet, the PivotTable should contain a calculated field that multiplies the Total field value by 0.1. |
| 7. | Go to the Products worksheet, which contains a PivotTable named Products. Modify the Products PivotTable so that it is easier to interpret as follows:
a. Remove the Customer field from the Rows area to focus on product and region 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 top of the group. | | 3/5 |
| | | Add rows to a PivotTable. |
| | | Change the layout of a PivotTable. |
| | | Remove Grand Totals from a PivotTable layout. |
| | | In the Products worksheet, the PivotTable should be formatted to hide Grand Totals for rows and columns. |
| | | Update the layout of a PivotTable with subtotals. |
| | | In the Products worksheet, the PivotTable should display subtotals at the top of the group. |
| 8. | Refresh the data source so that it displays accurate data, including the new record you added to the Orders table. | | 5/5 |
| | | Refresh Pivot Table data. |
| 9. | Sandra wants to display the average discount for each product rather than the sum of discount values in the Products PivotTable.
Modify the Products PivotTable as follows:
a. Change the Value Field Settings for the Sum of Discount values to determine the Average discounts.
b. Use Average Discount as the custom field name, which appears as the column heading.
c. Apply the Percentage number format with 1 decimal place. | | 2/6 |
| | | Summarize field values in a PivotTable. |
| | | In the Products worksheet, the Discount field in the Values area of the PivotTable should be summarized using the Average function. |
| | | Update the name of a PivotTable field. |
| | | Modify the number format of a PivotTable field. |
| | | In the Products worksheet, the Average Discount field in the Values section of the PivotTable should be formatted using the Percentage number format with one decimal place. |
| 10. | Sandra wants to change the format of Average Discounted Price values, which are difficult to interpret.
Modify the Average Discounted Price values by applying the Currency number format with 2 decimal places and the $ symbol so that these values are easier to understand. | | 0/6 |
| | | Modify the number format of a PivotTable field. |
| | | In the Products worksheet, the Average Discounted Price field in the Values section of the PivotTable should be formatted using the Currency number format with two decimal places and the $ symbol. |
| 11. | Go to the Monthly Sales worksheet, which contains a PivotTable named MonthlySales. Sandra 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 within cell A20 and its lower-right corner is within cell G38.
c. Add the title Monthly Sales to the PivotChart in the above position.
d. Apply Layout 3 to the PivotChart to display the legend at the bottom of the chart area. | | 0/6 |
| | | Create a PivotChart. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| | | Resize a PivotChart. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| | | Add a title to a PivotChart. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| | | Change the layout of a PivotChart. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| 12. | Add a slicer to the PivotChart as follows to make it easy for Sandra to filter the data:
a. Add a slicer to the PivotChart based on the Region field.
b. Position the slicer so that its upper-left corner is within cell H20 and its lower-right corner is within cell J33. | | 0/6 |
| | | Add a slicer to a PivotChart. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| | | Resize a slicer. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| 13. | Sandra thinks the Midwest quantity amount for September seems high. Examine the details of this amount as follows:
a. Drill down into the Midwest quantity amount for September to display the details of the sales on a new worksheet. (Hint: Double-click the amount.)
b. Use Sep Midwest as the name of the new worksheet. | | 0/6 |
| | | Drill down into a PivotTable. |
| | | The workbook should contain a worksheet named "Monthly Sales". |
| 14. | Go to the Region PivotChart worksheet, which shows the May product orders in the Pacific region based on the data in the Regions worksheet. Sandra wants to compare all the product order amounts, not just those for May.
Modify and format the PivotChart as follows to show all months:
a. Refresh the PivotChart to make sure it contains updated order information.
b. Change the PivotChart filter to compare data for all months. [Mac Hint: Deselect the date filter to perform this step.]
c. Apply Style 10 to the PivotChart to display the data more clearly. | | 2/6 |
| | | Refresh PivotChart data. |
| | | Filter a PivotChart. |
| | | In the Region PivotChart worksheet, the PivotChart should be filtered to show data for all months using the Date field. |
| | | Change the PivotChart style. |
| | | In the Region PivotChart worksheet, the clustered column PivotChart should be formatted using the Style 10 chart style. |
| 15. | Go to the Discounted Price worksheet. Sandra wants this PivotTable to focus on customers, not order numbers.
Reorder the fields in the Rows area so that the PivotTable displays data first by Customer and then by Order Number. | | 0/6 |
| | | Reorder rows in a PivotTable. |
| | | In the Discounted Price worksheet, the Customer field should be positioned before the Order Number field in the Rows area of the PivotTable. |
| 16. | Sandra is interested in the performance of the Pacific region during the months of September and October, which were the months of the most sales for kitchen utensils.
Filter the PivotTable as follows to show the data that Sandra requests:
a. Use the Region slicer to filter the PivotTable and display Pacific region orders only.
b. Add a second slicer based on the Date field.
c. Move the Date slicer below the Region slicer so that its upper-left corner is within cell J18 and the lower-right corner is within cell K28.
d. Use the Date slicer to filter the PivotTable and display September and October orders only. | | 2/6 |
| | | Filter a PivotTable using a slicer. |
| | | Add a slicer to a PivotTable. |
| | | In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable. |
| | | Resize a slicer. |
| | | In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable. |
| | | Filter a PivotTable using a slicer. |
| | | In the Discounted Price worksheet, a slicer based on the Date field should be added to the PivotTable. |
| 17. | Go to the Product Pricing worksheet. For each product and customer, Sandra wants to compare the unit price with the average discounted price, starting with products that have the most orders. She also wants to display the product pricing data by month.
To provide this information, modify the Product Pricing 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 Discounted Price field to the bottom of the Values area of the PivotTable.
c. Change the Value Field Settings for the Sum of Discounted Price 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 Product Pricing PivotTable to display January order data only. | | 1/6 |
| | | Sort a PivotTable. |
| | | In the Product Pricing worksheet, the PivotTable beginning in cell A5 should be sorted from largest to smallest and top to bottom. |
| | | Add values to a PivotTable. |
| | | Summarize field values in a PivotTable. |
| | | In the Product Pricing worksheet, the Discounted Price field in the Values area of the PivotTable should be summarized using the Average function. |
| | | Modify the number format of a PivotTable field. |
| | | In the Product Pricing worksheet, the Discounted Price field in the Values area of the PivotTable should be formatted using the Accounting number format with two decimal places. |
| | | Add a filter to a PivotTable. |
| | | In the Product Pricing worksheet, the Date field should be added to the Filters area of the PivotTable. |
| | | Filter a PivotTable using a filter. |
| | | In the Product Pricing worksheet, the Date field should be added to the Filters area of the PivotTable. |
| 18. | Return to the Orders worksheet. Sandra wants to display the quantity of the orders in the Southeast region.
Complete the Quantity per Region section as follows:
a. In cell M8, 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 Southeast region by using "Region" as the field1 argument and "Southeast" as the item1 argument. | | 0/6 |
| | | Create a formula using a function. |
| | | In the Orders worksheet, the formula in cell M8 should use the text "Quantity" as the data_field argument. |
remikaur8
IL_EX19_8a_RubyRawls_Report_1.xlsx
