Northwind Traders Performance Dashboard

The Objective

The objective of this challenge was building a top-level KPI dashboard for the executive team. 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, but you've been encouraged by your manager to have insights & recommendations ready to share with the VPs.

The Dataset

The dataset consists of 7 CSV files for orders, order details, employees, products etc.

Approach

I had to keep the following in mind when deciding on how to approach this project.

-Being a top-level dashboard, it shouldn’t have too many details and should be straight to the point. However, I made provision for deep dive by leveraging Power BI’s drill through feature to provide the possibility of details if needed.

-I had to define what success was for each of the key areas and I did this by comparing the performance of the most recent period (2015) to the same period of the previous period.

-The dashboard had to be built to evolve and accommodate new data. To achieve this, I put the orders details and orders dataset into a folder and connected Power Query to the folder. Since dimension tables won’t change much or don’t require aggregation, I could just use the CSV connector with these.

Key Reporting Areas

Revenue I focused on the latest period which is 2015 as management will most likely only be interested with current performance. I looked at revenue trend using a YTD metric since we had an incomplete year and compared this to the same period in the previous year as a benchmark. The Key here was to adjust the calendar to only compare the current performance to up to the corresponding last day of the previous year. This was achieved with a calculated column on the date table.

I also looked at the revenue generated by each product category and top customers as well while comparing all of them to the same period of the previous year. I also looked at the average order value as an important metric to measure performance.

Next, I analysed the number of orders and where we as shipping to as this would have an impact on the freight cost.

The freight cost was analysed but I looked at the freight cost per order across the various delivery destinations as well as with each carrier.

Some Key Insights for the VP

• Revenue has increased significantly (119.4%) compared to the same period in the previous year due to an increase in the number of orders, an increase in all the various categories and amongst key clients. The business looks solid but there is always room to improve by implementing up-selling and cross-selling strategies.

• The average order value has increased (4.8%) overall but 3 of the top clients saw a decline in their average order value. Offering some incentives to these clients like discounts or packages might help increase their order value.

• The average freight cost per order has increased by 16.5%, which is very high. This may be due to the carriers increasing their rates or just a change in the mix of products shipped or packaging used. Northwind will need a detailed and deeper understanding of the drivers to recommend the best actions to take. However, it will be a good time to negotiate better rates with carriers since volumes are increasing.