Setting Up a Sensitivity Table for a DCF

Hey guys,

I'm a trying to teach myself basic modeling in my free time.

I'm having trouble creating a Sensitivity Table. I know I need to use a data table but I'm having trouble setting it up, every time I try I get that my input cells are invalid. Right now I have WACC across the top and Long Term Growth Rate down the side.

I'm trying to find implied share price and I am using the WACC and Long Term Growth Rate from my assumptions tab and my column and Row input cells.

Can you guys point out what I'm doing wrong? Also I'm not sure what formula to put in the top left corner that the data table feeds off of.

Thanks in advance for your help

 
Best Response

I'm assuming you have WACC and perp growth inputs in A1 and A2, respectively, and a per share value your model spits out in A3. In the top left corner of your data table set it to equal A3 (per share value). Then you have WACC values across the top of the table and growth on the side. Highlight the table (including your WACC and growth that you are testing for sensitivity, open up the data table setup), put A1 as your row input and A2 as column input.

 
Alexkesz:
Thanks a lot it works now.

That's what I had before but when referencing my WACC and perp growth rates they were on a different sheet and that what seemed to be causing the error they just need to be on the same sheet, thanks

Right, your inputs must be on the same sheet as your data table, or else it will not work, as you've since figured out!

 

Thanks guys, I actually tried doing the linkages offsheet didn't work so simply put I'm just cutting back on the extent of variables, instead of looking at the realization / ton at 10 different input costs and 10 different yield percentages just cutting it down to 5 each and getting the data going manually - fortunately model is automated enough that it took like 10 minutes to do

 

What is the sensitivity table for? For the negative number issue, check to make sure your two variables aren't causing a negative denominator in your equation somewhere, such as perp. growth equation for terminal value in dcf if your variables are growth and discount rates.

 

Sometimes I have issues if I am using multiple tables linked to the same data. Basically only one will work at any given time. Never figured out why.

"For I am a sinner in the hands of an angry God. Bloody Mary full of vodka, blessed are you among cocktails. Pray for me now and at the hour of my death, which I hope is soon. Amen."
 

Aliquam distinctio incidunt voluptatem voluptates. Officia molestiae officiis autem molestiae distinctio. Eligendi repudiandae eveniet fuga nostrum sapiente excepturi. Sint cupiditate minus culpa et ab. Sequi itaque illo explicabo architecto occaecati non amet non. A aut voluptates eum pariatur quibusdam. Et eum nihil culpa numquam recusandae dolores.

Excepturi fugiat repellendus corrupti asperiores. Possimus voluptates voluptatem ab odio est voluptatem. Hic labore ullam quis nemo pariatur et aspernatur mollitia.

Maxime voluptas eveniet molestiae est aut eos quis consequatur. Nulla quidem et sit excepturi delectus non deserunt. Quia eaque maiores architecto qui molestiae. Enim soluta et amet possimus.

 

Est quos magni perferendis sit itaque magni aut. Aut atque nam omnis ipsam earum dolor iste. Dolor non aperiam expedita optio aut.

Est placeat quia at qui facere. Aliquid facere autem laboriosam unde dignissimos. Sint harum quisquam at velit incidunt laudantium. Illo culpa asperiores provident quibusdam. Inventore sit nemo error accusantium autem quia. Quasi soluta suscipit repellat id.

Nulla incidunt laboriosam eos consectetur quod ad. Velit ut quia repudiandae voluptas ipsa. Numquam facere laboriosam consequuntur necessitatibus totam nihil excepturi.

Rerum sit accusamus saepe incidunt recusandae sit optio. Dolor accusantium nam sed animi itaque aut.

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
DrApeman's picture
DrApeman
98.9
7
dosk17's picture
dosk17
98.9
8
CompBanker's picture
CompBanker
98.9
9
GameTheory's picture
GameTheory
98.9
10
numi's picture
numi
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...”