July 12 2009
Spreadsheet Skills: Better Scenario Analysis With OFFSET
Tagged Under : Scenario Analysis
This month we examine the use of OFFSET for scenario analysis and other calculations. A spreadsheet chart is attached to the bottom of this page for you to practice your new skills. By Liam Bastick, associate director with BPM Analytical Empowerment.
Question
I am aware of Excel’s Scenario Manager, but I’m looking for something more transparent. I want to do some scenario analysis in my financial model, where some key assumptions can be changed. I need to keep the alternative scenarios and I may need to add to them. Any suggestions?
Advice
Last month, we talked about INDEX and MATCH, functions concerned with position. This month, we look at OFFSET, a function which considers displacement. OFFSET is a function well suited to this scenario (if you will excuse the pun!).
The syntax for OFFSET is:
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 is explained below).
In its most basic form, OFFSET(Ref,x,y) will select a reference x rows down (-x would be x rows up) and y rows to the right (-y would be y rows to the left) of the reference Ref.
Consider the following grid:
Figure 1: Example table
OFFSET(A1,2,3) would take us two rows down and three columns across to cell D3. Therefore, OFFSET(A1,2,3) = 16.
Figure 2: OFFSET(A1,2,3)
OFFSET(D4,-1,-2) would take us one row up and two rows to the left to cell B3. That means OFFSET(D4,-1,-2) = 14.
Figure 3: OFFSET(D4,-1,-2)
We can use these mechanics to answer this month’s query and construct a very simple scenario table:
Figure 4: OFFSET scenario illustration
This example is included in the attached Excel file. Essentially, the assumptions used in the model are linked from cells J14:J20 (in yellow). These values are drawn from the scenario table to the right of the highlighted yellow range (for example, cells L14:L20 constitute Scenario 1, cells M14:M20 constitute Scenario 2).
The Scenario Selector is located in cell J11. Using OFFSET, we can retain all scenarios and select as we see fit. For example, the formula in cell J14 is simply OFFSET(K14,,$J$11) – that is, start at cell K14 and displace zero rows and the value in J11 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 is that the range of data can be added to. While the other functions require a specified range, we can keep adding scenarios without changing the formula/making the model inefficient.
Other uses
OFFSET has other practical uses in Excel, taking advantage of the height and width arguments.
Consider our OFFSET example above. If we extend the formula to OFFSET(D4,-1,-2,-2,3), it would again take us to cell B3, but then we would select a range based on the height and width parameters. The height would be two rows going up the sheet, with row 14 as the base (that is, rows 13 and 14), and the width would be three columns going from left to right, with column B as the base (that is, columns B,C and D).
Hence OFFSET(D4,-1,-2,-2,3) would select the range B2:D3.
Figure 5: OFFSET(D4,-1,-2,-2,3)
Note that OFFSET(D4,-1,-2,-2,3) = #VALUE! since Excel cannot display a matrix in one cell, but it does recognise it. However, if after typing in OFFSET(D4,-1,-2,-2,3) we press CTRL + SHIFT + ENTER, we turn the formula into an array formula: {OFFSET(D4,-1,-2,-2,3)} (do not type the braces in, they will appear automatically as part of the Excel syntax). This gives a value of 8, which is the value in the top left hand corner of the matrix, but Excel is storing more than that. This can be seen as follows:
- SUM(OFFSET(D4,-1,-2,-2,3)) = 72 (that is, SUM(B2:D3))
- AVERAGE(OFFSET(D4,-1,-2,-2,3)) = 12 (that is, AVERAGE(B2:D3)).
Indeed, we can construct a simple depreciation calculation, transpose references or even build a dynamic chart (one that displays more/less categories of information as required) using OFFSET’s height and width functionalities. The attached workbook provides several examples as illustrations.
Handle with care
While OFFSET is an extremely useful and flexible function, it is not transparent. The syntax is difficult for the user to grasp, and Excel’s auditing tools have problems too. The reference cell in the OFFSET arguments is the sole component that will be identified – for example, OFFSET(A1,2,3) will trace cell A1 only.
So spreadsheet users do get confused – in particular, regarding the reference. When calculating the rows and columns components, the reference cell is excluded, but it is included in determining the height and width.
