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?

 
Alexkesz:
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)

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 

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.

 
rufiolove:
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.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 
BanditPandit:
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 am wise because I know that I know nothing -Socrates
 

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.

Money Never Sleeps? More like Money Never SUCKS amirite?!?!?!?
 
sayandarula:
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.

 
sayandarula:
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 works until you start adding columns and copy pasting worksheets, etc

Stick with choose or offset

 

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

"The trouble with our liberal friends is not that they're ignorant, it's just that they know so much that isn't so." - Ronald Reagan
 

Offset over Choose, you can keep adding scenarios without having to edit the formula

"One should recognize reality even when one doesn't like it, indeed, especially when one doesn't like it." - Charlie Munger
 
leveredarb:
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.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 
leveredarb:
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.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 

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.

 
Best Response

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

 
George87:
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)

 
fryguy22:
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.

"After you work on Wall Street it’s a choice, would you rather work at McDonalds or on the sell-side? I would choose McDonalds over the sell-side.” - David Tepper
 

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.

 

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.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

1
redever's picture
redever
99.2
2
Secyh62's picture
Secyh62
99.0
3
Betsy Massar's picture
Betsy Massar
99.0
4
BankonBanking's picture
BankonBanking
99.0
5
kanon's picture
kanon
98.9
6
CompBanker's picture
CompBanker
98.9
7
dosk17's picture
dosk17
98.9
8
DrApeman's picture
DrApeman
98.9
9
GameTheory's picture
GameTheory
98.9
10
bolo up's picture
bolo up
98.8
success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”