Power BI Inventory Management to Prevent Over- and Understocking

Having the right product at the right store at the right time is critical. Late or inaccurate inventory information led to higher carrying costs, write-offs, and missed sales opportunities.

In some retail stores, there is a demand for certain goods while they are not available to purchase because of out of stock - managers ordered too few of them. The first goal is to quickly and easily see which goods are out of stock and act fast so they don’t lose any sales.

Other retail stores carry too much inventory for different reasons. The second goal is to proactively identify overstocks ahead of time and reduce them using recommendations on inventory store-to-store transfers to meet demand in other locations.

As a rule, retail outlets are managed by independent (stand-alone) inventory management tools and connected to local retail store databases.

It's next to impossible for inventory managers of the head office to consolidate, aggregate, calculate and visualize the data from these databases in real-time.

Inventory managers need analytics software to help them answer the following questions:

• What are our customers buying weekly and monthly throughout our retail network?
• Will we have enough stock? Which warehouses hold what quantities of inventory? Which products are likely to need re-stocking or a transfer.
• See details for the current stock on hold, and how old the inventory is (aging report) by product. Do we need to discount stock to get rid of inventory based on poor sales we are experiencing?

After gathering the technical and business requirements, we have created a specification describing the dimensions and metrics.

For example, we’ve selected a number of dimensions like “Organization” to mirror the structure of the company, “Product” to represent the product lines that the company sells, “Period and Business Calendar” and so on.

To implement the BI solutions we have deployed a Microsoft SQL Server. Using Microsoft SQL Server Management Studio we’ve created a central Data Warehouse (DWH) on this server.

• We have developed custom connectors that automatically import data from the local retail store databases (OLTP databases) to the staging database of DWH.
• We have tuned the transformation procedures for the raw data to adapt it to the DWH requirements.
• We have tuned the procedures of data transfer from the staging database of the DWH to a multi-dimensional Production database (OLAP cube).

Based on the data from OLAP cube in POWER BI we have visualized data as dashboards that help the inventory managers answer the above mentioned questions.

We have also set up different access rights for different user categories (each has access to their job-specific metrics).

We have prepared standardized dashboards.

The inventory manager simply presses the 'refresh' button to generate the required report, gaining a comprehensive overview instantly.

In the morning, an analyst might notice low inventory turnover or insufficient stock for a specific SKU in a particular shop. Using this data, the inventory manager can promptly recommend redistributing surplus goods to the shops in need.

In addition, we have implemented an automatic system to order and redistribute the remaining goods. The BI enabled the creation of a specification document for customizing the company's accounting software. The data is taken from the DWH: if the SKU remainder is above or below the optimum level in different retail shops, a transfer request will be made.

Three months after the implementation of this BI solution the company has seen the following improvements:

• The average monthly active inventory improved by 7,5%, which increased company revenue.
• The inventory turnover improved by 2,5%, which allowed to improve the gross margin return on investment (GMROI).