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.

As a data analyst working for the Canada division of GSS, I analyzed the Excel file containing Order data from 2009-2012 containing Orders/Customers/Sales data using PivotTables and Pivot Charts.

My Data Analysis of GSS

1: What are the Regional Sales by Product Category and Product Subcategory?

I created my First PivotTable showing Total Sales breakdown by Region, Product Category, and Product Sub-Category. I used this information from this PivotTable to answer the following questions:

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

Total Sales at GSS: $14,915,601

Which Product Category had the highest sales?

Product Category with Highest Sales: Technology

Which Region had the lowest sales? What was the Total Sales of Appliances in Ontario?

Region with Lowest Sales: Nunavut

Total Sales of Appliances in Ontario: $202,346.84

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

I created my Second PivotTable showing the total Shipping Costs organized by Ship Mode and Order Priority.

On the same worksheet, I also created a PivotChart (based on the PivotTable) to visually compare the shipping information.I used information from this PivotTable and PivotChart to answer the following questions:

What was the Total Shipping Cost for Critical orders?

Total Shipping cost for critical orders: $21,113.

GSS incurred the most shipping costs using which shipping method?

GSS incurred highest shipping cost by Delivery Trucks at $51,972.

3: Who are the most valuable customers?

I created this Third PivotTable showing the Customer Names who placed orders with GSS during 2009- 2012.

For each customer, I showed the total number of orders, Total Sales, and Total Profit. I also added a Filter that can be used to show the information specifically for each Customer Segment.

I used information from this PivotTable to answer the following questions. I filtered and sorted the data in the PivotTable to locate the answer.

Which Small Business customer had the highest sales?

Dennis Kane (small business customer) had highest sales at $75,967.59.

Which Corporate customer placed the most number of orders in 2009- 2012? How many orders were placed by the Corporate customer?

Roy Skaria (corporate customer) placed the most number of orders counting at 773 during 2009-2012.

Which Consumer customer was the most profitable one?

Emily Phan (consumer customer) was the most profitable at $34005.44.

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

Julia West (home office customer) made the least profit and sold only $31,154.38.