Northwind Traders Performance Analysis

LeishTon Consulting and BoardGov Limited

Power BI

About this project

An Executive Dashboard that seeks to provide quick insight into a company's performance in key areas, namely: Sales trends, Product performance, Key customers and Shipping costs as well as recommendations on how to maximize sales and minimize cost.

TABLE OF CONTENT

About the dataset
Recommended analysis | task
Approach
KPI's | metrics
Insights and Recommendations
References | helpful resources

ABOUT THE DATASET

Dataset contains sales & order data for Northwind Traders, a gourmet food supplier, including information on customers, products, orders, shippers, and employees.

In comparing Current Year to Previous Year (YOY), I made use of only the range of time where both Current and Previous Year made sales. For Instance, in 2015, available dataset was from January to 6th May. In Comparing with previous year, only dataset from Jan - 6th May, 2014 was used pending when new entries will be made. This then updates automatically.

TASK | RECOMMENDED ANALYSIS

Build a top-level KPI dashboard to help Northwind Traders' executives quickly understand the company's performance in key areas.
Its purpose should be to allow them to quickly understand the company's performance in key areas, including:

Sales trends

Product performance

Key customers

Shipping costs

The dashboard should be built to evolve and accommodate new data over time,
The manager has encouraged that I am to have insights & recommendations ready to share with the VPs.

“This is your chance to impress!”, according to him.

APPROACH:

Being an Executive Dashboard geared towards quick understanding of company’s performance, I have desisted from using more than one report page into fitting all analysis into a single page.
I have also divided my analysis into four sections to capture each of the requested key areas:

Section 1: devoted to Sales Analysis

Section 2: Cost Analysis

Section 3: Product Analysis

Section 4: Customer Analysis

Putting into consideration that data will continue to increase in the coming future, I have kept past year records as well as current year’s records. I have also kept Year filter placed at the topmost left.
I have also hidden insights & recommendations using Power BI’s Bookmark button ready to share with the VP’s.

KPI’s| METRICS

1. ON TIME DELIVERY RATE

Is Maven Northwind Traders meeting its goals in regards to promised delivery dates?

On-time delivery rate measures the ability to fulfill customer orders by the promised delivery date.

This KPI is calculated by dividing the number of on-time deliveries by the total no of deliveries.

The higher the number of on-time deliveries, the higher the On-time delivery rate (OTD). High OTD will result in higher customer satisfaction and Low OTD will result in lower customer satisfaction. Hence,

GOAL: therefore, will be to increase OTD rate in areas where we are having low OTD.

2.SHIPPING COST PER UNIT

Shipping Cost per unit refers to the average cost to ship one unit of product. It is the shipping cost for each unit of item shipped.

This KPI is calculated by dividing the total costs for shipping by the number of units shipped during a defined time period.

The higher the shipping cost per unit, the lower the profit per unit and the lower the shipping cost per unit, the higher the profit per unit hence,

Aim/Goal: Optimize cost by identifying the shipping cost per item and see deviations that fall outside their normal run rate and identify or find possible areas to reduce shipping costs.

3.SALES

This refers to the proceeds generated from the sell of goods to customers. This metric is calculated by multiplying Unit Price of item by its total ordered quantity. Goal: To understand sales trend and performance in time, by products and by employees. Also, to understand if we are meeting the sales target or not.

For this analysis:

1.On-time Delivery Rate was used as a proxy for measuring customer satisfaction

2.Shipping Cost per Unit KPI was used for Cost Analysis

3.Sales metric was used for both Sales analysis and Product analysis

INSIGHTS AND RECOMMENDATIONS

SALES

There has been an increase in sales from 2013 through April, 2015.
As at early May, 2015, there has been an increase in sales from Jan-1 May, 2015 when compared to previous Jan-1 May 2014.
In 2015, Feb, March and April have seen an increase in sales beyond the monthly target.
Sales is predicted to keep increasing for the rest of 2015 (May - December).

COST

Sales are increasing but so is the shipping cost per unit of every shipped item, thus eating on the total profit.
Renegotiate with United Company Shipper to reduce the shipping cost of items namely; Cote de Blaye, Sir Rodney's Marmalade, Thuringer Rostbratwurst, Aniseed Syrup, and Other products with higher shipping cost per unit.
Find out the shipping cost of these items by other shipping companies.

CUSTOMERS

Argentina, France, Spain, Sweden, Venezuela, USA are countries with low On-time deliveries (OTD).
These are regions where our brand reputations is impacted negatively as customers may churn due to late deliveries. hence, a decline in retention rate.
Some of the customers in these regions may find it difficult to trust our brand as they are frustrated and distressed due to late deliveries.
Customer complains due to late deliveries are majorly from these regions.
Find out the reason for late deliveries from Anne Dodsworth, Margaret Peacock, Janet Leverling and Nancy Davolio who are employees with the least On-time delivery rates (in 2015).

PRODUCTS

While "Cote de Blaya" had the most sales, "Raclette Courdavault" product had the most demand (2014-may, 2015).
Spring and Winter are peak seasons where "Cote de Blaya" had the most sales and "Raclette Courdavault" had most demands. Spring and Winter will be the perfect period for socking more of these products.

REFERENCES