PHA Risk Matrix Customization in our What-If Spreadsheet

Customize the risk matrix within our What-If Automated Excel Spreadsheet with detailed step-by-step instructions.

Would you like to change the Risk Matrix in our What-If PHA Spreadheet?

Perhaps, you would like to accomplish the following?

  • Change the color scheme?
  • Switch the alpha axis to numeric; so that, both are numeric?
  • Change the calculated Value to a numeric product of the x and y-axis?

All of this may be accomplished easily with some very basic VBA programming or Excel macro skills and the following set of instructions.

Here are instructions for the risk matrix customization scenario outlined above:

  1. An important preliminary note: existing "w-if-..." worksheet line items will not be updated by these risk matrix revisions. Only subsequently assigned questions will result in "w-if-..." line items containing the revised risk matrix and calculation. This behavior is due to conditional color formatting and risk matrix data that is copied initially from the hidden template worksheet when a question is assigned to a "w-if..." worksheet.
  2. Let's start with the Matrix worksheet to plan our revisions. Here is the default Risk Matrix chart, Likelihoods chart, and Assessment Values chart.
    Default Risk Matrix Likelihoods Assessments
  3. These are the revisions we performed for planning our new risk matrix. Note, only the Risk Matrix and Assessment Values charts were changed. The Likelihoods chart remains the same.
    Default Risk Matrix Likelihoods Assessments
  4. Next, we move on to changing the actual Risk Matrix data for the x and y-axis. These values are used to populate the dropdown lists, Likelihood and Severity, used in the risk calculation of each line item/question within a "w-if-..." worksheet. They are also used in the Template hidden worksheet. Please see the Lookup Tables worksheet for the following two tables. You may need to horizontally scroll to find the tables within the worksheet. Below are images of the default tables.
    LikelihoodSeverity
  5. These are the revisions we performed. Notice we performed significant edits to both tables.
    LikelihoodSeverity
  6. Next, we need to modify the conditional formatting used for dynamically setting each Risk cells' color. This conditional formatting is stored in the Template worksheet that is typically hidden. You may right-click on any worksheet tab, choose Unhide and then, choose Template to unhide the worksheet.
  7. After unhiding the Template worksheet, please select the Risk cell and choose Conditional Formatting -> Manage Rules from the Styles section within the ribbon. Here is an image of the default rules along with the Edit dialog displayed for the first rule.
    Orig RulesEdit Orig Rule
    Note, the above formula refers to a named range, risk_red_values, we will not use these default named ranges within our modified version.
  8. Next, we modify each conditional formatting rule to achieve our new color scheme based on the cell's value. Here is an image of the revised rules along with the Edit dialogs displayed for all three rules.
    Orig RulesEdit Orig RuleEdit Orig RuleEdit Orig Rule
  9. Plese Hide the Template worksheet when finished with the modifications.
  10. Finally, within our workbook VBA project, we need to edit the formula for assessing the Risk value based on the Likelihood and Severity. We go to the Developer tab, click the Visual Basic button within the ribbon, and then we should see the workbook VBA project that should look something like this when expanded.
    VBA Project
  11. Next, double-click on Module1 under modules and locate the following function GetRiskMatrixValue(). This is an image of the original function that concatenates the Likelihood (alpha value) and Severity (numeric value).
    GetRiskMatrixValue
  12. Here is our revised function that multiplies the Likelihood and Severity (x and y-axis) numeric values and returns the product as the Risk value. Note, I simply "commented out" the original code. See below.
    New VBA

 

Visit the product page for our What-If Automated Excel Spreadsheet