Skip to content
Shop | Excel system

Inventory Management System

Item master, classes, parameters, turns and reach, slow movers, cycle count plan and log, target stocks, key figures and audit.

Screenshot of the Inventory Management System workbook with example data
Example data from the workbook.

How it works

  1. 02 Setup holds the rules: class limits, service levels, holding cost rate, cost per order, maximum reach, limits for slow movers, count frequency and tolerance. 03 Items holds master data, stock and 13 weeks of demand.
  2. 04 Classes and 05 Matrix classify every item ABC and XYZ and show items, usage value and stock per field with the policy.
  3. 06 Parameters calculates safety stock, reorder point, order quantity, maximum, target stock and the status of each item.
  4. 07 Turns and Reach shows turns, days on hand and the gap to target per item and class. 08 Slow Movers lists stock without movement and excess with a decision per position.
  5. 09 Count Plan schedules the cycle counts per class. 10 Count Log records counts and calculates record accuracy and the causes of deviations.
  6. 11 Targets adds up the target stock per class and sets the goal for year one. 12 Key Figures tracks seven figures of the warehouse per month. 13 Audit holds the 25 statements. 14 Status is the one-page summary.

Definitions used in the workbook

  • Safety stock: z x SQRT(L x sD^2 + d^2 x sL^2), rounded up. z from the service level with NORMSINV, d and sD from the 13 weeks, L and sL in weeks.
  • Reorder point and maximum: Reorder point = d x L + safety stock. Maximum = reorder point + order quantity. Target stock = safety stock + half the order quantity.
  • Turns and days on hand: Turns = annual usage value / stock value. Days on hand = 365 / turns. Reach = stock on hand / average demand per week.
  • Record accuracy: Counts within the tolerance of the class / all counts.

Sheets in the workbook

  1. Start
  2. Setup
  3. Items
  4. Classes
  5. Matrix
  6. Parameters
  7. Turns and Reach
  8. Slow Movers
  9. Count Plan
  10. Count Log
  11. Targets
  12. Key Figures
  13. Audit
  14. Status

Who it is for

Materials managers who steer a whole warehouse with targets per class.

The method behind the numbers

This workbook is part of the Inventory Management toolkits. The Starter Kit adds the guide and the checklist for the first 30 days. The Excellence Bundle adds playbook, workshop pack, templates, deck and the complete Excel system.

Newsletter

One Lean idea.
Every two weeks.