Читать книгу Financial Forecasting, Analysis and Modelling - Michael Samonas - Страница 14

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.