How does this template calculate the number of kanban cards?

Cards = (daily demand x lead time + safety stock) / container quantity, rounded up, where safety stock is the lead-time demand times your safety allowance. With the Kanban Card Count calculator's example, 100 units a day, a 2-day lead time, a 10 percent allowance and containers of 20: 200 + 20 = 220 units, so 11 cards and 220 units of kanban inventory.

What counts as the replenishment lead time?

The whole time a card is away from the loop's stock: waiting to be collected, waiting at the supplying process, being made or shipped, and travelling back with the full container. In the example, the honed-tube supplier loop uses 8 days and the line-side seal two-bin a quarter of a day. If you only count the process time, the loop will be short of cards.

How much safety allowance should I use?

Enough to cover the demand and supply variation you actually see, and no more; it is a judgement per loop, not a standard figure. In the example internal loops use 10 to 20 percent and supplier loops 20 to 30 percent. For a loop with measured demand and lead-time variation, size its safety stock statistically with the Reorder Point and EOQ Planner and use that.

What does resizing a loop free up?

Every card pulled out removes one container of stock. In the example, the honed-tube loop runs 22 cards of 40 metres at 24.30 EUR a metre; it needs 15, so pulling 7 cards frees 7 x 40 x 24.30 = 6,804 EUR. Across all loops the net change is minus 8,737 EUR even after adding cards to the three loops that are short.

Why is a container bigger than the loop need flagged?

Because the loop then holds two containers whatever the formula says: one in use and one being replenished. For a part used 5 times a day with a 1-day lead time and containers of 50, the loop needs 5 units but holds at least 50, ten days of stock. Use a smaller container or accept the stock knowingly.

Can I use it without Excel?

Yes: a CSV with live formulas of the Kanban Loops table opens in any spreadsheet, with lead-time demand, safety stock, cards required, kanban quantity, days of cover, card change, status and the inventory values as formulas of each row, and totals below. The dashboard, charts, dropdowns and self-check are in the .xlsx.

Planning and Supply Chain

Kanban Sizing Excel Template

Size every kanban loop from its own demand, replenishment lead time, safety allowance and container quantity, compare it with the cards circulating today, and see what resizing does to stock and cash.

Overview

This kanban sizing template is a working Excel workbook for lean and continuous-improvement engineers, material planners and production supervisors who run pull systems. It ships with example data for a fictional hydraulic cylinder maker with 16 loops, production kanbans from its machining, weld and paint cells, supplier kanbans for seals, tubes and fasteners, and a line-side two-bin, so the dashboard and charts are populated the moment you open it; replace the rows with your own loops and everything recalculates.

Each loop is sized with the standard kanban formula, the same as the Kanban Card Count calculator on this site: demand during the replenishment lead time, plus a safety allowance on top of it, divided by the container quantity and rounded up to whole cards. The workbook then compares the result with the cards circulating today, so every loop reads On target, Below target (too few cards, a stockout risk) or Above target (too many cards, stock that only waits), and turns the card change into inventory value.

In the example, 8 of the 16 loops need resizing. Three are short of cards, including the piston loop that stopped the line twice in September, and resizing everything frees 8,737 EUR of kanban inventory, most of it on the honed-tube loop that still runs 22 cards although the supplier's lead time fell from 12 to 8 days and 15 cards now cover it.

What this template includes

Kanban Loops table: part, loop type, supplier or process, daily demand, replenishment lead time, safety allowance, container quantity, unit cost and cards in the loop now, one row per part per loop
Per loop: lead-time demand, safety stock, cards required (rounded up to whole containers), the kanban quantity they authorise, days of cover, card change and a status word (On target, Below target = too few cards, Above target = too many)
Kanban inventory value now and at the required card count, and the value freed or added by resizing each loop
Dashboard KPI tiles: loops to resize, loops short of cards, kanban inventory value at the required cards, and the change from resizing
Two native charts: cards in loop now against cards required for each loop, and kanban inventory value by supplier or process, now and required
Tables for every loop, by supplier or process and by loop type, with suppliers, processes and loop types as dropdown lists on Setup
Check column that names problems in words: supplier or loop type not on the list, a part and loop listed twice, a container bigger than the loop needs, a missing value
Method sheet with every formula, and a Verify sheet that re-checks the formulas against an independent reference, including the Kanban Card Count calculator's example (Self-check: PASS)

Suggested use case

Use it when you set up a pull system or at each monthly plan change: recalculate the cards every loop needs, pull the cards that only add stock, add cards where a loop is starving the line, and show the inventory value the change frees.

How to use it

On Setup, enter your company name and currency label, and list your suppliers and internal processes and your loop types.
On Kanban Loops, enter one row per part per loop with daily demand, the full replenishment lead time in days, a safety allowance, the container quantity, unit cost and the cards in the loop now.
Read Cards required, Card change and Status; fix anything the Check column flags.
Open the Dashboard to see which loops to resize first and what it does to inventory value by supplier or process.
Add cards to loops that are short before you pull cards from loops that carry too many, and resize again when the plan changes.

Frequently Asked Questions

How does this template calculate the number of kanban cards?: Cards = (daily demand x lead time + safety stock) / container quantity, rounded up, where safety stock is the lead-time demand times your safety allowance. With the Kanban Card Count calculator's example, 100 units a day, a 2-day lead time, a 10 percent allowance and containers of 20: 200 + 20 = 220 units, so 11 cards and 220 units of kanban inventory.
What counts as the replenishment lead time?: The whole time a card is away from the loop's stock: waiting to be collected, waiting at the supplying process, being made or shipped, and travelling back with the full container. In the example, the honed-tube supplier loop uses 8 days and the line-side seal two-bin a quarter of a day. If you only count the process time, the loop will be short of cards.
How much safety allowance should I use?: Enough to cover the demand and supply variation you actually see, and no more; it is a judgement per loop, not a standard figure. In the example internal loops use 10 to 20 percent and supplier loops 20 to 30 percent. For a loop with measured demand and lead-time variation, size its safety stock statistically with the Reorder Point and EOQ Planner and use that.
What does resizing a loop free up?: Every card pulled out removes one container of stock. In the example, the honed-tube loop runs 22 cards of 40 metres at 24.30 EUR a metre; it needs 15, so pulling 7 cards frees 7 x 40 x 24.30 = 6,804 EUR. Across all loops the net change is minus 8,737 EUR even after adding cards to the three loops that are short.
Why is a container bigger than the loop need flagged?: Because the loop then holds two containers whatever the formula says: one in use and one being replenished. For a part used 5 times a day with a 1-day lead time and containers of 50, the loop needs 5 units but holds at least 50, ten days of stock. Use a smaller container or accept the stock knowingly.
Can I use it without Excel?: Yes: a CSV with live formulas of the Kanban Loops table opens in any spreadsheet, with lead-time demand, safety stock, cards required, kanban quantity, days of cover, card change, status and the inventory values as formulas of each row, and totals below. The dashboard, charts, dropdowns and self-check are in the .xlsx.