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
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.
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!
Never mind figured it out. Thanks though
Offsheet data table for sensitivity analysis (Originally Posted: 05/23/2013)
Quick question about data tables (what if analysis - > data table)
I want to create a separate tab with all the sensitivity tables necessary for the analysis I'm conducting. However the variables for the table are on different sheets, some might be on Assumptions, others in the IS. I keep getting "invalid reference!" errors - any suggestions?
I cannot simply create data tables on the relevant sheets and then copy / past values elsewhere because for the model I'm working on; I might have to create a data table where the two variable inputs (yield on feedstock, plant capacity utilization) are on the assumptions page and EBITDA (output) is on the IS
help?
Shouldn't matter if they are linked properly, but you could create and "Assumptions or Inputs" box above at the top of the sheet and conduct sensitivity analyses below them. Just link the Assumptions/Inputs items to the IS, Assumptions tab, etc.
I'm pretty sure data table input and output linkages have to be on the same sheet as the data table.
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 could be wrong with my sensitivity tables? - Excel is acting like an .... (Originally Posted: 05/21/2013)
For some reason I keep getting negative or wrong numbers when doing a sensivity thru the what if analysis function. What could be wrong outside of the normal issues of wrong column, row,etc.
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.
Are you trying to sensitize data that won't change with sensitivity tables?
Why don't my sensitivity tables work? DCF analysis (Originally Posted: 01/24/2015)
Hi everyone, I just completed my very first DCF analysis on a company.
I'm not sure why the sensitivity tables are not working, would appreciate any help, many thanks in advance!
For example, the implied share price doesn't vary with either 1)discount rate, or 2)terminal growth rate
It works fine for me, have you checked whether you set excel to automatically recalculate data tables? If not, do so and it should work.
It works fine for me, have you checked whether you set excel to automatically recalculate data tables? If not, do so and it should work.
Got it working, thank you!
Help Needed: Sensitivity Table Providing Very Wide Range (Originally Posted: 05/02/2017)
Hi all,
My sensitivity table (WACC/Perpetuity Growth Rate) is providing a huge variance in EV values.
What would be the reasons for such a substantial range? For an $8.9bn company I'm valuing, by varying WACC and perpetuity growth +/- 0.5%, it gives a range of $10.9bn to $7.6bn.
How far out are you projecting in the forecast period? My guess is that the terminal value is the bulk of the valuation, which would probably be volatile to your assumptions.
Projecting 10 years - PV of terminal value is ~63% of EV. Does this mean it's likely to be a calc problem?
You probably have like 2 years of projections
Qui sint ut autem quisquam pariatur. Aspernatur qui in ducimus.
Voluptatem eveniet aut rerum aliquid. Esse hic facere ullam possimus beatae. Illum quam provident aut qui.
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...
Expedita enim animi quo eius quia. Tempora ab reiciendis labore alias. Et aliquam et temporibus provident tenetur totam. Porro reiciendis earum qui autem quasi non voluptates.
Vel itaque sed doloribus. Eligendi blanditiis labore recusandae impedit recusandae facilis a. Vero quod sed natus aut. Et quod architecto numquam ut quidem voluptatum nulla.
Quod ut accusantium et quos doloribus itaque. Sed ut soluta quia quo. Accusamus ea esse ut voluptate molestiae est facilis quia. Ab ratione unde aut ut. Qui sit beatae beatae tempora sequi nihil. Id dicta incidunt architecto aliquam. Dolor voluptas temporibus nesciunt libero est porro rem tenetur.
Eius enim voluptatibus enim voluptas dolorem dolorem. Optio et corrupti placeat magni vel suscipit. Necessitatibus deleniti iste aspernatur dicta.