Stock Turns: the biggest pile of inventory is where your cash goes to sit

Before you start

New to this? Two figures describe the money in any product line: how much stock you hold on average (average inventory, valued at what it cost you), and how much of that stock you sold in a year (cost of goods sold, or COGS — again at cost, so the two are comparable). Divide the second by the first and you get inventory turnover: how many times you sold through your own average pile during the year. Example: a line that sold £720k at cost while holding £60k of stock on average turned 720 ÷ 60 = 12 times — it cleared its shelf every month, so cash never sat idle. A line that sold only £380k while holding £472k turned 380 ÷ 472 = 0.8 times — over a year’s worth of cash frozen in stock. A big pile is not “well stocked”; low turns mean cash trapped on the shelf. This project teaches you to rank capital efficiency by turnover, and to see why the tempting shortcut — “the lines holding the most inventory dollars are where the money is stuck” — misranks it.

1. The Brief

You’re handed a distributor’s year-end product-line report — each line’s annual COGS and its average inventory, both at cost — and asked one finance question: where is our working capital trapped? The reflex is to sort by inventory dollars and point at the biggest piles. That read is half wrong, and it’s wrong in the expensive direction: it fixates on big lines that sell through their stock six times a year (cash working hard) while missing modest lines that barely turn (cash frozen). The fix is one division — inventory turnover = annual COGS ÷ average inventory — and a ranking. You’ll build the turnover measure correctly, watch the naive “most inventory dollars” list get two of its four picks wrong, and learn the rule that puts every line on the same comparable axis: how hard the cash is working, not how big the pile is. This is a capital-efficiency question — distinct from asking whether a line is about to stock out (that’s days-of-cover, on-hand ÷ daily demand, a different metric on a different axis).

2. Difficulty & time

Difficulty 2/10. One tool (pandas), one file, one concept, and Part A’s path is fully specified — the low end of the scale. It sits level with days-of-cover (2) — another “raw quantity is the wrong axis, use the ratio” inventory project — and below abc-xyz-inventory (3) and silent-row-loss (3): each of those has more moving parts (two segmentation axes at once, a join to validate) whereas this turns on a single idea — divide sales by stock — applied cleanly. Note it is not a duplicate of days-of-cover: that one divides on-hand by daily demand to catch stockouts (a time metric); this one divides COGS by average inventory to catch trapped cash (a money metric). Different question, different axis. Like abc-xyz-inventory, its real content is choosing the right axis, which keeps it a judgment skill and off the floor of the scale. Part A is a short read-and-run; Part B is where you do the work.

3. What you’ll be able to do after

Compute inventory turnover for a set of lines and rank them by capital efficiency, worst first.
Explain in one sentence why raw inventory dollars misranks efficiency, and give an example of a big-inventory line that’s efficient and a small-inventory line that’s trapping cash.
Convert turnover to days-inventory-outstanding (365 ÷ turns) and read it as “days the cash sits.”
Aggregate turnover to a group (category, region) correctly — by pooling COGS and inventory, not by averaging the per-item ratios — and say why averaging the ratios is wrong.

4. Prereqs & time box

Part A: ~30 minutes. Part B: ~1.5 hours. Both are hard caps — if Part A runs long, you’re overthinking it. You need to read a CSV with pandas, do arithmetic on columns, and group rows; nothing more.

For where to write and run Python (install, virtual environment, per-OS terminal, running a script step by step), see Start Here guide. No account and no GPU — requires_signup is empty.

5. Setup & data

The data is synthetic, generated locally by data/make_data.py from a fixed seed (byte-identical every run) — see Sources. Generate it once:

python data/make_data.py

That writes data/distributor_a.csv (Part A) and data/pharmacy_b.csv (Part B). Nothing is downloaded; there is no external source. Synthetic is the right call here: the lesson is whether your turnover ranking matches the true capital efficiency, and only generated data lets you know the true COGS and inventory for every line in advance.

6. Part A — Guided Build

Prefer the browser? If this project shows a ▶ Run button, you can run Part A right on the page — no setup. The commands below are for running on your own computer.

▶ Try it now — run the real Part A right here in your browser. This one button makes the data and runs Part A for you — you do not need to install anything, and you do not need to type any of the python … commands anywhere on this page (those are only for running on your own computer instead). First run loads Python + this project’s libraries: usually ~10–20 s, a little longer for machine-learning projects.

