Email:      Pass:    Pass?
Advertisements


Free Excel Forum

Sensitivity Analysis-data Table

Forum Register
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

Helpful Excel Macros

Basic Web Query in Excel - Import Data from the Web into Excel
- Import data from the web into Excel with this macro. This is a basic web query macro that will import data from any web
Filter Data to Show the Bottom 10 Percent of a Data Set in Excel - AutoFilter
- This free Excel macro filters a data set to display the bottom 10 percent of the data set in Excel. This is a great mac
Sort Data With Headers in Descending Order in Excel
- This Excel macro sorts data that has headers in descending order. This means that data is sorted Z to A and 10 to 1 - o
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m

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 am looking for some help regarding sensitivity analysis tables (spreadsheet attached for convenience). On tab "WACC" the sensitivity analysis works properly. However, my two sensitivity analysis tables on tab "Output Page" aren't working correctly. The enterprise value is the same moving across the rows and all of the analysis on the other table is 3.0%. I have highlighted the cells being referenced as well.

Please let me know what I have done wrong and any tips for the future. These tables always give me trouble. Thanks!



I have an output from an analysis which is in the form of a table (array) and I would like to run a 2x2 sensitivity on the inputs that govern the table's values.
However, when I try to do this the output is only the upper left corner value throughout the table.

Is there a limitation on sensitivity analyses derived from a table?

Essentially, I'm making a table from a table...

Any help would be appreciated!



Greetings,

I am trying to put together a sensitivity analysis where I put two variables into a seperate spreadsheet and would like the output to be put in the intercept cell (yellow) in the table. For example, I want to take the 2.4% value from terminal growth rate and 8.2% from terminal WACC and plug into the cells of my valuation in the second image (the green and red cells) I then want to take the yellow cell, my stock price per share from my valuation and input it in the sensitivity analysis table. See the sheets below: I think it more clear visually.

Any assistance would be greatly appreciated.

Best Regards.

table.jpg

dcff.jpg

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 '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?




Hey

need help programming VBA for sensitivity analysis in a paper on portfolio management.

Task:
See how sensitive our portfolio weights are to changes in expected returns etc



best regards,
VBA - Beginner

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




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


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 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