Master MRP with Excel Formulas
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.
First, let's calculate Demand During Lead Time (DDLT):
DDLT = (Consumption Rate * Lead Time)
Formula for Reorder Point (ROP)
Now, plug the DDLT into the reorder point formula:
ROP = (DDLT) - (Inventory on Hand + Safety Stock)
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,
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)
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.
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!😊