Run the guided script (or paste it cell-by-cell — each # %% is one step):

python part_a/analyze_turns.py

It walks six steps and writes part_a/turnover_summary_a.csv. The shape of the argument:

STEP 1. Load the 10-line report. Checkpoint: average inventory ranges from sealant drums at ~34,800 to standby generators at ~471,850 — a ~14× spread. That column is not the efficiency column.
STEP 2. The naive read: flag the four biggest inventory piles. Checkpoint: it flags GEN-STANDBY, WIRE-CU, COMPRESSOR, PIPE-PVC — the most cash sitting in stock.
STEP 3. The real read: turns = annual COGS ÷ average inventory, sorted worst (lowest) first. Checkpoint: lowest turns are GEN-STANDBY 0.81, then SOLAR-INVERT 1.19, VALVE-BRASS 1.51, COMPRESSOR 2.00. Copper wire, the second-biggest pile, turns 6.03.
STEP 4. Flag the four worst turners. Checkpoint: the worst-turns set is GEN-STANDBY, SOLAR-INVERT, VALVE-BRASS, COMPRESSOR — and only two of them (GEN-STANDBY, COMPRESSOR) were in the naive list. The naive read missed the solar inverters and the brass valves, and wasted worry on copper wire and PVC pipe.
STEP 5. The inversion, two pairs side by side: GEN-STANDBY (472k inv, 0.81 turns → ~453 days of stock) vs FILTER-HVAC (60k inv, 12.0 turns → ~30 days); and the subtle one — WIRE-CU holds more than VALVE-BRASS yet turns 6.03 vs 1.51. The bigger pile is the efficient one.
STEP 6. Write part_a/turnover_summary_a.csv with turns and days-of-stock per line.

Why this and not that

Why not just sort by inventory dollars? Because that answers “how much cash is in stock,” not “how hard is that cash working.” A big pile is fine if it sells through many times a year; a modest pile is a problem if it never moves. Dividing COGS by inventory converts two un-comparable piles into one comparable ratio: turns per year.
Why does a big pile (copper wire, ~199k) come out efficient? Because it sold £1.2M at cost — 6× its own average stock. A large pile only traps cash if sales are low relative to it; turns, not the pile, carries that ratio.
Why report days-of-stock too (365 ÷ turns)? Because “0.81 turns” is abstract but “453 days of cash on the shelf” is not. It’s the same fact as time, and it’s the bridge to Part B’s days-inventory-outstanding.

Functions you’ll meet (and where to read more)

Function	What it does here
`pandas.read_csv`	Load `distributor_a.csv` into a DataFrame
`DataFrame.sort_values`	Rank lines by turnover, worst first (STEP 3)
`DataFrame.nlargest`	Pull the biggest-inventory lines for the naive read (STEP 2)

If something looks off

If your worst-turns list equals your “most inventory dollars” list, check that annual_cogs actually varies across lines — the whole effect comes from sales differing between lines.
If everything turns fast, confirm you divided COGS by inventory (not the reverse) — a turnover below 1 means less than a year’s sales sitting in stock, which is the trapped case.
A huge pile showing up as efficient (copper wire) is the point of this project, not a bug — read it against the checkpoints above before assuming you broke something.

7. Part B — Your Turn

Part B is the optional “your turn” half — a guided fill-in-the-blank, not a blank page. You get a working starter (part_b/starter.py) that already ranks categories the wrong way; your job is to finish the # TODO it marks out. More open than Part A’s step-by-step, but you are never staring at an empty file. If you finished Part A and want to prove the skill, this is where it happens. New to Python? It’s completely fine to stop after Part A and come back later.

Same skill, new setting, two new twists. A pharmacy chain’s pharmacy_b.csv has one row per product, each tagged with a category (its annual COGS and average inventory). Finance wants the same capital-efficiency read — but this time at the category level, and expressed as days-inventory-outstanding (DIO = 365 ÷ turns): how many days the average dollar of stock sits before it sells. Rank categories worst (highest DIO) to best and flag the ones above a 90-day target. The starter rolls categories up the wrong way — it averages the per-item DIO — which lets one tiny stagnant item (a dead specialty line) drag an otherwise fast category over the line. The # TODO is to roll up by pooling: sum each category’s COGS and inventory, then divide.

Acceptance criteria (checkable)

Your part_b/starter.py writes part_b/out/report.json with:

slow_categories — the categories you flag as capital-trapping (pooled DIO above the 90-day target),
ranking_worst_to_best — every category ordered by DIO, worst (highest) first,
target_days — the DIO target you used (90).

Then:

python part_b/starter.py
pytest part_b/test_solution.py -q

You’re done when the tests pass: every truly-trapped category is caught, the flagged set’s F1 against the true set is ≥ 0.90 (the reference pools and hits 1.0), and the ranking is in true worst-to-best order. The naive mean-of-per-item-DIO rule false-flags two fast categories — F1 0.75 — and gets the ranking wrong.

Constraints — what must be true of your solution, not how to get there

The category figure must come from pooled COGS and inventory (sum, then divide), not from averaging the per-item turns or per-item DIO.
Every category must be judged on its own rolled-up numbers — don’t hard-code or eyeball it.
Don’t drop the tiny items — they belong in their category’s totals; they just shouldn’t dominate a ratio they barely contribute to.

Hints

Hint 1 — the roll-up is the new part

In Part A each line was already one row. Here a category is spread across 2–3 item rows, so collapse each category to one figure first. groupby("category") is the move — but what you aggregate matters (see Hint 2).

Hint 2 — pool, don't average

A category’s turnover is sum(COGS) ÷ sum(avg_inventory) across its items — not the mean of the items’ individual turns or DIOs. Averaging a ratio treats a £3k item the same as a £400k item, which is how a single dead specialty line drags a fast category’s average up. Sum first, divide once.

Hint 3 — the shape of the answer

grp = items.groupby("category").agg(cogs=("annual_cogs","sum"), inv=("avg_inventory","sum")), then dio = 365 / (grp["cogs"]/grp["inv"]), then slow = sorted(dio[dio > 90].index) and rank by dio descending. The trapped categories should be the slow-selling ones, not whichever holds the most or fewest items.

8. Self-check

Your trapped categories should be the slow sellers (overstocked vitamins, seasonal allergy lines), and at least one fast category should carry a tiny stagnant item without being flagged — that’s the whole point. If a fast category gets flagged, you averaged the ratios instead of pooling.
Re-running python data/make_data.py never changes the data (it’s seeded), so your ranking shouldn’t move between runs.
If you can state, in one sentence, why you pool COGS and inventory instead of averaging the per-item turns, you’ve got the transferable skill — the specific categories are secondary.

9. Stretch

Add the aggregate turnover to Part A: pool all lines (total COGS ÷ total inventory) for one company-wide number, and confirm it is not the mean of the ten line turnovers — the same pool-don’t-average rule Part B leans on.
In Part B, compute each category’s trapped cash — how much inventory it would free by hitting a target turnover (avg_inventory − COGS ÷ target_turns) — and sort the categories by dollars freed, not by DIO. A slow category with a tiny pile matters less than a mid-DIO category holding a fortune.
(Genuinely hard.) Turnover at cost and turnover at retail differ when margins vary by line. Given a per-line gross margin, work out when ranking by retail turnover reorders the lines versus ranking at cost, and argue which one a finance team should use to judge trapped capital — and why the answer isn’t the same as the one a sales team would give.

10. Ship it

Put this in a repo README so it counts as a portfolio piece: one sentence on the trap (“ranking by raw inventory dollars flagged 2 of 4 lines wrong; turnover fixed it”), the copper-wire-vs-brass-valve pair with both numbers (bigger pile, 4× better turns), and the one-line rule (divide sales by stock — rank by how hard the cash works, not how big the pile is). Add a short note on Part B showing you transferred the same idea to a different domain (a pharmacy, at the category level) and rolled items up by pooling, not averaging. That last part — recognising the same skill under a different surface, and knowing that a ratio must be pooled — is what an interviewer is actually checking for.

11. Sources

See SOURCES.md. The data is synthetic, generated by data/make_data.py from a fixed seed; there is no external source, and nothing is downloaded or committed.

Next up

Finished this one? Continue the Supply Chain track:

Units Mismatch: you can’t sum a column that mixes cases and eaches → · Browse all courses