How to Model Balances in Excel

Modelling Balances

Author:

Morten Siersted

Published:

23 Nov 2017

Comments:

3 Comments

Category:

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.

Modelling Balances in Excel

The equation is: Balance BEG + Initial balance + Upward flow – Downward flow = End balance

Introducing the Corkscrew

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.

Corkscrew elements

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

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

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

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

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

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

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

Accounts Payable Balance

5. Remove the yellow “placeholder” marking using the FAST format macros.

 

Morten Siersted
Morten Siersted
Morten founded F1F9 in 1999. He has played a key role in developing the FAST Standard, including setting up the FAST Standard Organisation in 2011.