Make your report writing magnetic! #6

I have just been revising my working guide on How to write a report that people read and leads to a "Yes". So, I thought it might be useful to share some of the content and encourage one or two of you to access this implementation guide.

Rule of 100

Spreadsheets have no place in forecasting, budgeting and reporting routines. A spreadsheet is a great tool for creating static graphs for a report or designing and testing a reporting template; what it is not, and should never have been, is a building block for your organisation’s reporting, forecasting or planning systems.

If you can build a forecasting, reporting or planning model in a spreadsheet and keep it under 100 rows, you can do so without much risk. But pass this threshold and you expose yourself, your team and the organisation to the risk of making decisions on the wrong numbers.

Forecasting requires a robust tool, not a spreadsheet built by an innovative accountant that no one can now understand. I always ask in my workshops: ‘Who has a massive spreadsheet written by someone else and prays before using it?’ You can see the pain in the instant response.

Often, the main hurdle is our reluctance to separate ourselves from our love affair with spreadsheets. It has been a long and easy relationship, albeit with painful interludes that have limited our - and our fellow teammates' - performance.

9 of the most common problems with Excel spreadsheets

Be very wary if you are utilising data from large spreadsheets. Here are 9 of the most common problems in spreadsheets:

1. Broken links or formulas - An individual might add or eliminate a row or column so that, when a group of spreadsheets is rolled up, the master spreadsheet is taking the wrong number from the one that was modified.
2. Consolidation errors - Often, a spreadsheet will lock up or show a screen full of “REF”, “REF”, and “REF” errors because it was not designed to be a tool for handling a rollup of dozens of different worksheets.
3. Input of the wrong numbers - Entering the wrong number can happen in any process, but spreadsheet-based systems often require rekeying of information, which can produce data inconsistencies. A spreadsheet might use a look-up table that is out of date, or an entry might have been inadvertently or mistakenly overwritten.
4. Incorrect formulas - A subtotal might omit one or more rows, columns or both. An individual might overwrite a formula because they believe theirs is more accurate. Or someone might use an outdated spreadsheet. Or allocation models might not allocate 100 per cent of the costs. Allocation methods might be inconsistent.
5. No proper version control - Using an outdated version of a spreadsheet is very common.
6. Lack of robustness - Confidence in the number a spreadsheet forecast churns out is not assured. In large spreadsheets, you will not have the time to check all the formulas because they can reside in any cell of the spreadsheet.
7. Inability to quickly accommodate changes to assumptions - What would you do if your CEO asked, “If we stopped production of computer printers, what would be the financial impact? I need the answer at the close of play today.” Your spreadsheets are not able to provide that quick answer.
8. Designed by non-IT staff - Most staff have not been trained in system documentation and quality assurance, which you expect from a trained IT developer. Most people know that whoever built the spreadsheet was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance of understanding.
9. Seldom one version of the truth - Many people in a business can use spreadsheets to create their forecasts at a ridiculous level of detail. This can lead, as a friend once said to me, “To the march of a million spreadsheets.”
10. Thinking that Excel is a much cheaper option than purchasing a robust piece of software - I have seen a spreadsheet that included every single mobile phone that had been sold in a country. The sales staff person who was dealing with this monster simply said: "I inherited it". These 'Supernova' spreadsheets drag the organisation into a black hole. With the data entry and maintenance being kept at such a low level in the staff hierarchy, the C-suite has no idea of the extent of the problem, nor the significant cost to the organisation from error-prone data and the resulting poor decision-making.

The full implementation guide includes a 110-page PDF white paper plus E-templates.