Excel 2003 does not use the overcharged term: “sensitivity.” Having avoided this trap, Excel falls into another. It has so many features that fall under Sensitivity Analysis that one remains ignorant of how much Excel can do in this area and where these facilities are.
For example, there is an entry on the Data menu called Table. So what else is new? Excel is all tables. Wait, it does not get any better. Select the entry and you get a little dialog box that asks for a row and a column cell. Now what? Now nothing. What are these cells and what do they do? In reality, the Data/Table entry is a very powerful sensitivity feature (although with some limitations). Once you use it, you get addicted to it. It comes under different names that are also not mentioned by Excel as we will see.
Excel has 4 direct facilities to analyze sensitivity. I say direct because there are other indirect facilities which I will introduce below. Here is what is available on the menus:
- What If Analysis (One way table variation)
- What If Analysis (Two way table variation)
- The Scenario Manager
- The Solver
The first two are covered by the Data/Table entry.
The indirect sensitivity analysis that Excel can perform is not based on preset facilities. Rather, you would have to use a combination of formulations using some statistical functions in addition (optionally) to programming the formulation using Excel’s Visual Basic for Applications (VBA). Sensitivity analysis can then be carried out through Monte Carlo Simulation modeling which can do a lot more, as we will see.