What-if-Analysis for Excel Part 2- Data Tables

What-if-Analysis

By using three Excel what-if analysis tools, you can experiment with different sets of values in your driver cells and explore how they affect the outcomes in different formulas. In the first part we discussed Goal Seek for Excel and in this article we will discuss Data Tables for Excel , if you missed the first part of What-if-Analysis goal seek for excel please go through it using the link below

http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-1-goal.html

What are data Tables for Excel

Data tables allow you to see the impact of one or two drivers on the output of a model, and display the results in a single table. Data tables are used to conduct a sensitivity analysis.

Using Data Tables

You can create data tables with one or two variables. Data tables must be on the same worksheet as the drivers themselves.I am going to talk about the two variable type as that is more frequently used for sensitivity analysis

The problem that we are going to solve is discussed below-

Where quantity is quantity sold , Unit Price is the cost to make each item , cost price is selling price . Revenues is Quantity * Cost price , CoGS(cost of goods sold) is -Quantity* Unit Price , fixed cost is some cost to produce items. Assuming we dont have any other expense or profit the

Net Profit=Revenues + CoGS+Fixed Cost

Now what we want to see how does the Net Profit change if we change Cost Price and the quantity simultaneously. This could be done by using Data Tables for Excel.

In a double variable table, you use both column and row oriented variables.

1.Create a link to the output cell: in a blank area of the worksheet, click in a blank cell and type ‘=‘, then click the desired output cell and press[Enter]. In this case we would link it to the Net Profit

2.Create a list of different input variables to run through the output formula, directly beneath the output cell and another list of input variables directly to the right of the output cell.

3.Highlight all of the cells that contain the formula reference and the variables

4.Click the Datatab, click What-if-Analysis, and choose Data Table. The Data Table dialog box is displayed.

5.Enter the Row input cell (i.e., the cell into which you want to substitute the row-oriented variables) . In our case it is the Quantity. Tip:You can click outside the Data Table dialog box and click the appropriate driver cell and it will be automatically populated in the Row input cell field.

6.Enter the Column input cell (i.e., the cell into which you want to substitute the column-oriented variables).In our case it is Cost Price.

7.Click OK 
   The impact of the two variables on the output cell is displayed in your data table.
     Note:If your table values are all the same value, your Optionsettings may require you to press [F9]to recalculate Data        Tables.

The Final output shows the relation between cost price and the quantity . You can also see the trend both follow by using Conditional formatting. The data table after creation can also be edited I can change the cost price from 6 to 7 and the change would be instantly reflected we don't need to update the table manually . Data tables are really helpful when we are seeing the impact of two variables on the third variable.We can store each change on variable separately by creating scenarios using Scenario Analysis which we will discuss in the next part .

For using Scenario Manager in Excel use the link-
http://manishexcel.blogspot.in/2015/09/what-if-analysis-for-excel-part-3.html