You have come to the right place if you are looking for the answer or some unique tips to create a project portfolio Bubble Chart in Excel. There are some quick steps to create a project portfolio Bubble Chart in Excel. This article will walk you through each and every step with appropriate examples. As a result, you can use them easily for your purpose. Let’s move on to the article’s main discussion.
What is Project Portfolio Bubble Chart?
Through the project portfolio bubble chart, one can easily make decisions and compare various products and projects to approve or reject them. There are many kinds of Bubble Charts used in portfolio management. A portfolio bubble chart has the advantage of quickly displaying the current portfolio’s balance. Portfolio bubble charts can help the portfolio governance team have more productive conversations about the portfolio’s management.
Risk-Reward Bubble Diagram in Portfolio Management
With the help of a risk-reward bubble diagram, projects can be organized by the quadrant in which they fall. There are four quadrants in the Risk Reward Diagram.
- Bread and Butter – Upper Left Quadrant: These projects will have low rewards but a high probability of success. These are simple and usual projects that you may deal usually.
- White Elephants – Bottom Left Quadrant: These projects will have low rewards and a low probability of success. You will try to avoid these projects but still, you have to face some of them.
- Pearls – Upper Right Quadrant: All managers and businessmen aspire to have more pearls. These are called the star projects which are of a high probability of technical success and return a very high reward.
- Oysters – Bottom Right Quadrant: These projects are of low success probability but will give a high return of profit.
Steps to Create a Project Portfolio Bubble Chart in Excel
In this section, I will show you some quick and easy steps to create a project portfolio bubble chart of Risk – Reward Diagram in Excel on the Windows operating system. This article contains detailed explanations with clear illustrations for everything. I have used the Microsoft 365 version here. However, You may use any other version depending on your availability. Please leave a comment if any part of this article does not work in your version.
Step 1: Create Dataset
First, you have to collect data on the projects that you are planning to start. You have to collect the required data to assume the technical probability of success, NPV, which is the return on the investment, and the estimated cost. Then, create a dataset accordingly given in the screenshot below:
Read More: How to Create Bubble Chart in Excel
Step 2: Insert Bubble Chart
Then, you can create a bubble chart based on the dataset that you have created. Follow the procedure below:
- Go to the Insert tab in the top ribbon.
- Then, click on the Scatter chart icon and select the 2D Bubble Chart
- After that, double-click on the created chart and go to the Chart Design tab on the ribbon.
- Then, click on the Select Data
- As a result, a new window named “Select Data Source” will be created.
- Here, click on the Add
- Then, a new pop-up window named Edit Series will appear.
- Here, select cell B2 for the Series Name box.
- Then, select cell range D5:D12 for the Series X Values box.
- After that, select cell range C5:C12 for the Series Y Values box.
- Lastly, select cell range E5:E12 for the Series bubble Size box and press the OK button.
- As a result, you will see a bubble chart will be created in the active worksheet. But, it has not become a Risk Reward. For this, we have to create four distinct quadrants in the chart.
Read More: How to Create a 3D Bubble Chart in Excel
Step 3: Format Bubble Chart
To make a risk-reward bubble chart, we have to make four quadrants inside the graph. For this, we have to shift the axis from the side to the middle positions. For this, you have to follow the below procedures.
- First, double-click on the vertical axis and you will see there will open a window on the right side of the worksheet named “Format Axis”.
- Here, specify the minimum as 0 and the maximum as 1.
- Then, mark the button “Axis Value” under the “Horizontal Axis Crosses” option.
- After that, insert 5 in the Axis Value box.
- After that, you have to do the same thing for the horizontal axis also.
- For the horizontal axis, specify the minimum as 0 and the maximum as 100.
- After that, insert 50 in the Axis Value
- Then, click on the Plus (+) icon aside the chart and mark Axis Titles
- Then, rename the vertical axis as “Probability of Success” and the vertical axis as “NPV”.
- Thus, now you have the risk-reward bubble chart ready for presentation.
- One more thing is still remaining. You may see that there are no identifications on the diagram for the quadrants.
- For this, you have to insert a Text Box on the Bubble Chart where you can specify the name for the quadrants. So, go to the Insert tab and click on the Text Box option to create a blank text
- Then, draw a new text box at a suitable position on the bubble chart.
- Then, type the name for the quadrant.
- Similarly, create more text boxes for all four quadrants and type the names inside the text boxes.
- Thus, you have created a project portfolio bubble chart.
Download Practice Workbook
In this article, you have found how to create a project portfolio Bubble Chart in Excel. I hope you found this article helpful. Please leave comments, suggestions, or queries if you have any in the comment section below. Goodbye!