Get an Instant Rating for your Excel Planning

If you can’t measure it, you can’t improve it.

Here is a quick way to measure and score your use of Excel in planning.  A tool for download can give you an instant rating.

Most businesses use spreadsheets for planning.  Excel is often the preferred choice for planning the business, financials, material and production.

The quality of a spreadsheet in planning has a huge impact on productivity and the accuracy of the results.  The way that a planning tool is designed and built will determine how much work goes into managing it and how many errors are likely to occur.

The Production-Scheduling.com website is a resource to help with best practices in using Excel for planning and scheduling applications.  You get the best results if you follow the Fast Excel Development Method.  We teach this with our Fast Excel e-Learning Program and automate it with the Development Template.

How Well Do You Use Excel?

However, it is not always practical to start from scratch.  I can help you avoid the biggest mistake in production planning.  Or keep your virtue in the face of the seven deadly spreadsheet sins.

What about your legacy planning workbooks?  Before you go and make sweeping changes to your current planning methods, it might be  a good idea to get a quick rating.  Find out how well you are doing right now.

Here is a very quick measurement that can quickly rate your spreadsheets.  The measurement rating is simple:

Number of Formulas / Number of Used Columns

The best planning workbooks will have a ratio close to 1.  Anywhere between 0.5 and 1.5 will indicate a world-class Excel planning tool.

Many workbooks that we have seen will have a formula:used columns ratio of 10, 100 or even more.

Check your own spreadsheets using this formula.  You won’t have to count them all manually: there is a tool that you can download here that will give you an instant results.

 

 To use this tool, simply type or paste in the file path to the workbooks that you want to analyse.  Of course, over-write the examples that you see in the download.  Then enter the name of the file that you want to analyse.  Repeat for all workbooks that you wish to compare.  And then hit “Analyse”.

Why Is The Ratio of Formulas to Used Columns Important?

Any workbook that is used as a business tool should be connected to system data.  This means that you are using the best source of facts to support your planning decisions.

Data that comes from a business system will take the form of tables.  It is quite easy to bring tables of data into spreadsheets.  You don’t need to be a database whizz- just export from the system and import using text files.

Once you have the source data in tables, it is a good idea to keep using tables for calculation.  In a table, every record in a column is the same kind of data.  Each column has only dates, or only item codes, or quantities. 

Most calculations are performed using other columns in the same row.  So it is easy to create a formula in one row that can be pasted to thousands of other rows.  Therefore, each column only needs one formula.

Live Formulas Versus Copy Paste-Values

So far that makes sense.  One formula, one column. Set it once at the top and paste it down the whole column.

Yet, if I want to calculate over thousands of rows, surely I will end up with thousands of copies of the same formula in each column?

Not if you want a fast, scalable and error-free spreadsheet tool.  Every time you copy a formula, you are creating an opportunity for error.  All that it takes is one wrong keystroke and the copied formula is now wrong but difficult to trace.

And it gets worse.  Most modern manufacturing and supply chain applications need to handle thousands of rows of data.  Excel can cope with up to 65,536 rows (2003 version) or 1,048,576 rows (2007+). 

But if you paste live formula to even a fraction of these rows, your workbook is now very bloated, slow and prone to crash.

the solution is to paste down the formula, and then copy and paste-special with values.  This means that you only have live formula for a fraction of a second and then the results are pasted into each cell.

Excel can easily handle tens of thousands of rows in each worksheet if they are static values.  If you pasted them from a formula, you know the static values represent the right calculation result.  If you want to refresh the tool, then you simply run the process again.

This technique automates very well using macros and Dynamic Named Ranges.  If you have not used Named Ranges before, then your Planning spreadsheet likely has a formula ratio of 10 or more.

Named Ranges allow you to copy paste a formula into a perfectly sized column.  The data that you calculate is probably changing.  The column height that you paste in the formula also needs to be changing to fit.  Dynamic Named Ranges use the OFFSET formula to resize to fit the table of data.

Our e-Learning System will show you how to use Dynamic Named Ranges in detail.  The Fast Excel Development Template automates this process, is free and comes with tutorial videos.

Set Your Formula in a Single Row

All planning tools that we use have formulas on Row 8.  All the data are in tables with the headings in Row 10.  This makes it easy to automate the formula copy-paste, copy-special, paste-values process.

Once you have become familiar with this system, you will find that it is the foundation to all Excel development.

Before and After Comparison

Here is a chart that clearly shows how this ratio can vary.

The Orange dots show the number of formulas and rows for a selection of “Before” workbooks that our clients have given us to re-engineer.

The Green dots show the same for the “After” workbooks that have had the Fast Excel treatment.

First, notice that there are many more “Before” workbook files than “After”.  This shows how companies tend to have their planning scattered across multiple files.

Next, see how the ratio of formulas to columns varies wildly in the “Before” picture, but is much more controlled in the “After” versions.

Remember that these are both log scales, so the workbooks at the top edge have one thousand times the number formulas than the ones in the center.

Imagine how slow a workbook would be that had a million live formulas in it.  Think how difficult it would be to audit that workbook and check for mistakes.

Now, imagine how fast it would run if we cut those formula by a factor of a thousand.

Use this download tool and the “formula to used-column” ratio to measure your own Excel Planning tools.

Let us know how you get on in the comments.  Pass on your score and I can give you some tips on how you can get started on the road to world-class planning with Excel.