ATLIQ MART
FMCG MANUFACTURER

Problem Statement

AtliQ Mart is a growing FMCG manufacturer headquartered in Gujarat, India. It is currently operational in three cities Surat, Ahmedabad and Vadodara. They want to expand to other metros/Tier 1 cities in the next 2 years.

AtliQ Mart is currently facing a problem where a few key customers did not extend their annual contracts due to service issues. It is speculated that some of the essential products were either not delivered on time or not delivered in full over a continued period, which could have resulted in bad customer service. Management wants to fix this issue before expanding to other cities and requested their supply chain analytics team to track the ’On time’ and ‘In Full’ delivery service level for all the customers daily basis so that they can respond swiftly to these issues.

The Supply Chain team decided to use a standard approach to measure the service level in which they will measure ‘On-time delivery (OT) %’, ‘In-full delivery (IF) %’, and OnTime in full (OTIF) %’ of the customer orders daily basis against the target service level set for each customer.

Start Now

Dashboard Requirement

We want to see our ‘ON TIME’, ‘In FULL’ and ‘OTIF %’ for our orders on daily basis.
We want to split ‘ON TIME’, ‘In FULL’ and ‘OTIF %’ by cities and by customers.
We also need to show the ‘ON TIME’, ‘In FULL’ and ‘OTIF %’ to their targets in the metrics, and the same applies to city split and customer split.
We need to show ‘LINE FILL RATE (LIFR)’ and ‘VOLUME FILL RATE (VOFR)’ metrics.
We want to show in a matrix visual the customer’s service level visually over all these metrics to our targets, also we will apply conditional formatting on values based on their gap to the target.
We also need to show the ‘OTIF’ performance vs Target, it should be drillable for months, weeks, and days.
We need to do for all metrics with a switch option, the user should be able to switch between metrics and the graph.
We need to show insights on products only with LIFR and VOFR, maybe add a sparkline next to it.

Task:

I am the data analyst in the supply chain team who joined AtliQ Mart recently. I have been briefed about the task in the stakeholder business review meeting. I will play the role of the new data analyst who is excited to build this dashboard and perform the following task:

Create the metrics according to the metrics list.
Create a dashboard according to the requirements provided by stakeholders in the business review meeting.
Create relevant insights not provided in the metric list/stakeholder meeting.

Data Organized

This dataset was downloaded from codebasics.io website and stored in FMCG Domain folder on PC.

It contains 6 data files: dim_date.csv, dim_customers.csv, dim_products.csv, dim_targets_orders.csv, fact_order_lines.csv, and fact_orders_aggregate.csv.

Data Cleaning & Transformation

Microsoft Power Query and Power BI will be used

Duplicates were checked with remove duplicates function
Gaps were checked with TRIM function
Blank and NULL checked with filter function

Data Analyzing

Measures

Total Order Lines = COUNT(fact_order_lines[order_id])
Total Orders = COUNT(fact_orders_aggregate[order_id])
Total Quantity Ordered = SUM(fact_order_lines[order_qty])
Total Quantity Shipped = SUM(fact_order_lines[delivery_qty])
On Time In Full Line = SUM(fact_order_lines[On Time In Full])
On Time In Full Delivery = SUM(fact_orders_aggregate[otif])
On Time Line = SUM(fact_order_lines[On Time])
On Time Delivery = SUM(fact_orders_aggregate[on_time])
In Full Line = SUM(fact_order_lines[In Full])
In Full Delivery = SUM(fact_orders_aggregate[in_full])
OTIF % = DIVIDE([On Time In Full Delivery],[Total Orders],0)
OTIF Target % = AVERAGE(dim_targets_orders[otif_target%])/100
OT % = DIVIDE([On Time Delivery],[Total Orders],0)
OT Target % = AVERAGE(dim_targets_orders[ontime_target%])/100
IF % = DIVIDE([In Full Delivery],[Total Orders],0)
IF Target % = AVERAGE(dim_targets_orders[infull_target%])/100
LIFR % = DIVIDE([In Full Line],[Total Order Lines],0)
VOFR % = DIVIDE([Total Quantity Shipped],[Total Quantity Ordered],0)

Data Modelling

Data Sharing

1. Visualization

Microsoft Power BI was used to visualize data.

2. Insights

When we examine the graphs by product categories, the Diary category products almost 3 times more orders than the Food and Beverages categories.
It is seen that the customers who place the most orders are Lotus Mart, Acclaimed Stores, Vijay Stores, Rel Fresh, Coolblue, and Propel Mart.
It is observed that the average OT, IF, and OTIF values fall behind the target values.
It is observed that the OTIF and OT values of 3 of the 6 companies that place the most orders (Lotus Mart, Acclaimed Stores, Coolblue) are noticeably low.
It is observed that OT, IF, and OTIF are noticeably low in Monthly, Weekly and Daily performance graphs.
It is observed that LIFR performance is slightly higher on a monthly basis in May and August, weekly performance is up and down, and there is a decrease in daily performance in the last days of the month.
It is observed that VOFR performance is slightly higher in August on a monthly basis, weekly performance is up and down, and there is a decrease in daily performance in the last days of the month.

3. Results

For Diary category products, priority should be given to on-time and in-full delivery.
Priority should be given to the orders of the main customers who place the most orders.
Service services should be improved, and if there is a shortage of employees, new employees should be recruited to the necessary departments.
An audit mechanism can be established to ensure smooth OT, IF and OTIF tracking of each product to be delivered.