Inventory Health Check From One CSV Export: What It Misses

What a one-export health check is

You already have the data. Somewhere in your billing or inventory system there's an "export stock" button, and it spits out a sheet with item names, quantities, batch numbers, cost and expiry dates. Usually that file only gets opened when the CA asks for closing stock, or when someone has a bad feeling about the back shelf.

An inventory health check just means reading that file on purpose. You're not counting stock or fixing anything yet. You're asking three questions: what's about to expire, how long will what I have actually last, and where is the money at risk sitting?

A single export can answer all three pretty well. It can't answer the questions that need time, and I'll get to those later. Knowing where one file stops is what keeps you from trusting it too much.

What the export needs to contain

Before running any check, look at the columns. For a useful stock health report you want these at batch level, not item level:

• Item name or SKU code
• Batch or lot number
• Quantity on hand
• Cost price (landing cost, not MRP)
• Expiry date
• Units sold over a recent window, like the last 30 days

The last column is the one that tends to be missing. Stock reports and sales reports often come out of different screens, so you may have to pull a sales-by-item report and paste it in next to the stock. Without sales you can still check expiry pressure. You just can't work out days of cover.

If batches and expiry dates aren't being recorded at all, fix that first. The expiry tracking spreadsheet template gives you a column layout to start from, and a free online expiry date scanner covers the quick ways to get dates off packs and into a sheet.

Check 1: expiry pressure

Sort by expiry date, soonest first. Then put every batch into one of three buckets: already expired, expiring within your "act now" window, and everything else. Pick a window that suits the category. Dairy and bakery need days. Packaged food and medicines can use weeks.

Anything already expired is a data problem as well as a stock problem. Either it's physically on the shelf and needs pulling, or it was sold or thrown away and nobody adjusted the record. Both are worth knowing about. A stock value that includes dead batches is exactly what a chartered accountant flags, as covered in stock audit red flags a CA looks for.

The "act now" bucket is where you can still recover money. Anything in it can still be marked down, returned to the distributor or moved to the front of the shelf.

Check 2: days of cover

Days of cover is quantity on hand divided by average daily sales. It tells you how long the current stock lasts at the current pace. Put it next to the expiry date and you get the most useful comparison in the whole file: will this batch sell out before it expires?

If days of cover is shorter than days to expiry, you're fine. If it's longer, some of those units won't sell in time, and you can estimate how many.

Days of cover also shows the opposite problem. A very short cover on a fast mover means you're about to run out, and running out of perishables costs money too. The hidden cost of out-of-stock perishables explains why stockouts and waste are two sides of one ordering mistake.

Check 3: where value at risk is concentrated

For each at-risk batch, value at risk = units that won't clear before expiry × cost price. Add a column, sort it from high to low, and look at the top of the list.

Say you print every near-expiry batch in your file on one list. It runs to several pages and nobody knows where to begin. Now say you print only the batches carrying the most money. That fits on one page, and one person can deal with it before lunch. Focus is what this check gives you.

A worked example on one export

Picture a grocery store whose export has 1,400 SKU-batch lines. After sorting, one line stands out: a biscuit SKU with 48 units on hand, expiring in 20 days, selling about 1.5 units a day, at a landing cost of ₹40.

The arithmetic for that line, as an illustration:

For example, taking the biscuit batch above:

• Units that will sell before expiry: 1.5 × 20 = 30
• Units left over at expiry: 48 − 30 = 18
• Value at risk at cost: 18 × ₹40 = ₹720
• Days of cover: 48 ÷ 1.5 = 32 days, against 20 days to expiry

Say you do the same for every line and sort by value at risk. In this illustration, suppose the top 15 lines add up to more than the next 300 put together. That's the pattern you're looking for: a short list that deserves a decision today and a long tail that can wait for the next shelf walk.

For that biscuit batch, the choices are clear. You can mark it down now while 20 days are left, ask the distributor about a return, or put it at the billing counter. The dead stock liquidation playbook walks through how to choose between those.

What one export can't tell you

This is where a one-time audit runs out. A snapshot is honest about today and knows nothing about the direction things are moving.

Trend. One file can't tell you if your expiry pressure is getting better or worse. The same amount at risk on one batch means one thing if it was much higher last month and something else if there was nothing at risk before. You only see the trend by comparing exports over time.

Whether your fixes worked. Say you marked down the biscuits. Did they clear, or did they sit at the lower price and expire anyway? The snapshot doesn't remember what you did, so it can't learn which kinds of action work in your store, for which categories, at what discount.

Tomorrow. The sales rate in a single export is an average over a window you picked. Festival weeks, monsoon slowdowns and a competitor's offer next door all change it. And tomorrow's delivery truck adds new batches the file has never seen.

Lead times and reorders. How much to reorder depends on how long your suppliers actually take to deliver. One export doesn't know that either.

None of this makes the snapshot useless. It means the snapshot is where you start, not a system you can run on.

Doing it by hand vs. letting it run daily

A spreadsheet can do everything above. Sort, divide, multiply, filter. If you have 150 SKUs and a free hour every Monday, a manual health check is a good habit. Pair it with the 2-page FEFO cheatsheet so the shelf matches what the sheet says.

The notebook-and-spreadsheet route runs into trouble in three places:

• Repetition. The check is worth the most when it's run daily, and that's when people stop doing it.
• Memory. Keeping a record of which markdowns worked means keeping a second log, and that log gets dropped first.
• Silence. A spreadsheet shows every row the same way. You still have to decide which three lines matter today, and that decision is the hard part.

Starting with your own CSV

If you'd like to run this check on your own stock without building the formulas, ShelfSense by ShelfLifePro does it with the same file. ShelfSense's free audit reads one CSV export, no account needed. You get expiry pressure, cover and value at risk ranked for your own store.

After that, the gaps in a single snapshot are what the daily agent is there to close. It reads your stock and expiry records once a day, alongside the system you already use. It shows the arithmetic behind each markdown, clearance or reorder call, and learns over time from which calls sold and which went to waste. You approve every action. The Watch tier is free forever at 30 SKUs — a daily scan and a health report. You can see how it reasons through a recommendation, or start with your CSV here.