Скачать или смотреть Illustrated Excel 2016 Module 12 SAM Project 1a

24 / 7 Live Support
[email protected]
website link: www.studyhelpme.com
WhatsApp: +44-7309655268
+91-9828671065
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
CustomerSP9
RegionNortheast
Order Number559Date8/15/2018ProductGrill SetQuantity40Unit Price$19.75Discount2.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: