Pivot Tables $-$ Application Exercise

The data provided for this exercise contains sales transactions from a retail store that sells furniture and office supplies. In many common business situations, your data will contain more information than you need to answer a question. Pivot tables are a very efficient way to quickly reduce a dataset down to the necessary info.

With the provided exercise data, create a pivot table on a new sheet and perform the following steps:

$1.$ Add the "Year" field to the "Columns" dimension.

$2.$ Add the "Category" field to the "Rows" dimension.

$3.$ To summarize the sales amounts by the category dimensions we have already chosen, add the "Sales" field to the "Values" dimension. Change the Sales values to have an Accounting format.

$4.$ Sometimes a business question involves comparison between two categories that aren't the same in terms of absolute volume. In this case comparing averages per sale can be more useful than comparing total sales. Change the Value Field settings for Sales to compute an "Average" instead of a Sum.

a$.$ What is the average value of Office Supply sales in $2027?$

$5.$ Uncheck the Sales field and drag the Shipping Cost field into the Values dimension. Change the Shipping Cost values to have an Accounting or Currency format.

$6.$ Drag the "Year" field to the "Filters dimension.

$7.$ Add the "Order Priority" field to the "Columns" dimension.

$8.$ Filter for only $"2028"$ sales.

a$.$ The company is willing to spend whatever it takes to fulfill critical priority orders. What are the total shipping costs for Furniture with a "Critical" priority in $2028?$

$9.$ Upload you Excel file.