Easy Risk Management with Jira, Confluence, and Excel
Are you searching for a simple but comprehensive solution for managing risks? Do you have Jira, Confluence, and Excel at hand? If so, this article shows you everything about how you can build a lightweight but mature tool for managing your risks.[1] The best thing about it is that there is no need to overload Jira and Confluence with any installed add-on. This way, you can save time and money and deliver high quality.
The Basics
With the approach presented in this article, Jira’s role is to hold the data as a single source of truth. In Jira, we define and manage our risks by creating and editing issues. We then use Excel and Confluence to control the data and create reports based on the information inside Jira.
For this article, I created a dedicated Jira project for risk management in my Jira Cloud instance called RISKMGMT. This project holds the issue types Risk, Task, and Sub-task. Of course, we can also add these issue types to an existing Jira project. Or, we can add the custom issue fields that I will describe later in this article to any other issue type of any Jira project—there are plenty of ways.
However, in my example, I use the issue type Risk for describing and managing risks. The issue types Task and Sub-task help to make everything actionable, like defining and tracking actions of mitigation. Please read my article A Kanban Task Management Approach for Jira to learn how to use tasks and sub-tasks effectively. Therefore, the current article concentrates only on the issue type Risk. So, let’s start with the details of the Jira configuration.
Configuring Jira
Since Jira holds all the data and is our tool to work with actively, we must configure Jira in a way that is compliant with the risk log Excel tool I present later. Of course, we only use native Jira features.
The Workflow
Risks are issues of the issue type Risk, and they follow a simple workflow. The workflow’s statuses mainly represent which risk management technique we apply to a given risk. The following workflow is available for download in my GitHub repository “risk-log.”
I always use the risk management techniques of avoiding (AVOID), mitigating (MITIGATE), transferring (TRANSFER), and accepting (ACCEPT) risks — which is pretty much project management common sense. Since we can find plenty of information elsewhere, I will only briefly describe these techniques.[2]
Please note that you can access all statuses from every other status except ARCHIVED. This is important because we want to change the status to whatever is correct for a given risk, given the current situation, every time. Therefore, although we will use a Jira Kanban board to visualize the risks, this workflow differs from a flow-oriented Kanban workflow.[3]
The workflow statuses are defined as follows.
- NEW
We just created a new risk. We ensure it has a meaningful name (summary field) so that everybody, not just us, understands the risk. We use this status to analyze the risk for further details. - IN CLARIFICATION
The risk should be moved to this status whenever it is unclear which risk management technique we should choose. This shows that further analysis is needed for clarification. - AVOID
Risk avoidance is used when the consequences of a risk are deemed too high to justify the cost of its mitigating. Therefore, when we avoid a risk, we eliminate any effect or potential loss. This technique usually involves developing an alternative strategy that is more likely to succeed but is typically linked to a higher cost. - MITIGATE
Risk mitigation represents an investment to reduce the risk. Therefore, it’s often also called risk reduction, which means reducing the likelihood and severity of a possible loss. - TRANSFER
By transferring risks, we shift them between different parties that have the capacity to mitigate the risk. Therefore, risk transfer is a risk-reduction technique like mitigating. - ACCEPT
We accept the risk as it is—at least for a certain period of time. This gives us a chance to focus on more urgent risks. There may also be risks you cannot mitigate, transfer, or avoid but want to monitor. For those risks, you may choose this technique. - RESOLVED
The risk was resolved or became obsolete. The transition to RESOLVED status forces us to set a meaningful resolution in Jira. Ideally, we also add a comment stating more information about the circumstances. A transition to itself allows us to change the resolution even after it has been resolved initially. - ARCHIVED
I use the status ARCHIVED to indicate that a risk is entirely obsolete and out of sight. In the next section, I will describe how we can use this status to easily remove an issue from a Jira board.[3] However, like for RESOLVED, there is a transition to itself, allowing us to change the issue’s resolution.
It’s important to note that we assume every risk issue as a potential risk unless it’s set to RESOLVED or ARCHIVED.
The Board
Together with the workflow comes the board that gives us an immediate overview of how many risks we have and which risk management technique we have applied to which risk. A Kanban board is the perfect match for this task since we don’t have Sprints to work things off.
However, this is not Kanban—except for its visualization using a board. The idea of this board is to move issues around from one status to another, back and forth, as often as needed. So, this has nothing to do with the ‘flow’ of Kanban, where you only move items from the left to the right.[3]
The board design follows the workflow design. However, there’s a good practice in controlling what’s shown on the board. The following board filter sorts out all issues with the status ARCHIVED even though the status is mapped to the last column.
This means that the column “Move to Archive” allows us to clean up our board in a controlled way: as soon as we drag and drop an issue to the column “Move to Archive,” the issue disappears from our board.[4] The board looks like the following.[5]
However, every issue must be set to the status RESOLVED first to ensure that a proper resolution has been set. Housekeeping made easy!
The Issue Fields
In addition to the standard issue fields like Summary, Attachments, Linked Issues, or Labels, the issue type Risk should provide at least the following (custom) issue fields to store risk-related information. I will describe how to set up and use these fields afterward.
- Description
You describe in detail what the risk is about. - Likelihood
A measure of how likely a risk turns into a problem. This is a custom field I created. I discuss its values below. - Impact
A measure of the harm that could be caused if a risk turns into a problem. This is a custom field I created. I discuss its values below. - Priority (used for Severity)
The combination of Impact and Likelihood corresponds to a mathematical expected value. This field is optional since our approach uses an Excel sheet to calculate the severity out of impact and likelihood. There is no automation to set the Priority field accordingly. - Flagged
A visible representation on our Jira board shows which risk issues become a problem and cause a potential loss.
Using a Template for the Issue Description
Whenever we analyze something, we should write our thoughts in the most concise prose possible—and avoid bullet points. We should also have others check that the information is complete and understandable.
To take the first step toward completeness and clarity, I recommend using templates to fill in. A great template always covers the questions WHAT, WHY, HOW, and WHO. Thus, our description field should contain the following headlines.
- Current Situation (WHAT?)
We describe the current situation without describing its impact. - Problem (WHY?)
We describe what’s problematic about the current situation. This means describing the risk and its possible impact. Separating this part from the description of the current situation is complicated, but it helps to gain better insights. This method strengthens our train of thought. - Risk Handling (HOW & WHO?)
We describe what must be done to avoid, reduce, accept, or transfer the risk’s impact. We may also describe who or which party is responsible. - Contingent Action (HOW & WHO?)
This action must be taken once a risk has turned into a problem—thus, it is for flagged issues only. Therefore, we describe what must be done to turn the problem back into a risk. Also, here, it’s good to define who is responsible.
For example, a well-described risk looks like this.
Values for Likelihood
With likelihood, we define how confident we are that a risk turns into a problem. Of course, you should implement the levels in Jira using a single-choice dropdown field. I define the options as follows.
- Almost Certain
It is clear that a risk will soon turn into a problem. Regarding confidence levels, I always take 80% or higher. - Likely
With confidence between 50% to 80%, these risks will turn into problems. - Possible
Confidence between 20% to 50% makes a risk possible. - Unlikely
5% to 20% is an unlikely event. - Rare
From below 5%, it’s rare. - Unspecified
This should be set as the default value. Use unspecified as long as you have not decided the likelihood of a risk.
To be clear, thinking that we can precisely calculate the likelihood of an event is an under-complex thought. Therefore, I recommend relying on one’s gut feelings, taking the above percentages not too seriously, and having good discussions with colleagues regarding estimating a risk’s likelihood. And that is what it is: an estimate!
Values for Impact
With impact, we define the expected harm in case of occurrence, which means how bad it is when a given risk turns into a problem. The following impact levels provide orientation and should be implemented using a single-choice dropdown field. The options are as follows.
- Severe
One or more critical objectives cannot be achieved, and generated costs are exorbitantly high. - Significant
The outcome of critical objectives is significantly reduced. Generates high costs. - Moderate
The outcome of desired results is moderately reduced. Moderate costs. - Minor
There’s only a minor impact on the outcome of the desired results. - Insignificant
The risk has little or no impact on the desired results. However, this impact level is useful when documenting all identified risks. - Unspecified
This should be set as the default value. Use unspecified as long as you have not decided about a risk’s impact.
It would be best if you also listed risks at the minor and insignificant levels to have a complete view. Remember that a risk’s impact may change over time due to external reasons. When that happens, it’s always good to be prepared and have the risks listed.
Values for Severity (or Priority)
The severity of a risk is the combination of likelihood and impact, resulting in an expectancy value. It is the most crucial parameter for risk prioritization. However, we will use a heuristic instead of an exact formula. Of course, the idea of being able to calculate the severity exactly is an under-complex thought.
I do not use severity inside Jira since Jira has no (default) automation to determine the value for the issue field Severity out of Likelihood and Impact. I leave it to the Excel sheet I present later in this article.
However, we can manually document the severity in Jira in some ways. First, the built-in Priority field for stating the severity may be favorable because it is visualized on the cards of a Jira board. Nevertheless, we may create a dedicated custom field named Severity instead since Priority is a global field that cannot be customized project-wise.
Anyway, I define the severity levels as follows.
- Critical
We must address these risks immediately and with maximum priority! The impact is significant, and indeed, it will turn into a problem. - High
We must take immediate action to mitigate this risk. - Medium
The risk needs additional active monitoring besides the standard routines but should still be manageable with minor mitigation actions. - Low
The default routines should be sufficient to monitor these risks. - Negligible
We can ignore risks at this level. - Unspecified
We must act immediately to evaluate these risks with a proper likelihood and defined impact. If a risk has an unspecified severity, we may set it to the status IN CLARIFICATION.
Flag Risks that Turned into a Problem
As mentioned, we use flags to identify which risks have become problems. This means the corresponding issue is flagged as long as a problem remains. We remove the flag once we mitigate the problem and return it to a risk.
As you can see above, Jira uses an excellent visual representation of flagged issues. Thus, we can easily see which issues need additional attention.
When adding or removing a flag, Jira asks us whether to leave a comment. I suggest putting in some information about what happened. It’s an easy way to document decisions; a few words should be sufficient.
Hide Risks via Security Level
If risks need to be hidden from a broader audience since they hold confidential information, we may use a separate and restricted Jira project to maintain those risks. We can also use a single Jira project where the so-called Security Level issue field is configured. However, since its configuration requires some Jira know-how, I don’t want to explain it in this article. Read the Atlassian documentation[7] or ask your Jira admin how to configure it if needed.
And that’s it. We now have everything together to manage our risks inside Jira. Let’s see how we can prepare an excellent report for our stakeholders.
The Excel Export
To create a report, we need the data first. Thus, we must make clear that the exported data only covers relevant risks. Therefore, we filter out every risk with the status ARCHIVED.
The whole export should then cover all the columns required for further processing. This means it should hold all fields defined inside the risk log Excel tool I present in the next section.
After applying the JQL filter, we export the data to an Excel sheet and download it. However, exporting data from Jira is always tricky, especially regarding date fields. You may use Jira apps like Dataplane Reports to get better export results.[8]
Still, since we want to keep our Jira instance clean, I recommend using my Python tool, Tiny Jira Exporter, available on GitHub. You can find a pre-configured YAML configuration file in another GitHub repository called Risk Log, where I store the Risk Log Excel tool together with everything you need for it. Using it, we automatically receive the issue fields in the correct order required by the risk log Excel tool.
The Python tool Tiny Jira Exporter has another advantage. Let’s say we want to generate a risk report every last day of the month. In this case, we can use, e.g., a Cron job that runs the script automatically at the end of each month. It’s a bit techy, so we may ask a developer or our Jira administrators to set this up.
Please note that I use my exporting tool to get the data from Jira. Therefore, the following image shows the CSV file I exported using the Tiny Jira Exporter opened with Excel.
However, if you download an Excel file directly from Jira, the file contents will look different. Thus, you must ensure that the downloaded data fits the fields in the Export worksheet of the risk log Excel tool presented in the section below.
Now we have the data, it’s time to process it.
Using the Risk Log Excel Tool
You can download the Excel tool Risk Log.xlsx from my GitHub repository “risk-log.”[9] I will continue to call it the “risk log” or “risk log Excel tool.”
The basic idea behind the risk log Excel tool is that it helps us keep a better overview and that we can use its visualizations for reporting. Therefore, it processes and prepares the information we get from Jira.
Copying the Export
Once we export the Jira data, we can copy the values from the exported file to the table in the risk log’s worksheet “Export.” We must ensure that we copy the proper data to the correct columns. The last column, “BL.existsInRiskLog,” is for a checkup, so do not overwrite it—BL stands for business logic.
However, transferring data from Jira to the risk log is manual. Therefore, when pasting the data to the Export table of the risk log, we must ensure the following things:
- The risk log’s export table should be empty at best so that we do not end up with redundant entries.
- As mentioned above, the columns of the downloaded export file must be the same and in the same order as the columns inside the risk log’s export table.
- We should only paste the values without their formatting.
Once we have copied the data, we must ensure the risk log lists all the risks we got from Jira. Therefore, we must ensure that the column “Key” of the worksheet “RiskLog” holds all Jira keys of the exported data.
We must do this now: Copy the contents of the column “issueKey” of the worksheet “Export” and paste them into the column “Key” of the worksheet “RiskLog.” The result should look similar to the following image.
Please note that this is the only action we must do within the “RiskLog” worksheet. You may change the sort order of lines, but that’s it. However, if you want to customize the Excel sheet further to your needs, feel free to do so.
Check for Missing or Duplicated Keys
Copying the keys sometimes produces duplicates or missing items. The good thing is that the risk log helps us with this.
In the worksheet “Export,” there is a column called “BL.existsInRiskLog.” And in the worksheet “RiskLog,” there is a column called “BL.existsInExport.” Whenever it shows something other than a “1,” we know we must do something. In addition, the “RiskLog” worksheet also shows a hint in the upper left corner of the exact problem.
Consequently, whenever we see something missing, we must add or remove the respective Jira issue keys to or from the column “Key.” For instance, when the “RiskLog” worksheet holds a risk that has been archived in Jira and thus has not been exported, we must adjust the “RiskLog” worksheet.
Et voila, our risk log is ready, and we know how to use it. We now have all the information, including the automatically calculated severity. For easy access, we can click on the link in the column “Jira” to automatically open the correct Jira issue. To do so, however, we must adjust the “Base URL” in the worksheet “MasterData.”
We can now see why we configured Jira as described above. The Jira configuration must fit the design of the risk log Excel tool.
Inspecting the Risk Matrix
Since we have all the data in the risk log, we can now examine the evaluation.
The risk matrix is an excellent tool for immediately seeing the current status of your project. The more items are in the upper right corner, the worse. Thus, our goal must be to move all the defined risks to the green or at least yellow part.
As a rule of thumb, there should never be a risk in the upper right corner—this means we have a risk of critical severity.
Examining the Risk History
The risk matrix, severity index, and risk distribution inform us about the current situation. However, it is always important to see how risks develop. As already said, moving the risks from red to green is essential—and at best, we can fully resolve the risks. And this is when the risk history comes into play to visualize the development over time.
To create the risk history chart shown in the above image, we add a new line whenever we create or update a risk report. We enter the date of the export in the column “Date” and copy the severity index values to the other columns. That’s it.
And, of course, the greener the chart becomes, and the fewer items are shown at all, the better it is.
Creating Reports in Confluence
This is easy to do. You add the data from the risk log Excel to a Confluence page. At best, we have created a Confluence page template to create a new report page.
The Report Page
The report page holds the severity index values using the Page Properties macro. Additionally, we provide aggregating information with the following fields.
- Summary
A summary that explains the overall status. - Assessment
A status using a lamplight system. We use Confluence’s Status macro to represent it.
Please note that, in addition to red, yellow, and green, there is one additional purple status called CRITICAL. The status of the risk assessment is defined as follows.
An example report page may look as follows.
In the above image, see a table of page properties on the left that holds all the information I mentioned above. I have also attached the risk log Excel tool to the page and linked it as a download. This way, the original data will always be at hand.
On the right, I document the current top risks and those that are new or have been resolved since the last report. This allows us to add meaningful information to each risk, making decisions understandable at a later point.
Please note that I avoid linking the risk issues using the Jira macro. The reason is that I want to document the current status. The Jira macro would change once there are changes to the respective issues inside Jira. Therefore, I am not using it here.
I also add the visualizations from the risk log. Thus, stakeholders do not need to download the risk log Excel tool to look at it. For charts, it’s an easy copy-and-paste job. However, there is an easy way to transform also the cells of an Excel worksheet into an image. We mark the cells of the risk matrix, copy them, and paste them into an MS Word document as an image. After that, we can copy the image inside the Word document and paste it to the Confluence page. Done.
And, of course, the risk history is also on the page.
Please note that these pages hold the label “risk-report.” This allows us to collect them using the Page Property Report macro on the overview page.
The Overview Page
Whenever I use page properties on pages, I also use the Page Property Report macro on an overview page that acts as the root page.
The overview page contains general descriptions and also links to the Jira boards. It holds a button using the Create from Template macro to create new pages easily using the correct template. The Live Search macro allows us to search for content on the risk report pages. Like the Page Property Report macro, the Live Search macro is restricted to pages holding the label “risk-report.”
Please note that the numbers in the table are the same as in the table of the risk history worksheet in the risk log.
And that’s it. We now have Jira configured, we know how to export its data, and we discussed how to prepare the reports inside Confluence. I welcome you to try it out!
General Advice on Reporting
Please don’t report too often. On the one hand, it is a manual effort that may become an overhead. On the other hand, stakeholders may get annoyed by too many report e-mails or meetings, especially when there is little or no change. Consequently, we should note that too many reports have an inflationary effect. From my experience, once a month should be sufficient.
In a volatile, high-risk environment, we may combine our report with a meeting where we present the insights. However, just showing the numbers in those meetings is not valuable for stakeholders. Present insights or, even better, work out insights and actions together with the meeting participants.
Of course, actively working on risks is an essential risk management task. However, preparing the report fulfills the following purposes.
- The disciplined report preparation with a fixed cadence forces us to actively deal with and reflect on the risks.
- We can keep our stakeholders informed to prevent time-consuming escalations, and in case of an escalation, we have a solid basis for it.
- The report is another way to demonstrate methodological maturity, which may increase the trust of our stakeholders.
However, we should not leave risk management as a monthly task. I recommend weekly check-ins, where we quickly discuss the risks and their status with a small group of stakeholders—or even alone. Depending on how many risks we have, this should not take longer than 15 to 30 minutes.
Pros and Cons
I see the following pros of the approach.
- It’s cheap
Most companies I know use Jira, Confluence, and Excel. Thus, since the whole approach is based on the basic functionality of these tools, there are no extra costs. - Easy to customize
Since Jira, Confluence, and Excel offer many degrees of freedom, you can easily customize this approach to your needs. - Always a reference
When there is an escalation, I add the Jira issue key to the escalation e-mail or meeting invitation summary field. - Good integration
Since we don’t need other tools, the approach is well-integrated with our systems. The risk management can happen in the same Jira project as we may have our work items defined. For instance, we can link the risk Jira issues with other issues or mention them on Confluence pages.
And, of course, I see one negative point with this approach.
- Manual export
The exports must be done manually, and we must manually enter the data into the risk log Excel tool. Especially in the beginning, this task requires some time and discipline. It may also be a source of failure. Therefore, I recommend automating the Jira export using my exporter Python tool. However, preparing a report manually has the advantage that we revisit the risks and stay informed.
Overall, the advantages outweigh the disadvantages. Which was, of course, the reason for writing this article.
One last word
I presented a risk management approach that is easy to understand and implement. However, if you find this approach too much and, for example, a simple Miro board is sufficient for you to handle your risks, then go for it. Always try to find the leanest method that suits you well.
[Methodological maturity] is about using the most appropriate methods. It is always essential to ensure that the methods are as lightweight as possible and as elaborate as necessary. Or, as a lean thinker would say: everything else is waste.
About Competence, Discipline, and Methodological Debt, Boris Karl Schlein
Footnotes
[1] This article will not discuss setting up risk management processes or how to facilitate risk assessment workshops. Its focus lies only on the tools and how to configure them.
[2] To learn more about risk management techniques or treatments, read the article Avoid, Mitigare, Transfer, or Accept? PMP Exam Guide on ProjectPractical.com. Wikipedia is also a great starting point.
[3] An actual Kanban system requires you to set up WIP limits and does not allow you to move cards back in the process. The Risk Management board is also not designed to reach a constant flow of cards. It’s only about managing the status of risks.
[4] I got this idea from another Kanban board software, Kanbanize.
[5] You may wonder about the board’s name, “L2 Risk Management // RISKMGMT”. The L2 stands for “Level 2”. I consider the issue type Risk to be a level two work item. The first level is tasks, where you define actions for mitigation. The second level is for risks, where you coordinate the tasks by linking them to risks. Please read my article A Scrum-like Multi-level Kanban Implementation to understand how the levels work.
[6] I am bad at creating good examples. So, I autogenerated this description using Google Gemini. However, I adjusted Gemini’s response afterward since it wasn’t 100% accurate. But I think the result is quite cool. :D
[7] If you want to use Jira’s security level feature, please read Create security levels for issues in the Atlassian documentation.
[8] Find Dataplane Exports on the Atlassian Marketplace. However, it is available for Data Center only. Thus, I recommend using my Tiny Jira Exporter Python tool.
[9] I licensed the file RiskLog.xlsx under a Creative Commons Attribution-NoCommercial-ShareAlike 4.0 International License “CC BY-NC-SA 4.0”. Thus, it’s free to use. This information is also stated inside the file on the worksheet “GeneralInfo.”
Resources
GitHub ’tiny-jira-exporter’
This is the home of my Python tool, Tiny Jira Exporter. You can, of course, download and use it for free. It’s still under development but works fine for exporting issue data. However, using the tool requires installing Python and the required Python modules. Therefore, I also plan to provide an easy how-to guide for non-tech people to use it easily. Windows users may download the current exe file from the releases page. This way, you don’t need to worry about Python at all.
GitHub ’risk-log’
Here, you can find the risk log Excel tool and everything you need to set up Jira and export the Jira issues with my Tiny Jira Exporter.
Related Articles
A Kanban Task Management Approach for Jira
Boris Karl Schlein
A Scrum-like Multi-level Kanban Implementation
Boris Karl Schlein
Using Labels in Jira and Confluence — Effectively and Efficiently
Boris Karl Schlein
Competence and Discipline: The Pillars of Excellence
Boris Karl Schlein
About Competence, Discipline, and Methodological Debt
Boris Karl Schlein
