When using Microsoft Project Online to plan, manage, track and control projects, project managers can benefit from using collaboration capabilities that are available in the platform. For each new project you start, there’s the option to set up a Project Site in which project artifacts — such as risks, issues and documents — can be created, managed, tracked and shared with people who are involved in that project initiative.
The risk management process involves taking proactive steps to identify and track events and/or conditions that have the potential to affect future planning for a project. Microsoft offers an useful article on the best practices for managing risks in Project Online.
Once project managers and team members start recording and tracking the risks that have been identified, the organization might start thinking about creating a report to help people make sense of all the risks that exist on the portfolio.
In this article, I share how I create risk matrix reports in Project Online using Microsoft Excel.
Step 1: Retrieve Data from Project Online
First, use Microsoft Excel to retrieve data from Project Online. Open Microsoft Excel. On the Data tab, click From Other Sources | From OData Data Feed. You can use the following URL string to retrieve the data that will be used on the report:
https://<YOURCOMPANYTENANTNAME>.sharepoint.com/sites/pwa/_api/ProjectData/Risks()
Follow the wizard until you’re prompted on how you want to view the data in your workbook dialog. At this point, I would recommend bringing the data into a PivotTable Report. In this example, I have organized my PivotTable to display the following columns: Project Name, Risk Title, Impact and Probability:
Step 2: Define the Criteria for the Layers
A risk matrix report combines two dimensions, impact and probability, to determine whether the risk is sufficiently controlled. Thus, the second step is to create the criteria for the layers we’ll be using in the report. In this example, I’ve created nine layers with the following nine combinations:
- High impact and low probability;
- Medium impact and low probability;
- Low impact and low probability;
- High impact and medium probability;
- Medium impact and medium probability;
- Low impact and medium probability;
- High impact and high probability;
- Medium impact and high probability; and
- Low impact and high probability.
Notice that the layers will be very important, because they’ll be used as the reference for counting the risks according to each possible combination. In Excel, create the layers as follows:
Step 3: Create the Report
Once you have created the layers that will be used on the report, in Excel add a new tab. Create your risk matrix table as follows:
You’ll then have to use the COUNTIFS function to count the number of items in accordance with the combination of impact and probability. Use the screenshots below as a reference:
- Place the cursor in the cell that represents the high impact vs. low probability combination. Start the COUNTIFS function. As the first criteria_range, use the Impact column from the risks list tab:
- While still in the COUNTIFS function, go to the next argument (criteria1). Here you need to determine which criteria will be applied to the first range that has been selected. As you use the Impact column, you’ll need to determine the criteria by using the information that has been organized in the layers:
This stipulates that for each row in the impact column, Excel will only consider values that are greater than or equal to 7.
- While still in the COUNTIFS function, move to the next argument, which will be represented by the criteria_range2. This time you will select the Probability column:
- Finally, you need to determine the criteria to be applied, which will be available in the layers combination:
You can close the function to see the result:
Now, repeat the steps for each possible combination in the risk matrix table to get your result:
The table above shows the distribution of all the existing risks that have been created in the existing portfolio, combining their impact and probability. Through this matrix you can easily identify how the risks are distributed. For example, we can see that the highest proportion of risks is concentrated in an area with medium impact and low probability (36 items), while the second highest concentration of risks hase medium impact and medium probability (20 items).
Layout Adjustments and Final Considerations
To make effective use of the available data, you can create additional visualizations to increase the understanding of your report. Here are a few suggestions:
Create charts to consolidate the information, such as “Risks by Category” or “Number of Risks Assigned by Resource.”
Add slicers to the report to allow people to filter the data. Here I used a slicer for “Risk Status” and another for “Project Name.”
Add conditional formatting to the risk matrix table, so that when the number of risks in each cell is equal to zero, the font receives the same color as the fill (to make the number zero “disappear,” which, because it’s not needed, is a good thing).
The final layout might look like this:
Remember that you can change the criteria for the layers to better adjust them to the risk management process that is in place in your organization.
