How to create One Variable Data Table using What-if-Analysis?

One Variable Data Table

To create a one-variable data table, you enter a series of input values either across columns of a single row or down the rows of a single column. The formulas in a one-variable data table refer to only one input cell: a row input cell for input values entered across a row or a column input cell for input values entered down a column.

Example: Say you need to find out the projected sales for year 2013. For this you have total sales value of 2012 and expected growth percentage in year 2013.  To get the projected sales for 2013 you apply a formula which includes the above value and factored in.

Now to find out if the total projected sales varies from 14% to 17% then how much will be the total sales for 2013 (with respect to the growth above percentages)? That is, to perform what-if analysis if the sales varies from 14% to 17%.

Step 1: Create a small table with your available values and apply formula {=$B$2+(B2*B3)-(B2*B4)} in cell B5. You can apply any formula. Data table in one variable only if a formula is used to find out the required value.

 

Step 2: Copy the original formula entered in cell B5 into cell E1. The copy of the original formula (into which the series of different growth rates in D2:D5 is to be substituted) is now the column heading for the one-variable data table.

Step 3: Select the cell range D2:E5. The range of the data table includes the formula along with the various growth rates.

Step 4: Choose What-If Analysis => Data Table in the Data Tools group on the Data ribbon. Excel opens the Data Table dialog box.

Step 5: As the different growth rates are in column so click in the Column Input Cell text box and then click cell B3. Excel inserts the absolute cell address, $B$3, into the Column Input Cell text box. Click OK. By doing this Excel calculates the projected sales value in column E.

 

 

 

 

 

So column D is the different expected growth percentages and column E is having the projected sales value with respect to different percentages.

 

For more details on this write to samrat.biswaas@gmail.com