4.3 Modelling Example I: Integrated Forecast for a Simple Business Without Dynamic Adjustment
For this example, we will assume that there is a business which:
- Is a start-up business.
- Is initially financed only with an injection of cash from the owner (which therefore also represents the owner’s equity in the business). This injection is used to cover start-up costs and losses.
- Creates its sales revenues by buying items and then immediately reselling them at a higher price.
- Suppliers are paid immediately, and customers settle immediately for their purchases.
- There are no storage costs, no shipping costs, and the business does not own any premises or other capital items.
- No investment is required.
- There are some fixed costs of operations, and these are all cash costs that are incurred and settled immediately.
- There are no taxes.
Let us assume that the forecast of the Income Statement of the business is as follows:
Note that:
- There are no revenues or product costs until period 2.
- The profit becomes positive for the first time in period 4.
- Although it is not shown here explicitly (in order to focus on the relevant points), in practice the forecast of the individual items (such as Sales Revenue or Operations Costs) would likely use some of the techniques in an earlier course (such as growth-based and ratio-driven forecasting).
A Cash Flow Statement can also be created, noting that (with the simplifying assumptions above), the cash flow is the same as the Profit.
The Cash Flow Statement can be used to create a corkscrew, which calculates the ending period cash balances (row 14), and using the assumption for the base case that the ownere has put in $150 of cash initially (so that the starting balance for cash in period 1 is $150, which is captured as a carry-forward amount):
The Profit from the Income Statement can also be used as an input into a corkscrew for the balances of Equity, noting that the initial equity is the $150, corresponding to the owner’s injection of cash (which therefore creates an equity liability of the business to its owner):
As a final step, the Balance Sheet is created. In this case, the Assets are cash only, and the Liabilities are the Equity only:
The Balance Sheet balances, as can be seen from the figures in row 22 and 24, and confirmed by the check in row 26. This logical check returns TRUE if the items in row 22 are the same as those in the corresponding columns of row 24.
Finally, as can be seen from the formula display in column J, all the formulas use only simple arithmetic or are direct references to other cells. In particular, there are no Excel functions used or required.