Scenario Analysis In Excel 2007

Excel spreadsheets (.xls)

One of the more interesting features in Excel 2007 is the ability to quickly get decent analysis for different scenarios. Take an interesting case where I have this data for company XYZ. I’m asked to calculate the Net Present Value of this project/company over 5 years using that data. I will create a second table below with the data. By doing this, I will be able to easily see the impact of changing my assumptions. Here is what it looks like:

Most of the data in the tableis fairly straightforward to calculate. Unit sales are given in the example and increase by the sales growth number (C6) every year. Prices are also given with growth rate. The interest rate is necessary to calculate the NPV obviously (you can get all formulas and data in the spreadsheet, link is at the end of this post).

Basically, as you can see, the NPV in my current scenario is $134K or so.

Let’s suppose that for a few of these assumptions, the estimate is very uncertain. In my example, let’s suppose that the sales growth could change quite a bit. One thing that I could do is build different scenarios where I will change both that and the cost growth. I will name the scenarios. How do I do it? I will go into “Data/What-if-analysis”

Then, I select scenario manager and I can create different outcomes. How? I simply click “add”:

Then I can name that scenario, select the cells that I will be changing (ideally the same ones as other scenarios):

Then, I will be asked for those values:

Once I’ve build all of my scenarios, I can click on “summary” and then see the results:

Here is the result that I get:

I could then create charts, etc It can be incredibly powerful.

You can download my spreadsheet here for a better look at my scenario analysis.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************