Why expose an aged, high risk and vulnerable Excel to Covid19 - A reminder of good healthcare practice!
RECENT ARTICLES
Why expose an aged, high risk and vulnerable Excel to Covid19 - A reminder of good healthcare practice!
October 2020
Reports that coronavirus cases have been understated due to “Excel errors” misses the point that it is not Excel that is at fault
The recent reports that coronavirus cases have been understated due to “Excel errors” misses the point that it is not Excel that is at fault, even if it was old and vulnerable, but the people and processes surrounding its use but the organisations failing to take spreadsheet risk seriously.
It is therefore timely to remind ourselves how to mitigate these risks:
Is a spreadsheet required? The answer is only yes if core systems are not able to generate the outputs that the spreadsheet is being built for.
If it is required then it should be subject to the same control framework that covers the core systems with users who are properly trained in their use.
Don’t just start building a spreadsheet, a third of the time should be spent on the specification and design, a third on building and a third on testing.
Put every variable on a variables sheet, all of them! It may be handy to have a 10% overhead variable just next to the results table so it can be changed quickly but don’t, someone else will come along and miss it.
And plan for that ‘someone else’ it’s not ‘your’ model forever, someone else needs to know how it works, including you when you don’t use it for six months! Create a simple flowchart in the front sheet of the model and produce an operating manual – and allow plenty of time for this important activity.
Of course, the flowchart and operating manual will be easy to do because your spreadsheet will obey the principle of having inputs, calculations and results on separate worksheets. To be clear on the last point – don’t perform any calculations on your input or the results sheets, and it goes without saying that your results sheets are just that, nothing else.
Don’t have any hard-coded values in your formulae – even the months in a year. Admittedly this is unlikely to change, but the standard VAT rate didn’t change for years. When it does, it is easier to change the variable once rather than changing it (or forgetting to) in every formula that involves VAT.
VLOOKUP and HLOOKUP are difficult to audit and you can be caught out when you insert a column or row in the source tables at a later date. If you do use them, then use the COLUMN or ROW functions to create a dynamic link to the source data or, better still, use a combination of INDEX and MATCH functions.
Keep macros to a minimum, (they are also difficult to audit), keep them simple and ensure they are documented including within the text of the macro itself, so that future users know what each part of the macro is doing.
Make your timescale dynamic. Have the start period as a variable and have the column headings as formulae. And have the start period in the same column on every spreadsheet.
Have a worksheet dedicated to containing all the checks within the workbook. Does the balance sheet balance? Does a table of results both cast and cross-cast? Create a ‘mastercheck’ that returns, say, “All Checks OK” when indeed this condition is met by all the other checks. This can be displayed at the top of each worksheet so that the user is immediately alerted if this is not the case.
·Talking of the top of each worksheet. This should contain useful data for the user, for example, the name of the workbook, the version, the current year or period end.
And don’t forget that there may still be a need to print out worksheets so ensure that they are formatted for this event with appropriate headers and footers.
Going back to the coronavirus example, this arose due to the limitations in the numbers of rows and columns in worksheets in Excel 1997-2003 .xls files. It is not necessary, or maybe affordable, to have the latest version of Excel but be aware of such limitations and, again, ensure that controls are in place to check, say, that the total number of records per the source data agrees with what has been loaded into Excel.
Blue-Plate consultants can advise on, or build, spreadsheets that conform with spreadsheet modelling best practice. There are also guides that are freely available such as the Institute of Chartered Accountants’ “Twenty Principles of Good Spreadsheet Practice” and their “Financial Modelling Code”.
Whatever you do, obey the rules and ensure that controls are in place when using ‘mission-critical’ spreadsheets to avoid becoming the next “Excel errors” story.
Stephen Lockwood
Consulting Director
Blue-plate Consulting Ltd