Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Sensitivity Table

I need to create a sensititivity table (tables). I am at a loss. I know you can create one using the table function in excel (data-->table).

Here is what I have to do to the attached model. Can someone help me set up this sensitivity table? Need as soon as possible.

In FY2010 assume sales = $3 billion
For the following: 13-15% EBITDA (not EBIT) margin
what EBIT margin is that?
what EBT margin is that?

ALSO -- for interest expense line assume a $200-$250mm reduction in debt...what does that get you to in EPS? To figure change in interest subtract $200 and $250 x 7%

please make me sensitivity chart for each

These are my instructions. The "for each" == i'm not even sure how to set it up so i'm not even sure what it should look like. EPS is what I ultimately need to get to.

The weighted average interest rate at October 31, 2006 was 7.1%.


Post your answer or comment

comments powered by Disqus
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.

Hi,
I need help building a sensitivity table using my attached model.

What would first quarter 2007 EPS be (CELL D52) if total Selling, General and Administrative (cell D22 is the dollar amount with the increase showing in cell D65) was anywhere from 12 to 15%?

Need help asap if possible!!! Thanks

Good afternoon,

I've inherited the following code, which uses solver to create a sensitivity table for a given return in a derivatives model:


	VB:
	
 CommandButton1_Click() 
     'Yield Analysis
     'Application.ScreenUpdating = False
    x = Range("Correlation") 
    v = Range("SwapCur") 
    w = Range("DebtLTV") 
    Range("ValYLD").Value = 0.005 
    For i = 1 To 5 
        For j = 1 To 7 
            Range("ValYLD").Value = 0.005 
            Range("DebtLTV") = 0 
            a = Range("IRRung") 
            b = Sheets("Cashflow").Cells(35 + j, 4) 
            c = Range("SwapFX") 
            d = Sheets("Cashflow").Cells(35, 4 + i) 
            Range("SwapCur") = b 
            y = a * (((b - c) / c) * d + 1) 
            SolverOptions AssumeNonNeg:=False 
            SolverOk SetCell:="IRRNoSwap", MaxMinVal:=3, ValueOf:=y, ByChange:="ValYLD" 
            SolverAdd CellRef:=Range("IRRNoSwap"), Relation:=3, FormulaText:=0 
            SolverSolve Userfinish:=True 
            SolverReset 
            Range("DebtLTV") = Range("Gearing") 
            Calculate 
            Sheets("Cashflow").Cells(35 + j, 11 + i) = Range("IRRSwap").Value 
            Sheets("Cashflow").Cells(35 + j, 4 + i) = Range("IRRNoSwap").Value 
        Next j 
         
    Next i 
     
    Range("DebtLTV") = w 
    Range("Correlation") = x 
    Range("SwapCur") = v 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It seems that the code does not cope well when negative numbers are used as inputs, as the results it gives are unexpected and incorrect. I have fiddled with various elements but to no avail. Any comments can would be most apprecatied- I'm totally stuck.

All inputs are percentages. i= 1to 5 are always positive, j 1-7 percentages, centred around "SwapFX". It is these inputs, along with "swapcur" which are now negative, and previously have not been. Please do ask me for any further info needed- I apologise if this somewhat garbled.

Many thanks,

David

******** ******************** ************************************************************************>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)boutI7J7K7L7M7N7O7P7Q7D8E8G8J8M8P8=
CDEFGHIJKLMNOPQ3Sensitivity*Analysis*1*-*Terminal*Value*Approach*-*Growth*In*Perpetuity*Method4***************5***PV*of*Terminal*Value************6***at*a*Perpetual*Growth*Rate*of:**Enterpise*Value**Equity*Value*(EV*-*Net*Debt)**Fair*Value*Per*Share**7*WACCPV*of*FCF2.0%3.0%4.0%2.0%3.0%4.0%2.0%3.0%4.0%2.0%3.0%4.0%8From*Model*9.54%2,511.1*6,022.1**8,533.3**8,120.6**$20.58*9*8.00%*************10*9.00%*************11Sensitivity*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

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?

Hey all,

