Revitalizing the Flow: A Data-Driven Analysis of Supply Chain Operations Using PowerBI

Introduction

CLIFE Mart is a growing FMCG manufacturer headquartered in Ogun State, Nigeria. It is currently operational in three cities Lagos, Port-Harcourt & Ibadan. They want to expand to other metros/Tier One Cities in the next two years. CLIFE 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 continuous 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 customer's daily basis.

Statement of Business task

To generate insights to solve a supply chain issue in the FMCG domain and build a dashboard showing these insights.

Data Source and Tools used

The data consists of six files containing fact tables and dimension tables namely dim_customers, dim_products, dim_date, dim_targets_orders, fact_order_lines and fact_orders_aggregate. The data was made available by Octave Incorporation. Description of the columns in each table is shown below:

For this project, I used PowerBI to create the relevant metrics and build the dashboard required to communicate my findings to the necessary stakeholders.

Data Cleaning and Transformation

Data cleaning and transformation are an important aspect in data analysis process as it ensures accuracy and credibility of the insights generated from the data. All the datasets were loaded into PowerBI power query for the data cleaning and transformation process.

For this project, the following steps were undertaken:

1. Using the data preview feature on power query, I was able to identify that they were blank rows in the dim_date columns. I removed these blank rows and ensured that each columns are encoded in the proper type.

2. I created two new column under the fact_order_table where I calculated the average line fill rate and volume fill rate.

3. Then I created another three new columns under the fact_order_aggregate table where I calculated the percentage of the In Full, On time and On Time In Full metrics.

4. I created measures where I calculated the In Full, On Time & On Time In Full metrics for the three cities where the company is currently operational.

5. Then, I created measures where I calculated the In Full, On Time & On Time In Full metrics for all the customers the company provide services for.

6. I created measures where I calculated the Line Fill rate and Volume fill rate for each of the products being shipped to the customers.

7. Then I created measures where I calculated the percentage increase or decrease for each of the required metrics over time.

At the end of the process, I have cleaned and transformed the columns provided in the dataset and also created new relevant metrics that would aid my analysis

Exploratory Data Analysis and Visualization

• First of all, I switched my canvas orientation to letter format and selected grey color for the background.

• I also added the logo of the company whose data I am currently analysing as well as a couple of slicers which contained fields such as Date and Product category.

- Then, I displayed a quick summary preview of the data which also reveal some Key Performance Indicators(KPI)

- We see that the company offers 18 Product under 3 different categories and that the average line fill rate and volume fill rate are well above the target set by top management.

- Although The Company fell short of the target set for On Time delivery. In Full delivery and In Full & On Time delivery by a significant percentage.

• The Performance of the company was especially poor in On Time & In Full Delivery and this could be the reason key customers did not extend their annual contract.

- From the bar chart above which illustrates the deliveries of the company across the three cities, we can see that the performance of the company in terms of the percentage of in full & on time deliveries is extremely poor with just 16.34% in Lagos (the highest) and 15.57% in Port-Harcourt (the lowest)

• The percentage of deliveries made on time across the three cities are better compared to percentage of deliveries in full and packages delivered both on time and in full. Though they still fall way below the target.

- And in the tables above showing the deliveries of the company filtered by each customer and product type and contains spark lines and conditional formatting, we see that the Percentage of in full deliveries to Elite Mart, Royal Mart & Info Stores were very poor , special attention needs to be paid to this customers so the company would not lose them

Overall, those are insights that can be gleaned from the above analysis and help guide the actions of management in fixing the issues of essential products being either not delivered on time or not delivered in full over a continuous period of time.

Recommendations

Based on my analysis, my recommendations for the management are as follows:

-Conduct a thorough review of the supply chain processes and identify any bottlenecks or areas where delays are likely to occur.

-Develop a more proactive approach to supply chain management, using real-time data to monitor inventory levels, production schedules, and delivery times. This can help ensure that products are delivered on time and in full, reducing the risk of customer dissatisfaction.

-Implement a customer feedback system to gather information on customer satisfaction levels and identify areas for improvement. Use this feedback to make targeted improvements to the supply chain process, focusing on the areas that are most important to customers.

-Consider implementing a supply chain management software solution that can help automate key processes and provide real-time visibility into inventory levels, production schedules, and delivery times.

Here is a link to the full project : Dashboard