mrp excel formulas

Master MRP with Excel Formulas

By Brian Hernandez
AD

Mastering Material Requirements Planning (MRP) is crucial for businesses to manage inventory levels and ensure effective production planning. Excel's powerful formulas play a key role in automating these tasks, streamlining workflows, and making MRP more efficient. Let's explore some essential MRP Excel formulas that can help you optimize your processes.

Before diving into the formulas, ensure you're familiar with the basic MRPcolumns: Bill of Materials (BOM), Quantity, Reorder Point, Reorder Quantity, Lead Time, Safety Stock, and Inventory on Hand. These will be referenced throughout this guide.

Calculating Reorder Point and Inventory Requirements

The reorder point helps determine when it's time to replenish inventory. It's calculated by subtracting inventory on hand and safety stock from the sum of demand during the lead time.

AD
Top 21 Excel Formulas
Top 21 Excel Formulas
the excel formulas chart with numbers and symbols for each item in this table, you can
the excel formulas chart with numbers and symbols for each item in this table, you can
Excel Formula mind map
Excel Formula mind map

First, let's calculate Demand During Lead Time (DDLT):

DDLT = (Consumption Rate * Lead Time)

Excel formulas
Excel formulas
ms excel formula
ms excel formula
Exel Formulas
Exel Formulas

Formula for Reorder Point (ROP)

Now, plug the DDLT into the reorder point formula:

ROP = (DDLT) - (Inventory on Hand + Safety Stock)

AD

Example: If your consumption rate is 5 units/day, lead time is 7 days, and you have 20 units on hand with a safety stock of 10 units,

50 Must-Know MS Excel Formulas Every Student & Professional Should Learn 📊⚡
50 Must-Know MS Excel Formulas Every Student & Professional Should Learn 📊⚡
a poster showing the top 22 excel formulas
a poster showing the top 22 excel formulas
a poster with the words and numbers for formulas in excel, as well as an image
a poster with the words and numbers for formulas in excel, as well as an image
the top 2 excel formulas are shown in this poster, and it is also available for
the top 2 excel formulas are shown in this poster, and it is also available for
the top 15 excel formulas for each user in their workflow, including numbers and symbols
the top 15 excel formulas for each user in their workflow, including numbers and symbols
< table style="width:100%; border:1px solid black;"> DDLT=(5*7)35 units ROP=(35)- (20+10)5 units

Formula for Reorder Quantity (ROQ)

Calculate the reorder quantity, which is the total amount to order when the reorder point is reached:

ROQ = (DDLT) - (Inventory Received Since Last Order)

AD

Example: Assume you've received 10 units since your last order,

ROQ = 35 - 10 = 25 units

Calculating Safety Stock

Safety stock is vital to prevent stockouts and production disruptions. It's typically expressed as a multiple of the standard deviation of daily usage times the square root of the average daily usage.

AD

Formula for Safety Stock

Safety Stock = Z * (σ * √Daily Usage)

Where:
Z = Safety factor (usually 1.28 for 90% service level)
σ = Standard deviation of daily usage
Daily Usage = Average daily usage

Using Excel's NORM.S.INV function for Z

Thanks to Excel's STAT function library, finding the Z value for a given service level is straightforward. For a 90% service level, use:

Z = NORM.S.INV(1 - (1 - 0.9)^(1/365))

Substitute the calculated Z value back into the safety stock formula. For illustration, let's assume the standard deviation of daily usage is 8 and the average daily usage is 50 units:

Safety Stock = 2.25 * (8 * √50) = 36 units

Monitoring Inventory Levels: Stockout Days

Another crucial metric is the stockout days, which measures the average number of days when inventory is depleted before a new order arrives.

Stockout Days = (Average Daily Usage * Lead Time) / (ROQ / (Inventory on Hand + Safety Stock))

Example: With an average daily usage of 50 units, a lead time of 7 days, and ROP and Inventory on Hand values from previous examples,

Stockout Days = (50 * 7) / (25 / (20 + 36)) = 0.5 days

Understanding and effectively utilizing these MRP Excel formulas can have a significant impact on your inventory management and production planning processes. Continue to refine your usage and adapt as your business grows. Happy planning!😊

AD