Description

Case Scenario

Headquartered in Memphis, TN, Grenadier Super Store (GSS) specializes in office supplies and furniture. The company's customers range from individual consumers and small businesses (retail), to corporate organizations (wholesale) located in the United States and Canada. You are an intern working for the Canada division of GSS.

Your supervisor has given you an Excel file containing Order data from 2009-2012 and he would like you to analyze Orders/Customers/Sales data using PivotTables and PivotCharts.

Project Requirements

Using data from the starting data file, please create PivotTables and PivotCharts that can be used to answer the following questions.

What are the Regional Sales by Product Category and Product SubCategory?

Please create ONE PivotTable named “Total Sales” showing Total Sales breakdown by Region, Product Category.

Use information from the PivotTable to answer the following questions:

i.                    What was the Total Sales figure included in this data set?

ii.                  Which Product Category had the highest sales?

iii.                Which Region had the lowest sales?

What are the Total Costs of Shipping by Order Priority and Ship Mode?

Please create ONE PivotTable named “Shipping Costs” showing the total Shipping Costs organized by Ship Mode and Order Priority. On the same worksheet, please also create one a PivotChart (based on the PivotTable) to visually compare the shipping information.

Use information from the PivotTable and PivotChart to answer the following questions:

i.                    What was the % Total Shipping Cost for Critical priority orders?

ii.                  GSS incurred the % most shipping costs using which shipping method?

Who are the most valuable customers?

Please create ONE PivotTable named “Customer” showing the Customer Names who placed orders with GSS during 2009- 2012. For each customer, please also show the total number of orders, Total Sales, Total Profit, and create a calculated field called “%Profit” using this formula (Profit/Sales).

Use information from the PivotTable to answer the following questions:

        i.            Which Small Business customer had the highest sales?

      ii.            Which Corporate customer placed the highest number of orders?

    iii.            Which Consumer customer was the most profitable based on dollars?

     iv.            What is the sales figure of the least profitable Home Office customer based on profit %?