material requirement planning excel

Excel Material Requirement Planning: Streamline Your Inventory

By Mark Lopez
AD

Material Requirement Planning (MRP) is a critical process in inventory management, ensuring businesses have the right materials at the right time. While dedicated software exists, Microsoft Excel remains a popular choice for many due to its accessibility, flexibility, and affordability. This article explores the fundamentals of MRP and guides you through creating a simple yet effective MRP system using Excel.

Before diving into Excel, let's understand the basics of MRP. At its core, MRP calculates the quantities of each raw material required to produce a finished product, based on the production plan and bill of materials (BOM). It also considers inventory on hand, open orders, and safety stock levels. The output of an MRP system includes recommendations for new orders, receipts, and transfers.

Setting Up Your MRP Worksheet in Excel

To create an MRP system in Excel, you'll need to set up a worksheet with specific columns for each type of data. Start by listing all the items in your inventory or BOM, followed by relevant columns such as item description, current stock, lot size, lead time, etc.

AD
an image of a dashboard showing the different tasks that people can use to manage their work
an image of a dashboard showing the different tasks that people can use to manage their work
the project management spreadsheet is shown in green and orange, as well as an image
the project management spreadsheet is shown in green and orange, as well as an image
Project Management Dashboard
Project Management Dashboard

Next, create additional columns for MRP calculations. These might include required inventory, on-order inventory, planned receipts, gross requirements, scheduled receipts, planned orders, and safety stock. Each of these columns will be filled with data based on the formulas you'll enter.

Calculating Gross Requirements

Gross requirements represent the total demand for an item over a specific period. This is calculated by subtracting the current stock from the required inventory. The required inventory is determined by the production plan and any scheduled receipts. If the result is negative, it means the stock is sufficient, and no additional orders are needed.

a spreadsheet with multiple columns and numbers in the bottom row, including one column
a spreadsheet with multiple columns and numbers in the bottom row, including one column
a spreadsheet showing the different types of tasks that are needed for each task
a spreadsheet showing the different types of tasks that are needed for each task
the project budget sheet is shown in this image
the project budget sheet is shown in this image

In Excel, the formula for gross requirements might look like this: `=MAX([Required Inventory] - [Current Stock], 0)`

Determining Planned Orders

Planned orders are the number of items you need to order to meet gross requirements. This figure takes into account on-order inventory, safety stock, and lot size. To calculate planned orders, first determine the net requirements by subtracting on-order inventory and safety stock from gross requirements. Then, divide the net requirements by the lot size.

AD

The Excel formula for planned orders is: `=ROUNDUP([Net Requirements] / [Lot Size])`

Lean Management Training Courses • Sabat Consulting
Lean Management Training Courses • Sabat Consulting
a screenshot of a project schedule with multiple tasks in the workflow diagram below
a screenshot of a project schedule with multiple tasks in the workflow diagram below
the dashboard is full of many different things to see in this screengrafion
the dashboard is full of many different things to see in this screengrafion
Project Task Tracker Template for Efficient Organization
Project Task Tracker Template for Efficient Organization
an image of a spreadsheet with numbers and other items in it, including the data
an image of a spreadsheet with numbers and other items in it, including the data
Free To-Do List Template: Excel & Google Sheets Spreadsheet for Tasks
Free To-Do List Template: Excel & Google Sheets Spreadsheet for Tasks

Incorporating Production Data into Your MRP

Once you have the basics of your MRP worksheet set up, the final step is to integrate production data. This includes details from your production plan, such as the number of items to be produced, the date of production, and any specific requirements for raw materials.

With this information, you can calculate the required inventory for each item based on the BOM and update your MRP calculations accordingly. Keep in mind that variant bills of material (BOMs) and phantom items can add complexity to these calculations.

AD

Handling Variant Bills of Material

Variant BOMs allow for the same end product to be made from different combinations of raw materials. When calculating required inventory, you'll need to consider all possible variants. One approach is to use a separate line in your MRP worksheet for each variant, breaking down the required inventory by component materials.

Project Tracker Template for Efficient Business Management
Project Tracker Template for Efficient Business Management

Alternatively, you can use a pivot table or lookup function to dynamically calculate required inventory based on the variant selected.

Accounting for Phantom Items

Phantom items represent parent items in the BOM, rather than actual physical materials. They don't have their own columns in your MRP worksheet; instead, their subtypes (the actual materials) will inherit their required inventory. To account for phantom items, simply ensure that each subtype has its own line in your MRP worksheet and that it's linked correctly to its parent.

AD

Regularly reviewing and updating your MRP system will help ensure your inventory levels are optimal and that you're not caught short or overstocked. With practice, creating and maintaining an MRP system in Excel can become a quick and efficient part of your production planning process.

Now that you've seen the potential of Excel for MRP, why not start implementing it in your own business? Start with a simple spreadsheet, and as your needs grow, consider integrating your MRP with other tools, such as enterprise resource planning (ERP) software. The next step in your inventory management journey awaits!

AD