Many businesses are facing funding gaps in the current climate, with economic activity severely impacted by measures to contain the coronavirus outbreak. While significant schemes to support businesses with loans have been made available, smaller organisations may find that they need to provide supporting documentation that they may not have had to before – such as P&L or cash flow forecasts.
Such forecasts are often prepared in Excel – but spreadsheets are notoriously error-prone. Here’s a guide to what ICAEW has to offer accountants on good practice guidance for spreadsheet development.
Our fundamental guidance is the straightforward, high-level 20 Principles. These are high-level and applicable by any spreadsheet user, whether expert or novice, and were designed by a panel of spreadsheet trainers, financial modellers, and risk experts to help reduce spreadsheet risk and error.
You can read the full list at www.icaew.com/excel20principles, but for a taster, let’s look at my personal favourite:
Be consistent in structure
A spreadsheet of any magnitude can be daunting to review, with perhaps thousands of formulas. But if those formulas are clearly organised into consistent blocks which all perform the same calculation, then things become that much simpler.Consistency also reduces the chances of errors by making it easier to update formulas during development.
Financial Modelling Code
Released in late 2018, the Code takes the 20 Principles and applies them specifically to the domain of financial modelling. Based on input from a panel of financial modelling experts, it takes a view on what the goals of any good financial model should be, while still leaving room for individual preference on how to achieve those goals. The Code has wide cross-industry support from financial modelling outfits, accounting practices, and industry players including Microsoft.
The Code (and the list of supporting organisations) are available at www.icaew.com/financialmodelling, but once again let’s take a look at a sample section:
Don’t hide things
All aspects of a model should be visible and easily accessible. Hidden rows are easily missed, can be confusing and are easy to change or delete inadvertently. An exception to this rule is the hiding of empty columns to the right and below the used range, which can be done to aid workbook navigation.
Hidden rows don’t always work as expected when copying and pasting ranges, and easily lead to mistakes being made down the line, as well as making the model harder to interpret and review. The “grouping” functionality is a good replacement that can tuck away excessive detail while making it clear to the user where the hiding has been done and how to restore the hidden detail.
ICAEW’s Financial Modelling Committee have also recently begun a series of blogs exploring the Code in more detail; you can read the first one here.
If you’d like to learn more about how to use Excel efficiently and effectively, consider joining our Excel Community, where we share blogs, webinars, and a series of online training modules.
For further guidance and support for businesses, please visit the ICAEW designated coronavirus (COVID-19) hub page.