4.1 Introduction

The forecasting of financial statements is a common application in financial modelling. These are often referred to as “integrated” financial statement models, because the various statements (notably the Income Statement, Cash Flow Statement and Balance Sheet) must be forecast so that they are consistent with each other. This requires that the statements be linked together using Excel formulas.

In fact, the creation of integrated financial statement forecasts rarely requires the use of advanced Excel. Most of the line items can be forecast or calculated using standard arithmetic operations (addition, multiplication, division) to create formulas that use growth- or ratio-based calculations, as discussed in an earlier course. There are nevertheless some challenges in creating realistic financial statement models in practice: These are a mixture of the need to understand the terminology and concepts, as well as the potential complexity associated with managing large models with many interconnected components.

Aside from the forecasting of individual line items and linking them correctly, one of the main principles in building an integrated financial statement model involves ensuring that the model is sufficiently dynamic. This means that it adjusts to reflect core aspects of how the real-life situation would behave, specifically in relation to items on the Balance Sheet. It does this by adjusting the financing of the business in order to ensure that the items are realistic (for example, that they respect any practical or regulatory constraints).

This key principle of building integrated financial statement models that contain a “dynamic adjustment” mechanism can be demonstrated in the context of a business structure that is so simple that there is no need to first study the underlying meaning of financial statements in detail. This is the focus of this Chapter, which first shows how an initial model can be created in a way that is integrated (and the Balance Sheet is in balance), before discussing the need for a dynamic adjustment mechanism, and adjusting the model to reflect this. (In practice, this requires the use of the IF function (or similar or equivalent functions, such as MIN, MAX, and so on).

Whilst later courses cover economic concepts and principles of corporate finance in more detail, the fundamental principles behind the creation of integrated financial statement models can be covered without needing this more detailed knowledge.