Toggles in models
I need help in creating a toggle for different growth scenarios in a financial statement projections model Im making.
So, for example, if I wanted to have different growth rates, how could i make a "toggle" to input different scenarios?
So, if I had 1% growth as worst case and 5% as best case, how could i click a toggle to make the financial statement projections use 1% or 5% respectively?
I 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)
I'd go with the Choose formula vs. the Index(Array...) formula, if only because it's easier to use. I'd literally google "how to use a choose function in excel" and see what's out there. I wouldn't be surprised if someone had a YouTube video out on it.
Yeah, 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.
Offset:
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.
i'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.
This works until you start adding columns and copy pasting worksheets, etc
Stick with choose or offset
I've used both: the Offset/Match and Choose functions. I prefer choose b/c its less tideous.
I use offset mostly but that's because I'm self taught and don't know any better. I have to agree with SanDiegoBolt that it is very tedious but useful if you have variables outside a particular growth rate.
Regards
Offset over Choose, you can keep adding scenarios without having to edit the formula
just 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.
its 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.
+1 for dropdown list. Just make the toggle options in a hidden sheet
Am I the only one waiting for swagon's post in the topic with such a name..?
I just learned this last night too... used Choose.
Drive your model off one line which has the "Used" Growth rate. Underneath that line have your best, worst and base case scenarios. In the used growth rate line use the choose formula and select all those cells.
I 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)
The advantage of index over offset is that with offset if you offset by too much accidently (i.e. go outside of your intended grid), for example by adding a 0 etc.. you'll still get a "positive" result, it'll just keep on offsetting. With index you'll get REF, telling you've typo'd.
Reiciendis delectus doloribus quia distinctio dolore qui. Quos adipisci voluptas et voluptas magnam. Autem non deleniti optio. Veniam quis eos tenetur esse. Sequi cupiditate id mollitia voluptate. Voluptas officia aut facere nihil quo.
Quo officiis voluptatem nobis blanditiis ad. Consequatur pariatur qui vel blanditiis enim. Quisquam ut repellendus et aspernatur praesentium quidem iusto. Excepturi aut esse eaque dolorem.
Deleniti sapiente aliquid est architecto qui vero et. Deserunt consequatur similique omnis. Ut adipisci sapiente eius.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Vitae adipisci veniam amet. Officia temporibus dolores laborum dolores vero eos omnis.
Sit blanditiis aut tempora et beatae consequatur. Eum et non consequuntur accusantium. Ut qui accusantium ratione recusandae et eum rerum. Officiis labore labore repudiandae ut debitis quam. Velit voluptate voluptas omnis a sed ut.