The Biggest Production Planning Mistake with Excel

by Kien Leong

Post image for 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 DashboardsExcelcharts.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.

{ 17 comments… read them below or add one }

Emilio November 9, 2010 at 2:28 am

Can you provide more detail about this?

I will send you the current planning book, it looks something like your picture. Any suggestions or ‘makeover’ would be great!

Kien Leong November 9, 2010 at 3:24 am

@Emilio:

Sure, send it through to kien (at) production-scheduling.com. We can’t promise to ‘makeover’ everything we receive, but at least we can give you some pointers. Often we can fix something up using the Development Template that you can use to save hours a day.

John Broughton November 9, 2010 at 4:45 am

This gets me thinking about all of our Excel tools and reports. Time for an audit to see which one passes and which ones fail. Thanks for the insight

Fran Tanquera November 9, 2010 at 3:39 pm

Great idea, you have me thinking on this now. Software architectures, but DIY in Excel. We need flexible development and custom to the operation.

Some examples before and after be very helpful to get a good sense. Can you offer tutorial? Thanks

Fran.

Gemma Nes November 9, 2010 at 4:47 pm

congratulations for calling this out. hopefully get better excel from anyone reading this. nicely done

Darci December 3, 2010 at 2:23 am

One month after reading this article, I could realize the real benefits of importing data from txt files. Combing this great idea with separation of data, calculation and report, I’m saving precious time while doing planning in my worksheet (that makes Production Scheduling, Master Plan and MRP).

Previously, I used to have links to other worksheets from Marketing and Production Dept., catching data with a lot of usage of SUMIF live formulas. Every time I did one simple change and pressed enter, it was recalculating all the live formulas (more then 30 thousand), generating the Gantt Graph, preparing the Master Plan and calculating the MRP. As Mr. Kien said, we like to see all the things in the same place, but this is the most stupid idea. It was taking from me about 30 seconds for every enter.

I removed all the SUMIF formulas from the data/calculation sheet. After strugling a lot, I could replace them to PivotTables and a bunch of macros (VBA) for feeding the calculation. It took a lot of time (almost 1 month development), but now it’s calculating all the things in about 10 seconds, but only when I send the command (instead of calculating every time after pressing enter).

I seriously recommend you to learn some VBA and apply it together with the tips that Mr. Kien gives here.

Thank you for this great website. I wish I could have time to translate its entire content to my language and share with other brazilians.

John Ingram January 1, 2011 at 4:38 pm

funny. I have been there in that “**@!#%&$!!” moment. So many bad sheets out there, so little time to fix them or even read.

I know this is a goodpractice and it sounds a good idea. Thing is, how to get our current excel tools in this form? It sounds great but I think there is a lot of time investment.

Kien Leong January 2, 2011 at 6:13 am

@Jon
True comment. There can be a lot of time to rectify. The best place to start is with newly created tools/workbooks – do it right first time. Then you can move onto legacy files.

Many times, we are developing new Excel planning tools as part of an operational improvement initiative. So there is a defined business benefit and acknowledged ROI for the results. When you look at the improvements possible in delivery performance and stock turns, the project overhead is usually a rounding error in comparison.

Having said that, there are big benefits that flow from good habits that you may never see coming. It is always worth doing things right. This is the real power of operational excellence.

cheng sally February 19, 2011 at 10:15 am

I totally agree with you.It’s good idea how to correct currently human daily operations.
Thank you very much

G. Martinez March 12, 2011 at 6:58 pm

I have an excel spreadsheet with multiple worksheets that my boss gave me. He wants me to fix it because it freezes up due to extensive data. He would like me to use Access to hold the data, but wants to print reports for the schedulers automatically. He did not explain anything, he just sent me the file, everytime I ask him to go over it he blows me off. I would like to do this for myself and to show him that I can do it, but without explanation I really don’t know where to start. Can you help me?
Signed, A woman who wants to show them that I can do it.

Kien Leong March 14, 2011 at 3:10 am
G. Martinez March 16, 2011 at 4:45 am

Kien,
You are the best, I just got the password today to unlock the spreadsheet, that should help, I couldn’t even see most of the spreadsheet.
Thank you,
G.M.

Willian Goetze September 2, 2011 at 5:48 pm

Someone I work with visits your site regularly and recommended it to me to read as well. The writing style is superior and the content is top-notch. Thanks for the insight you provide the readers!

Randy Butrum October 31, 2011 at 7:47 am

Thanks so much for the tip about not combining data, calculations, & reports on the same worksheet. I have been doing this with my daily & monthly reports. Now I have separated them & I can’t believe how much faster the reports run now. Thanks again.

mattgenton January 2, 2012 at 9:25 pm

hi to all at production-scheduling.com i thought i had sent this newyears eve but it didnt send so i have sent it again all things good for the new year to every one
– matt-gent

Shrikant R April 18, 2012 at 9:54 pm

Could you please mail me an exel planning Sheet
I’m looking one for production line

Mohan c July 17, 2012 at 3:17 am

Using MS Access is a better idea than anything mentioned here.

{ 18 trackbacks }

Previous post:

Next post: