Whatif analysis

Post date: Apr 1, 2013 9:35:38 PM

What if whatif analysis?

Sometimes it is also known as sensitivity analysis is a tool and way to calculate value of a formula in Microsoft Excel or OpenOffice based on different scenarios (different values). For an example if unit price of an item is 100 and you have a table with unit price, number of items and total value of all the items, you can create different scenarios to calculate total price based on different number of items.

Unit price

100

No of items

1

Total value

100

Using whatif analysis you can

    • Analyse results based on different values of changing variables for example cost of travel based on miles and or type of vehicle used etc.

    • If you want to see how will you get a desired results you can use Goal Seek

    • See the effects of one or two variables on a formula using data tables.

    • Prepare forecasts and advanced business models

    • It is different in Microsoft Excel and OpenOffice, here I am presenting an example in OpenOffice and after that I will give some links for Microsoft Excel examples

Whatif analysis in OpenOffice

In OpenOffice spreadsheet following are the cells: Select cells with values 200, 50 and calculated value 100000 and,

Go to Tools>Scenarios as shown bellow (Note that Scenarios option will not be enabled if you select only one cell, atleast two cells should be selected, I have selected three in a row):

Clicking Scenarios will show a dialog box as shown bellow. Here you can change name of your scenario. I have changed suffix in default name as 50 users so full name became "Platform cost_50 users"

You can also select color for this scenario, I have selected blue.

Click OK, spreadsheet cells which were selected earlier will be surrounded with a border and row above value cells will have your scenario name with the background color you selected. There is a drop-down in this row. There is no magic till now.

Now in the same row change the value you want to change, for example I have changed value of column B as 100.

Value of cell C will change because of formula you have in it not because of anything you did in Scenario. Now select the row (value cells C, B and C). Go to Tools, click Scenarios, a dialog box will appear; change the name of this new Scenario. I have given it Platform cost_Scenario_100 users:

Now click on drop-down and you can see two scenarios. By clicking on any of the scenarios you will get calculated value on cell C. Actually whatever you type in this row and keep creating the scenarios you will only get that. It is saving various scenarios in a drop-down kid of thing.

Whatif analysis in Microsoft Excel

What if analysis tool in Microsoft Excel is bit different.

    1. Take same table (three columns), select the cell or cells (even a single cell can be selected) and

    2. go to Data>Whatif Analysis>Scenario Manager.

    3. A new dialog will appear in which you can add a new scenario by clicking "Add".

    4. Clicking Add will show a new dialog and you will have to give a name to your scenario,

    5. select changing cell (by default the cell which was selected earlier is selected),

    6. click OK>

    7. another dialog "Scenario values" will appear.

    8. Put the value for this scenario and click OK

    9. Keep adding values by repeating steps 4 through 8 for as many scenarios you want to create.

    10. Now for each scenarios you can see the value by clicking on Show button on Scenario Manager

    11. If you click Summary button on Scenario Manager you will get another dialog to choose either Scenario summary or Scenario Pivot Table report.

    12. Scenario summary will show a table with values for all the scenarios.

    13. Pivot table report will also show a table with changing values and corresponding calculated results in different rows.

Examples of Microsoft Excel whatif analysis:

http://www.youtube.com/watch?v=n9MdkxnE_CA

http://www.youtube.com/watch?v=ZyehZAbj9Iw

http://helpdeskgeek.com/office-tips/excel-what-if-analysis-tutorial/