I'm trying to figure out how to make a sensitivity table based on new values.

example:
Table 1 is the sensitivity table in workbook 1.
Model 2 is a workbook with a set price and EBITDA

Step 1: I link a cell (A1) in table 1 to the "price cell" in Model 2, and another cell in table 1(B1) to the "EBITDA cell" in Model 2.
Step 2: I make a second cell (A2) in table 1 where I increase the price cell by 5%. (ex =A1*1.05)
Step 3: Here is my ? - i'd like to make a new cell (B2) in table 1 that would ask - if the "price cell" in model 2 = the new price cell in table 1 (A2), the "EBITDA cell" in model 2 would = ?

I hope this makes sense...

Thanks everyone!

I have an LBO model with Inputs/IS/BS/CF/schedules/Returns all on different excel sheets. I'm trying to run sensitivities such that I can change one cell on the input sheet (ie revenue growth rate) and see what effect that would have on a particular cell in the Returns sheet (ie..IRR). It's the equivalent of trying to run a data table across multiple sheets. Is there any function / plug-in / etc. that would allow me to do this automatically?

Thanks in advance,
Rushi

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

so i want to have a macro when executed will update a sensitivity table based on where my cell cursor is. How can I do this?

This is what I have so far but obviously the input cell is fixed to A1, how do i make it dynamic based on cell cursor. Thanks.

Sub Macro1()
'
'
Range("SensitivityTable").Select
Selection.Table ColumnInput:=Range("A1")

End Sub

{cross-posting from excel.programming because I think this is relevant
(maybe even moreso) here too.}

I have an Excel (2000) financial model whose bottom line values are
dependent on results pulled from a simple MS Query (3 of them,
actually, and together they take about 10 seconds +/- to run).

