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:
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.
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
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.