Why Doing Material Requirements Planning in Excel is Doomed to Fail

    9/11/19, 10:00 AM

    mrp-excel

    Spreadsheets are cool

    Let me start out by saying I like spreadsheets. I use Excel to analyze data, create different scenarios, communicate ideas, etc. Excel is on every computer I own. I really can’t imagine not being able to use Excel in my daily life.

    I also like wine. I love visiting wineries in California, sharing a bottle with my wife or friends, and learning about the production of the wine. I have a big wine fridge in my house. When visiting friends, I usually bring a bottle of wine as a gift.

    However, I’ve never brought a bottle of wine to a funeral, had a glass when I was working, or thought about making wine my morning beverage instead of decaf coffee. (Yes, I drink decaf coffee. Don’t judge.) Even though I like wine, it’s not appropriate for every situation.

    Spreadsheets aren’t appropriate for every situation either. I will not use a spreadsheet to run a business—hard stop. I won’t do it. I do not run Visual South on spreadsheets. I did not run production facilities on spreadsheets. As I said, Excel is great for analyzing data from the system I use to run Visual South, but spreadsheets aren’t the right tool to run your company or production. Getting more specific, I’m going to tell you in this article why material requirements planning (MRP) in Excel is doomed to fail. 

    Don’t you sell software?

    Of course, as president of a company that sells and implements ERP software, this is exactly what I’m supposed to say, right? Throw those spreadsheets out and buy our MRP software!

    How to Select ERP...and not regret it

     

    But I say this because it’s true. I learned this lesson a long time ago when I tried doing what you’re thinking about right now. I had a problem, and I understood and liked spreadsheets, so I used Excel to try to solve that problem. I found it was not a solution, but just another band-aid. A better band-aid, but a band-aid nonetheless. You know who else tells me this? Every prospect I’ve ever talked to. “We use too many spreadsheets. We need to get away from that!” This is a very common refrain.

    Related: Why Use ERP’s Built-in Functionality?

    Example of material requirements planning using Excel

    Why can’t spreadsheets solve the problem? Let’s look at an example. A production area that seems like it would be a perfect fit for spreadsheet use is material requirement planning. After all, MRP is just math, and spreadsheets are great at math.

    For our example, we’ll make a bike. No variation, no different models, just a standard bike. We only need to plan for the major components:

    • 1 – bike frame
    • 2 – wheels
    • 2 – tires
    • 2 – tire tubes
    • 1 – crank
    • 2 – pedals
    • 1 – seat with tube
    • 1 – handlebar/fork assembly
    • 1 – chain

    Figuring out what I need to make 100 bikes is pretty easy in a spreadsheet. List the components, quantity needed for each bike, multiply by the number of bikes needed, and done!

    Now Let's look at what's missing.

    Demand is unrealistic. If you’re a bike manufacturer, building bikes is something you do every day. You aren’t going to build 100 and then close up shop. Demand is ongoing. You’ll need to either know when your customers want the bikes and how many they want, or you need to create a forecast to determine how many bikes need to be built and when. That means adding another tab in the spreadsheet that tracks demand, which you’ll have to update every day.

    Does the company sell spare parts? In our example, we calculated the parts needed based on the number of bikes being built. What if a customer wants to buy a replacement seat? The spreadsheet doesn’t account for that, so you’ll have to account for independent demand requirements. Add another tab to the spreadsheet where you can enter spare parts information.

    When are the raw materials needed? Ongoing production means you not only need to know what you need, but when you need it. You have to match the raw material needs with the production schedule. Add time buckets on your spreadsheet to schedule the right materials at the right time.

    When do you need to order materials? Materials don’t just appear when you order them. Each material needs to be associated with a lead time. For example, if you need 50 bike frames on October 1 and it takes four weeks to get them, you need to place the frame purchase order no later than September 1. Add a spreadsheet column for each material’s lead time.

    Do you have any inventory? If you need 50 frames but only have 20 in stock, you only need to order 30 frames. That’s why every material requirement planning calculation factors in inventory. Add a spreadsheet tab to track raw material inventory, which will need to be updated every day because it changes every day. (I should also mention that entering the inventory values is the easy part. You need to count inventory first to enter the right number.)

    Spreadsheets don’t create purchase orders. When your bike company’s accounting department receives invoices from vendors who supply your raw materials, they always ask, “Is this vendor invoice correct?” To answer that question, accounting looks at two documents: The purchase order to prove the materials were indeed ordered and the packing slip to see if all materials were received. The material requirements planning Excel sheet provides what needs to be ordered and when, but it’s a separate task to enter the actual purchase order into the accounting system.

    Is there already a purchase order to buy what you need? The spreadsheet provides a list of what needs to be ordered and when, but how are you going to inform the spreadsheet that the materials have been actually ordered? And when they’re due in? Add another spreadsheet tab for open purchase orders and update it every time you receive parts or create new purchase orders.

    The product selection is unrealistic. In this example, the company made only one type of bike. That’s unrealistic. Companies build dozens, hundreds, or even thousands of different parts and products. Take that spreadsheet and expand it to handle all the parts and products you make—that’s a lot of tabs to add.

    Does the product design ever change? Of course, it does. Make sure every design change is reflected in the spreadsheet.

    It sounded so easy before…

    There you have it. That simple spreadsheet calculating material needs isn’t so simple anymore. That’s because the data needed to calculate material needs isn’t simple—there are a lot of moving parts. Keep in mind, I’ve only touched on high-level issues. There is an entire list of nuances to consider. Can you use alternative materials? What if you need 12 of something, but the material comes in cases of 48? I could go on, but you get the idea. Spreadsheets aren’t designed to support material requirements planning.

    Actually, I said that wrong. Spreadsheets can calculate all day long with no issue. It’s the human care and feeding of the spreadsheet that is the issue. The entire system is really a house of cards that ends up collapsing under its own weight. It’s destined to fail, because it’s a failed strategy.

    Related: 5 Characteristics of Successful ERP Implementations

    Wait, there’s more

    There are two other reasons why you shouldn’t do material requirements planning in Excel sheets. The first is it introduces too much risk to the organization. Even if all the updating requirements can be eliminated (they can’t be) via an integration with your accounting system, your solution is custom built, usually by one person. You just made that person very valuable. A critical tool for a critical function of your company is controlled by one person. If that person leaves, there will be chaos.

    The second reason is cost. If you think developing and maintaining a spreadsheet to do your material requirements planning is less than buying and implementing an ERP system, you are mistaken. You can get a spreadsheet like I’ve discussed off the internet for free. Maintaining it is expensive and it is going to fail. It’s just a matter of time before you need material requirements planning software. When that happens, contact me. I’ll be here. Unfortunately, the costs you’ve already sunk into that free spreadsheet are gone. Now you’ll have to invest in the real solution – an ERP system. If you did that to begin with, you’d be money ahead.

    CloudSuite Industrial demo

    VISUAL ERP demo

    Jack Shannon

    Written by Jack Shannon

    Jack is the President of Visual South and has been working with the product since 1996 when he bought it in his role as a Plant Manager. Since 1998 he has worked for Visual South with roles in consulting, sales and executive management.