I'm trying to run a simple data table to see how those values vary by
changing a single assumption (that's what data tables are for).

In my data table, I have a single assumption (Fed Funds Rate) that
should affect the results of the query. This assumption itself is not a
parameter in the queries, however another variable calculated in the
model (that changes w/ FFR) is passed as a parameter.

The problem I'm having?

The query results are NOT changed in the various input variable (Fed
Funds Rate) scenarios I'm trying to depict - the results are held
constant and in so doing the data table is flat out wrong.

I've also confirmed 3 "likely suspects" are set up as they should be:
- Enable automatic refresh is activated on spreadsheet open when
prompted
- The parameter properties are set to "Refresh automatically when cell
value changes"
- Background refresh is NOT enabled in the query data range properties
(this actually doesn't make a difference checked or not)

Anybody have any guidance? Is it just not possible to use these 2
things (MS Query and data sensitivity tables) together? I suppose I
could cook up a macro to do this as a workaround; good thing I don't
have to do it in volume.

{cross-posting from excel.programming because I think this is relevant
(maybe even moreso) here too.}

I have an Excel (2000) financial model whose bottom line values are
dependent on results pulled from a simple MS Query (3 of them,
actually, and together they take about 10 seconds +/- to run).

I'm trying to run a simple data table to see how those values vary by
changing a single assumption (that's what data tables are for).

In my data table, I have a single assumption (Fed Funds Rate) that
should affect the results of the query. This assumption itself is not a
parameter in the queries, however another variable calculated in the
model (that changes w/ FFR) is passed as a parameter.

The problem I'm having?

The query results are NOT changed in the various input variable (Fed
Funds Rate) scenarios I'm trying to depict - the results are held
constant and in so doing the data table is flat out wrong.

I've also confirmed 3 "likely suspects" are set up as they should be:
- Enable automatic refresh is activated on spreadsheet open when
prompted
- The parameter properties are set to "Refresh automatically when cell
value changes"
- Background refresh is NOT enabled in the query data range properties
(this actually doesn't make a difference checked or not)

Anybody have any guidance? Is it just not possible to use these 2
things (MS Query and data sensitivity tables) together? I suppose I
could cook up a macro to do this as a workaround; good thing I don't
have to do it in volume.

I have an Excel financial model whose bottom line values are dependent
on results pulled from a simple MS Query (3 of them, actually, and
together they take about 10 seconds +/- to run).

I'm trying to run a simple data table to see how those results vary by
changing a single assumption.

In my data table, I have a single assumption (Fed Funds Rate) that
should affect the results of the query. This assumption itself is not
a parameter in the queries, however another variable calculated in the
model (that changes w/ FFR) is passed as a parameter.

The problem I'm having?

The query results are NOT changed in the various input variable (Fed
Funds Rate) scenarios I'm trying to depict - the results are held
constant and in so doing the data table is flat out wrong.

I've also confirmed 3 "likely suspects" are set up as they should be:

- Enable automatic refresh is activated on spreadsheet open when
prompted

- The parameter properties are set to "Refresh automatically when cell
value changes"

- Background refresh is NOT enabled in the query data range properties
(this actually doesn't make a difference checked or not)

Anybody have any guidance? Is it just not possible to use these 2
things (MS Query and data sensitivity tables) together?

I suppose I could cook up a macro to do this as a workaround; good
thing I don't have to do it in volume.

Regarding an Excel sensitivity I'm trying to compile, I have a model I built where there is one very important input and two very important outputs. I'd like to show a sensitivity that calculates each of the two important outputs for every given value of the one important input. Ex:

Input (given) | Output 1 | Output
x% | a value 1 | b value 1
y% | a value 2 | b value 2
z% | a value 3 | b value 3

I feel like there is probably a simple set up using the 'what-if' functionality, but it's escaping me for some reason. Any help would be most appreciated.

Thanks,

Charles

Hello,

I am reviewing a sensitivities table where for example in cell F43 there is the formula

Code:
=IF(AND(Case="Sensitivity Case",Units_2009=Units1,
ASP_2009=ASP1),'Monthly Model'!$N$295,Sensitivities!F43)
Obviously the formula refers to itself but it does not create a circular reference (Sensitivities!F43).

Any ideas on how this could work?

thanks
Andy

Hi,

I am trying to do a sensitivity analysis, but it is time consuming, chaning one number, and coping and pasting into a sensitivity table.

If you look at simple spreadsheet below, and if I had to respectively vary the following from -25% to 25%:

Transport per tonne
Salary per tonne
Production per tonne
Number of tonnes
Price per tonne

I will be able to see how Profit/Loss is affected.

How do i do this? Please help.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB8B12B14=
ABCD1Number of tonnes10 2 3Costs 4Transport per tonne5 5Salary per tonne6 6 Production per tonne6 7 8Total cost170 9 10Revenue 11Price per tonne40 12Total Revenue400 13 14Profit/Loss230 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 there,

I am trying to create a sensitivity table that is driven off the XIRR
function. I get some positive values, a neg. value and mostly #NUM!. Can
someone please help?

Hi,

With reference to the attached, there are 3 tables which basically reflect:
1. Quantity
2. Price change
3. Change in profits (P*Q)

I want to conduct a sensitivity analysis for the change in profits based on a fixed inventory and by hard coding manually changes in price. However, the Profit Changes need to reflect the relationships between two products.

A few queries: Does a Hlookup return a value? If so, is there anyway I can get it to return a formula instead? II tried a 2-variable data table but the single variable data table seems to work better.

Problem is, I can't get the "Benz-Lincoln" portion to reset; I tried running a data table for Benz-Lincoln and it worked. Then, I hid the columns and ran one for Buick-Hummer which also worked. But now when I want to run it again for Benz-Lincolm, I get the "Cannot change part of a merged table" error message.

What can I do in order to constantly re-evaluate the data table by just amending the period and inserting the Column Input cell?

Thanks very much!

Hello, All

Please gelp me, by question:
How i can do case sensitive grouping by field in pivot table.

Regards

I'm trying to do a sensitivity analysis of cashflow in a financial model by means of Data Tables. In the data table I run through different values of an input variable.

The model uses an iterative process (circular reference that is run through for a set number of steps or until steady state is reached) to calculate the interest cash flow. If I block the iterative process, the data tables work well, but obviously the interest rate is not calculated.
When the iterative process is activated, the data tables produce faulty results.

How can I do a sensitivity analysis of an iterative process to assess the influence of (over 200) input parameters ?
Is it possible to do this with an output format like data tables?

Best regards,
Jochem

ps. sorry if this is already covered in a thread (send me the url please?). can't seem to search the forum at the moment.
pps. is there any way to move this thread to the general questions area?

First let me say I know very little about VBA however I need to get something similar to the slicer effect for the non excel 2010 users. What I am trying to do is allow the user to select a date from drop down for min or even type in the date and then select or type a date for max then the pivot table will filter for the range between the 2 dates. Due to the sensitivity of the data I can't post the actual data but attached is a sample file trial date range.xlsm

Any Help you can provide would be greatly appreciated.

Hi all

I have a question regarding pivot tables.
I have attached a file along with this.

There are actually 3 data fields; Product, Region and Quantity.

Task:
I would like to put the information onto a pivot table. Appended in the attachment is only 1% of my data, actual size would be about 5000 records.

Question:
The data changes weekly.
(1) I would like to create a macro that idendify the range of data and then, create a pivot table. Just a click at the command button would actually activate this macro..

or

(2) I am wondering if the data changes, would the pivot table to sensitive enough to change as the data in the range changes? would it be dynamic?

Hi

I need a vlookup statement to be case sensitive but not sure how to do it.
I have a formula which needs to look up the result of another cell in a different table and then output the value next to it.
The look up table contains the letters A - Y and a - s and has a corresponding number which I need output.
The cell (E2) with the value I need to lookup does not remain constant and can change to any of the letters shown above.
Currently if I look for an 'a' for example it will always return the number associated with 'A'.
The formula looks like this:

	VB:
	
) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried to use EXACT but can't get it to work as it always returns FALSE. When I use exact this is the formula I use:

	VB:
	
)) ,0) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have also posted this query on another forum - link below
http://forums.aspfree.com/showthread...079#post125079
Please can you help?

I'm just learning VB, so I apologize if I do not describe this correctly.

I have a situation where I am generating a column of numbers based off of user input of a min, max, and increment number using ActiveCell Offsets and adding the increments. I also generate a number in column to that is offset as (-1,1) to the initial column's first number. I am planning to use this to do a sensitivity analysis on this table with a cell referenced elsewhere. What I'm creating looks like this:

......A..........B
1 .............500
2 1000
3 1005
4 1010


	VB:
	
 SensitivitySetup() 
     '
    Dim MinOutlay As String 
    Dim MaxOutlay As String 
    Dim Increments As String 
     
    MinOutlay = InputBox("Minimum Capital Outlay") 
    MaxOutlay = InputBox("Maximum Capital Outlay") 
    Increments = InputBox("Value of Capital Outlay Increments") 
     
    ActiveCell.Offset(0, 1).Formula = "=PortfolioAllocation!G6" 
    ActiveCell.Offset(1, 0).Value = MinOutlay 
    ActiveCell.Offset(1, 0).Select 
    Do Until ActiveCell.Value >= MaxOutlay 
        ActiveCell.Offset(1, 0).Value = ActiveCell.Value + Increments 
        ActiveCell.Offset(1, 0).Select 
    Loop 
     'Here I need to highlight the table, which is A1:B4 for example above, but can change dynamically based on 'the number
of times it loops and generates the offset
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I tried to highlight the table including the initial column 2 value (B1 in this case) using selection with xlUp, but I cannot get it to pick up the blank cell at the top of column 1 (A1). I cannot figure out another way to select every cell in the table regardless of the number of rows generated in column 1. What makes this more difficult is that the formula is based off of the current Active Cell, so I can't just assume the user will start the macro from A1.

Does anybody have a suggestion? I hope this question makes sense.

Thanks for your help!

I have recently discovered the "data table" tool on Excel and have found it to be extremely useful for sensitivity analysis. The Excel help describes how one can create 1 and 2 variable data tables. Now I would like to create a data table using three variables, however. (I would like to vary three different variables and see what effect they have on one formula.) Is this possible with Excel? Can pivot tables be used this way?

- Craig


No luck finding an answer? You could always try Google.