MRP calculator with a worked MRP example, week by week

An MRP example: one purchased part, ten weeks

MRP, material requirements planning, is the planning run inside your ERP that decides what to buy, how many, and when. The material requirements planning lesson explains the method in full. MRP reads three inputs: the master schedule, which says which products you build in which week; the bill of materials, which says which parts each product uses; and your inventory records, which hold stock on hand, open POs, and each part's lead time, lot rule and safety stock. It gives back planned orders, each with a quantity and a release week, and exception messages for open orders that no longer fit the plan.

The example is mounting bracket BR-210, used twice in pump P-100 and once in pump P-200. You start with 420 on hand, an open PO for 500 due in week 2, a safety stock of 150, a 3-week lead time, 2% scrap and a supplier minimum of 600 in multiples of 200. Fig. 1 follows the bracket through ten weeks. Each week's builds draw stock down. An open PO lands in week 2, and MRP plans three orders so stock never falls below safety stock. Each planned order lands in the week stock would otherwise have dipped under the floor.

MRP works out every week in four steps

MRP works through the grid one week at a time, left to right. It explodes the parents' build plan into a gross requirement, nets it against stock and open POs, sizes an order with the lot rule, and offsets that order by the lead time. Hover over or tap any term to see what it stands for.

Explode the bill of materials

For a part used in two products, add each product's builds times its quantity per, then add the scrap allowance and round up:

Net against what you have

Stock left from last week and the open POs due this week cover what they can. The net requirement is what it takes to keep safety stock intact:

Size the order with the lot rule

Lot-for-lot orders the net requirement. A fixed quantity orders whole lots. An MOQ with an order multiple orders at least the minimum, in steps of the multiple:

Offset by the lead time

The order has to be placed a lead time before the week that needs it. A release week before week 1 is past due:

Check one week by hand

In the illustrative example, week 4 has the first planned order:

• Builds: 80 of P-100 at 2 brackets each is 160, and 120 of P-200 at 1 each is 120, so the bill of materials needs 280.
• Scrap: 280 × 1.02 = 285.6, rounded up to a gross requirement of 286.
• Available: week 3 ends with 388 on hand and no open PO lands in week 4, so 388 − 286 = 102.
• Net: the safety stock is 150, so the week is 150 − 102 = 48 short.
• Lot size: max(48, 600) = 600, a multiple of 200, so MRP plans 600. On hand ends week 4 at 102 + 600 = 702.
• Offset: 4 − 3 weeks of lead time puts the release in week 1, this week.

Work an MRP grid for your own part

The calculator opens on an illustrative bracket used in two pumps, so a complete plan is visible before you change anything. Type your own build plan and open POs into the grid, set the lead time and lot rule, and read the reason for each planned order under it. Everything runs in your browser, and the link keeps your inputs.

Run the same MRP grid in Excel or Google Sheets

Download the MRP grid template (Excel, opens in Google Sheets too). It holds the same illustrative bracket as live formulas: change the inputs in column B and the build plan and open PO rows, and the gross requirement, projected on hand, net requirement and planned orders recalculate. The calculator's Download CSV button exports whatever grid you have on screen. Each row uses one formula across the weeks:

• Gross requirements: =ROUNDUP((builds P-100 × qty per + builds P-200 × qty per) × (1 + scrap), 0)
• Net requirements: =MAX(0, safety stock − (last week's projected on hand + open POs − gross))
• Planned order receipts: =IF(net > 0, CEILING(MAX(net, MOQ), multiple), 0)
• Planned order releases: =OFFSET(planned receipt, 0, lead time), the receipt the lead time later

Set part targets from the kit, not part by part

A pump build needs every part in its kit. If each of 10 purchased parts is on hand for 98% of builds, and the parts run short independently, the whole kit is complete for 0.98¹⁰ = 81.7% of builds. About one build in five waits for a part.

Work backward from the kit: a 95% kit target across 10 parts asks for 99.5% on each part. Cheap parts can carry more than that, which lets an expensive part run a little lower while the kit still hits its target. Treat the part figure as a cycle service level, the chance of no stockout before a build, and label it that way wherever you quote it. A fill rate, the share of demand served from stock, measures something else. On lumpy demand a 95% cycle service level can sit beside a fill rate near 50%.

Measure lead time from your receipts and buffer for the supplier you have

Measure lead time from your own PO receipts: received date minus order date, per supplier and part. A supplier who lands on the same day every time needs little buffer; one whose deliveries swing needs more at the same target. A swing above about a quarter of the average lead time marks unreliable supply. The spread goes into safety stock through σ_LT:

Z is one-sided: 1.645 covers 95% of replenishment cycles. The safety stock calculator works the formula from a supplier quote or your PO history.

Use safety lead time when timing is the risk

When receipts land late and demand holds steady, safety lead time often beats safety stock. It plans each order to arrive a set number of weeks early. Set 2 weeks of safety lead time in the calculator and slide the real lead time to 5: the bracket never runs short. The grid shows the cost too: each order arrives two weeks early, and the first one is past due today. Short shipments are a different risk, a yield problem that lead-time spread does not capture.

Keep strategic buffers in their own layer

A single-source part, or a disruption your receipt history has never seen, needs a strategic buffer above safety stock. Size it in days of cover, so the time you can run without the supplier is at least the time the supplier needs to recover. Keeping it separate shows what resilience costs.

MRP is only as good as its inputs: check them before you trust a suggestion

MRP's arithmetic holds up. Bad inputs send the plan wrong, most often these:

• The typed lead time. MRP offsets every order by the number in the item master and never checks it against real receipts. Move the slider in the calculator to watch a typed 3 weeks that runs 5 in practice turn into a stockout. The supplier lead time lesson shows how to check the field.
• Open POs that will not land on their due date. MRP counts a scheduled receipt in full, in the week it is due, whether or not the supplier confirmed it. An unconfirmed PO that slips two weeks leaves the same hole as a missing one. See purchase order tracking, and list each open line by its promised date in the open PO tracker.
• Stock you cannot issue. Unposted receipts, stock in quarantine, or a second warehouse counted twice all change the netting.
• A stale bill of materials. A wrong quantity per or a missing scrap allowance multiplies through every week. If the floor issues 2.1 brackets per pump and the bill of materials says 2, MRP buys short every time.
• Infinite capacity. MRP assumes your plant can build whatever the master schedule says. If the line cannot run the week 6 builds, the brackets for week 6 arrive too early.
• MOQ overbuy. Turning a 48-unit need into a 600-unit order is correct arithmetic and may still be the wrong buy. Price the leftover with the EOQ calculator before you accept it, and see MOQ vs EOQ for when the minimum is worth paying.
• The wrong tool for the part. For cheap parts with steady use, a reorder point or a two-bin kanban does the job with less setup and the same protection. The reorder point formula sets one.

Where your ERP may count differently

If your ERP's suggestion differs from the grid by a few units or a week, check three settings before you suspect the arithmetic. Your ERP may add scrap on top of the bill of materials quantity, as this grid does, or treat it as a yield and divide by (1 − scrap). At 2% scrap the two differ by less than half a unit in a thousand. Your ERP may net safety stock as a floor, as here, or leave it out of netting and report it as an exception. And most ERPs store and plan lead time in days, while this grid plans in weekly buckets, so a 17-day lead time becomes 3 weeks here. The material requirements planning lesson walks through the method in more depth.

MRP plans with the dates you give it. Cartograph follows up on every open PO: Scout Agent emails and calls your suppliers to confirm dates and expedite late lines. You learn which receipts your plan counts on will land late while there is still time to act. Get Cartograph free to see it on your own POs.

MRP questions