The examples used in the section show that the creation of integrated financial statements can be relatively straightforward in principle. In practice, it can also become quite complex and challenging for several reasons.
In terms of the general creation of such models:
- There is a wide set of applications which use financial statement modelling (e.g. business valuation, credit assessment, financing structures, and corporate negotiations). As a result, there is a large variety of ways that models may be implemented to meet the bespoke needs of each application.
- There are usually many more line items than shown in the simple example. The modeller must understand the specific terms and concepts associated with all the line items, and how particular line items or transactions affect each of the statements. For example, there is usually a requirement for investment in plant and machinery, and the assets created by this investment will depreciate over time, which is recognised as a cost, but it is not a cash item. The impact of this on the Income Statement, Cash Flow Statement and Balance Sheet must be understood and captured in the model.
- Some line items may require complex calculations to capture correctly. For example, there are often quite sophisticated structures that are used for debt financing or tax calculations.
- There are usually timing issues that need to be reflected in the link between the Cash Flow Statements and Balance Sheet (such as customers not paying immediately, or suppliers not being paid immediately). This can create complexity in the formulas.
- The totality of the linkages between items can be complex to keep in mind: Some line items may be forecast independently of others (for example, the Sales Revenue may often be forecast only using historical data and a growth rate assumption), whilst others are dependent on these or linked to them.
In terms of the dynamic adjustments that are generally used in such models to try to model the reality or constraints of the situation:
- There are numerous (Balance Sheet) measures that can be used as criteria to determine the need for dynamic adjustment logic and formulas. The appropriate criteria are often specific to a particular application, and are not standardised across models.
- In models with a dynamic adjustment, similar items may be calculated several times and appear in several places: In the earlier example, cash flow and cash balances are calculated both at the “interim” (pre-adjustment) level, as well as at the final (post-adjustment) level. This creates larger models and ones which can be harder to understand and follow.
Finally, several other issues can result in models which are complex or potentially inaccurate:
- It is often difficult to maintain a clear (top-to-bottom) flow in the model. Although this was achieved in the simple model, in larger models with many other line items, it can easily arise that the logic flow is not as linear, and so may make the model harder to follow and understand, and more error-prone.
- It is easy to create models that may not be realistic (for reasons other than those used in the dynamic adjustments). For example, models may use unrealistic assumptions on the level of long-term costs or capital investment (leading to excessive profit margins or high valuations). These situations can be harder to detect, as they are typically exposed only by performing cross-check calculations or calibrations (such as analysing return-on-capital measures, or comparing the key performance metrics with those of other companies or industries).
- Where the dynamic adjustment is made by using interest-bearing debt or loans (rather than equity), then there is the possibility to create circular references if one also implicitly assumes that cash flows are continuous within each period (rather than occurring at the end of each period) and that interest is immediately charged (within the same period) on the new financing within the period. (For example, new borrowings within the period would create interest charges within the period, meaning that additional borrowing within the period is required, and so on.) This circularity is resolvable by iterative methods, as long as interest rates are not very high, but it also creates significant complexity. Whilst we generally consider the use of circularities in this context to not be worthwhile (compared with using alternative implicit assumptions), there is a lack of standardisation about this amongst modellers. Partly, this is because the issus is rarely analysed properly, in terms of assessing the true benefits and costs of doing so. A discussion of this is done in detail in later materials. In addition, where a model does contain circularities, it is often better to use VBA macros to resolve them; this is discussed in the course on VBA macros.