ERP Insights: Sales and In-Stock Percentage

The C-suite team of a retail store with 1,700 locations nationwide is analyzing several of the company's inventory and sales metrics, including in-stock percentage of products, value of inventory in stock, total sales, sales by product, and geographical distribution of sales by state. Demand planning has been an area where the company has struggled to meet target KPIs in the past, and the executive team is making this a top priority moving forward.

In-Stock Percentage is a key metric for the company and is defined as the percentage of a product's orders that can be fulfilled immediately due to adequate inventory of the product. When a product does not have enough inventory to fulfill orders, this results in delays in the fulfillment process and often a poor customer experience. For this particular company, the Target In-Stock % is 97% for all products, and there is a critical equilibrium between maintaining a positive customer experience (high %) and avoiding excess inventory that will need to be discarded if it reaches the end of its shelf life (lower %).

Inventory In Stock is the dollar value (USD) of the inventory currently in stock for the selected product(s).

---

Key steps in this project included:

• Creating data model (star schema) by joining several tables, including In-Stock % Data, Geospatial Data, Product Lookup, and Date Lookup tables
• Creating a custom Date Lookup table via Power Query and date scaffolding to provide an improved user experience, including an easy-to-use date slider
• Creating dynamic Y-axis that adjusts range via a DAX measure based on In-Stock %, providing more intuitive visuals than a fixed Y-axis
• Using conditional formatting and data bars to easily compare sales and inventory levels by product
• Adding custom tooltips to quickly view relevant data points at a glance
• Removing redundant X and Y-axis labels for greater clarity in visuals

---

Data Source: Silvertone Analytics