An American bike company called Pro Bikes Inc. has two primary sales channels. These are the physical stores and online stores. Within the online channel, the company sells bikes from its own website and from other popular online platforms. The company sells various bikes and bike accessories such as helmets, tires, knee pads, water bottles, etc.
The company has three brands of bikes and each brand has multiple sizes, colors and related varieties. In total, the company sells 125 unique bikes. Managing such a high number of SKUs (stock keeping units) is associated with cost in form of inventory technology. The management wants to conduct a product rationalization exercise to fine tune its product portfolio. It has assigned the responsibility to its lead business analyst.
As a business analyst, I’ve defined the scope of the exercise. It includes creating a mechanism to identify products that make financial sense for the company and identify the ones that do not. I’ve made three buckets in which products need to be categorized. The purpose of 'bucketing' products is to make data driven recommendations on whether or not to retire products that are not contributing, so that company’s resources can be utilized on other products efficiently. The buckets are as follows.
- Top contributor – Products with high sales and high margin
- Bottom contributor – Products with low sales and low margin
- Middle contributor – Products with decent sales and decent margin
The three buckets form part of the requirements. Following are the requirements captured in user story format.
Since customer information is sensitive, the data resides in company’s secured SQL server. I have been granted a read only access to the SQL database to perform analysis. The sales information is available in the fact table named “dbo.FactInternetSales”. In addition to SQL database, I am going to use an excel sheet that contains mapping of products to product categories
Apart from the pre-installed Microsoft Office Suite, we need to install 2 important softwares.
-
Power BI Desktop is used which is an open source Data Visualization software created by Microsoft as part of the Microsoft Business Intelligence Toolkit.
1.1 Download Power BI from here.
1.2 Step by step installation guide is available. -
Microsft SQL Server, a relational database management system developed by Microsoft is used for Data Storage, Retrieval and data transformation.
2.1 Download the SQL Server 2019 Developer version
2.2 SQL Server 2019 Developer Step by step installation guide. -
Microsoft SQL Server Management Studio, a software application first launched with Microsoft SQL
3.1 Download SQL Server Management Studio 18.10
3.2 SQL Server Management Studio - Step by step installation guide.
SELECT
[CustomerKey]
,[OrderQuantity]
,[UnitPrice]
,[DiscountAmount]
,[CustomerPONumber]
,[OrderDate]
FROM [AdventureWorksDW2019].[dbo].[FactInternetSales]
2.1 Data type change : It can be seen that the data type of “OrderDate” column in Customer table is Date\Time
For the analysis, time component is not required. Using Transform option, the data type is changed from “Date\time” to “Date”
2.2 Turn on Column Quality and Column Distribution : Column Quality and Column Distribution options are enabled from View section. These options help in scanning the data for any errors or blank values
In order to apply slicer across years, months and days a calendar table is needed. DAX provides a function called “Calendar” which needs start date and end date as input. To create the table with dynamic start and end date, following DAX code is used.
Calendar_Table =
//Calculate Start Date
var start_date = MIN(Sales_Table[OrderDate])
//Calculate End Date
var end_date = MAX(Sales_Table[OrderDate])
RETURN
// Create a dynamic calendar table
CALENDAR ( start_date, end_date)
In addition, new columns are added which represent year, month, week and day. This is to provide ability to slice the data across multiple periods.
To be able to use all three tables, relationships must be defined across them. Calendar and Sales table are related by Orderkey-Date relationship. Sales and Product Category Table are related by productkey (common among them). The final data model is as shown below.
1. Slicers to choose from Products and Product Categories : "ProductName" and "ProductCategory" columns are used in slicers.
2. Card - Total Sales card : "Product Sales" columns is used in card.
3. Card - Average margin : A new column is needed to represent margin. Margin can be calculated as follows. Product Margin = Sales – Cost – Tax. The newly calculated column is displayed on the card.
Product Margin =
var margin = CALCULATE(SUM('Sales Table'[SalesAmount]) - SUM('Sales Table'[TotalProductCost]) - SUM('Sales Table'[TaxAmt]))
RETURN
IF(margin = 0, 0, margin)
4. Card - Products with High sales, High margin and low sales, Low margin : To identify product with highest sales as well as margin, ranking of both sales and margin is needed. New column called Sales Rank as shown below
Further two new columns called Margin Percentage and Margin Rank are created as shown below.
Now to combine the Sales rank and Margin rank, another new column called Cumulative Rank is created as shown below
Top N filter is used to display product with high sales, high margin as well as product with low sales, low margin.
DAX code available below
Sales Rank =
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Product Sales], 'Product Category'[Product Sales], DESC), -1)
Margin Percentage = DIVIDE('Product Category'[Product Margin],'Product Category'[Product Sales], 0)
var bike_found = SEARCH("Bike", 'Product Category'[Product Category],1,0)
RETURN
IF(bike_found =1, RANK.EQ('Product Category'[Margin Percentage], 'Product Category'[Margin Percentage], DESC), -1)
Cumulative Rank =
IF(OR('Product Category'[Sales Rank] = -1, 'Product Category'[Margin Rank] = -1) = FALSE, 'Product Category'[Sales Rank] * 'Product Category'[Margin Rank], -1)
5. Pie chart – Contribution to sales by category : To identity top contributors, bottom contributors and middle contributors, a new column is created in Product Category table. Using the newly created column, pie chart visual is displayed
Sales Contributor Category =
SWITCH(TRUE(),
'Product Category'[Sales Rank] = -1, "NA",
'Product Category'[Sales Rank] <= 25, "Top Contributor",
'Product Category'[Sales Rank] >= 75, "Bottom Contributor",
"Middle Contributor"
)
6. Decomposition tree – Sales contribution across brands : Product Sales is analyzed by Product Category and Product Name using a Decomposition tree visual.
7. Table – Product wise Sales and Margin : Metrix visual along with conditional formatting shows Sales and Margin of each product from the portfolio.
- Out of 150 models, the first 25 products by sales generate nearly 60% of the sales for the company. Based on the data, the “Road 150 Red 48” is the best-selling product of the company. It generated $1.2 Mn in sales and $0.3 Mn in profit.
- 50 products fall in bottom contributor category that generate less than 5% of the sales. The “Mountain 300 Black 38” is product that is generating low sales and low margin. Recommendation is to consider retiring the product. In case there is inventory of this product, the company may decide to run special discount offers to sell if off quickly.
- Products in Middle Contributor category generate 36% of the sales. The details of these products are shared with sales and marketing teams. Recommendation is to identify factors that are working well in case of Top contributors and replicate those to Middle contributors