Applies to: @RISK for Excel 6.x and Up

Getting Actual Regression Coefficients from a Tornado Diagram

 

Overview

The tornado diagram displays the sensitivity of a simulated output to each input in units of standard deviation. While this is useful for comparison, these values are scaled and are not the actual regression coefficients.

This article explains how to obtain the true regression coefficients.

 

Methods to Obtain Regression Coefficients

There are two ways to obtain the actual regression coefficients:

• Calculate them manually from the scaled coefficients shown in the tornado diagram
• Use the RiskSensitivity( ) worksheet function to obtain them directly, with no additional calculations required

The second method is recommended.

 

Using the RiskSensitivity Worksheet Function

The RiskSensitivity( ) function can return the actual coefficients used in a multiple regression.

To retrieve regression coefficients, set the fifth argument (result type) to 3, which returns the equation coefficient.

 

Example

Suppose you are interested in the sensitivities of the output in cell A1.

To find the name of the input with the largest impact on the output:

=RiskSensitivity(A1, , 1, 1, 1)

In this formula:
• The third argument (1) specifies the highest-ranked input
• The fifth argument (1) specifies that the result returned is the input name

To find the actual (unscaled) regression coefficient for that same input:

=RiskSensitivity(A1, , 1, 1, 3)

If this 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 Notes

The rank number (third argument) can range from 1 to the total number of @RISK input variables. If the rank number is too large, the function returns #VALUE.

@RISK returns sensitivity results only for inputs whose coefficients are significantly different from zero, up to a maximum of 100 inputs. For all other inputs, the regression coefficient returned is zero.

 

Getting the Constant Term

You can also retrieve the constant (intercept) term of the regression equation by setting the fifth argument to 4 (result type = equation constant):

=RiskSensitivity(A1, , 1, 1, 4)

This returns the constant term used in the regression equation.

 

Additional Information

The attached example demonstrates both types of regression tornado graphs, including scaled coefficients and mapped values. It also shows how to use worksheet formulas to retrieve these values along with the actual regression coefficients and the constant term.

 

Related Articles:

Interpreting Regression Coefficients in Tornado Graphs

Regression Equation from Calculated Sensitivities

 

Attachments:
KB139_RegressionCoefficients.xlsx