Problem

Inventory management is a crucial aspect of any business, and having accurate insights into stock availability and time to replenish can make or break a company's success. Thermo Fisher's Biosciences division was struggling with Inventory management due to the lack of a comprehensive analysis tool. The existing system did not provide forecasting capabilities, making it difficult to plan for future inventory needs. Additionally, there was no master view of inventory available, leading to confusion and inefficiencies in managing stock.

I as a Data Analyst for BID IT analytical team recognized the need for a solution that would consolidate inventory data from various sources and provide accurate forecasting and insights into stock availability. I also created a master view of inventory report that would enable effective management of SKUs and lot numbers.

PS: This is the recent project I worked for Thermo Fisher, Blurred the sensitive information to adhere to data privacy rules.

Key Features:

Forecasting Stock Availability:
- The dashboard enables forecasting of stock availability based on SKU and Lot numbers.
- Insights into future stock availability to support inventory planning and management.
Time to Replenish Stock:
- The report provides analysis on the time required to replenish stock.
- Factors such as fulfillment cycle and markdown variance are considered to estimate the time to replenish.
Master View of Inventory:
- A comprehensive master view showcases available SKUs and lot numbers.
- Details include quantity available, quantity on hand, and branch quantity on reserve.

Data Integration and Visualization:

Data Sourcing and Cleansing:
- Data was sourced from Thermos's EDW, ensuring reliable and accurate information.
- SQL in Dbeaver was used for data cleansing and profiling, ensuring data quality.
Power Query for Data Integration:
- Power Query was employed to merge data from various inventory tables.
- This consolidation enables a comprehensive analysis of stock inventory.
Visualizations using Power BI:
- The report utilizes Power BI's visualizations to present insights effectively.
- Visuals such as charts, tables, and graphs enhance the understanding of stock inventory trends.

Key Approach

Sourced data from Thermos's EDW E1 and performed data cleansing and profiling using SQL in Dbeaver
Utilized Power Query to merge inventory tables for comprehensive analysis of stock inventory trends
Developed the "Cell Finder" inventory stock analysis report using Power BI, providing forecasting capabilities based on SKU and Lot numbers
Analyzed time required to replenish stock, taking into account factors such as fulfillment cycle and markdown variance.

Dashboard:

Conclusion:

The "Cell Finder" inventory stock analysis report demonstrates the power of Power BI in analyzing stock inventory data. By forecasting stock availability and providing insights on time to replenish, this dashboard supports effective inventory planning and management. The master view offers a consolidated overview of available SKUs and lot numbers. With data sourced from Thermos's EDW, cleansed using SQL, and visualized in Power BI, this report serves as a valuable tool for optimizing stock inventory strategies.

Impact

Improved accuracy in forecasting stock availability by 45%.
Reduced time required to replenish stock by 18 days.

"This solution has revolutionized ADME team's inventory management system. The forecasting capabilities and master view of inventory have enabled them to optimize stock inventory strategies and improve overall efficiency."

Cell Finder Inventory Stock Analysis Management with Power BI