The Reality of Your Manufacturing World

by Kien Leong

Manufacturing Reality and Master Data

Manufacturing software is an hungry beast.  You need to feed the system with data.  Get it right, and your manufacturing planning system will reflect reality.

The cost of getting it wrong goes way beyond inaccuracy.

It can force the business into an ill-fitting strait-jacket.  Discipline that fixes the wrong processes can be deadly to business performance.

Successful implementation of manufacturing software depends on good master data.  As we all know: Garbage In, Garbage Out.

An Alternative To Software Sticker Shock

The SAP solution to the data problem is simple: Change your business to fit the system.  Oh, and budget another million or two for the consultants to help you do this.

the trouble is, executives often lavish plenty of attention on the buying cycle but perhaps less on project execution.  The result is flawed data and system routines that do not correctly reflect the business requirements.

Clients ask us to build them manufacturing planning and scheduling systems in Excel for three main reasons:

  1. Flexibility.  They need to manipulate the master data in a way that is not supported by off-the-shelf manufacturing software systems.
  2. Speed.  They need to develop a planning capability and do not the time to go through the usual bureaucracy and politics required for IT budget approval.
  3. Cost.  They need manufacturing planning software, but their business does not support the cost of implementing a large package system.

So, people want something that is better, faster and cheaper.  No surprise there.

Small Businesses and Multinationals Have the Same Needs

Interestingly, in the list of priorities, cost almost always comes last.  There are an increasing number of Software-as-a-Service (SaaS) solutions for ERP that can give you a system with a handful of users for a few hundred dollars per month.  So, low cost is not the primary goal that brings clients to request spreadsheet planning systems.

Another interesting point: this list of priorities is the same, no matter if the client works for a small enterprise or a large multinational company.

We usually have a mix of clients on the go: from a six person craft workshop to the multi-billion Fortune 100 company.  They need better, faster and cheaper.

There is no budget for Advanced Planning and Scheduling/Optimization.  This could be because the cost of the system is in the same ballpark as than their annual revenue.  Or it could be because they already have one- the unfortunate fact that it doesn’t work is no excuse to go buy another application.

The Boundaries of Flexibility

So, Excel-based systems can be more flexible, faster to develop and a better return on investment.  They can be fitted to the business, rather than fitting the business to the system.

However, don’t take this flexibility too far.  Any system needs inputs that conform to standards and should be mapped onto real-world processes.  You need to give the system a model that represents the real world.

In a Simple Truth About Capacity Planning, I touched on a foundation of successful planning with capacity constraints.  This hints on requirements for data that link demand in units of product with production load in units of time.

Let’s wrap this up with the other master data items that are the key essentials to providing a planning system with inputs.

Item Master.  This is the beginning.  It is the list of all product and parts in the business.  It will usually have some kind of hierarchy based on groups and families.  Any attribute for SKUs and part numbers is listed here.  Als0 included are parameters such as material lead-times, order policies and stock settings.

Demand.  This usually comes as a set of tables, including open sales orders, demand history, sales and customer forecasts.  Each line item is an actual item line, so we can link the raw demand.  Forecasts are often family level and apply to a period rather than a single transaction.

Bills of Material.  There are two types of BOM: a multilevel BOM that lists all parent and child relationships; and a product BOM that gives all dependencies for products and sales items.  Critically, the BOM should be in a single table.  Many MRP users are familiar with viewing BOMs one product at a time.  Despite the fact that everyone in manufacturing knows what a BOM is, getting a single data table for all Bills of Material is often a challenge.

Open Purchase Orders.  One of two elements to supply for material planning.  A list of open purchase orders combined with inventory and total demand are master data inputs to material availability projections and purchase action calculations.

Inventory.  The other master data to form material supply.  Even for companies who do not have an MRP system, there is usually an accounting system that transacts material and it can be an alternative the source.

Routing.  A list of the process steps that need to be performed to convert material to parts and products.  Process Routings should specify work centers that can perform the work to a standard time and quality.  Each work center can comprise of multiple resources (machine and/or labour).  For businesses that have custom products and engineer-to-order, the Routing needs to come from an order or product configurator.

