Our client is one of the largest retail suppliers who is tracking millions of records across multiple retail stores in different cities and would like to know how to walk the fine line between 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.
An inventory manager just has to press the “refresh” button for the necessary report and gets the whole picture.
In the morning, an analyst can see that in a certain shop there is a low inventory turnover or not enough stock for a certain SKU. Based on this data the Inventory manager can quickly make suggestions for moving the surplus goods to the shop that needs them.
In addition, we have implemented an automatic system to order and redistribute the remaining goods. Thanks to the BI, making a specification document for customization of the company’s accounting software became possible. The data is taken from the DWH: if in one of the retail shops the remainder of a certain SKU is above the optimum inventory level and in another retail shop, it is below this level, a request for transfer of these goods will be formed.
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).