How do I do sensitivity analysis in excel 2007?

Sensitivity analysis in Excel 2007

1. Set up your model or calculation in Excel.
2. Identify the variables that you want to analyze.
3. Create a table that lists the possible values for each variable.
4. Create a formula that calculates the outcome of your model or calculation based on the values in your table.
5. Use Excel's built-in Data Table feature to perform the sensitivity analysis.
6. Specify the range of cells that contains your table of variable values and the cell in your formula that references the variable you want to analyze.
7. Excel will automatically calculate the outcome of your formula for each value in your table of variable values.

Performing sensitivity analysis in Excel 2007 is a powerful tool that allows you to explore how changes in certain variables affect the outcome of a particular calculation or model. This could be a financial model, a budget, or any other type of calculation that involves multiple variables. By following these steps, you can easily gain valuable insights into your calculations and models.

Lets say that you are looking at NPV and IRR (a financial example). You can
place the NPR and IRR on the horizontal axis (x axis). Lets say these go in
C14:D14. Make sure these are references to the cells where you actually
calculated the NPV and IRR, based on the cash flows generated by the project
that you are analyzing! Hard coding these values will not work in your data
table/sensitivity analysis. Then, you can place numbers corresponding to a
growth rate on the vertical axis (y axis). Lets say these go in B15:B20.
Starting at 0% and going to 25%, in increments of 5%, may be a good idea.
Then click on B14 (the empty cell between your x & y vales). Move down and
right so that B14:D20 are selected. Then click, Data, Table, Column input
cell (this should be your growth rate, whatever cell it is on your
spreadsheet), Ok. Your data table should populate with all relevant
information. Write back if you get stuck somewhere.
--
RyGuy


"Kat" wrote:

How do I do sensitivity analysis in excel 2007?

You are free to use the free Sensitivity Analysis Add-In Macro provided at http://www.life-cycle-costing.de/sensitivity_analysis/ for private and/or commercial projects.
It allows for up to 20 input and up to 20 output values to be varied / observed at a time. Either being varied separately (one input at a time) or in all possible combinations of all inputs.
Optionally, you can also select to search for any zero crossing of the respective output value, since such break-even or loss/profit churns are often of interest anyway.

Regards
Thomas