Modelling tips and tricks: Using OFFSET for scenario analysis

istock_savushkin_ss.jpg
istock_savushkin_ss
Share this content
Tags

Welcome to a new series of applied tips and tricks in Excel for accountants and financial analysts wherever you may be. As a professional modeller, FCA and Excel MVP Liam Bastick highlights some of the more useful functions for financial modelling/Excel spreadsheeting. 

In the first part of a two-part discussion on OFFSET, Liam explains how OFFSET can assist with “what if?” analysis.

OFFSET at the outset

Here is a useful function for modelling that is often maligned: OFFSET. This function considers disposition or displacement and has the following syntax:

OFFSET(Reference,Rows,Columns,[Height],[Width])

The arguments in square brackets (Height and Width) can be omitted from the formula (they both have a default value of 1 which will be explained further in my next article). 

In its most basic form, OFFSET(Reference, Rows, Columns) will select a reference Rows rows down (-Rows would be Rows rows up) and Columns rows to the right (-Columns would be Columns rows to the left) of the Reference.

For example, consider the following grid:

image 1

OFFSET(A1,2,3) would take us two rows down and three columns across to cell D3. Therefore, OFFSET(A1,2,3) = 16, viz.

image 2

OFFSET(D4,-1,-2) would take us one row up and two rows to the left to cell B3. Therefore, OFFSET(D4,-1,-2) = 14, viz.

image 3

It is this displacement technique that can create a scenario table:

4

This example is included in the attached Excel file. Essentially, the assumptions used in the model are linked from cells L17:L24 (mainly in cyan). These values are drawn from the scenario table to the right of the highlighted yellow range (e.g. cells N17:N24 constitute Scenario 1. The “Base” case, cells O17:O24 constitute Scenario 2).

The Scenario Selector is located in cell H12. Using OFFSET we can retain all scenarios and select as we see fit. For example, the formula in cell L18 (highlighted) is simply =OFFSET(M18,,$H$12), that is, start at cell M18 and displace zero rows and the value in H12 columns across. In the illustration above, the formula locates the cell one column to the right, which is Scenario 1.

The advantage of OFFSET over other functions such as INDEX, CHOOSE and LOOKUP functions (we’ll cover these another time!) is that the range of data can be added to. The image below shows Scenarios 6 and 7 added in an instant. Whilst the other functions require a specified range whereas we can keep adding scenarios without changing the formula / making the model inefficient.

5a

It should be noted that OFFSET is a volatile function, i.e. a function that causes recalculation of a formula in the cell where it resides every time Excel recalculates. This occurs regardless of whether precedent cells/calculations have changed, or whether the formula also contains non-volatile functions. One test to check whether your workbook is volatile is close a file after saving and see if Excel prompts you to save it a second time (this is an indicative test only).

OFFSET is also what is known as a non-auditable function in that it does not recognise dependent cells that are linked via an OFFSET function. For example, in my illustration above, the $3.70 in cell N18 is clearly used.  However, if you were to select this cell and trace dependents (ALT + M + D), you would get the following message:

5

This should not put you off using OFFSET; it is a function that frequently calculates much quicker than the alternative options and its advantages may often outweigh the potential pitfalls.

It’s such a versatile and useful function I shall be continuing with further examples next time.

About Liam Bastick

Liam Bastick

Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their strategic valuations team in London. He was also a senior member of the UK Post Office’s M&A and strategy teams and has worked for / assisted various other Australian modelling companies including BPM, Corality, Navigator Project Finance, PKF and SumProduct.

He has worked in the UK, Australia, Belgium, Denmark, France, Germany, Hong Kong, Indonesia, Malaysia, New Zealand, United States, Switzerland and Vietnam, with many internationally recognised clients, constructing and reviewing strategic, operational and valuation models for many high profile IPOs, LBOs and strategic assignments.  Liam is a Fellow of the Institute of Chartered Accountants (ICAEW), a Fellow of the Institute of Chartered Management Accountants (CIMA) and is a professional mathematician.

Replies

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.