Sensitivity Analysis-data Table

Private Sub CommandButton2_Click()
Dim tb, sa, msa, md, mct, inin, inno, feas, dur, cbp, adp As Variant
Dim tbo, sao, msao, mdo, mcto, inino, innoo, feaso, duro, cbpo, adpo As Variant
 
Dim wts As Variant
 
tb = Worksheets("Sensitivity Analysis").Range("D3:D17")
sa = Worksheets("Sensitivity Analysis").Range("G3:G17")
msa = Worksheets("Sensitivity Analysis").Range("J3:J17")
md = Worksheets("Sensitivity Analysis").Range("L3:L17")
mct = Worksheets("Sensitivity Analysis").Range("N3:N17")
inin = Worksheets("Sensitivity Analysis").Range("Q3:Q17")
inno = Worksheets("Sensitivity Analysis").Range("S3:S17")
feas = Worksheets("Sensitivity Analysis").Range("U3:U17")
dur = Worksheets("Sensitivity Analysis").Range("W3:W17")
cbp = Worksheets("Sensitivity Analysis").Range("Y3:Y17")
adp = Worksheets("Sensitivity Analysis").Range("AB3:AB17")
 
tbo = Worksheets("Dynamic Sensitivity").Range("B2:P3")
sao = Worksheets("Dynamic Sensitivity").Range("B8:P9")
msao = Worksheets("Dynamic Sensitivity").Range("B14:P15")
mdo = Worksheets("Dynamic Sensitivity").Range("B20:P21")
mcto = Worksheets("Dynamic Sensitivity").Range("B26:P27")
inino = Worksheets("Dynamic Sensitivity").Range("B32:P33")
innoo = Worksheets("Dynamic Sensitivity").Range("B38:P39")
feaso = Worksheets("Dynamic Sensitivity").Range("B44:P45")
duro = Worksheets("Dynamic Sensitivity").Range("B50:P51")
cbpo = Worksheets("Dynamic Sensitivity").Range("B56:P57")
adpo = Worksheets("Dynamic Sensitivity").Range("B62:P63")
wts = Worksheets("Sensitivity Analysis").Range("D2, G2, J2, L2, N2, Q2, S2, U2, W2, Y2, AB2")
 
[A69].Value = tbo(1, 1)
[B69].Value = tb(1)
End Sub

******** ******************** ************************************************************************> Microsoft Excel - Financial Model Template.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout I7 J7 K7 L7 M7 N7 O7 P7 Q7 D8 E8 G8 J8 M8 P8 =
C D E F G H I J K L M N O P Q 3 Sensitivity*Analysis*1*-*Terminal*Value*Approach*-*Growth*In*Perpetuity*Method 4 * * * * * * * * * * * * * * * 5 * * * PV*of*Terminal*Value* * * * * * * * * * * * 6 * * * at*a*Perpetual*Growth*Rate*of: * * Enterpise*Value * * Equity*Value*(EV*-*Net*Debt) * * Fair*Value*Per*Share * * 7 * WACC PV*of*FCF 2.0% 3.0% 4.0% 2.0% 3.0% 4.0% 2.0% 3.0% 4.0% 2.0% 3.0% 4.0% 8 From*Model* 9.54% 2,511.1 * 6,022.1 * * 8,533.3 * * 8,120.6 * * $20.58 * 9 * 8.00% * * * * * * * * * * * * * 10 * 9.00% * * * * * * * * * * * * * 11 Sensitivity* 10.00% * * * * * * * * * * * * * 12 * 11.00% * * * * * * * * * * * * * 13 * 12.00% * * * * * * * * * * * * * 14 * * * * * * * * * * * * * * * Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Hi, would appreciate if anyone could help me how to go about finishing this Sensitivity table. The inputs "from model" in row 8 have been linked to the model. The output should be that of "changes in WACC" and "change in growth rate".

Suresh

I use data tables from the 'what-if analysis' tools for 1-way sensitivity analysis of my model parameters. I usually love this method, however this particular model is only calculated after calling a macro, and I'm having trouble getting the data table to swap in the 1-way values as the model is being calculated. I already set the data table's 'column input cell' to call the macro using the 'Sub Worksheet_Change(ByVal Target As Range)' method, and this works if I directly input a value into that cell, but the data table swap-ins do not exhibit this desired effect. Any good codes out there?

Hello folks,

