There are lots of different ways of building a solid scenario manager for efficient scenario analysis, and some are better than others. Most people would agree that the OFFSET is the most common Excel function for this purpose, followed by LOOKUP and INDEX, but every now I see a new creative, or silly, way of solving this rather simple problem by using obscure Excel functionality.
Excel workbook with 13 different ways of creating a Scenario Manager
I have put together an Excel workbook with different ways of creating a Scenario Manager - i.e. to pick up the relevant value based on the selected scenario. My standard way of doing this is with an OFFSET as I like that you can expand the range of scenarios without having to re-code the function. From a transparency point of view though I would say that using LOOKUP would make the most sense.
Which Excel functions are acceptable from a best-practice point of view?
My preferences (not just from a financial model audit point of view but also as a user/builder of models) are OFFSET, LOOKUP and INDEX.
The worst offenders are definitely
- SUM (array)
- Nested IF (I can’t believe people still do this..)
- Choose (very old-school)
- MMULT (over-engineering to the n:th degree)
- ‘+’ (Although there are not complex Excel functions, it still gets very messy and hard to update)
- INDIRECT (I have never seen this one in practice, and I really hope it stays that way…)
- PRODUCT (array) (again, over-complication a simple problem)
List of all 13 solutions in Excel
All 13 solutions are listed below (and in this Excel Workbook: “Scenario analysis for freaks - 13 silly options”). The cell references in the list below will probably make more sense once you have had a look at the workbook.
- =OFFSET($F9,0,$D$7-1)
- =SUMIF($F$7:$J$7,$D$7,$F11:$J11)
- =SUMPRODUCT(($F$7:$J$7=$D$7)*1,$F12:$J12)
- ={SUM(($F$7:$J$7=$D$7)*$F13:$J13)}
- =LOOKUP($D$7,$F$7:$J$7,$F14:$J14)
- =IF($D$7=$F$7,F15,IF($D$7=$G$7,G15,IF($D$7=$H$7,H15,IF($D$7=$I$7,I15,IF($D$7=$J$7,J15,”N/A”)))))
- =CHOOSE($D$7,F16,G16,H16,I16,J16)
- ={MMULT((D$7=$F$7:$J$7)*1,TRANSPOSE($F$17:$J$17))}
- =($D$7=$F$7)*F18+($D$7=$G$7)*G18+($D$7=$H$7)*H18+($D$7=$I$7)*I18+($D$7=$J$7)*J18
- =INDEX($F19:$J19,0,D7)
- =INDEX($F20:$J20,0,MATCH($D$7,$F$7:$J$7))
- =INDIRECT(CHAR(96+COLUMN(F$21)+$D$7-1)&ROW())
- ={PRODUCT(IF($F$7:$J$7=D7,$F22:$J22,1))}
I know that offset is a controversial topic, so it would be interesting to hear other people’s views on how to best construct a scenario manager. Feel free to email me a file with your preferred approach (or more ways of how NOT to do it) and I will post it on the blog.
Read the full Excel Data Table tutorial 
