Home → Techniques and Tips → @RISK Simulation: Numerical Results → Regression Coefficients in Your Worksheet
Applies to: @RISK for Excel 5.x–7.x
The tornado diagram shows sensitivity of a simulated output to each input in units of standard deviation. Can I get the actual regression coefficients?
You can do a calculation from the coefficients that are displayed in the tornado, as explained in Interpreting Regression Coefficients in Tornado Graphs.
You can also use a worksheet function to obtain the regression coefficients directly, with no need for further calculation. The function is RiskSensitivity( ). In the function, set the fifth argument to 3 (result type = equation coefficient). @RISK will then return the actual coefficient that would appear in a multiple regression.
Example: Suppose you're interested in the sensitivities of the output in cell A1. Then the function
=RiskSensitivity(A1, , 1, 1, 1)
will tell you the name (fifth argument = 1) of the input that has the largest impact or highest rank (third argument = 1), and the function
=RiskSensitivity(A1, , 1, 1, 3)
will tell you the unscaled regression coefficient (fifth argument = 3) of that input for the output in A1. For instance, if that RiskSensitivity( ) function returns 0.72, it means that a one-unit increase in that input corresponds to a 0.72-unit increase in the output.
Technical note: The rank number (third argument) can be anything from 1 to the number of @RISK inputs in the model; if it is too large the function returns #VALUE. However, @RISK only returns sensitivities for the inputs whose coefficients are significantly different from zero (to a maximum of 100 inputs). For all other inputs, @RISK returns zero as a coefficient.
Beginning with @RISK 6, you can also get the constant term of the regression equation, by setting the RiskSensitivity( ) function's fifth argument to 4 (result type = equation constant). To find the regression constant in older versions of @RISK, please see Regression Equation from Calculated Sensitivities.
The attached example shows both types of regression tornado graphs, with (scaled) coefficients and with mapped values. It also shows how to use worksheet formulas to get those two plus the actual coefficients of the regression equation, including the constant term.
See also: All Articles about Tornado Charts
Last edited: 2017-06-09