For analysis and visualization purposes, I used EXCEL and Tableau in this project.

Business task

Exploring the chain's sales performance over the past few years across all product categories.

Key stakeholders

·        CFO of the chain

·        Sales team

·        Sales mangers/director

Business task

Exploring the chain's sales performance over the past few years across all product categories.

Key stakeholders

·        CFO of the chain

·        Sales team

·        Sales mangers/director

·        The data set used in this case study can be found here.

·        This data is made available by Michael Martin of Tableau community.

·        It is a public dataset.

·        The data was updated on 11/28/2017

·        The Excel file contains 3 worksheets: orders, people and returns.

·        The data date range is from 01/03/14 to 12/30/17

·        The orders table consists of 21 columns.

·        The people table consists of 2 columns.

·        The returns table consists of 2 columns.

·        The data is reliable, original, comprehensive, current, and cited.

·        The data contains no null values.

·        Maximum order date in 'orders'        12/30/17

·        Minimum order date in 'orders'        01/03/14

·        Total profit for the time period        $286,397.02

·        Total sales for the time period        $2,297,200.86

·        overall profit margin=total profits/total sales   12.47%

Explore the relative makeup of Profit by category and segment for this year:

Sales by segment

·        By using PivotTables, the data was aggregated and manipulated to meet the business task.

·        Only of the years 2015 and 2016 as they are the last recorded full years in sales.

·        There are 3 segments which are consumer, corporate and home office.

·        The total sales for that period are 1079738.107 $

·        Consumer, corporate and home office segments contribute to the sales of that period with a total of 52.18%, 31.11% and 16.71% respectively.

·        The consumer segment contributes the most to the sales of that period with around: 56.65 % in 2015 and 48.73 % in 2016.

·        The home office segment is contributing the least to the sales with around: 15.99% in 2015 and 17.27% in 2016.

Sales by category

·        Overall, the highest sales category is ‘technology’ with a percentage of 34.5% of 2015’s total profit, 37.1% of 2016’s total sales and 36.04% of the total sales.

·        Second is ‘furniture’ with a percentage of 36.2% of 2015’s total sales, 32.65% of 2016’s total sales and 34.21% of the total sales.

·        The least sales category, however, is ‘office supplies’ with a percentage of 29.1% of 2015’s total sales, 30.1% of 2016’s total sales and 29.7% of the total sales.

·        As for ‘technology’ category, the most sold item is ‘copiers’ with a percentage of 29.6 % of its total sales in 2015 and 44.6% in 2016.

·         While technology’s least sold item is ‘machines’ with a percentage of 8.8 % of its total sales in 2015 and 7.3 % in 2016.

·        As for ‘furniture’ category, the most sold item is ‘chairs’ with a percentage of 206.5 % of its total sales in 2015 and 82.8% in 2016.

·         While furniture’s least sold item is ‘tables’ with a percentage of -116.4 % of its total sales in 2015 and -42.3% in 2016.

·        As for ‘Office supplies’ category, the most sold item is ‘chairs’ with a percentage of 30.2 % of its total sales in 2015 and 29.1% in 2016.

·         While Office supplies’ least sold item is ‘supplies’ with a percentage of -0.09 % of its total sales in 2015 and -1.9% in 2016.

Profit by segment

·        There are 3 segments which are consumer, corporate and home office.

·        The total profit for that period is 143413.778 $

·        The consumer segment contributes the most to the profit of that period with around: 46.19% in 2015 and 43.73% in 2016.

·        In 2015, ‘Accessories’ is the most profitable item for the consumer segment with a percentage of 23.2 %.

·        In 2016, ‘Binders’ is the most profitable item for the consumer segment with a percentage of 14.8 %.

·        In 2015, ‘Tables’ is the least profitable item for the consumer segment with a percentage of – 12.13 %.

·        In 2016, ‘Tables’ is the least profitable item for the consumer segment with a percentage of -3.5 %.

·        The home office segment contributes the least to the profit of that period with around: 20.24% in 2015 and 18.37% in 2016.

·        In 2015, ‘Binders’ is the most significant driver of sales for the home office segment with a percentage of 19.3 % of its profit

·        In 2016, ‘Binders’ is the most significant driver of sales for the home office segment with a percentage of 20.7 % of its profit

·        In 2015, ‘Tables’ is the least significant driver of sales for the home office segment with a percentage of – 3.15 % of its profit

·        In 2016, ‘Tables’ is the least significant driver of sales for the home office segment with a percentage of -9.6 % of its profit

Profit by category

•      Overall, the most profitable category is ‘technology’ with a percentage 54.37% of 2015’s total profit, 48.63% of 2016’s total profit and 51.10% of the total profit.

•      Second is ‘office supplies’ with a percentage of with a percentage 40.73% of 2015’s total profit 42.86% of 2016’s total profit and 41.95% of the total profit.

•      However, ‘furniture’ comes third with a percentage of 4.89% 2015’s total profit, 8.51% of 2016’s total profit and 6.96% of the total profit.

 •     As for ‘technology’ category, the most profitable item is ‘Phones’ with a percentage of 31.04% of its total sales in 2015 and 23.78% in 2016.

•      While technology’s least profitable item is ‘machines’ with a percentage 8.89% of its total sales in 2015 and 7.31% in 2016.

•      As for ‘Office supplies’ category, the most profitable item is ‘Binders’ with a percentage of 30.27% of its total sales in 2015 and 29.14% in 2016.

•      While Office supplies’ least profitable item is ‘supplies’ with a percentage of-0.10% of its total sales in 2015 and-1.99% in 2016.

•      As for ‘furniture’ category, the most profitable item is ‘chairs’ with a percentage of 206.57% of its total sales in 2015 and 82.80% in 2016.

•      While furniture’s least profitable item is ‘tables’ with a percentage of -116.40% of its total sales in 2015 and -42.40% in 2016.

Performance over time

·        The biggest growth in sales was in 2016 with Technology showing the highest growth, which was 37.15% of the total sales of that year.

Total sales trends over time

·        In general, quarters 3 and 4 produce most sales over the time between 2014 and 2017, where quarters 1 and 2 are least.

·        Quarter 4 of 2017 is the best-selling quarter with 31.8% of that year’s total sales.

·        Quarter 1 of 2014 is the worst-selling quarter with 20.6% of that year’s total sales. 

• Q4 is the most profitable for Superstore:

• November then December are the highest in sales in Q4.

• Q4 sales are the highest in the category of technology, followed by furniture, whereas office supplies are the least sold items.

• The linear forecast trendline suggests that sales at the Superstore is increasing over time and by year; however, this trend is based on data from the available years only. More historical data would be very insightful in exploring such trend.

Average order value (AOV)

·        Total sales/number of orders= 458.6147

·        The histogram shows that the values are not normally distributed.

·        The highest average sales order was in the South region: 237.26, while the lowest average was in the Central region: 189.07.

Basic financial models 

·        Salespeople at Superstore: 2% of their region's total sales times its annual turnover is as follows for 80%, 90% and 100% of its annual turnover:

·   100000 of annual sales: 1600$, 1800$, 2000$

·   150000 of annual sales: 2400$, 2700$, 3000$

·   200000 of annual sales: 3200$, 3600$, 4000$

·        The number of units Superstore needs to sell to reach $2,000 in profits is 533 units.  

Data visualization can be found here.