What If Analysis in Excel 2007

[David]

I have the following Data Inputs

Capital Expenditure C1
Cost of Borrowing C2
Income C3
Expenses C4
Discount Rate C5

These inputs are used to generate a project discounted cash flow and a
amortization table

I then calculate perormance indicators such as
NPV
Profitability Index
Breakeven ratio

I the want to create three scenarios 1) Capital Expenditure increase by 10%
2) Income decrease by 10% and 3)cost of borrowing increase by 1% and would
like to see the effect it has on all the performance indicators.

Q1) When you click on the changing cells option in the what if analysis can
you use a formulae to say C2*-0.1
2) Is it possible for your scenario results to change automatically should
you change the data inputs

 

[Monkey-See, Monkey-Do]

I'd recommend doing this as a table with the column headings as normal +10%
-10% or whatever else and then just duplicate the columns, alternatively you
could have each of the scenarios as a different tab at the bottom of the page
if you didn't want to see the whole table

 

[David]

Would this not be far too much of work as I would have to repeat the whole
excercise again, it has about 15 inputs with about 30 outputs, I would like
to use the what if analysis in the data tab, click on the scenario manger,
tell it which cells I want to change and link that to a formulae and then
choose the result cells which would be the performance indicators. This
spread sheet is going to be used as a finacial analysis tool, so the users
will only need to enter their data in the data sheet and everything else will
be automated including the scenario analysis, is it possible to automate
scenario anlysis

 

[Monkey-See, Monkey-Do]

It would take about 2 mintues of work!?

The user would be able to just insert the root data and the other data would
be calculated and populated...

You might be able to do it with "what if" but I've never used that. As far
as I'm aware you can't enter cell identifiers into the changing values fields
so it's not much use for formulas

I suggest getting your main sheet working and then copying the whole sheet
for the number of times you have scenarios. Then change the values you've
identified in column C (link them to the front sheet e.g. =Sheet1!C1*1.1 for
plus 10%)

Rename your tabs to match the scenario type and viola. All you have to do
is change the values on the front sheet and all the other scenarios will
automatically calculate.

You might even copy those values back to the first sheet in a summary table
and then you don't even have to change tabs to see the info...

 

[Bruce_NC]

Here is a link that will help you create scenarios that will automatically
show.

http://www.contextures.com/xlScenario03.html

 

[Mike Middleton]

David -

Another popular way to investigate sensitivity is to perform single-factor
sensitivity analysis and show the results in a tornado chart or spider
chart. Examples of Excel add-ins for automating this process are available
from www.treeplan.com (SensIt) and www.palisade.com (TopRank). These add-ins
handle sensitivity analysis of a spreadsheet model with multiple inputs and
a single output. Of course, you can run the add-in several times, once for
each output of interest.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[David]

None of the suggestions have helped unfortumately, is it possible for me to
email my spreadsheet so you could see exactly what is required

 

[Mike Middleton]

David -

The automation you seem to be seeking will likely require a custom macro,
e.g., VBA.

Based on the feedback you have received so far, I suggest you rethink
carefully what you want, redescribe your situation, and then post in the
microsoft.public.excel.programming newsgroup (assuming you want to consider
a VBA solution).

- Mike