How to Create Risk Burndown Chart in Excel (with Easy Steps)

Risk Burndown plan means to lessen the work risk from the higher level to the lower. This value is time-dependent. With time goes, risk lessens. Depending on the value of risk burndown, we can take initiatives to lessen the risk and complete the task in proper time with extra effort. In this article, we will discuss how to create the risk burndown chart in Excel with proper illustrations.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


What Is Risk Burndown Chart?

The risk burndown chart is a visual representation of data in Excel. From this chart, we will be able to get information about the risk percentage of any project in terms of time. From this chart, we will be able to estimate how much extra effort or extra time is needed to complete the project.


Steps to Create a Risk Burndown Chart in Excel

In this section, we will discuss how to create a risk burndown chart in Excel in detail in the below section. We want to create a risk burndown chart for a software project. This project deadline is 6 months.


Step 1: Collect and Arrange Data to Create a Risk Burndown Chart

  • First, we will insert practical data into the dataset.

Prepare dataset for Risk Burndown Chart

  • We will use a formula to calculate the remaining work using the formula on Cell J6.
=$C6-SUM($D6:$I6) 

Here, we subtract the sum of the total working hour from the estimated work hour. Extend the formula from Cell J6 to Cell J11.

Read More: How to Create Sprint Burndown Chart in Excel (with Easy Steps)


Step 2: Modify Source Data Properly

  • For the modified data, we introduce a new data table in the dataset.

New chart for Risk Burndown

  • Now, we will calculate the working hour for each month. For that, we will use the following formula on Cell D15.
  =SUM($C$6:$C$11)/6

Apply SUM function for Risk Burndown Chart

We just divided the total estimated work hour by 6 months. Then, expand this till Cell I15.

  • Now, we will calculate the actual work hour for each month.
  • Use the following formula on Cell D16.
=SUM(D$6:D$11) 

Similarly, extend this formula till Cell I16.

  • Now, we will calculate the remaining work and the ideal burndown amount. In the starting, both are the same. We will use this formula on Cells C17 and C18.
  =SUM($C$6:$C$11)

  • Now, we will calculate the remaining work hours from Cell M1 to M6. Use the following formula at Cell D17.
  =C17-D16

Extend this formula till Cell I17.

  • Now, we will calculate the ideal burndown using the following formula.
=C18-D15 

Extend this formula from Cell D18 to I18.


Step 3: Create a Chart from the Modified Data

In this section, we will create an Excel chart based on the modified data.

  • Choose the Range B14:I18.
  • Select the Insert tab.
  • Then, we will choose the Line chart from the Chart group.

Risk Burndown Chart: Create chart

  • Look at the chart.

There are four lines in the chart. The legend is indicating different lines.

Read More: How to Create Budget Burndown Chart in Excel (With Easy Steps)


Step 4: Format the Graph as Risk Burndown Chart

Now, we will customize the chart for better understanding.

  • First, we will customize the horizontal axis. Select the horizontal axis and press the right button of the mouse.
  • Choose Select Data from the Context Menu.

  • Choose Planned Work from the Legend Entries section.
  • Then, click on the Edit button.

  • Choose Range C14:S14 as the Axis label range.
  • Then, press the OK button.

  • We can see the Horizontal Axis Labels have been changed.
  • Again, press the OK button.

Risk Burndown Chart: Change Horizontal Axis

  • Look at the chart again.

We can see the axis value has been changed.

Now, we will change the chart pattern. We will change the chart type of planned work and actual work data.

  • Click on the line of planned work from the chart.
  • Press the right button of the mouse.
  • Choose Change Series Chart Type from the Context Menu.

  • The Change Chart Type window appears.
  • First, mark the Planned Work and Actual Work series to make them secondary axis.
  • Then, choose Clustered Column from the Chart Type section.

Risk Burndown Chart: Change Chart

  • Finally, press the OK button.

We can see chart type has been changed successfully.

  • Click on the secondary axis of the chart.
  • Press the right button of the mouse and choose Format Axis from the Context Menu.

  • Set the Maximum Bounds to 3600.

Risk Burndown Chart: Set value in Format Axis

  • Finally, look at the chart.

This is our final chart. We will be able to know the difference between actual work speed and estimated work speed with monthly values from this graph. From this, we will estimate the risk.


Conclusion

In this article, we described how to create a risk burndown chart in Excel. We explained all the steps in detail. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo