The Reality of Your Manufacturing World

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.