Constructing 3Dimensional or 3 Variable Sensitivity in Excel based Models

We know Date Table feature (What-If Analysis button, and then click Data Table) available in MS Excel for creating one- or two-variable based sensitivity. It may be of interest to evaluate the Model output sensitivity with more than two variables.

Following approach can create same in Excel.

Let’s take example of a simple Financial working which estimates NPV of free cash flows basis inputs highlighted in green and blue fill color and simplistic computation. Inputs in blue fill color are cells used for performing sensitivity analysis. 

Data Table feature available under What-If Analysis button can provides sensitivity of Model output (which is NPV in current case) for max two variables.

Following approach provide sensitivity of output NPV using three variables i.e., 1) X - Product Growth Rate, 2) Y – Tax Rate and 3) Z – Discount Rate:

1.  Data Table syntax take inputs in form of Row input cell reference and column input cell reference.

2.  Present approach is to provide input syntax as combination of multiple inputs.

3.  We can integrate X - Product Growth Rate and Z – Discount Rate as Row input cell reference to generate new key which will act as modified input to Data Table function.

4.  Following steps integrate existing multiple input variables into modified unique input: -

a.  Arrange existing input variable for creating appropriate format for Data Tables

b.  Identify largest input variable (40% discount rate for current discussion) and convert same from decimal numbers into Binary number (as discussed in following step) and the compute length (or Places) of largest input variable. Example 40 in Decimal format is 101000 and length of same is 6.

c.  Convert all existing input data series, X - Product Growth Rate and Z – Discount Rate from decimal format to Binary format (using DEC2BIN function). Example, 20% sales growth rate after multiplication with 100 provides integer number 20 and on converting to binary becomes 010100.

d.  Concatenate Binary keys of X - Product Growth Rate and Z – Discount Rate

5.  Create two variable Data Table for modified inputs of Concatenated series and refer cells highlighted in red font in Model image for Row and Column input cell.

6.  Output of Data Table using modified inputs may be rearranged in 3-dimensional format

Once data table is rearranged, same may be dissected for creating meaningful interpretation

We can also use Decimal to Octal or Decimal to Hexadecimal format instead of Decimal to Binary format as discussed in above approach