How To Use The OFFSET Function To Activate Scenarios in Financial Models

Показать описание

Scenario analysis takes your financial model to the next level by allowing you the flexibility to quickly change the assumptions of the model and reflect important changes that may have taken place in regard to the company’s operations. This could be due to shifts in the economy, the deal environment, or company-specific issues.

This tutorial discusses the use of OFFSET & MATCH Functions in performing Scenario Analysis in a completed financial model. Find definition of these functions below:

OFFSET Function

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).

In its most basic form,

OFFSET(Reference,Rows, Columns)

Reference refers to the cell being referenced.

Rows and Columns are numbers.

Rows downward (-Rows would be Rows rows up) and
Columns to the right (-Columns would be Columns rows to the left) of the Reference.

The offset function is a fantastic tool in Excel and will make it very easy for you to adjust your model for changing expectations. All you really need to know is that the offset function asks you for three things:
1) set a reference point anywhere in your model, 2) tell the formula how many rows you would like to move down from that reference point, and
3) tell the formula how many columns you would like to move to the right of the reference point. Once you have provided that information, Excel will pull the data from the desired cell.

The advantage of OFFSET over other functions such as INDEX, CHOOSE and LOOKUP functions is that the range of data can be added to.

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.

MATCH Function
Excel’s MATCH function returns the position of an item in a range.

The syntax is:
MATCH(lookup_value, lookup_array, [match_type])
Where:
• lookup_value is the value you want to find in the lookup_array
• lookup_array is the range of cells that Excel searches through to find the lookup_value
• match_type tells Excel how to match the lookup_value
o If match_type = 1 (default value if omitted), Excel returns the greatest value less than or equal to the lookup_value. For this to work properly, the values in the lookup_array must be in ascending order.
o If match_type = -1, Excel returns the smallest value greater than or equal to the lookup_value. For this to work properly, the values in the lookup_array must be in descending order.
o If match_type = 0, Excel looks for a value that exactly matches the lookup_value. In this case, the lookup_array may be unordered.
***************************************************

Channel Contents You Should Be Interested In:

***************************************************

Let’s connect:

***************************************************