I am using Excel 2003 on Windows XP.
I am using a excel workbook for financial calculations.
I want to perform a sensitivity analysis to see the effect of volume on the profit margin. However, the calculations are complex and the volume is not directly used in the formula used for calculating profit margin and both the volume and margin are in different sheets.

Since there is no direct link between the two variables, I am not able to use data tables.

Do you know of any other method to perform the sensitivity analysis?

The model I am building will serve as a generic model and therefore should not be very rigid.

I hope I am clear.
Any help will be greatly appreciated.

Cheers
Rishu

Hi to all,

currently using excel for my company feasibility study which required me to do some calculation of cashflow and also calculation of IRR percentage.
need to run sensitivity analysis on it also.

although excel is powerful enough to handle my requiement right now. but at time i need to do sensitivity analysis which need to manually key in data (eg. change of sales figures) and check the IRR result.

tried to use excel macro to run this procedure for me, but if i change my model, i will have to change the VB code in excel also.

now i embark to search for ways to do IRR calculation using Access.

would appreciate if anyone could give me some pointers to help me as i am starting this project from scratch.


thank you.
chinkha

Hi,

I want to create a sensitivity table that will produce a variety of return on cost percentages based on the base rent and the Upgrade cost.

I found a guidance on using Excel spreadsheet for sensitivity analysis
at the following address: http://msl1.mit.edu/rdn/d_table.pdf
However, it seems that the guidance was for former version of excel. I
tried the example given in the guidance in Excel 2003 and it does NOT
work! Please kindly help me should you have latest information on this
issue.

Many thanks!

Curlynguyen

Hi There,

I have a complex model that I need to do a sensitivity analsyis on. I have three variables which I want to vary from 80% to 120% in 10% increments, the three variables will produce one results. Rather than changing each value manually and copying the resulting value to create a table, I would like to be able to create a table that will carry all this out automatically.

I do remember doing this before, but for the life of me I can't remember how. Any ideas?

Thanks

I am trying to find out how to make a three dimensional sensitivity analysis using data tables.

Example: 1,2,3 would be on right hand side as columns. 2,3,4 would be on the left hand side as columns, row would XYZ variables --output would be in the middle.

Any help or any link I could go to. Thanks much

I have heard that sensitivity analysis is easy with ' spin buttons'.

But my problem is, I CANT FIND the spin button in my Excel 2007.

I know that i have to go to DEVELOPER (just beside 'VIEW') and then insert a spin button. BUT SURPRISINGLY, in my Excel 2007, i dont see DEVELOPER beside 'view'!!

what has gone wrong????

Does anyone know of a way to creat a 2 variant sensitivity table in which the row and/or column inputs can change/vary with a look up or reference formula? For example suppose I am calculating margin on a sale and my costs are based upon 2 raw materials (oil and natural gas). In this table my sensitivity is margin, my row input is price, and I want to be able to toggle between oil and natural gas costs for my column input. Can this toggle be created?

I have a file that was created by someone else and it uses Tables, which take a lot of time to calc, but do a lot of work. However, i cannot understand how they work. Can someone point me in the right direction? I at least know it is a CSE formula. Not much, but a little.

