Description

Assignment 3 (GSS)

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, and Product Sub-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?

iv.                 What was the Total Sales of Appliances in Ontario?  

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 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 greatest number of orders in 2009- 2012?

    iii.            How many orders were placed by the Corporate customer?

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

       v.            What is the sales figure of the least profitable Home Office customer?

At the minimum, each PivotTable should:

Contain all required information

Be well structured in order to easily locate information and provide accurate and complete answer(s) to the question.

Use clear and meaningful headings and labels

Use an appropriate number format

Be placed on its own worksheet with an appropriate worksheet (i.e., tab) name.

The PivotChart should be on the same worksheet as the PivotTable.

Make sure to use an appropriate chart type with complete and meaningful chart elements (e.g., chart title, data labels, legend keys, etc.) and clear and appropriate titles and labels without being too cluttered.

Save the file as Assignment3_Last Name and submit via Canvas.