Financial Forecasting, Analysis and Modelling. Michael Samonas

design to divide your model into sections such as: Documentation, Data, Assumptions/Inputs, Workings, and Outputs, as we have already mentioned above. The first sheet of the model should serve as a user guide, step-by-step documentation as to how the model works. It may seem time-consuming, but it greatly increases the productivity of the whole team, and frees up time when, at a later stage, as you try to remember how you built the model, you will need to revise it.

      ○ Always avoid hard-coding numbers into formulae and try to keep your formulae as short as possible. Always split complex formulae into multiple, simple steps.

      ○ Use formatting for description rather than decoration. For example use different colour text for assumptions compared with formulae and output results. Use consistency in colours (e.g. blue for inputs) to highlight cells where data must be entered. When using more colours do not forget to add a legend explaining what each colour means. Always keep in mind the KISS principle: Keep It Simple Stupid.

      ○ Present the data as clearly and in as uncluttered a form as possible. Always separate inputs into at least 3 columns, one with the particular inputs, the second describing the Units of Measure (UOM) of each input, and the last one with the values of each input.

      ○ If you distribute your model to others, do protect it to prevent inadvertent changes. By default, anyone can change anything on any spreadsheet. To prevent unauthorized changes you should either protect your worksheets or your workbook as a whole. As a workbook owner you should always keep one copy of your original model in a directory that only you can change.

      ○ Designate ownership and track who is changing what. If you decide not to prevent changes in your model then try at least to monitor them. To do so you can simply make use of the Track Changes tool in Excel 2010 and 2013. Microsoft's Track Changes function allows revisions to be made to a document and keeps a complete record of all changes made. Track Changes can be invaluable if you have created a business document and you distribute it to others to work with it.

      ○ Design your worksheets to read from left to right and from top to bottom, like a European book. This is a common recommendation in the literature. Perhaps it is a remnant of paper-based documents, but it seems that following such a design does make spreadsheets easier to navigate and understand.

      ○ Finally when incorporating charts in your model, always label the axes and use titles.

      Although this is not a book on how to build good spreadsheets, the interested reader could visit the site of The European Spreadsheet Risks Interest Group – EuSpRIG¹⁰ where they can find plenty of information and research papers about spreadsheet best practices. Perhaps one of the most important papers on this site is that of IBM – Spreadsheet Modelling Best Practice. This is a 100-page guide on how to develop high quality spreadsheets. This guide is of interest to anyone who relies on decisions from spreadsheet models. The techniques described include areas such as ensuring that the objectives of the model are clear, defining the calculations, good design practice, testing and understanding, and presenting the results from spreadsheet models.

      1.3.4 Step 4: Checking the Model's Output

      The model is not ready until we ensure that it produces the results it was designed to. Errors in the data or formulae could be costly, even devastating. The received wisdom is that about 5 % of all formulae in spreadsheet models contain errors, and this rate is consistent across spreadsheets. Errors may occur at the functionality level, the logic level, the design level, etc. A simple way to check our model is to introduce checks directly in the forms. Some of these checks will be very generic and will therefore be included early. For example in case of a balance sheet the obvious check is the sum of assets vs the sum of equity and liabilities. In case of a cash flow statement the cash and cash equivalents at the beginning of a period should be equal to the cash and cash equivalents at the end of the previous period. Moreover the cash and cash equivalents at the end of a period should be the same as the cash account of the balance sheet of that period. Other checks will be more model-specific, and the need for them will not be obvious at the beginning – therefore, new checks will be included throughout the model building phase.

      As a minimum, we can test our model subsequent to the building phase by playing with the inputs and see if this produces reasonable results (reasonableness tests). For example, if a formula is supposed to add a set of values then we can test it by providing known data, and checking that the answer is the expected one. Moreover we can change each of the input parameters and see if the output results change accordingly.

      There is free textbook on Wikibooks (Financial Modelling in Microsoft Excel/Testing)¹¹ which provides a detailed checklist of the best practices on how to error-check a spreadsheet. From checking its functionality, i.e. whether the model does what it is supposed to do, to checking the business logic in the model. From identifying the risk factors, i.e. what could go wrong, and how serious that could be, to checking the inputs of the model and its calculations, i.e. examining all formula cells, where they read from, and where they feed their result to.

      Of course there are error-checking tools that can make our life easier. Excel 2003 and later versions have a built-in error-checking tool. For example in Excel 2003 under the Tools menu, just select Error Checking. If the Excel Error Checking tool detects a possible error, it pops up a dialog box. This box gives several choices, which range from ignoring the warning to taking action. The more updated the version of Excel the better the error-checking tools that have been incorporated. However, the error-checking tools built into Excel are simple but limited. A number of more sophisticated error-checking products are available on the market.

      1.4 EXCEL AS A TOOL OF MODELLING: CAPABILITIES AND LIMITATIONS

      Microsoft Excel is the ideal tool for the kind of modelling covered in this book. Its immense popularity throughout the business world today for processing quantitative data and developing analytical solutions is widely acknowledged. One could argue that it is one of the most powerful and most important software applications of all time. It would not be an exaggeration to claim that if civilization were about to vanish and somebody could pass future generations a single wonder of our time, this should be Excel. Excel is everywhere you look in the business world – especially in areas where people are handling numbers, such as marketing, business development, sales, and finance. In a 2011 study, Weiser Mazars found that Excel was the favorite tool of insurance finance and accounting. Teams relied heavily on Excel to compensate for shortcomings in the information flow. Most leaders they questioned did not have plans to change this process. Moreover, they found that 87 % of companies rely on Excel in their planning, budgeting, and other performance management processes.¹² In a different survey performed by gtnews during 2014 in relation to the technology that Financial Planning and Analysis (FP&A) professionals are using, almost three-quarters (73 %) of those surveyed indicated that Excel is still the primary tool they use in more than half of all their analytical work, even if they also have a standalone system.¹³

      But while Excel is reasonably robust, the spreadsheets that people create with Excel are incredibly fragile. For starters, there is no way to trace where your data came from, there is no audit trail (so you can overtype numbers and not realize it), and there is no easy way to test spreadsheets. The biggest problem is that anyone can create Excel spreadsheets – badly. Because it's so easy to use, the creation of even important spreadsheets is not restricted to people who understand programming and do it in a methodical, well-documented way. That is why one should be aware of Excel's limitations. Tim Worstall, a Forbes contributor, proclaimed recently in a Forbes article that “Microsoft's Excel Might Be The Most Dangerous Software on the Planet” after JP Morgan's loss of several billion dollars due to a spreadsheet error.¹⁴ Moreover, a CFO.com article states that the error rates in spreadsheets are huge; Excel will dutifully average the wrong data right down the line and there's no protection against that.¹⁵ Finally, an article entitled “Beware of the Spreadsheet” appeared in the international edition of Accounting and Business, in which David Parmenter argued that the use of large spreadsheets for reporting, forecasting, and planning should be abandoned because of their susceptibility to errors.¹⁶ Excel is a tool and as such its use should be underpinned by proper controls as we mentioned earlier