There are two basic types of line item in financial models – flows and balances.
Balances are amounts at a point in time. Balances can be financial or non financial.
Every balance has similar properties. This blog explains what those properties are, and gives a standard model component that can be used for all balances.
In order to model balances, we need the following components.
The equation is: Balance BEG + Initial balance + Upward flow – Downward flow = End balance
Introducing the Corkscrew
The corkscrew is a standard modeling component that allows balances to be modelled consistently.
It deals with the 4 components we’ve already seen and adds the ability to “inject” an initial balance into the corkscrew at a specific point in time, for example, a “last actuals” balance.
1. The upward and downward flows are always links. Avoid the temptation to calculate the flows in the middle of the corkscrew. The purpose of the corkscrew is to calculate the balance, not the flow.
2. The upward flow and downward flow are presented in the same order each time, aiding consistency.
Beginning and End Balances
3. The beginning balance is calculated by looking backward at previous period’s end balance.
Formula in J26:
= I29
4. The end of period balance is calculated as the beginning balance, plus the upward flow, less the downward flow.
Formula in J29:
= J26 + J27 – J28
Incorporating Initial Balance
5. The initial, or “pre-existing” balance can be an input or a calculated line called up to the corkscrew. The flag is included in the corkscrew to tell the calculation when to “inject” the initial balance.
Formula in J29:
= IF(J24 = 1, $F23, J26 + J27 – J28)
Formatting the Corkscrew
6. The words “plus” and “less” are included in Column D to indicate the upward and downward flows. Since all the flows are present as positive numbers, it helps to show what is being added and what is being subtracted from the beginning balance, to get to the end balance.
7. Since the corkscrew is a “non-standard” calculation block, it is indicated with top and bottom borders. This makes it easy to “see” in a financial model.
Template Corkscrew
Most financial models contain lots of balances. It is important to be able to quickly set up a standard corkscrew.
We recommend keeping a “template” or blank corkscrew in the Tmp sheet of your model. Whenever you need to set up a balance calculation, you can copy this template. Each time you copy the template you’ll need to:
– Change the labels.
– Update the links of the following components:
– Upward flow
– Downward flow
– Initial balance
Copy and Paste the Corkscrew
1. Select the corkscrew
Shift + Spacebar + Down arrow
2. Copy the corkscrew using Ctrl + C.
– Then paste to your desired location:
– Selecting a row: Shift + Spacebar
– Enter
Update the Labels
3. Select the labels column (from A to E).
Use ‘find and replace’ to update the labels:
- – Press Ctrl + H
- – Find what: [xxx]
- – Replace with: The name of your balance, in this case “Accounts payable”
- – Press Alt + A as a shortcut for “Replace All”
Update the Links
4. Once the labels are replaced, update the links for:
- – Initial balance: Link to the initial balance
- – The upward flow
- – The downward flow
Accounts Payable Balance
5. Remove the yellow “placeholder” marking using the FAST format macros.
Comments
Hi Morten,
This is a rigorous approach to modelling balances in circumstances in which the cash flow can be accurately ascribed to specific P&L and balance sheet accounts. However, I find there are applications in which the FAST-based corkscrew is not appropriate. For example, in modelling forecast balances for publicly-listed equities using the indirect method of operating cash flow disclosure, the disclosure of a specific balance sheet line item in the company’s accounts may be insufficient to link it with a particular operating P&L line item and vice versa. In the case of PP&E, capex (under the direct method of disclosing CFI) and depreciation will be disclosed, making the FAST corkscrew appropriate. However, an operating balance sheet line item such as ‘Other accruals’ could relate to, for example, ‘Cost of sales’ or ‘Administrative expenses’ or ‘Distribution costs’ or some sub-account within these disclosed line items — or indeed any combination thereof — and there may be no way of knowing. As such, it is not possible (beyond complete guesswork) to back out an accurate ‘Cash paid’ output as prescribed by the FAST corkscrew template.
My approach in such circumstances, and one I have seen commonly used in practice, is to simply model a target balance (typically based on a % of some P&L item like revenue or COGS), and then export the y-o-y change in the balance to the cash flow statement forecasts, from which the operating cash flow can then be calculated using the indirect method (by contrast, I have only seen the direct method used in FAST models that I have had the opportunity to review). This can be done in a FAST-compliant way without using a corkscrew, but rather by using two calculation blocks; for example, one to calculate Accruals as % of Opex, and the other to calculate the y-o-y change in the forecast balance. For balances in which the flows are identifiable, like in the PP&E example above, the corkscrew approach can be used.
I’d be interested to hear your thoughts on this.
Keep up the good work.
Regards,
Stephen
Stephen – I hear you. The approach described does presume that these flows are identifiable and I agree that modelling something simply to fill up a pre-determined modelling structure (with no impact on the financial statements) should be challenged.
Your approach sounds sensible and is certainly one that we use. Advantage: fewer rows to review / gets to a useful answer more easily. The only disadvantage I can think of is that now not all balances are modelled in a cork screw which may raise consistency concerns. But I think the advantages of your approach outweigh that disadvantage.
Does this help at all?
Kind regards,
Andrew
I agree. Thanks for that Andrew.
Kind regards,
Stephen