Note: Excel file available for download at the bottom of this post.
The two most commonly used functions for scenario selection are =CHOOSE() and =OFFSET(). The =CHOOSE() function is the simpler of the two, and is sufficient for most simple scenario analysis. If, for example, you only intend to run three scenarios, then you may prefer =CHOOSE(). This function can cause frustration, however, if you are constantly adding or deleting the number of scenarios in your model. The reason is that it requires you link directly to the scenario. The image that follows demonstrates:
For this reason, as the number of scenarios expands, or if you intend to add or delete scenarios regularly, =OFFSET() may be a more flexible alternative. With the added flexibility comes some additional complexity, but nothing that can’t be learned in a few extra minutes. As you can see, the =OFFSET() function does not link directly to the scenario, but instead to a “reference cell” from which it counts to retrieve the correct scenario:
From this “reference cell,” which is indicated by a blue box in the image above, =OFFSET() can count both rows and columns, which provides even greater flexibility should you require it. In the example pictured, the formula is referencing the value 1 in cell C14 to count down one row from cell G15 (blue box) and retrieve the value 5%.
To select a value to the left or right of the reference cell, input a value for the column (this is the last variable in the formula). As an example, look to the image below. With the exception of the inputs listed in the column titled “Period 1,” all of the other inputs have been deleted to demonstrate that the function is referencing the same cell across all periods (this will make more sense when you look at the file).
CLICK THE LINK TO DOWNLOAD THE FILE:Choose_Offset.xlsx
