material requirements planning excel

Mastering Material Requirements Planning with Excel: A Comprehensive Guide

By Ashley Thomas
AD

Material Requirements Planning (MRP) is a critical process in inventory management and production planning, ensuring that you have the right amount of materials, at the right time, at the right cost. While there are various software solutions available, using Microsoft Excel for MRP can be an effective and cost-efficient approach, especially for small to medium-sized businesses. In this comprehensive guide, we will explore the intricacies of setting up a Material Requirements Planning system using Excel.

Excel, with its flexibility and widespread use, offers numerous tools and features that can be leveraged to create a robust MRP system. From calculating safety stock and reorder points to scheduling production runs and managingInventory levels, Excel can handle it all.

Setting Up Your MRP System in Excel

Before diving into the details of MRP, it's crucial to correctly set up your Excel workbook. This process involves creating multiple sheets to store different types of data, such as item master, Bill of Materials (BOM), inventory, and planned orders.

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
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,

First, let's discuss the Item Master sheet. This sheet will contain detailed information about each item in your inventory, including the item code, description, unit of measure, price, lead time, and reorder point.

Item Master Sheet

To create the Item Master sheet, follow these steps:

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
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
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
  • Create a new sheet and rename it as "ItemMaster".
  • Add headers in the first row: ItemCode, ItemDescription, UnitOfMeasure, Price, LeadTime, ReorderPoint.
  • Enter the relevant data for each item in the following rows.

Bill of Materials (BOM) Sheet

The BOM sheet is a critical component of your MRP system, as it defines the ingredients required to produce an end product. For each finished item, the BOM sheet lists the raw materials, components, or subassemblies required and their respective quantities.

To create the BOM sheet, follow these steps:

AD
  • Create a new sheet and rename it as "BOM".
  • Add headers in the first row: ItemCode, ItemDescription, Quantity, UnitOfMeasure.
  • Enter the BOM data for each finished item in the following rows.

Calculating Material Requirements

Now that you have set up the necessary sheets, you can proceed to calculate material requirements. The primary goal of MRP is to determine the net requirements for each component, based on current Inventory levels, planned orders, and the BOM.

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
a spreadsheet with the words, good docs and other things to do
a spreadsheet with the words, good docs and other things to do
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
Interview Schedule Template | Template Business
Interview Schedule Template | Template Business
the project tracker spreadsheet is shown in red, white and blue with text that reads
the project tracker spreadsheet is shown in red, white and blue with text that reads
Kanban Board Google Sheets Excel Template Task Tracker Project Planner Project
Kanban Board Google Sheets Excel Template Task Tracker Project Planner Project

To calculate material requirements in Excel, you can use a combination of formulas, such as SUMIF, IF, and the net requirement formula. The net requirement formula is calculated as follows: Net Requirements = Gross Requirements - Available Inventory + Planned Orders.

Calculating Gross Requirements

Gross Requirements represent the total quantity of materials needed to fulfill demand. This can be calculated using the SUMIF function to sum up the BOM quantities for all finished items that require the specific component.

AD

For example, if you have item A (a finished product) that requires 2 units of component B, and there are 100 units of item A planned for production, the gross requirement for component B would be 200 units (100 units x 2 units per item A).

the project cost sheet is shown in this image
the project cost sheet is shown in this image

Calculating Net Requirements

Net Requirements take into account available inventory and planned orders to adjust the gross requirements. This ensures that you only order the materials you need, reducing inventory costs and optimizing production.

To calculate net requirements, use the following formula: Net Requirements = Gross Requirements - Available Inventory + Planned Orders.

AD

Generating Purchase Orders

Once you have calculated the net requirements, you can generate purchase orders to meet those requirements. This involves comparing the net requirements to the reorder point and, if necessary, sending a purchase order to your supplier.

At this stage, you can create a new sheet for purchase orders, with columns for Purchase Order Number, Supplier, Item Code, Quantity, Price, and Lead Time. Use the Excel's autorecord feature or manually input data to generate purchase orders.

Creating a Purchase Order

To create a purchase order, follow these steps:

  • Create a new sheet and rename it as "PurchaseOrders".
  • Add headers in the first row: PurchaseOrderNumber, Supplier, ItemCode, Quantity, Price, LeadTime.
  • Enter the purchase order data based on the net requirements and reorder point information.

Creating and maintaining an MRP system in Excel requires careful planning and organization. However, the benefits are immense, as an effective MRP system can help you reduce inventory costs, improve production efficiency, and enhance overall customer satisfaction. By following the guidelines outlined above, you can establish a robust MRP system in Excel that caters to the unique needs of your business. To stay competitive in today's fast-paced market, it is crucial to continually refine and adapt your MRP system to accommodate changes in your inventory, production, and customer demands.

AD