22.11 Inventory and Supply Chain

By Ravindra Bagale Last updated: 6 October 2026

Planners ask days of cover and how many days the SKU was stocked out. A high on-hand number feels safe and is often cash stuck in the godown. Module 21.3 is the project brief.

KPI	What the number means	Who uses it	Action it drives
Days of cover	On hand divided by average daily sales. Here, daily sales are the 30-day quantity divided by 30.	Planner	Cover under 15 days: raise a PO. Cover over 90 days: stop the next PO.
Stockout rate	Stockout days divided by days in the window (30).	Planner, store lead	A stockout is lost sales. On-hand today does not show last week's empty shelf.
On hand	Units in the godown today.	Only with cover	A large on-hand of a slow SKU is not a healthy warehouse.

Fictional sample rows. A snapshot, plus the last 30 days of sales. Do not add these on-hand numbers across seven dates (Module 21.3). Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblStock (Table Design › Table Name).

SKU	City	On hand	Sold in 30 days	Stockout days
Atta	Pune	300	300	0
Oil	Pune	600	150	0
Rice	Nashik	40	120	6
Dal	Nashik	90	90	0
Sugar	Kolhapur	20	60	10
Tea	Nagpur	200	50	0

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI	Excel formula	Result	Number format
Days of cover	`=SUM(tblStock[On hand])/(SUM(tblStock[Sold in 30 days])/30)`	48.7 days	`0.0`
Stockout rate	`=SUM(tblStock[Stockout days])/(ROWS(tblStock)*30)`	8.9%	`0.0%`

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column	Formula in the first row
Days of cover	`=[@[On hand]]/([@[Sold in 30 days]]/30)`
Stockout rate	`=[@[Stockout days]]/30`

Expected result per row:

SKU	Days of cover	Stockout rate
Atta	30.0 days	0.0%
Oil	120.0 days	0.0%
Rice	10.0 days	20.0%
Dal	30.0 days	0.0%
Sugar	10.0 days	33.3%
Tea	120.0 days	0.0%

One city with SUMIFS. Days of cover for Pune only:

=SUMIFS(tblStock[On hand],tblStock[City],"Pune")/(SUMIFS(tblStock[Sold in 30 days],tblStock[City],"Pune")/30)

Result for Pune: 60.0 days. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

	Good KPI	Vanity metric
From the same rows	Rice cover 10 days, oil cover 120 days	Oil on hand 600, the biggest pile
Why	Rice will run out. Oil will sit for four months.	A "stock health" card that sorts by on-hand puts oil first and rice last. That is the wrong purchase order.

What the manager does. The Nashik planner raises a rice order this week and does not touch dal (cover 90 ÷ (90 ÷ 30) = 30 days). The Pune planner cancels or delays the next oil purchase. Atta in Pune is 300 ÷ (300 ÷ 30) = 30 days, which is the planned cover in this file. Leave it. Kolhapur sugar is also 10 days of cover and was stocked out 10 of 30 days. Same action as Nashik rice.

Ravindra Bagale's Tip

Never SUM on-hand stock across several snapshot dates. Take the latest date only, then calculate days of cover.