The Biggest Production Planning Mistake with Excel

Excel is the most widely used software for production planning. Production planning is one of the most common applications for Excel in manufacturing.  Yet, there is one common mistake that people make when production planning in Excel.  Avoid doing this one thing and you will save yourself hundreds of hours of unnecessary and repetitive work with production planning data.  The mistake:  Combining data, calculation and reporting on a single sheet.

You can see examples of this problem everywhere. Imagine a worksheet with part numbers in rows going down the left side and dates going across the top. In the middle are numbers that represent production volumes for each week or month. Some of those numbers are static values that have been manually entered or cut-pasted. Others represent calculations and formulas. This single sheet is used to store data, calculate and present a report.

The reason we see this mistake everywhere is that it is a very natural one to make.  When we imagine a production plan, we visualise it in two dimensions.  If we were to draw a picture of what we want to see, it would have products going in one dimension and time in the other.  It could be a scheduling Gantt Chart or simply a production planing report, humans like to organise data in a matrix.  We want to look across time from left to right and see how things will change.

The problem with this arrangement is that it makes calculation awkward and troublesome.  Computing is built on the concept of data normalisation.  Databases are formed from lists with few columns (fields) and many rows (records).  Any kind of software or custom-designed production planning system will work best when calculations are made to a long column of data. If you try to store and manipulate data in a matrix form, the ensuing calculations tend to be either very complex, or more likely static and manual. Any change to the matrix and someone has to re-wire the whole workbook, changing formulas and copying data from one sheet to another.  Data are better kept in tables and presented in matrices.  If you need a tool to convert a matrix to a normal tables you can download this Matrix to Table Converter.

The Fast Excel Development Method is a powerful technique for spreadsheet-based production planning.  You can learn about the principles here, or take a deep dive with our Fast Excel e-Learning Program.  However, even if you choose to ignore all of the other principles, you will get huge amounts of value from applying this one idea.

When I think back to my early days of building production planning and scheduling systems, this was definitely an “A-Ha!” moment.  We all have those moments of total clarity when everything that we have been learning suddenly makes sense.  This one was mine.  I have spent more years of struggling with spreadsheets than I care to remember and after learning this principle I literally saved hundreds of hours in a single year.

So, on to the detail.  Exactly how can we Separate Data, Calculation and Reporting?  Well, the first step is to give a single role to each worksheet.

A Data sheet in the Excel workbook is purely there to manage data.  Preferably, the data comes into the sheet via a text file import or ODBC connection with another database.  Excel is not the right place to store data and it is likely that multiple users are reading and writing production planning data.  Keep the data in a database and pull it into Excel when you need it.  A Data sheet in an Excel-based production planning and scheduling system is there to interface between the host system or text file store.  read more about data integration here.

Calculations are typically applied in a purpose-built calculation sheet.  This sheet pulls the data from other sheets, performs some calculations and then presents the results to be used in another calculation or report.  These sheets need no formatting or sub-totals and can be designed purely for fast, accurate calculations.  Any change in the input data is automatically updated; no need for all that manual cut/copy-paste.  The calculations themselves are defined in a single row of formula.  They get pasted down to every row below and then replaced by values.

Reporting is done for the sole purpose of presenting completed calculations.  There should be no calculations and certainly no data stored in a report.  Just ask yourself: “What would happen if this report file or sheet were deleted?”   If the answer is any of the following: “lost information”, “Disaster” or  “**@!#%&$!!” then you know you have a problem in mixing data and calculations with your production planning report.

Fortunately, Excel makes creating reports and charts very easy.  PivotTables can transform those normal tables that are difficult to read into matrices and reports, nicely presented and formatted.  Refreshing the data refreshes the report with no need for manual updating.  If you are still struggling with the horrendous charting engine or the somewhat counter-intuitive PivotTables wizard in Excel 2003, then it might be time to upgrade to Excel 2010.  If you know your way around a PivotTable and can shape up a reasonable-looking chart, then Excel 2003 is all that you need to put this plan into action.  Some good learning resources are listed below, many of them are free.

For a comprehensive example of this principle in action, you can study the PS-Cycles Production Scheduling System.  The Fast Excel Development Template provides an easy way to perform data inport, calculations and reporting whilst using Dynamic Named Ranges and much handy automation.  These resources are all free, although you may need to register or log-in so we can send you more free and valuable stuff.

Leave us a comment and let us know how you get on in rebuilding your production planning tools.  Maybe you already apply this principle and want to know more advanced techniques to really leverage what you already do.  We do production planning tool makeovers from time to time, so if you leave comments and send us an example, maybe we can send you back something that you can use. I look forward to hearing from you.

Learning Resources

Fast Excel e-Learning Program – Our 17 Modules to teach you the Fast Excel Method step by step.

Fast Excel Development Template – A free download and e-learning modules to kickstart the development process.

PivotTables and Excel Fundamentals– Chandoo.org has some great resources for Excel devlivered with wit and humour.  OZGrids and MrExcel is a good source for tutorials and the forums are a priceless source of help.

Excel Charts and Dashboards – Excelcharts.com has a terrific tutorial on creating dynamic charts and information dashboards in Excel.  Jon Peltier provides a number of ways to get good results from the Excel charting engine.