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.
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.
22 thoughts on “The Biggest Production Planning Mistake with Excel”
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!
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.
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
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
congratulations for calling this out. hopefully get better excel from anyone reading this. nicely done
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.
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.
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.
I totally agree with you.It’s good idea how to correct currently human daily operations.
Thank you very much
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.
Hello G. Martinez
I would be happy to give you some pointers and describe how to approach this issue.
Why don’t you send me the spreadsheet, I can take a look and let you know how to approach this? Email me at [email protected].
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.
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!
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.
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
Could you please mail me an exel planning Sheet
I’m looking one for production line
Using MS Access is a better idea than anything mentioned here.
i disagree Mohan C, i dont think MS Access is a good tool for production scheduling.
It’s great to learn about the natural mistake: combined data, calculation and report in single sheet and your good principles. Thank you so much!
Is it possible to add “Firm zones” or “Frozen zones” for some materials when you are planning in excel?
Hi Wendy, I believe you asked this question in a recent webinar. I will answer here as well for the benefit of others. Yes, it is possible to add frozen zones for both product demand and materials. We would need to carefully define what is frozen and exactly how this is done. This term can be used to mean different things. But usually it applies to a production schedule that is frozen some time out before it is due to start. And so the system would have to record all schedules created in earlier runs. By default our systems would usually work with the latest information. Frozen zones do risk shortage/overproduction if demand or supply changes inside the zone. But I understand why they might be requested.
This is great, time for me to hunt through my workbooks and do some reconfiguration! Thanks again for the insightful articles!