Calendars.  This is the beginning and end time for production shifts.  This should be the time that is available to do work, set up or changeover but not perform maintenance.  Calendars give capacity to production resources.  Companies often do not have accurate shift calendars maintained in an MRP system, so these can often be managed in Excel.

Resources.  A list of resources that perform work in production.  They are grouped into work centers and it is this level that links to the process routing. Usually, this is either machines or people whichever is the constraint.

Work Orders.  An Excel-Based planning and scheduling system will usually generate a work order signal to manufacturing.  The transaction may still be managed in the host MRP system.  We need some kind of report on completions to calculate the net requirement for production.  In fast, high volume production this may be a back-flush and the information may be in the inventory file.  For longer production cycles, it is usually important to get some kind of WIP report from the production floor.

Nine basic elements that form the reality to your manufacturing.

These are the input tables that we use to drive an operations planning and scheduling system.  They can be database connections or more simply, text files that are generated by the host system and placed in a shared drive.

Excel-based planning and scheduling systems offer plenty of flexibility, can be developed quickly and are very cost effective.  This flexibility is limited to certain key master data that need to accurately represent the business.

These master data elements are the hooks with which an Excel planning system can run the business.  Put the right attention to building this master data set, and the Fast Excel Method can give you powerful planning software to be deployed quickly at a manageable cost.

Visit our free Design Your Own System tool and customize to meet your business requirements.

{ 10 comments… read them below or add one }

Brian Hudson May 18, 2011 at 11:01 pm

Great article, comes with an “it makes you think” feelin and when you see what large organisations are prepared to spend on their ERP’s. Many fail or at most are running ineffectively for various reasons. They way I see it is more the way businesses use software rather than the software itself. Afterall if the goal is to take a journey you can do it in various ways, however the most important thing is to complete the journey.

Keep up this good stuff.

Regards

Kien Leong May 23, 2011 at 7:03 pm

Thank you for your comment.

I agree, ERP is a tool and like any other the way you use it determines the effectiveness.
Your journey analogy is much like difference between efficient and effective. Organisations tend to use ERP to “do things right” without first “doing the right thing”

scor June 13, 2011 at 7:41 pm

ERP implementation can be a headache. In my opinion, ERP is just database engine. It doesn’t do anything much if you don’t buy advanced planning and scheduling module which is very expensive.

Cliff Reddy July 5, 2011 at 5:22 am

Gona try and see if I can get anywhere with this, I am used to ERP systems like SAP,hope I can get this one right

Charles W July 8, 2011 at 1:33 am

This is really helpful, thanks.

Irek Pilawski July 11, 2011 at 1:12 pm

Hello Kien,
I have done MRP system in Excel for 60 products. I have seen MRP system in Excel for about 600 product and the company could not find person to put the data to it without mistakes.
Each phase company need IT solution which is good and safe.

Excel is good for a small company as the same as Access is good for a middle company.

You have many programing languages which give you so many solution. For example you can use CSS, JAVA/PHP and SQL/MySQL, or Access with VB, Excel with VB or SAP and Excel and you have the same effect or not.
Why is or isn’t. Excel and Access are unstable platform at all for me (subjective evaluation). I know, if you have more then 2% fake or isn’t update date. It will destroy all planning.
What is good in Excel. All events are by control only one person but this person should create table and understand how is all data connected between tables. Understand process.
I have taught a person who has taken responsibility for my MRP system in Excel and she couldn’t understand what she should do and how the system has worked.

So that. System MRP/ERP/It system should be:
* Safe – nobody can steal the data

* Flex – can be change when situation outside or inside has changed

* It has 100% correct data

* Can be use by person from street after only one day training. Company can work when a planner move to other company. With Excel employee is almost irreplaceable and more expensive becaouse he have to know VB.

* Enter data and read data are easy.

Below you have list of ERP systems and each ERP system is different in points above. So, it more important they are not so expensive as SAP. SAP isn’t only one system on the world. Why is so popular is different issue?
http://en.wikipedia.org/wiki/List_of_ERP_software_packages

