How does the template calculate the net requirement?

Net requirement = MAX(0, safety stock - (last week's projected on hand + scheduled receipts - gross requirement)). With 420 units on hand, 380 arriving, 1,250 required and 100 of safety stock, the net requirement is 1,250 + 100 - 800 = 550 units, the same as the MRP Material Requirements calculator.

How are planned orders sized?

Each planned receipt is the net requirement rounded up to the item's order multiple. With a multiple of 250 and a net requirement of 70, the planned receipt is 250 and projected on hand ends 180 above safety stock, which covers the following weeks until it runs out. A multiple of 1 is lot-for-lot.

When must an order be released?

Release week = receipt week minus lead time. A controller board needed in week 4 with a six-week lead time should have been released two weeks before the plan start, so the row says Overdue: expedite the supplier or move the demand. A release that falls in week 1 says Due soon.

Why does the table use cumulative sums instead of last week's balance?

A row in an Excel table cannot refer to the row above without a circular reference. The template compares cumulative requirements with cumulative supply instead; the running maximum of the uncovered amount, rounded up to the order multiple, gives the same planned receipts as the classic week-by-week MRP record, which the Verify sheet checks.

Does it explode a bill of materials?

No, it plans one level. Type each component's gross requirement from your master schedule times its quantity per, for example 340 fans times 6.5 kg gives 2,210 kg of steel sheet that week. Planned releases of a made item are not exploded into its own components automatically.

Can I plan more items or weeks?

Yes. The item master and MRP Lines are Excel tables that grow as you add rows, and the row calculations work for any number of weeks. The dashboard blocks cover eight items and 12 weeks as shipped; the Method sheet explains how to extend them, and a warning appears if your item list is longer.

Planning and Supply Chain

MRP Planner Excel Template

Net each item's weekly gross requirements against stock, open orders and safety stock, and get planned orders rounded to your order multiple, the week each must be released and the releases that are already late.

Overview

This MRP planner is a working Excel model for production and material planners, buyers and small-plant owners who need time-phased material planning without an ERP module, or a transparent check on one. It ships with a fictional 12-week plan for an industrial fan maker with eight components, including a controller board whose six-week lead time means three releases are already overdue, so the dashboard shows what to expedite on day one.

Each row nets one item's week: safety stock plus gross requirements to date, minus on hand and scheduled receipts to date, is what new orders must cover. Planned receipts follow from the largest uncovered amount so far, rounded up to the order multiple, which gives exactly the week-by-week MRP record for lot-for-lot and order-multiple rules without circular references. Projected on hand, the net requirement and the release week (receipt week minus lead time) follow.

The dashboard shows the MRP record for any item you pick, a summary of planned orders, first release dates and overdue releases per item, and a matrix of planned releases by item and week. For one week the net requirement is the same formula as the MRP Material Requirements calculator on this site, and the Verify sheet compares the workbook with an independent period-by-period calculation.

What this template includes

Setup sheet for the plan start and an item master with unit, source, on hand, safety stock, lead time in weeks and order multiple
MRP Lines table with one row per item per week: gross requirement and scheduled receipts in; net requirement, planned receipt, projected on hand, release week, planned release and release status out
Lot-for-lot or order-multiple lot sizing and a lead-time offset, with Overdue and Due soon flags in words
A Check column that catches items missing from the item master, dates that are not plan Mondays and duplicate item weeks
Dashboard KPI tiles for planned orders, releases due this week, overdue releases and items affected
The classic MRP record for the item you pick, with a chart of gross requirement, planned receipts, projected on hand and safety stock
Item summary and a planned-release matrix by item and week that works as the buyer's action list, plus a chart of orders to release per week
Method sheet with every formula, and a Verify sheet that compares the workbook with an independent week-by-week MRP calculation and the MRP Material Requirements calculator
CSV with live formulas of the MRP Lines for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to plan purchased and made components without an ERP MRP module, to sanity-check what your ERP suggests, to see which orders a buyer must place this week, and to spot the long-lead items where a release date has already passed.

How to use it

On Setup, set the plan start to this week's Monday and list your items with unit, on hand, safety stock, lead time and order multiple.
On MRP Lines, give every item one row per week with its gross requirement (schedule x quantity per, plus service demand) and any open order due that week.
Fix anything the Check column reports, then read each row's planned receipt, release week and release status.
On the Dashboard, pick an item to see its MRP record, release what is Due soon and expedite or reschedule anything Overdue.

Frequently Asked Questions

How does the template calculate the net requirement?: Net requirement = MAX(0, safety stock - (last week's projected on hand + scheduled receipts - gross requirement)). With 420 units on hand, 380 arriving, 1,250 required and 100 of safety stock, the net requirement is 1,250 + 100 - 800 = 550 units, the same as the MRP Material Requirements calculator.
How are planned orders sized?: Each planned receipt is the net requirement rounded up to the item's order multiple. With a multiple of 250 and a net requirement of 70, the planned receipt is 250 and projected on hand ends 180 above safety stock, which covers the following weeks until it runs out. A multiple of 1 is lot-for-lot.
When must an order be released?: Release week = receipt week minus lead time. A controller board needed in week 4 with a six-week lead time should have been released two weeks before the plan start, so the row says Overdue: expedite the supplier or move the demand. A release that falls in week 1 says Due soon.
Why does the table use cumulative sums instead of last week's balance?: A row in an Excel table cannot refer to the row above without a circular reference. The template compares cumulative requirements with cumulative supply instead; the running maximum of the uncovered amount, rounded up to the order multiple, gives the same planned receipts as the classic week-by-week MRP record, which the Verify sheet checks.
Does it explode a bill of materials?: No, it plans one level. Type each component's gross requirement from your master schedule times its quantity per, for example 340 fans times 6.5 kg gives 2,210 kg of steel sheet that week. Planned releases of a made item are not exploded into its own components automatically.
Can I plan more items or weeks?: Yes. The item master and MRP Lines are Excel tables that grow as you add rows, and the row calculations work for any number of weeks. The dashboard blocks cover eight items and 12 weeks as shipped; the Method sheet explains how to extend them, and a warning appears if your item list is longer.