Two-variable data table is an excellent tool in excel to perform sensitivity analysis with two ranges of possible input values. It helps in getting output for dual range of input values for given formula. This avoids repeating the formula for each of the range of input values and above all it's dynamic to use.
The formulas in a two-variable data table refer to two input cells and shows corresponding output. We can insert a range of values for the Row Input Cell across the first row of the table and range of values for the Column Input Cell down the first column of the table.
Also read: How to create One-Variable data table in Excel 2013
Also read: How to create One-Variable data table in Excel 2013
Let's straight away jump into an example of costing to understand it better. The example given here is used to calculate the Total Cost of the company for two variables i.e. Number of units produced and Variable cost per unit (assuming fixed cost as constant).
For example, the fixed cost is $1,000 (cell B2), number of units is inserted in cell B3 (say 500) and variable cost per unit in cell B4 (say $2.5 per unit)
Output calculated as: [Fixed_Cost]+[Units_Produced*Variable_Per_Unit] OR = B2+(B3*B4)
The final output derived for this is in cell B6 ($2,250).
Now, the two-variable data table is useful when you want resultant Total Cost for different number of units produced and different variable cost per unit (sensitivity analysis). Let's insert the range of units ranging 100 to 1,000 in column C in the cell range C10:C19 and similarly range of Variable cost per unit 1.0 to 5.0 in Row 9 in the cell range D9:L9.
Following steps will be useful to create two-variable data table that will give Total Cost for different inputs of Units produced and Variable cost per unit:
For example, the fixed cost is $1,000 (cell B2), number of units is inserted in cell B3 (say 500) and variable cost per unit in cell B4 (say $2.5 per unit)
The final output derived for this is in cell B6 ($2,250).
1. Copy the formula for calculation (originally in cell B6) into cell C9 (at the intersection of row and column of inputs) by typing =(equal to) and then clicking cell B6 (to create formula reference for your output)
This enables the table function to understand what formula to apply on the input cell in order to get the output.
2. Select data range C9:L19.
This range includes input variables, the formula linked in step-1 and range desired for output variables.
3. Go to Ribbon. Click...Data--->What-if Analysis--->Data Table [ Shortcut: Alt+AWT ]
This will show a Data Table dialog box on the Excel screen.
This will show a Data Table dialog box on the Excel screen.
4. Click on the Row Input Cell text box in the dialog box and then select cell B4 by clicking it (since the input values of "units" is inserted in row). Similarly, click on Column Input Cell text box and select B3
This will insert absolute cell reference $B$4 of the formula into Row Input Cell text box and $B$3 of the formula into the Column Input Cell text box of the dialog box.
This will insert absolute cell reference $B$4 of the formula into Row Input Cell text box and $B$3 of the formula into the Column Input Cell text box of the dialog box.
5. Click Ok.
On clicking "OK", the dialog box will get closed. On minute look into the formula bar, the cell range C9:L19 will show TABLE function.
On clicking "OK", the dialog box will get closed. On minute look into the formula bar, the cell range C9:L19 will show TABLE function.
6. Copy format to the output range by Format Painter.
Alternatively, copy from original output (cell B6) and paste format into output range
[Shortcut: Alt+EST]
Alternatively, copy from original output (cell B6) and paste format into output range
[Shortcut: Alt+EST]
- The output cell range (here C9:L19) will show TABLE function with formula cell (B4,B3) inside {} brackets. This means it is an array. Hence, you cannot delete part of this range (will throw error while doing so). To delete or clear the content it is necessary to select entire range.
- The input ranges are dynamic. If you change the input variables, it will automatically change the output variable.
- If the excel is in automatic mode, all the data table gets updated each time you refresh or save the excel workbook (and this may be irritating sometimes..!!). In order to prevent Excel from updating these data table until we Refresh (F9) or Calculate sheet (Shift+F9) on the Formulas tab,
Go to File--->Options--->Formulas--->Workbook Calculation--->Automatic except for Data Tables
[Shortcut: Alt+MXE]
[Shortcut: Alt+MXE]
OR Go to "FORMULAS" tab >>> Calculation Options >>> Automatic except for Data Tables
More interesting articles on this website:
No comments:
Post a Comment