Capital that had stopped moving
Stock that no longer sold kept its place on the shelf and its value on the books. It was money the business had already spent, still recorded as an asset, and unavailable for anything else.
Case study 02
Pharmacy Inventory Intelligence
Fragmented inventory, purchasing and stock information turned into management intelligence — what to stop buying, what to finish, what to protect, and where the cash was trapped.
Business AnalysisInventory IntelligenceDecision Support
01Problem
02Process
03Data
04Solution
05Implementation
- Role
- Business analysis, inventory analysis and decision framework development — hands-on, end to end.
- Context
- A pharmaceutical wholesale and retail operation. Organisation withheld; all figures shown are representative.
- Analytical scope
- Stock position, sales movement, purchasing behaviour, margin, supplier terms and assortment — at item level.
- What it produced
- A reconciled stock position, an item-level classification framework, and a decision table linking classification to purchasing action.
The problem
Inventory was the largest thing the business owned and the thing it understood least well. Not because nobody was counting — because counting is not the same as knowing.
No shared language for the stock
The operation could produce a stock list. It could not describe it. Dead, slow, excess and core were used loosely and meant different things to different people, so no two conversations about inventory started from the same place.
Information held in pieces
Stock position sat in one place, purchase history in another, sales movement in a third. Any question that crossed them — is this item worth reordering? — had to be answered by hand, one item at a time.
Purchasing driven by habit
Reorder decisions were made from what looked low on the shelf rather than from movement, existing cover and margin. The pattern that created the excess kept running while the excess was still on the floor.
Nothing to prioritise with
Management could see a total inventory value. It could not see which items to act on first, which action each one needed, or which decisions would free the most cash.
Why it mattered
The commercial consequenceIn pharmacy, inventory is not a storage problem. It is a working-capital problem with a clock attached.
Working capital
Every unit that does not move is cash already spent and unavailable — for stock that would sell, for supplier terms, or for anything else the business needs to fund.
Expiry exposure
Pharmaceutical stock has a shelf life. Slow-moving items do not simply sit; they age towards total loss and a claims process that costs time whether or not it recovers value.
Purchasing quality
Without movement and cover in view, buying repeats itself. The same over-ordering recurs next cycle, and the position gets worse while the analysis is being written.
Margin
Not all trapped capital is equal. Low-margin lines that turn slowly cost more to hold than they return, and are the first place to look — not the largest quantities.
Stock availability
Cutting inventory indiscriminately is as damaging as overstocking. Core items have to stay available; any reduction plan that risks them is not a plan.
Management decisions
A total inventory figure is not a decision. Leadership needed to know which items, which action and in what order — at the level someone could act on.
Operational control
Where the counted position and the system record disagree and nobody can say why, the record stops being trustworthy — and every decision taken from it inherits the doubt.
My role
This was hands-on analytical work, not a review written from a distance. What follows is what I personally did.
Analysed
Worked through inventory, purchasing and sales-movement data at item level to establish what the operation actually held and how it behaved — rather than what the system summary implied.
Reconciled
Reconciled the counted position against the system record and the master baseline, and worked through the variance categories — matched, excess, short, not found and expired — until each was explained rather than absorbed as an unknown.
Structured
Brought fragmented stock, purchasing and movement information into a single item-level view that could be analysed as a whole and rebuilt when the underlying data changed.
Classified
Defined and applied an item-level classification — core, slow-moving, excess, dead — across the inventory rather than a sample, so the categories described the whole position.
Developed
Developed the decision logic that connects each classification to a purchasing action — what a given state indicates about demand and cover, and what should follow from it for buying, holding or running the item down.
Designed
Designed the analytical framework itself: which operational inputs the analysis would draw on, how they combine at item level, and the classification and decision structure through which the results are read.
Validated
Checked the classifications, variance figures and item-level results back against the source operational data, and cross-verified them across working sessions, so that every figure reported could be traced to its source.
Translated
Converted the analysis into specific operational actions — stop, reduce, finish, maintain, investigate — attached to named items and sequenced by what they would release.
The analysis
Eight questions, not oneThe question "why is so much cash tied up in stock?" is not answerable as asked. It had to be broken into questions that data could actually settle.
Stock position
What do we actually hold, and does the record agree with the shelf?
Sales movement
What is moving, how quickly, and what has stopped moving altogether?
Purchasing behaviour
What have we been buying, how often, and in what quantity relative to demand?
Dead, slow & excess stock
Which items no longer earn their place, and which are simply over-covered for the demand they have?
Margin
Which of the trapped capital is worth holding, and which is expensive to keep?
Supplier considerations
Which items can be returned, exchanged or claimed, and which supplier terms shape the buying decision?
Assortment
Which lines does the operation genuinely need to carry, and which are duplication?
Cash-release opportunities
Where is the most capital recoverable for the least risk to availability?
analytical framework · inputs → classification → item state structure only
Analytical inputs
- stock position
- sales movement
- purchase history
- cover vs. demand
- margin
- supplier terms
- shelf life
- assortment role
Resulting item state
-
Core
Moves consistently. Cover is proportionate to the demand the item actually has.
-
Slow-moving
Still sells, but far more slowly than the quantity held assumes.
-
Excess
Sells, but on-hand cover runs well beyond any sensible purchasing horizon.
-
Dead
No movement across the review period. Capital fully committed, with shelf life still running.
Evidence
Reconstructed · representative dataAnalysis that stays in a spreadsheet is an opinion with a deadline. This work became operating intelligence: the stock position, the portfolio classes and the purchasing consequences are held in one place the business reads, rather than reconstructed each time somebody asks.
Operational data Intelligence Classification Decision Action
evidence · inventory intelligence — implemented solution reconstruction · representative data
Commercial
operations
operations
Overview Dashboard
Commercial operations SalesPurchasingInventoryCustomersSuppliers
Intelligence CommercialPortfolio classesBusiness categoriesClassification review
Inventory
Stock intelligence — position, criticals, expiry and velocity (velocity = 3-month average from sales)
DashboardStock PositionCritical ItemsExpiryDead / Slow / New Stock
Inventory value
OMR 96,400
closing snapshot
SKUs on hand
1,310
of 2,640 on master
Retain / Special / Exit
1,486 / 502 / 652
portfolio class
Exit-class value
OMR 5,720
recovery pipeline
Availability
91.4%
54 criticals
Month-end stock cover
2.05 mo
closing stock ÷ net trading COGS · target 1.5
Value by category
Medicine Cosmetics Disposables & consumables Baby nutrition Adult nutrition Consumer health Medical devices Other categories
Portfolio classes (value)
Velocity from trailing sales · criticals recalculated on each stock refresh · portfolio class drives the purchasing recommendation.
The measures on this screen are the analysis made continuous. Portfolio class, exit-class value, availability and month-end stock cover are the same quantities the classification produced — recalculated on each refresh instead of assembled by hand.
evidence · purchasing intelligence — implemented solution reconstruction · representative data
Commercial
operations
operations
Overview Dashboard
Commercial operations SalesPurchasingInventoryCustomersSuppliers
Intelligence CommercialPortfolio classesBusiness categoriesClassification review
Purchasing
Purchases and scheme capture — scheme value already inside COGS
Overview Scheme capture Purchase returns & adjustments
Purchases
OMR 58,200
selected period
Scheme value received
OMR 9,100
15.6% of purchase spend
Purchases / COGS
108.4%
above target — overstock risk
Suppliers used
24
of 41 on master
Optimal-tier orders
22%
scheme tier discipline
Purchases & scheme value by month
Spend by supplier — previous month
Supplier identities withheld. Bars show spend with scheme value beneath.
Priority-account rule: the reorder engine adds a demand buffer on items linked to
priority accounts, so their sub-orders never strip store stock to zero.
This is where the inventory position becomes a purchasing consequence. Purchases against COGS, scheme capture, supplier concentration and the reorder engine are the mechanism through which a classification turns into what gets bought next — and the point at which over-cover either corrects or repeats.
evidence · commercial brief — management view reconstruction · representative data
Morning commercial brief
Latest complete trading month closed · latest stock position loaded · selected period trading complete · 54 critical stock-outs · recovery candidates OMR 23,900 · 1 red alert
Today's buying →Net sales
OMR 46,300
latest complete month
Trading gross profit
OMR 4,050
Purchases
OMR 38,700
purchases / COGS 92.1%
Inventory health
91.4%
652 exit-class · 68 items pending review
Trading margin
8.7%
official ERP basis
Critical stock-outs
54 SKUs
Availability below the service floor
Expiry risk (90d)
Batch position required
Batch-wise stock needed to compute
Recovery candidates
OMR 23,900
Exit-class items with a recovery route
Supplier opportunities
No active offers loaded
Recurring schemes tracked separately
Inventory decisions are not taken in isolation. Availability, exit-class value and recovery candidates sit beside sales, margin and purchasing on the same management view, which is what makes them arguable against everything else competing for the same cash.
From data to decisions
Analysis → decision → actionA classification that does not change what anyone does is just a label. Each state had to resolve to an action a buyer or a manager could take on a named item.
decision logic · item state → operational action simplified representation
| Item state | What it indicates | Operational action |
|---|---|---|
| Core | Demand is real and repeating; the item earns its shelf space. | Maintain. Protect availability and hold the purchasing cadence steady. |
| Slow-moving | Demand exists, but the quantity held assumes more of it than the operation sees. | Finish existing stock. Run the position down and reduce the order quantity at the next cycle rather than stopping outright. |
| Excess | On-hand cover runs far beyond demand; further buying adds no availability. | Stop or reduce purchasing. Suspend reordering until cover returns to a sensible horizon, then resume at a corrected quantity. |
| Dead | Capital is committed with no return, and shelf life continues to run against it. | Stop purchasing; pursue recovery. Route to return, exchange or supplier claim where terms allow, and recover the shelf space. |
| Variance | The record and the physical position disagree, so neither can be relied on yet. | Investigate. Resolve the discrepancy before any purchasing decision is taken on the item. |
| Expired | The value is already lost; only the recovery route remains open. | Remove from sellable position. Segregate from sellable stock and hand off to the claims and stock-removal process. |
The last two rows are where this case study ends and the next one begins: recovery and removal are a controlled, multi-role process in their own right — analysed and redesigned separately as the Supplier Claims & Stock Removal Workflow.
Sequencing the actions
Knowing the action is not the same as knowing the order. Sequencing was part of the deliverable.
Protect availability first
No recommendation was allowed to put a core item at risk of stock-out. Availability is the constraint the rest of the plan has to work inside, not a trade-off to be made later.
Weight by value, not by count
A long list of low-value dead lines and one high-value one are not the same problem. Higher-value items were actioned first because each decision releases more capital.
Fix the cadence, not just the order
Where over-cover came from ordering rhythm rather than a single bad order, the correction is the purchasing interval. Otherwise the same position rebuilds itself after the clear-down.
Outcome
What the work enabledThe work is judged by what management could do afterwards that it could not do before.
Clearer inventory visibility — a single item-level view of what is held, what moves, and what does not.
A structured classification that gave the operation shared language for its stock, in place of competing opinions about what counts as "slow".
Purchasing decisions grounded in movement, cover and margin rather than in shelf appearance and habit.
A reconciled position in which variance was explained by category rather than carried as an unknown.
Cash-release opportunities identified and ranked, rather than a general sense that too much capital was tied up somewhere.
Management priorities expressed as actions on named items, in order — not as a single inventory total.
No percentage, monetary saving or performance figure is published here. The commercial results belong to the organisation and remain confidential. What is shown is the method and the structure of the decision, which is what transfers to another operation.
management visibility · before → after simplified representation
Before
Stock, purchasing and movement information held separately. A total inventory value was visible; the items behind it were not. Any question that crossed the three sources was answered by hand.
After
One item-level view, classified and ranked. Every line carries a state and a recommended action, and the largest cash-release opportunities sit at the top rather than somewhere in the list.
- Core
- Slow-moving
- Excess
- Dead — action
What this demonstrates
This was not primarily a reporting problem. It was an operational decision problem.
A report can only describe a position. The work that mattered here was establishing what the position meant and what should be done about it — and that required knowing how a pharmacy actually buys, stores, dispenses and writes off stock, not only how to analyse a dataset.
The analysis was the middle of the job, not the whole of it. It was bracketed by operational understanding at one end and a specific, sequenced set of actions at the other.
Operations Data Commercial logic Practical action
Domain understanding decided which questions were worth asking. Analysis established what was true. Commercial logic decided what was worth doing. Implementation turned it into something someone could act on. Remove any one of the four and the work stops short of being useful.
The underlying project involved confidential operational and commercial information. The organisation is not identified, and no real customer, supplier, order or commercial figure appears on this page. The intelligence views are reconstructions of the implemented solution: the module set, metric definitions and chart structure are those of the working screens, while all values are representative and supplier identities are withheld. Nothing here is a literal screen capture. The explanatory diagrams are structural representations of the analytical framework and decision logic, and carry no thresholds, values or results.
Contact
Facing a similar operational problem?
If capital is sitting in stock you can't see, or purchasing decisions are being made without the evidence behind them, describe the situation. I'll tell you how I'd approach it.