See-Through Modelling. Dominic Robertson

Чтение книги онлайн.

      Model flow

      Model flow is defined as the logical direction of information in the model. In much the same way as for that of a book where the reader will read from top to bottom and left to right this is also the model flow across and within sheets.

      Model flow is from top to bottom and left to right.

      In practice this means that logic in the model will try to use information from above and behind it as far as is possible. This gives the modeller and the user a clear starting point for where to place information and how to perform calculations. Every now and then it is necessary to use information from ahead of the present calculations and I call these counter flows.

       Model counter flow is any flow of information that travels against the model flow.

      For example, when calculating the corporation tax in any period of the model it is necessary to start with the profit before tax line from the financial statements and possibly make some adjustments in order to derive taxable profit. The tax sheet normally resides just before the financial statements sheet since the results of the tax sheet are used in the financial statements, so the profit before tax line used in the tax sheet is defined as a counter flow.

      The number of counter flows should be kept to a minimum in order to preserve the conventions and maintain the integrity of the model.

      Examples of unavoidable counter flows include:

      Links

      A model contains thousands of links between cells sometimes on the same sheet and sometimes across different sheets. These links are called cell references. Here I discuss the definitions and implications of at-source cell referencing for best-practice modelling and daisy chain referencing, which is the poor alternative.

      At-source cell referencing

      Whatever inputs are required for a particular calculation there will exist the optimum at-source location for all the ingredients. In other words, whether the ingredients are raw inputs or other calculations, for each one there will be a single best location source.

      At-source referencing means collecting inputs for a calculation from the correct place of first calculation or input rather than from any other location in the model. The overall picture of the references in the model is one where there are no daisy-chains.

      Daisy chains referencing

      Daisy chains are referenced links in the model that do not go back to the original source. Daisy chains are not good because if a calculation is deleted or changed the repercussions can be difficult and time-consuming to predict and repair.

      Suppose that the RPI index is used by two different costs as part of the creation of nominal cost lines in the model. Figure 3 shows the trace precedent arrows in Excel showing the two types of references that could be made. Example 1 shows that Cost 2 uses the index from Cost 1, whereas Example 2 shows that in both Cost 1 and Cost 2 the reference to the index is the same.

      Figure 3: Example of a daisy chain link and a correct parallel reference

      Example 1 is the wrong way of referencing the index and Example 2 is correct.

      As the model becomes more complex and the number of links reaches the tens of thousands, correct parallel at-source referencing becomes very important for the integrity of the model. Parallel at-source referencing allows for:

       chunks of logic to be deleted or added more freely without risk of errors

       faster tracking to dependents from inputs in cases where this is necessary

      The tree analogy

      The tree analogy is one of the central concepts in this book. The tree analogy (shown in Figure 4) helps explain the structure of a model as well as hinting at the most efficient way of getting around the model.

      Figure 4: Tree and root system

      The tree analogy suggests that:

       root ends are the inputs into the model

       roots are the main calculations in the model

       the tree trunk is the financial statements

       the tree’s branches are the further results and analysis in the model.

      Furthermore, the tree analogy also suggests that:

       further results of the model are made of elements drawn from the financial statements

       financial statements are made of elements drawn from the main calculations and inputs in the model, but not the further results.

      Model structure and the tree analogy

      Considering the structure of a model the tree analogy also suggests that:

       financial СКАЧАТЬ