Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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.


View Answers     

Similar Excel Tutorials

Enable the Analysis ToolPak in Excel
How to enable the Analysis ToolPak in Excel. This is an add-in program that comes with Excel and allows you to use ...
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 grea ...
Get a Table Look with Only the Formatting in Excel
How to get the nice formatting of a table without turning your data into an actual table. Formatting data as a tab ...
Sort Data Alphabetically or Numerically in Excel 2007 and Later
This Excel tip shows you how to Sort Data Alphabetically and Numerically in Excel 2007. This is a great feature to ...

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:


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!


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.



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

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.



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

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


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?


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

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.


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.


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

Many 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

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?


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.

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

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?

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

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.


Ive used data tables a fair amount in excel but given the time they take to recalculate (i have many in one s/sheet) im looking to create a macro rather which only runs when i choose to run it, but I've never really used macros before so am hoping for an easy example to get started.

Ive looked in the forum but cant seem to find an example of a simple macro which shows how to perform 'what-if' or sensitivity analysis on a variable calculated within a s/sheet - by assuming different values for a dependent variable also referenced in the s/sheet - so would appreciate any pointers.

Thank you.

p.s. im aware of the possibility of putting Calculation Options to 'Automatic except Data tables' but it is still very slow when one opens or saves the s/sheet and 'Manual' doesn't work for me as need rest of the worksheet to be dynamic.

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.