35. More Real-World Project Briefs
35.6 Inventory
Business question. Which city will stock out of a SKU first, at the current on-hand quantity?
Tables. Stock (snapshot date, City, SKU, On Hand). Movement (Date, City, SKU, Qty In, Qty Out) if you want a ledger. SKU. Date. One snapshot is easier for a first build: do not also sum the ledger or you will double count.
Measures.
On Hand = SUM(Stock[On Hand])
Qty Out = SUM(Movement[Qty Out])
Days of Cover = DIVIDE([On Hand], DIVIDE([Qty Out], 30))
Days of Cover assumes the selected movement window is about a month. Say that in the subtitle. If there were no sales, DIVIDE returns blank, which is correct: you do not have a cover number.
Pages. A matrix, SKU by city, with On Hand and a sparkline of Qty Out by week (section 14.24) if your Desktop has sparklines. A table filtered to On Hand less than a threshold you type in a what-if parameter (section 21.4). Cities are the six class cities only.
Sample layout — fictional data. The picture uses the worked rows in this brief. It does not add a table.
Worked rows (fictional sample data). One snapshot. Qty out is the last 30 days. Days of cover is on hand divided by (qty out ÷ 30).
| SKU | City | On hand | Qty out (30 days) |
|---|---|---|---|
| Atta | Pune | 90 | 90 |
| Oil | Pune | 180 | 30 |
| Rice | Nashik | 20 | 60 |
| Dal | Nashik | 60 | 60 |
| Sugar | Kolhapur | 15 | 45 |
| Tea | Nagpur | 100 | 25 |
On hand 465. Qty out 310. Daily out is 310 ÷ 30. Days of cover for the file is 465 ÷ (310 ÷ 30) = 45.0 days.
Rice in Nashik: daily out 60 ÷ 30 = 2, cover 20 ÷ 2 = 10 days. Oil in Pune: daily out 30 ÷ 30 = 1, cover 180 ÷ 1 = 180 days.
Starter file: brief_35_6_inventory_snapshot.csv – the worked rows above as a CSV (fictional practice data). Load it with Get data › Text/CSV, build the measures, then check your cards.
Expected values to check
On hand 465 · Qty out 310 · Days of cover 45.0 · Rice Nashik 10 days · Oil Pune 180 days
| Good KPI | Vanity metric | |
|---|---|---|
| From the same rows | Rice cover 10 days | Oil on hand 180, the biggest number |
| Why | Nashik rice runs out in about ten selling days. | Oil looks like a healthy godown and will sit for half a year. |
What the manager does. The Nashik planner raises a rice order and leaves dal (60 ÷ (60 ÷ 30) = 30 days). The Pune planner delays the next oil purchase. Atta is 90 ÷ (90 ÷ 30) = 30 days. That is the planned cover in this file. Do not reorder atta because rice is short.
Ravindra Bagale's Tip
A common mistake is summing On Hand across seven snapshot dates, so stock looks seven times too big. Filter the snapshot to the last date, the MAX date, then sum. Do not add the ledger and the snapshot together. Don't forget this.
Ravindra Bagale's Tip – मराठी
एक common चूक म्हणजे सात snapshot dates वर On Hand ची बेरीज करणं, म्हणजे stock सातपट मोठा दिसतो. Snapshot ला शेवटच्या date चा, म्हणजे MAX date चा filter द्या, मग sum करा. Ledger आणि snapshot एकत्र बेरीज करू नका. अजिबात विसरू नका.
Ravindra Bagale's Tip – हिंदी
एक common गलती है सात snapshot dates पर On Hand का जोड़ लगाना, तो stock सात गुना बड़ा दिखता है. Snapshot पर आख़िरी date का, यानी MAX date का filter लगाओ, फिर sum करो. Ledger और snapshot को एक साथ मत जोड़ो. बिल्कुल मत भूलो.