Sensitivity Analysis

Zutroy

New Member
Joined
Sep 1, 2006
Messages
2
Hi,

I'm building a business case at the moment and am evaluating the investment using NPV.

What I need to do is complete a sensitivity analysis on the core drivers of the NPV (i.e. revenue, implementation costs, operating costs, etc.)

I have tried to used the data table feature but it makes no sense to me.

I am look to calculate and graph the effects at 20% - 200% at 20% intervals.

How can I use this data table to complete my sensitivity analysis?

Any help would be greatly appreciated
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Hi Zutroy
Welcome to the board

I I understood well you want to know how to use the feature Data>Table.

The Data Table is a very useful feature because it allows you to write a model just once and then to be able to test what happens to the results if a parameter varies without changing anything in the model.

Here is a simple example with one variable.

I'll use the following example from the help:

"Suppose you want to save money for a special project occurring a year from now. You deposit $1,000 into a savings account that earns 6 percent annual interest compounded monthly (monthly interest of 6%/12, or 0.5%). You plan to deposit $100 at the beginning of every month for the next 12 months. How much money will be in the account at the end of 12 months?

FV(0.5%, 12, -100, -1000, 1) equals $2301.40"

Now we want to know what's the impact of different monthly interest rates let's say frpm 0.45% to 0.55%.

First let's build the model. I did it in A2:B6. All constants except B6

=FV(B2, B3, -B4, -B5, 1)

Now let's see what happens if the montly rate changes

In E2:F2 I wrote the headers, "Rate" and "FV".

Now the important part.

In E4:E14 I wrote the values of the montly rate that I want to test. In this case I used a step of 0.1%.

In F2 I point at the result of the model.

=B6

Now I select E3:F14 and choose Data>Table

In this case I have my varying values in a column, so I leave the "row input cell" empty and tell the data table what I am varying, writing in the "Column input cell" tne address of the rate

$B$2

Now click OK. You'll get the result that you can see in my post, where you see the results for the various monthly rates.

Hope this is what you want.
PGC
Book1
ABCDEFG
1
2Rate0.50%RateFV
3Nper12$2,301.40
4Pmt$100.000.45%$2,291.04
5Capital$1,000.000.46%$2,293.11
6FV$2,301.400.47%$2,295.18
70.48%$2,297.25
80.49%$2,299.33
90.50%$2,301.40
100.51%$2,303.48
110.52%$2,305.56
120.53%$2,307.64
130.54%$2,309.72
140.55%$2,311.81
15
Sheet1
 
Upvote 0

Forum statistics

Threads
1,203,460
Messages
6,055,556
Members
444,797
Latest member
18ecooley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top