Data Analysis with MS Excel – Sensitivity analysis with the Data Table menu.

A sensitivity analysis shows how changes in certain values in a model will affect the outcome of the formulas in the model. For example, how changes in the volume of sales and variable costs rate will affect the net profit rate.
With Excel you can perform this analysis easily using the Data--->Table menu.
Suppose this simplified P&L report (download the Data Table example)


This will be the sensitivity analysis mentioned before:


You can build this table entering a formula in each cell, or, if you are an intermediate to advanced user, enter one array multi-cell formula.
Instead, you can use the Data--->Table command.
To build this Data Table with Excel follow these steps:
1 – Build a table, with a reference to the formula being evaluated in the let upper corner; values for one of the variables in the upper row and values for the second variable in the leftmost column of the table



2 – Select the table, including the blank cells (in our example select the range B12:G17)

3 – Activate Data--->Table from the menu. In the dialog that opens you have to enter a reference to the row input cell (Sales, B4 in our example) and to the column input cell (variable costs rate, C6 in our example).



Press OK and Excel will build the sensitivity analysis table automatically.

4 – You can hide the reference to the formula being evaluated (or the formula itself) in the upper left corner of the table, formatting the font with the same color like the background.

This example shows a two-variable data table. There are also one-variable data tables.

Categories: Data Management_

Technorati Tags: Data+Table

If you enjoyed this post add to del.icio.us