4.6 Modelling Example II: Integrated Forecast for a Simple Business With Dynamic Adjustment
The following describes a simple example of the principles of implementing a dynamic adjustment. The assumption used (for the criteria to check) is that the cash amount on the Balance Sheet must never be negative, and that – in order to avoid this – an additional injection of cash (equity) would be made in order to do so.
Therefore, the model first calculates the interim cash balances that would result from the base assumptions, and use this to check whether (and how much) additional financing is required. It is then implicitly assumed that this financing activity takes place, so that the final cash balances can then be calculated.
The following image shows the calculations for the interim cash balances (prior to any adjustments), as well as the amount of adjustment required to the financing:
- Row 9 contains the initial cash flow (i.e. prior to any new injection).
- Rows 12-14 use a corkscrew to calculate the interim cash balances (shown on row 14) based on the initial owners’ equity and the initial cash flow.
- Row 16 calculates the cash injection that is required. That is, it applies an IF function to the interim cash balance (in row 14), to determine if (and how much) additional financing is necessary:
The model also implicitly assumes that the required additional financing takes place as needed (i.e. only in period 3 in this case). Given this, the total cash flow can be calculated by adding the new financing injection to the interim cash flow. This creates the final Cash Flow Statement (rows 19 and 20):
The final Cash Flow Statement can then be used to calculate final cash balance, and (on the assumption that the cash injection is provided as equity rather than as a loan), then the final equity balances can also be calculated (using the corkscrews on rows 23 to 25 for cash, and that on tows 28 to 31 for equity):
(Note that this still results in an equity balance of zero in period 3. In practice, the cash injection would generally need to be set at a higher level, in order to keep the level of cash (or of Balance Sheet equity) above a (strictly positive) threshold level, rather than allowing it to be zero. This is not done here in order to keep the presentation of the core principle (of dynamic adjustment) as simple as possible.)