Excel offset function increases modelling risk

In this tutorial, we look at the important uses of the OFFSET function, e.g. scenario-based modelling. However, we also examine modelling risk and some alternatives to the OFFSET function.

Offset in Excel

The Excel OFFSET function returns a reference to a range. The general form of the function is:

OFFSET(reference,rows,cols,[height],[width])

It can be broken down into five parts:

• Reference: The cell where we are starting from
• Rows: How many cells we should shift DOWN (relative to the starting reference cell)
• Cols: How many cells we should shift to the RIGHT (relative to the starting reference cell)
• Height: Specifies how tall the selected range should be
• Width: Specifies how wide the selected range should be

Note: If Rows = 0, it means we don’t move vertically at all. If it is negative, it means we move up. Similarly, if Cols = 0, it means we don’t move horizontally. If it is negative, it means we move to the LEFT. If both Rows = 0 and Cols = 0, it means that there is no movement at all.

Height and Width are optional. If they are not specified, their value defaults to 1, i.e. refer to a single cell.

Some uses of offset

The OFFSET function can offer elegant and flexible solutions, especially when it is used in conjunction with other functions.

The OFFSET function is commonly used for these types of tasks:

• Returning a cell that is located a specified number of rows and columns away from our reference cell
• Automatically calculate the year-to-date figures by changing only a single input to specify the last month
• Return a reference to the last N cells in a range (where ‘N’ is a pre-specified number of cells), e.g. delay in working capital
• Return figures corresponding to a particular “scenario” or “case” number, or perhaps for a particular year or month
• OFFSET can be used with MATCH to extract data from tables, useful when working with scenarios
• OFFSET can be used with SUM to find sums of a range relative to the current cell, e.g. calculation of straight line depreciation (refer to the NavigatorPF link below).
• OFFSET can be used with INDIRECT for purposes of dynamic data validation lists
• OFFSET can be used for lagged variable lookup, useful when looking up inflation rates from a pre-specified number of periods ago

However, despite these uses, there are a number of issues associated with OFFSET in relation to model risk.

Example: Offset in scenario-based models

The example in the attached workbook demonstrates how the offset function can be used to model projected margin.

Initially, a financial analyst might indicate the different values for sales prices, costs and volume sold under different scenarios:

Then, what we would like to do is to model the projected margin under different scenarios, whereby the user could select a particular scenario.

Then, the offset formula can be used to extract the desired input values based on the selected scenario:

Subsequently, these input values are used to produce the gross margin projections.

How the offset function increases modelling risk

Transparency

Firstly, the OFFSET function increases modelling risk because modellers may not fully understand how it works. This means that even though the original modeller understands how to use the OFFSET function, future maintainers of the same model may not have the same understanding.

Inadvertently, users may make unwanted changes to the document without fully understanding the effects of the OFFSET function. For example, when inserting a column, references produced by the offset function may refer to the incorrect column.
Using OFFSET can lead to confusing formulae, especially when used in combination with other functions within the same cell.

Tracing Dependents/Precedents

Secondly, the OFFSET function does not work well with Excel’s inbuilt trace functionality. Tracing functions are powerful features in Excel because they allow you to find out which formulae are used by certain cells, indicating “precedent” and “dependent” relationships visually through the use of blue arrows. The inability to trace OFFSET functions is a significant weakness, and increases risk due to non-traceability.

Volatile

Thirdly, the OFFSET function is a volatile function. Volatile functions cause cells to be recalculated irrespective of whether precedents were changed. Generally, it is good practice to avoid volatile functions because they have negative performance implications.

Alternatives to offset

Whenever possible, the OFFSET function should be replaced by simpler, traceable functions.

Previously, we had mentioned that a combination of OFFSET and MATCH offers flexibility when working with scenarios, because it allows you to add scenarios without the need of changing the index lookup. However, the major issue is that this formula would not be traceable.

Thus, it might be preferred to replace the OFFSET/MATCH with a MATCH/INDEX combination or formulae such as VLOOKUP – which are traceable.

Related tutorials

• Navigator Project Finance tutorial http://www.navigatorpf.com/tutorials/offset-function-excel
• The Financial Modeler tutorial (using OFFSET to calculate YTD results, with downloadable Excel workbook http://www.thefinancialmodeler.com/2009/offset-function-tutorial/
• Using the offset function to return a range to the last 7 cells in a range http://excelsemipro.com/2010/10/the-offset-function-last-7-data-points/
• Top 10 functions to avoid in financial modeling http://www.finance30.com/profiles/blogs/top-10-excel-functions-to

COMMENTS