Imagine a scenario that your inventory system is telling you have 500 units of a product in stock. Your sales records show what went out, and your purchase orders tell you what came in. But when you count the actual products on your shelves, the numbers don't match. Where did the miscalculation or missing occur ? Was it damaged, misplaced, recorded incorrectly, or stolen?

For many growing retail businesses, finding this answer isn't easy. This was the challenge one of our client also faced. Despite operating a business generating approximately $10 million annually, it had no reliable way to investigate the unexpected product shortage across its stores.

AutoBizz took on the challenge of building a system that could turn fragmented inventory records into measurable insights.

Collecting the data we needed from Shopify

To understand where the products were disappearing, we first needed to connect what the business purchased, what it sold, and what it actually had.

We started with the purchase orders. It was not easy to gather this information, however. Shopify didn't offer a direct and convenient means to export purchase order data, the way we needed. So we exported the purchase orders that were available in PDF files and merged them into a single PDF file and took the data from the PDFs and converted it into a structured format in a spreadsheet. The records were sorted by Product name, SKU and Quantity purchased. This provided us with the purchase history that we needed instead of having to go through every individual document each time.

We then pulled sales info directly from Shopify to see how many units were sold. Accurate inventory counts were also required of the beginning and end. These are obtained by using queries on ShopifyQL to find out what stock we needed for our calculations.

At this point, we had the three essential pieces: starting inventory, purchased quantities, and sold quantities.

The Formula

After we had all the necessary information gathered, we started the process of reconciling the information. We calculated the expected inventory for each product using a formula like:

Expected Inventory = Starting Inventory + Purchased Quantity − Sold Quantity

We then compared that expected quantity against the actual ending inventory we have. The difference will reveal which products had fewer units than expected. From there, we easily can calculate the shortage quantities, financial values, and shrink percentages to generate the inventory shrink report.

The better Visualization

Having the calculations was useful, but we wanted to make the results accessible without requiring the client to work through backend data So, we stored the calculations in a backend table and developed an interactive dashboard to visualize the results.

The client could easily filter and investigate individual products, compare expected and actual stock counts, and identify which items had inventory shortages.

Instead of manually checking multiple records, now they have a centralized view that help them understand where discrepancies were occurring. The biggest value wasn't simply knowing how many products were missing. It was finally being able to investigate why the numbers didn't match.

The Next Step

The biggest value wasn't simply knowing how many products were missing. It was finally being able to investigate why the numbers didn't match. Our client have gained its first structured inventory shrink monitoring capability.

But our work didnt stop it here. We moved beyond the initial reporting process and started engineering an automated system that can do all these process automatically. That story unfolds in the next chapter of this blog.