Why a company use MRP or scheduling? Why a company doesn’t produce product when receive order or better produce for stock and wait for client? MRP risk when you did mistake or have the wrong data.ERP can be a black box.

Below is my logistics definition which I has written in 2002. My be it is change your understanding planning and logistics.

The logistics is a science that analyses the efficiency of logistics processes in terms of cost, time, security and inter-relationships associated with their specific market on which the organization operates or the specific type of production carried out by organizations, and also their transformation with changing conditions external or internal .
The Logistics Process – the flow of material (raw material, product information – such as the data, the technical, process or economic documentation), with elements associated with it as databases, storage, transport routes, etc. At the same time maintaining the links between the material and information associated with it during the flow.

As you see from my definition doesn’t exist “no standard process data”. Each kind of production have different standard process data. Each planner or company can develop unique standard process data.

I have recalled MS Project as e.g. process of work management. As softwear you can use better or worse. I have seen something like that.Two planners have done differents phases in MS Project for the same production process/job/order. We don’t think the same and we don’t develop the same solution for the same objective. Most important is the solution is the best for this moment, day, month and year/situation.
In your situation you have line production and nest production. At first phase is good MRP(line production) for second phase is good project and working man-hours (LEGO bricks or MS Project idea for work management).

Tony Rice July 11, 2011 at 11:51 pm

Hi Irek:
Thank you for your very extensive comments, I shall reply to just some of them.
Many people find Excel to be unstable, because of the way they are using it, not because it is Excel. We have a big US multinational running an MRP system we wrote for them in Excel, using our fast Excel Development Method, and that is stable. It has 11 levels in the BOM, they have 1400 products, and MRP explodes out to 300 000 records. No data is captured in the system, it is all imported from their host ERP system.
Each day the user hits one macro button to run the system, but many users view reports from their own “report viewer” workbooks. So, training to run the system is very easy. The complexity comes in tracing demand and inventory through so many levels of the BOM.
You are correct, there are other programming languages that are more robust than Excel. However, this client could not get the group standard APO system working, and group IT would not allow them to implement any other software. They already had Excel.
We also set out believing that “Excel is OK for small companies”, but to our surprise, many of our clients are big fortune 500 clients who have turned to us because their own IT departments have failed them.

Irek Pilawski July 12, 2011 at 12:16 pm

Dear Tony,
I see that we talk about two different things and areas.
We talk about system as MRP or similar software as MS Project or about Reporting system e.g. as BI used as scheduling. You have to import the data from system ERP and develop schedule in order to see a picture of existing situation. This is first difference between reporting software and IT system as MRP and ERP. In MRP and ERP system you can develop reports but you could not develop Relational Database Management Systems in Excel.
I have said that Excel can’t be reporting system and the data base in one for big company.
Second issue if a lot of a working function and connection between tables in spreadsheet without using VB and Macros. In this situation Excel is more and more unstable.
Of course I can have not enough knowledge and I am wrong in this point.

I am recalled to this sentence “The SAP solution to the data problem is simple: Change your business to fit the system. Oh, and budget another million or two for the consultants to help you do this.” My experience showing that you can develop one report in SAP to improve the situation and you haven’t delays with reaction for situation. Of course when the value of delay between event and reaction is known and acceptable. Calmly you can use Excel as schedule.You received a picture of situation for the given moment.
About your MRP system wrote in Excel, with yours fast Excel Development Method. I can’t have opinion about it. Because I have never used it. VB with Excel you can develop MRP. I have done it with import the data from accounting software.
Last point ERP system change work of people and ERP system and company have to adapt mutually. If not you have disaster.

Global Logistics September 17, 2011 at 11:40 pm

+1 like to your valuable article ,this is useful for me and I guess my friend (she respond in planning div.). May I share this to her . . . I think she necessary to read it to improve her skill. Thank you very much

3PL September 17, 2011 at 11:47 pm

Excellent data to the people who respond in planning term. Excel is the main tool for officer, it can do everything!

{ 2 trackbacks }

Previous post:

Next post: