Back to Blog
ShelfSense by ShelfLifeProNov 19, 20266 min read

Cash Tied Up in Inventory: Find It in One Export

Find the cash tied up in inventory that won't sell: group stock at cost by days since last sale, spot what expires first, and act on each slice.

SE

ShelfLifePro Editorial Team

Inventory management insights for retail and pharmacy

The money you can't see on the shelf

The supplier wants payment on Friday. The bank balance says you can't pay. And the shelves are full.

That's the strange part. The money didn't go anywhere. It's sitting in cartons on the top rack, in the back of the godown, in the cooler behind the faster brands. You paid for it weeks ago and it hasn't turned back into cash. Some of it never will.

The good news is you can find it in an afternoon, with one export from the system you already use and a spreadsheet.

Why the stock value number hides it

Your billing or accounts software gives you one closing stock figure. It looks healthy because it's a single total. Stock that sold yesterday and stock that hasn't moved since Holi get added together as if they were the same kind of asset.

They aren't. A packet that sells every day turns back into cash within the week. A carton nobody has touched in months is cash you've lent to the shelf with no repayment date. And a batch that will expire before it sells is cash that's already partly gone. Your books just haven't noticed yet.

So the job isn't to find out how much stock you hold. It's to split that one number into slices by how likely each rupee is to come back.

The export you need

You need one row per item (or per batch, if you track batches) with these columns:

  • item name and code
  • quantity on hand
  • cost price, not MRP: you want what you paid, because that's the cash that's stuck
  • last sale date
  • units sold over the recent past, so you get a daily sell rate
  • expiry date, where the item has one

If you run Tally, the stock summary gives you quantity and value, but batch-wise expiry depends on how you've set it up. This breakdown of what Tally does and doesn't track on expiry is worth a read before you export. Missing expiry dates? Do the first pass without them. The days-since-last-sale split works on its own.

Grouping stock at cost by days since last sale

Add a column: today's date minus last sale date. Then multiply quantity by cost to get stock value at cost for each row.

Now sort rows into four buckets: sold within the last month, sold one to two months ago, sold two to three months ago, and nothing sold for longer than that (or never sold since it came in). Sum the cost value in each bucket.

This is the core of the exercise. The first bucket is working stock. The middle two are slow movers. The last is your dead stock value, and if you want the line between those two categories drawn properly, slow movers vs dead stock goes through it item by item.

Free Tool

Not sure how much you're losing to expiry?

Run a free inventory waste audit, find your bleeding SKUs in 60 seconds. No sign-up required.

Run free audit

The slice that expires before it sells

Here's where the days-since-last-sale view misses something. An item can sell every single day and still lose you money, because you bought more than you can sell before the date.

For each row with an expiry date, work out:

units that won't clear = units on hand − (daily sell rate × days left to expiry)

If that comes out positive, those units will still be on the shelf when the date passes. Multiply them by cost and you have the rupees at risk on that line. Add those up across the store and you have the expiry slice.

This slice cuts across the buckets. Some of it sits in the "healthy" first bucket, which is exactly why nobody spots it: the item is moving, so nobody worries about it.

A worked example: one grocery store

Worked example: picture a grocery store with 1,200 SKUs and ₹18,00,000 of stock at cost. After the export and the date maths, the buckets come out like this:

  • sold in the last month: ₹12,60,000
  • last sale one to two months ago: ₹2,70,000
  • last sale two to three months ago: ₹1,35,000
  • no sale in over three months, or never sold: ₹1,35,000

For example, in this store ₹5,40,000 (three in every ten rupees) is in the slow or dead buckets. That's cash that could have paid Friday's supplier.

For example, take one line in the expiry check: a fruit juice with 40 units on hand, selling about 0.5 units a day, expiring in 30 days. At that rate it clears 15 units, so 25 won't clear. At ₹60 cost a unit, that's ₹1,500 at risk on a product that "sells fine".

Suppose the store finds ₹85,000 of expiry risk across all rows, and ₹40,000 of it sits in the first, fast-moving bucket. That ₹40,000 would never have shown up in a dead-stock review. It only shows up when you set sell rate against date.

What to do with each slice

For example, using the store above, each slice gets a different treatment:

  • Expiry slice (₹85,000): act first, because it has a deadline. Check which batches can go back to the supplier under return terms, then mark down the rest early, while there's still time for a smaller discount to work. Dynamic markdown pricing for near-expiry products covers how deep and how early.
  • Dead bucket (₹1,35,000): decide, don't wait. Return it, bundle it, sell it through a liquidation channel, or write it off. The dead stock liquidation playbook lays out the options in order of what they recover.
  • Slow buckets (₹4,05,000 combined): stop the bleeding upstream. Freeze reorders on these items until stock comes down, cut the order size, and ask whether the shelf space would earn more with something else.
  • Working stock (₹12,60,000): leave it alone, apart from the expiry lines you've already pulled out.

The order matters. The expiry slice comes first because it loses value every day. Dead stock is already lost value, so a week's delay costs less. Slow stock is a buying problem, and you fix buying problems at the next purchase order.

Doing this by hand every month

A spreadsheet handles this fine once. The trouble comes with repeating it. Sell rates change, new batches arrive with new dates, and the slice that was safe in the first week of the month is at risk by the third.

If you do it by hand, put it in the calendar for the first week of every month and keep each month's bucket totals on one sheet. The trend tells you more than any single month. Is the dead bucket shrinking? Is the expiry slice showing up in the same categories again and again? That second pattern usually points at a supplier's case-pack size or a scheme you shouldn't have taken.

What a monthly sheet can't do is catch a batch that turns risky between reviews. For that, someone has to run the sell-rate-against-date maths far more often than anyone has time for. The expiry loss calculator helps you put a rupee figure on what that gap is costing you.

Running the scan daily

That daily repetition is what ShelfSense by ShelfLifePro is built for. Once a day it reads your stock, batch, cost and expiry records, works out which batches won't clear and which slow movers are losing margin, and ranks the calls by money at risk, with the arithmetic shown for each one. It recommends a markdown, a clearance or a reorder change, and you approve or skip it. Tally users can export to CSV today; a direct connection is on request.

ShelfSense's free audit reads one CSV export, no account needed. If you've already pulled the export for this exercise, run it through ShelfSense and compare its slices with the ones you worked out yourself.

SE

ShelfLifePro Editorial Team

The ShelfLifePro editorial team covers inventory management, expiry tracking, and waste reduction for pharmacies, supermarkets, and retail businesses worldwide.

One question, optional

What would stop you from trying ShelfLifePro?

See what batch-level tracking actually looks like

ShelfLifePro tracks expiry by batch, automates FEFO rotation, and sends markdown alerts before stock expires. 14-day free trial, no credit card required.

Prefer to talk it through? Book a 30-min call

Newsletter

Get the monthly expiry brief

One short email a month, only when there is something worth reading. FEFO tactics, markdown math, and stories from Indian retailers. No spam.

No spam. Unsubscribe in one click. Email only, no WhatsApp spam.

WhatsApp tips

Get expiry tips on WhatsApp

Leave your number and we say hello on WhatsApp, then share expiry-tracking tips for Indian retail now and then — FEFO, markdowns, monsoon stock. No broadcast lists; reply STOP any time.

Reply STOP anytime. We never share your number.