A great function for scenario analysis in Excel

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 December article.) Reference refers to the cell being referenced. Rows and Columns are numbers.

In its most basic form, OFFSET(Reference,Rows,Columns) will select a reference. Rows is number of rows down from the Reference (-Rows would be number of rows up), and Columns is number of columns to the right (-Columns would be number of columns to the left) of the Reference. For example, consider the following grid:

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

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, as shown below.

It is this displacement technique that can create a scenario table like the one pictured below.

This example is included in this 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 (eg, cells N17:N24 constitute Scenario 1, aka 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.

Note the deliberately blank cells in column M (M17:M24). These are by design: If the scenario number cell (H12) is left blank, having this range of blank cells avoids creating an unintentional circularity in the model (ie, a formula that refers to itself). This prevents model instability, incorrect results, and potential file crashing/corruption. Little tricks like this distinguish experienced modellers. (That’s right: It means I fell for it in the past!)

The advantage of OFFSET over other functions, such as INDEX, CHOOSE, and LOOKUP, is that the range of data can be added to without having to amend their respective formulas. The image below shows Scenarios 6 and 7 added in an instant. Whilst the other functions require a specified range, we can keep adding scenarios without changing the formula/making the model inefficient.

It should be noted that OFFSET is a volatile function, ie, 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 nonvolatile functions. One test to check whether your workbook is volatile is to 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 nonauditable 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:

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

Since it’s such a versatile and useful function, I shall be continuing with further examples in a future column.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of An Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.