material requirements planning in excel

Excel-based Material Requirements Planning: Streamline & Optimize Your Supply Chain

By Amanda Walker
AD

Material Requirements Planning (MRP) is a critical process in modern manufacturing, ensuring that the right materials are available at the right time while minimizing excess inventory. While specialized MRP software exists, many businesses prefer to use Microsoft Excel for its versatility, accessibility, and low cost. This article explores how to set up an effective Material Requirements Planning system in Excel, optimizing your production and supply chain management.

Before diving into the details, let's highlight the key benefits of using Excel for MRP: it's user-friendly, allows for real-time updates, and integrates well with other spreadsheet tools. Plus, it provides a familiar environment for most users, facilitating quick onboarding and easy collaboration.

Setting Up the Excel MRP Worksheet

The first step in Material Requirements Planning with Excel involves creating a worksheet that tracks material requirements, receipts, and orders. This master list helps maintain visibility and control over your inventory, ensuring that materials are available when needed.

AD
Excel Material Requirements Planning (MRP) Template
Excel Material Requirements Planning (MRP) Template
Project Management Dashboard Excel Template for Better Planning
Project Management Dashboard Excel Template for Better Planning
a spreadsheet showing the table and numbers for each item in this project,
a spreadsheet showing the table and numbers for each item in this project,

To set up your Excel MRP worksheet, follow these steps:

  1. Create headers: Use columns to track items such as Part Number, Description, Quantity on Hand, Reorder Point, Unit of Measure, and Quantity per UOM.
  2. Populate the list: Input your inventory data, using realistic quantities and reorder points based on historical data and supplier lead times.
  3. Add calculations: Use formulas to calculate planned order receipts, scheduled receipts, gross requirements, and planned orders based on demand and supplier lead times.

Tracking Inventory Levels

Regularly updating the Quantity on Hand column ensures that your MRP system reflects your current inventory levels accurately. Monitor these levels closely to avoid stockouts or excess inventory.

a diagram showing the flow of various processes in an organization's workflow, including
a diagram showing the flow of various processes in an organization's workflow, including
the excel skills roadmap is an excellent way to learn how to use it
the excel skills roadmap is an excellent way to learn how to use it
the project schedule is shown with several tasks to be completed on it, including an important task
the project schedule is shown with several tasks to be completed on it, including an important task

To keep this data up-to-date, consider using Excel's built-in data validation features to enforce data entry rules and maintain data integrity. You can also set up automated notifications or alerts for when inventory levels reach critical thresholds.

Planning Material Requirements

With an accurate inventory track, you can now calculate material requirements based on production schedules and customer orders. Use Excel's built-in functions, such as SUMIFS or VLOOKUP, to determine gross requirements and net requirements after accounting for existing inventory and on-order materials.

AD

Once you've determined the net requirements, you can calculate the planned order quantity based on your use preferences (economic order quantity, for example) and available safety stock. These calculations help balance the costs of inventory holding and ordering, ensuring optimal inventory levels.

a project plan is shown in the middle of a large sheet with several different sections
a project plan is shown in the middle of a large sheet with several different sections
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
4+ Excel Project Management Template
4+ Excel Project Management Template
Make a chore planner in Excel
Make a chore planner in Excel
a dashboard showing how to write a project plan in excel and word document formats
a dashboard showing how to write a project plan in excel and word document formats
the project planner is displayed in microsoft's office 365 dashboard, which shows how to use it
the project planner is displayed in microsoft's office 365 dashboard, which shows how to use it

Implementing Bill of Materials (BOM) Management

A critical component of any MRP system is tracking the materials required to produce each finished product. By implementing a Bill of Materials (BOM) in your Excel worksheet, you can efficiently plan the material requirements for each production run.

To set up BOM management, create a new worksheet with columns for Item Number, Quantity Required, Unit of Measure, and other relevant information. Use a reference number to link each BOM to the relevant finished good in your inventory list. This way, changes in production schedules will automatically impact material requirements.

AD

Managing Sub-Assemblies

Some BOMs might include sub-assemblies, which are assemblies of parts themselves. To manage sub-assemblies in your Excel MRP system, create a separate BOM worksheet for each sub-assembly. Use the same reference system to link these sub-assemblies to their respective parent items.

Simplify Your Tasks With This To Do List Spreadsheet Excel Spreadsheet Shorts X99MFh7igig
Simplify Your Tasks With This To Do List Spreadsheet Excel Spreadsheet Shorts X99MFh7igig

When calculating material requirements, ensure that your formulas account for the cumulative effects of these sub-assemblies. This will help you accurately plan for the materials needed at each stage of production.

Nesting BOMs

In some cases, you may have BOMs that are nested within other BOMs (i.e., a finished good's BOM includes another BOM as a component). To manage these nested BOMs in Excel, create separate worksheets for each BOM and use the reference system to link them together.

AD

When calculating material requirements, ensure that your formulas follow the path of the nested BOMs, accurately-accounting for the materials required at each level. This might involve using functions like SUMIFS or INDEX MATCH to trace the materials required through the nested BOM structure.

With an effective Material Requirements Planning system in Excel, you can optimize your inventory and production processes, improve efficiency, and reduce costs. Regularly review and update your MRP worksheet to reflect changes in production schedules, customer orders, and supplier lead times. By maintaining a well-organized, accurate MRP system in Excel, you'll be well-equipped to navigate the challenges of modern manufacturing and supply chain management.

AD