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

Designing and building the financial model is the next phase of the process. The specification phase (Step 2) should lay out the structure of the model in detail. In this step we first identify the outputs of the model. It is good practice to present the output of the model on a separate sheet. This output sheet is a combination of model inputs and formulae and should read directly from the workings sheet of the model. There may be more than one output sheet in case the resolution of step 1 requires the handling of uncertainty and creating sensitivity analyses. Moreover, the control panel described in the specification part of the modelling process, if any, is part of the output of the model. Next we build the assumptions sheet that forms the inputs to our model. If our model needs past data to build on, i.e. historic financial statements, we collect them and adjust them to the right level of detail. Depending on the problem we have to solve we will not need all of the income statement, balance sheet, and cash flow statement accounts, and thus some will need to be grouped together. Finally we build the workings or calculation sheets and fill their cells with formulae. Thus, the sheets where the calculations are taking place should always be separate from both the input and the output sheets. Also no hard-coded values should be typed directly into the calculations of the workings sheet. In case the purpose of the model is to forecast future financial statements, all the relevant key drivers of the historic financial statements are calculated and forecast into the future. The forecast key drivers will make the building blocks of the future financial statements as we will see in Chapter 3 where we will build a financial model from scratch in order for the reader to grasp all the aforementioned abstract rules.

The following indicate best practices that will help you build models that are robust, easy to use, easy to understand, and painless to update. Best practices are of greatest concern when documents are used by more than one person:

○ Use a modular 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.