Production Planning with Excel - Is Your Spreadsheet Error Free?

Posted by Kerstin Sieckmann on Apr 30, 2018 4:34:00 PM

If you enter the term „excel horror stories“ into your search engine you’ll get a long list of stories about financial disasters, wrong scientific insights or organizational nightmares being caused by the spreadsheet software. TransAlta lost 24 million due to a cut and paste error, designations of genes like SEPT2 get changed unnoticed into data by Excel, the NASA had wrong measure data in their Excel tables with which they keep on working, and so forth. Are you sure that there is no mistake hidden in your Excel sheets with which you run your company or plan your production?

My favorite story is about the organizational nightmare happening during the Olympic Games in London 2012 when a staff member entered 20,000 instead of 10,000 into the ticket system. The synchronized swimming competition was completely overbooked because of that and the organizers had their hands full to book the ticket owners to other competitions. The seats configuration brought the error to light just in time.

 

A risk already known for a long time

Ray Panko from the University of Hawaii has investigated the phenomenon of errors in Excel sheets already in 1998 and warned in his publication "What We Know About Spreadsheet Errors" against blindly relying on the spreadsheets. Nothing has been learned from that apparently because, as is shown by the British study of F1F9 "Nearly 1 in 5 large businesses have suffered direct financial loss due to poor spreadsheets."

 

Everybody can work with Excel 

It’s true, Excel is on everybody’s computer, it is easy to use and even complex calculation tasks can be carried out by less experienced Excel users. Nevertheless, Excel is a software that is operated by human beings who, naturally, make mistakes. One copy/paste in the wrong row or a reference to a wrong cell or simply typing the wrong number – and you’ll get wrong results.

 

Test your Excel error risk

Kenneth R. Baker, Lynn Foster-Johnson, Barry Lawson, and Stephen G. Powell, team members of the Spreadsheet Engineering Research Project at Tuck School of Business, Dartmouth College have developed a free Spreadsheet Risk Diagnostic Tool (p. 4 in the paper "Spreadsheet Risk, Awareness, and Control") with which you can calculate the risk for your company. It is a simple, short questionnaire, actually, but working through it you’ll inevitably start asking yourself how likely it is that you are using error-prone spreadsheets yourself. And realizing possible errors is the first step to avoiding them.

Besides, the document offers some best practices about how to reduce the risk of errors in Excel spreadsheets.

 

Awareness of spreadsheet risks

The significance of the error rate of spreadsheets is shown by the formation of a European Spreadsheet Risks Interest Group. The website of this organization does not only list “horror stories”, as they are mentioned above but also offers best practices, training videos etc.

 

Visualization is a way to bring errors to light

What brings me to the topic “Excel horror stories” at all?

Some of our customers use Excel for production planning. They contact us for visualizing their data by planning boards to make their planning more efficient and their delivery date commitments more reliable. When their testing data get uploaded to the new planning table there will sometimes be unexpected reactions like: “This isn’t possible”, “There’s still a bug in there”.

But quite quickly it becomes clear that it was not the visualization that was incorrect but the data. It turns out, for instance, that up to now nobody has noticed that some operations didn’t show a duration. Or people find out that an intermediate operation has been ignored. There are different reasons why the visualized data don’t comply with the actual production process. Recognizing discrepancies in Excel spreadsheets is difficult but by visualizing them in their context (resources, deadlines, orders, capacities, durations etc.) they become clear immediately

 

Excel for Production Planning

If you are aware of the risks, using Excel for your planning, and you implement some control mechanisms to reduce the risks of errors in your data, the software can be an excellent tool for your production planning and scheduling. And indeed, Excel is very popular especially for SMB manufacturers, because, as said above: Everybody can work with Excel. However there are limitations for production scheduling and these two blog posts delve deeper into this subject, so I listed them here:

4 Signs That Your Production Schedule With Excel No Longer Scales

Operational Production Scheduling With Excel: Chances And Limitations

Customer Case: Improved Delivery Times With NAV By Visual Scheduling

 

Want to see an alternative to your Excel- or whiteboard-based manufacturing planning?

As a software company, focused on Gantt chart visualization, we are aware of the problems, small production companies are facing in their daily planning. So we developed an easy, ready-to-use manufacturing planning software for job shops and small make-to-order companies. Take 60 seconds to learn what our just plan it is all about.

 

  

Topics: Business Central Production Scheduling, Visual Scheduling Fundamentals