Sensitivity Analysisdata Table


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
It is hard to put all this in words so I think if you look at the attachment to see what I'm looking for. I did include all the details there.
Basically it is a Sensitivity Analysis table but I not sure if sensitivity table can even handle this problem.
If you have any other Idea on how to solve problem please let me know.
P.S. I know I can just do things manually and get it over but it is for management and It needs to be automated.
Thanks
Similar Excel Video Tutorials
Data Table Creates 100 Formulas
 See how to create a Contribution Margin Income Statement Analysis across various units using a 1 Variable Data Table (What if analysis). See how to cr ...
Similar Topics
I am having problems with the following code:
Code:
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
The statement I can write to A69 but get an error (subscript out of range) writing to B69. As near as I can tell both arrays, tb and tbo are dimensioned properly. Is there a problem with my code?
I have an excel model with more than 250 input variables and these input variables are used in more than 50 sheets for different calculations. I have to do a sensitivity analysis on all these variables (that is changing the input variables by a certain value and noting the change in output; change is output per unit change in the input value is the sensitivity). Doing the sensitivity analysis of these input variables manually is tedious and time consuming. Are there any excel based software or program that can do this kind of analysis automatically? Can someone please provide some insight? Thanks.
I'm performing my first sensitivity analysis for a life cycle cost estimate and all of my outputs are coming up the same. I believe my problem has something to do with the fact that B6 is just an inserted value, and not a formula; however I could be wrong.
Please see the attached dummy worksheet.
Thanks for the help!
I need to do a sensitivity analysis using Solver table. It is a one way table and the range of the base input values needs to be 20% to 40% with increments of 5%. When I attempt to enter 20% in the first slot, it says I must enter a numeric value in the box. I am not sure what I could be doing wrong? Any ideas? Thanks!
I am using cell I10 as my input for the solver table sensitivity analysis and cell b23 as my output. The values of input for the table are what I am having the issue with.
I am fairly new with the linear programming so be gentle...
~Rio~
Hi Im doing work on a Subdivsion and have made a sensitivity analysis to gain a range of end values based whether expenses and values are increasing/decreasing.
I have finished a table and it seems to work correctly, but am wondering for future use there must be a better way to set this up???
I have read Excel Help but havnt been able to get anywhere.
If anyone is able to help, that would be great!
Here is what I have done for now.
Subdivision.xls
Thanks
******** ******************** ************************************************************************>
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 'whatif analysis' tools for 1way 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 1way 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 swapins 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 1way 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 'hardcoded' 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.