Thanks.
******** ******************** ************************************************************************> Microsoft Excel - option1.xls ___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout G52 O52 H53 I53 J53 K53 L53 P53 Q53 R53 S53 T53 G54 H54 I54 J54 K54 L54 O54 P54 Q54 R54 S54 T54 G55 H55 I55 J55 K55 L55 O55 P55 Q55 R55 S55 T55 G56 H56 I56 J56 K56 L56 O56 P56 Q56 R56 S56 T56 G57 H57 I57 J57 K57 L57 O57 P57 Q57 R57 S57 T57 G58 H58 I58 J58 K58 L58 O58 P58 Q58 R58 S58 T58 G59 H59 I59 J59 K59 L59 O59 P59 Q59 R59 S59 T59 G60 H60 I60 J60 K60 L60 O60 P60 Q60 R60 S60 T60 G61 H61 I61 J61 K61 L61 O61 P61 Q61 R61 S61 T61 =
F G H I J K L M N O P Q R S T 51 IRR Sensitivity Table   NOI           Equity X Sensitivity Table       NOI     52   14.6% -10% -5% 0% 5% 10%     40.6% -10% -5% 0% 5% 10% 53 Cap Rate 7.00% 14.6% 20.1% 23.1% 26.1% 28.9%   Cap Rate 7.00% 0.4x 0.3x 0.4x 0.5x 0.5x 54 7.25% 31.5% 16.7% 20.1% 23.1% 26.0%   7.25% 0.6x 0.3x 0.4x 0.4x 0.5x 55 7.50% 28.7% 13.6% 17.1% 20.3% 23.2%   7.50% 0.6x 0.3x 0.4x 0.4x 0.5x 56 7.75% 26.0% 10.6% 14.2% 17.4% 20.5%   7.75% 0.6x 0.3x 0.4x 0.4x 0.5x 57 8.00% 23.3% 7.6% 11.3% 14.6% 17.8%   8.00% 0.5x 0.3x 0.3x 0.4x 0.5x 58 8.25% 20.7% 4.6% 8.4% 11.9% 15.1%   8.25% 0.5x 0.3x 0.3x 0.4x 0.5x 59 8.50% 18.1% 1.7% 5.6% 9.1% 12.4%   8.50% 0.5x 0.3x 0.3x 0.4x 0.4x 60 8.75% 15.5% -1.2% 2.8% 6.4% 9.8%   8.75% 0.5x 0.3x 0.3x 0.4x 0.4x 61 9.00% 13.0% -4.1% 0.0% 3.8% 7.3%   9.00% 0.5x 0.2x 0.3x 0.4x 0.4x 2 Hotel IRR Analysis  
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The sensitivity analysis output tables for my copy of Excel 2003 do not include the Objective Coefficient and the Allowable Increase/Decrease columns. Also, I get Reduced Gradient in place of Reduced Cost, and Lagrange Multiplier instead of Shadow Prices.

I have Excel 2003, Small Business Edition, which is not supposed to be a "reduced" version (at least that's what I paid for). So I'm not sure why this is happening.

Any way of causing Excel to give the same output tables that I see on other versions of Excel?

Hi,

I have a derivative pricing sheet, with pricing on sheet 1 and sensitivity analysis on sheet 2. I do the sensitivity analysis with Excel's data tables.

Problem is, there are 12 tables and the pricing sheet takes forever to calculate because of the calculating tables in sheet 2.

How can I prevent these tables from calculating?

How does one reference a range on different worksheets? Here's what I tried that didn't work:

Range("Sensitivity Table!B23:Sensitivity Table!P24").Select

How do you make a 1-way verticle table?

Where does the output cell have to be?

Do all the input cells need to be on the worksheet where the sensitivity analysis is being done?

Do the input cells need to be 'hard-coded' or can they be linked from different worksheets?

Thanks for all your help.

I'm having trouble writing a name checking VBA code where case sensitivity is removed.

Given:
uName is a string
Worksheet 1, A1 = Cow

uName = cow

if uName <> Sheet1.A1.Value
do something

For some reason, case sensitivity matters. If i changed it to lower case C, it would work. I'm assuming excel changes the letters into values. Maybe I should set uName as an integer? I have no idea. Can someone help?

Thanks

We as well as know, to use from sensitivity analysis by excel, we should enter the range of an independent
variable into a column and then enter one or two dependent variables (requested result) into other two columns.
Now, my question is:
How can I enter 6 independent variables to find the results for one or two dependent variables?
Is it possible by using of excel formula or I need to use VB code?
If I need the VB code, could you please give me the VB code?

Best regards
Reza

I'm building a sensitivity table, and I want to know if there is any formula inside of excel that will accomplish the following:

Let's suppose cell A1,B1 are inputs that drive a whole bunch of other formulas and then returns a value in D1

How do I create a formula that lets me put in E1,F1, G1, etc. what the value of D1 would be IF A1 and B1 took several different values?

so lets say (for a VERY simplified example) that D1 = A1 * B1. Currently A1 = 2 and B1 = 3. therefore, D1 = 2*3 = 6

How do I put a formula into E1 that returns what D1 WOULD equal if A1 and B1 took different values.

Hello,
Is there any compelling reason to use a 2 way data table for a sensitivity analysis rather than an array formula?
Thx
b

Suppose that you start with 5 less units of transportation (18 instead of
23). How much would you be willing to pay to get back those 5 units?

Shall I come up with a new sensitivity report where my Transportation constraint is equal to a maximum of 18 units? Or I can find the price I would be willing to pay from the original sensitivity report?

I attached the excel file in Zip format.

Many Thanks in Advance.