Toggles in models

AlexkeszI use a choose formula, it is kind of hard to explain how to set it up without showing you. The formula will choose a number for a list given the input. 1 will select the first number 2 the second ect.

use INDEX(array of your options, toggle cell)

rufioloveYeah, have to agree with the choose function. Index works too but is a bit more difficult than choose.

Let's say you have growth rates starting at 1% and going to 5%

if the 1% is in cell A2, and 5% is in A6, your choose formula would be =choose(A1,A2, A3, A4, A5, A6)

where A1 equals the option 1-5 that you want to select. This is pretty awesome because you can then link cell A1 through to a Case Assumptions page so that if you want to run a certain scenario that has a 1% growth rate for example, you could direct link cell A1 to that chosen scenario on the assumptions page and it would flow through your model.

Hopefully that is helpful. Probably a bit easier to understand by seeing it, but the choose function / index / offset functions are very helpful in terms of selecting static inputs to flow through a dynamic model.

Yea choose works when you haven't an array, but when you do, index allows you to add more cases without altering your formula, and it's just far quicker to type out.

BanditPanditOffset:

Put in - A2: 5 A3: 3 A4: 1 B4: offset($A$1,$B$1,0)

Now test in B1 w/1,2,3...case 1, case 2, case 3...best/base/worst

Offset is the most efficient way to do it, and small efficiencies matter when you're working until 4am.

sayandarulai'll assume you're using excel 2007 or 2010. my method will allow you to easily toggle between scenarios from a dropdown list.

put your growth scenario in a cell (let's call it B2), and make sure every formula on your sheet that depends on growth rate refers this cell. next, put your different growth scenarios (1%, 5%, 10%, etc..) in an array.. let's say B4:B6. then, click on B2, go to the "Data" ribbon, and click "Data Validation". in the "Settings" tab, choose "List" in the dropdown. in the "Source" box, enter B4:B6.

this will allow the user to select a growth scenario of 1%, 5%, or 10% from a dropdown list in cell B2.

This is what I do too for bear, base and bullish scenarios.

sayandarulai'll assume you're using excel 2007 or 2010. my method will allow you to easily toggle between scenarios from a dropdown list.

put your growth scenario in a cell (let's call it B2), and make sure every formula on your sheet that depends on growth rate refers this cell. next, put your different growth scenarios (1%, 5%, 10%, etc..) in an array.. let's say B4:B6. then, click on B2, go to the "Data" ribbon, and click "Data Validation". in the "Settings" tab, choose "List" in the dropdown. in the "Source" box, enter B4:B6.

this will allow the user to select a growth scenario of 1%, 5%, or 10% from a dropdown list in cell B2.

This works until you start adding columns and copy pasting worksheets, etc

Stick with choose or offset

leveredarbjust do offset, by far the simplest and most flexible, choose and index suck compared to offset

Can't just make sweeping statements, defend your stance big boy, i've got a bottle of bollinger open for you if you do.

leveredarbits the quickest to write, you can easily add more choices without having to alter the original formula.

index is undoubtedly quicker to write (it's an array) and you can also add more scenarios without altering your formula.

George87I find that using a choose formula, works best. Write out the growth rates for each year at the bottom of your model, then up top, in the YoY % growth rate cell, below the total revenue forecast, insert the choose function, which will work like this:

=choose(first value entry is the scenario toggle button cell that you absolute reference and create the toggle via data validation, second value entry is the growth rate for scenario #1: 2012 @1%, third entry is the growth rate for scenario #2: 2012 @2%, fourth value entry is the growth rate for scenario #3: 2012 @3% and so on). Once this is done, you will drag the formula across the

Somewhere way below your model you write down the growth rates for the forecast years:

2012 2013 2014 2015 2014 2015 etc 1% 1% 1% 1% 1% 1% worst case aka scenario #1 2% 2% 2% 2% 2% 2% better case aka scenario #2 3% 3% 3% 3% 3% 3% best case aka scenario #3 and so on

Hope that was clear enough, good luck

Choose is absolutely not the easiest way, really tedious to add another case especially if you have a large amount of detailed drivers. I usually go offset (disclaimer - haven't really used index but sounds like that is good as well)