MRP Gross to Net Calculation Worksheet
Gross to net is the core calculation inside every MRP engine: take what the plan needs, subtract what you already have or have coming, and what remains is what must be ordered. This worksheet lets you do it by hand for one item so you can check what your ERP produced.
| Week | Gross requirements (units) | Scheduled receipts (units) | Projected on hand (start) (units) | Safety stock (units) | Net requirement (units) | Planned receipt (lot for lot) (units) | Projected on hand (end) (units) | |
|---|---|---|---|---|---|---|---|---|
0 | 0 | 80 | ||||||
60 | 60 | 20 | ||||||
90 | 90 | 20 | ||||||
0 | 0 | 20 | ||||||
60 | 60 | 20 | ||||||
200 | 200 | 20 |
Lot for lot is assumed: planned receipts equal net requirements. For fixed or minimum order quantities, round the planned receipt up yourself and the ending on hand will rise accordingly. Enter each week's starting on hand from the previous row's ending on hand. Offset planned receipts by lead time to get planned order release dates.
Check your ERP's MRP output
Optional: we can review how planned orders are being calculated from your ERP data.
Optional. The tool and every export work without this. No spam.
How to use the worksheet
Enter one row per planning bucket, usually a week. Put the dependent or independent demand in gross requirements, open purchase orders or shop orders in scheduled receipts, and the starting projected on hand for that week. Safety stock is the level you want to protect. The sheet computes the net requirement and the planned receipt, then the ending on hand, which you copy into the next row as the starting figure.
- Use one worksheet per part number.
- Keep buckets the same size throughout.
- Copy ending on hand into the next row start.
- Export to Excel to add lead time offsets.
The logic behind it
Net requirement equals gross requirement minus scheduled receipts minus projected on hand, plus safety stock, floored at zero. When the result is positive, MRP creates a planned order for at least that quantity, sized by the lot sizing rule on the item master, and offsets its release date by the lead time. When the result is zero, no action is needed. Ending on hand is then starting on hand plus all receipts minus gross requirements.
Reading the example
In week 1 the 100 scheduled units cover the 80 gross, so no net requirement appears and on hand climbs to 80. In week 2 there is no supply, so the net is 120 minus 80 plus the 20 safety stock, which is 60. Week 6 shows a large 200 requirement that drops straight to net because on hand is only at safety stock. Spikes like that are where planners ask whether to build ahead.
Common mistakes
The usual errors are double counting supply, mixing bucket sizes, and forgetting that safety stock raises the net rather than reducing on hand. Another is treating allocated stock as available: if an order already reserved the on hand, subtract the reservation first. When your ERP disagrees with this sheet, the cause is nearly always lot sizing, a time fence, or supply that sits outside the planning horizon.
Frequently Asked Questions
What is the difference between gross and net requirements?
Gross requirements are total demand for an item in a period before considering any stock or supply. Net requirements are what is left after subtracting on hand and scheduled receipts and adding safety stock. Only the net quantity triggers new planned orders.
Does MRP subtract safety stock from available inventory?
Effectively yes. Safety stock is a target that must remain on hand, so MRP treats inventory at or below that level as unavailable and creates a net requirement to restore it. This worksheet adds safety stock to the net calculation for the same effect.
Why is my planned receipt larger than the net requirement?
Because of lot sizing. Minimum order quantity, fixed order quantity, multiples or rounding values on the item master increase the planned order above the net. The surplus flows into the next period's on hand.
How do I get the planned order release date?
Subtract the item lead time from the planned receipt date, using the calendar your plant works to. Export the worksheet to Excel and add a release date column if you need it for purchasing.
If your ERP planning output never quite matches what planners calculate by hand, Netray can build a read-only explainer that shows the gross to net steps behind each planned order.
Related Tools
BOM Explosion Requirements Worksheet
Explode a bill of materials into component requirements, with quantity per, scrap allowance and extended cost for each line.
Supply Chain & InventoryLot for Lot vs Fixed Order Quantity Simulator
Compare the annual cost of ordering exactly what is needed each week against ordering a fixed batch, and see where the cost curve bottoms out.
Supply Chain & InventoryMRP Planning Time Fence Calculator
Derive a demand time fence and planning time fence from your lead times and see how much of the horizon is frozen.
Discrete ManufacturingMaster Production Schedule Template
A weekly MPS grid with forecast, customer orders, projected on hand and planned production quantity for a single product.
More Supply Chain & Inventory tools
See all- Oracle Fusion Inventory Turns and Carrying Cost Calculator
- Distribution ERP Fill Rate Calculator
- Distributor Rebate Tracking Worksheet
- Branch Transfer Planning Worksheet
- Distributor Slow-Moving Inventory Review Worksheet
- Distributor Vendor Performance Scorecard Worksheet
- Distributor GMROI Calculator
- Distributor Backorder Cost and Fill Rate Calculator
- EOQ with Vendor Minimum Order Calculator