Demonstration files for Excel data science skills
EXERCISE:
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.
QUESTION AND ANSWER:
What are the Regional Sales by Product Category and Product SubCategory?
(See Regional Sales by Cat tab of Excel file.)
What was the Total Sales figure included in this data set? 14,915,600.82 Which Product Category had the highest sales? Technology Which Region had the lowest sales? iv. What was the Total Sales of Appliances in Ontario? Nunavut 202,346.84
What are the Total Costs of Shipping by Order Priority and Ship Mode?
(See Shipping Costs tab of the Excel file.)
What was the Total Shipping Cost for Critical orders? $21,112.68
GSS incurred the most shipping costs using which shipping method? Delivery Truck (determined via Slider)
DISCUSSION: If the Delivery Truck is the most economical but the slowest shipping method and Express Air is the fastest but the most expensive one, do you think the company appropriately spent shipping costs based on the Order Priority? Please explain your answer.
It seems odd that the aggregate costs for each delivery method are distributed roughly evenly across all priorities: in other words, express deliveries are not concentrated under a critical order priority.
If we evaluate the total Profit across each delivery method and order priority (see second pivot table), it appears that critical deliveries by truck may be losing money, although this might be due to factors other than shipping cost.
I do not think the loss on critical truck deliveries is appropriate. I recommend evaluating the mismatch between order priority and delivery method, and investigating why the delivery truck method is losing money in the case of critical orders.
Who are the most valuable customers?
(See Customer Summary tab of the Excel file.)
Which Small Business customer had the highest sales? Dennis Kane
Which Corporate customer placed the most number of orders in 2009- 2012? How many orders were placed by the Corporate customer? Adam Hart with 27 orders
Which Consumer customer was the most profitable one? Emily Phan
What is the sales figure of the least profitable Home Office customer? $93.22