Menu
Designing a Project Portfolio Dashboard [Part 1 of 2]
Project Management - 27 comments
In this 2 part tutorial, we will learn how to design a project portfolio dashboard. Part 1 will focus on user needs & design. Part 2 on Excel implementation.
Background: Project Portfolio Dashboards
As you may know, we sell a set of Excel Project Management templates. These templates help plan, track, manage & report a project right from Excel.
While these templates good, they have one limitation. They work for one project at a time. Many customers have asked me if I come up with a project portfolio dashboard that can tell what is going on in a set of projects in one view.
And that is where we begin.
Who uses a Project Portfolio Dashboard?
Program managers, group project managers, IT directors, Project owners & CXOs are the target audience for a project portfolio dashboard. These are the people that oversee multiple projects and want to know what is going on with each of them a high level.
To keep our design task reasonable, we can also say that,
- Project portfolio consists of at least 5 projects.
- The dashboard is updated once a month or so.
What should Project Portfolio Dashboard communicate?
When it comes to a portfolio of projects, the users will not be interested in minute details of each project. Rather, they want to know what is going on, where the attention should be focused, how the plan vs. actual performance is, how money is spent etc. In other words, high level details followed up option to drill down.
As per my experience, our dashboard should communicate:
- Program overview
- Total projects, budgets, teams
- Program health indicators – Progress, budget vs. actual, time to complete, un-addressed risks
- A summary of all projects
- Project progress %s, budget vs. actual spend
- Team sizes, outstanding issues, risks & tasks
- Drill down view of individual projects
- Weekly plan, task level progress & budget
- Critical issues, risks by project
- Contact details
- Management comments
How should our Dashboard look like?
Based on above details, we can design a rough sketch of our portfolio dashboard. This sketch will guide us when we implement the dashboard in Excel.
Here is a design I came up with:
The numbers correspond to section 1, 2 & 3 in “What should they communicate” above.
How should our dashboard behave?
Because this dashboard contains a lot of data and is used by top management, it makes sense to define its behavior too.
Lets keep these points in mind when implementing the dashboard:
- The data entry for this dashboard should be easy & track-able
- Reason: lots of data, so users need to be alerted if data entry is incomplete. Also, one set of users enter the data, while someone else views the report.
- The dashboard should allow for drill down to know more
- Reason: top managers & project sponsors like to ask questions. So, we should allow for interactive drill down, but still present vital stats up front.
- The dashboard should be printable
- Reason: Project sponsors & steering committees have lots of members, often from different parts of a company. And they like to view static version (print out, pdf) when discussing.
Next steps: Implementing the dashboard in Excel
That is all for this installment. In the next part, Learn how to create a project portfolio dashboard using Excel.
Do you use Project Portfolio Dashboards?
When I was working as a business analyst, I used to design such dashboards for some of our clients. Later on, as an Excel consultant too, I have developed few different versions of portfolio & program dashboards. The hardest thing with designing these dashboards is that they have just too much of data. A very high level summary will not do because executives & project sponsors always ask follow up questions like, “So why is this project spending more than $x?”, “What is our mitigation strategy for this risk?”, “Who is handling this issue?” etc.
But with a bit of balancing act, we can design a good portfolio dashboard that is not too bloated and not too simplistic.
What about you? Do you use or develop such dashboards? What is your experience like? What would you like such a dashboard to communicate? Please share your ideas and suggestions using comments.
More on Project Management using Excel
If you are a project manager or analyst, chances are, you are using Excel to manage portions of your project. At Chandoo.org, you can find lots of resources, tips & templates for this. Check out,
Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Issue Trackers & Risk Management
Project Status Reporting – Create a Timeline to display milestones
More on using Excel for Project Management
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Even faster ways to Extract file name from path [quick tip] | Why am I killing Vitamin XL? » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
27 Responses to “Designing a Project Portfolio Dashboard [Part 1 of 2]”
Chandoo, your sketch looks promising & the key principles to consider when designing it are extremely valid. However, the biggest pain I have experienced when designing similar project portfolio overviews is how to make it scaleable. I.e how to fit in neatly "a summary of all projects" in case there might be 5 or 20 or even 50 projects.
This is a problem I faced too. I think the best option is to show lagging / struggling projects upfront and give user a choice to scroll down to see more projects. Another way would be to give a sorting control to user so that they can see "Good news first" or "Bad news first".
Nice Chandoo! Like the way you handle this. Love the subject. Every tip is welcome 🙂
Looks great! Cant wait to see part 2.
Timely topic for me. Looks very promising. Would like to see costs broken out by type (i.e. Internal, External and Capital). Would like to see how we are doing on contractor spend as well as other expenditures.
Thanks. It is a good idea to see where money is going. Often such details are captured by accounting systems in companies (ABC etc.) Once Project wise costing details are available, we can easily add additional charts, information to this dashboard.
PS: I will only be using highlevel & task level budget performance in this dashboard, but you can extend to do more.
Chandoo,
This is a fantastic topic,and addresses the very reason I stumbled upon your website in the first place. The questions you're asking are very valid - how much detail should be included, and how much is too much? What is really important?
The second part of that question, which is just as important as the first part, is how to display the information in a tangible, useful way.
If your audience is Project Managers AND the company president, how do you summarize what is happening with 50+ projects in a way that is meaningful to both?
How do you display that information in a sortable, way that shows viewers what and when milestones are, and what conflicts could potentially arise when deadlines overlap...
I'll leave it to your genius to figure that one out! In the meantime, I'll keep tinkering.
Thanks Chandoo...I am looking forward to seeing Part 2 of this.
my problem is simple...my PM's use MS Project. If I were to create a portfolio dashboard it would be very time-consuming (going back-n-forth bebween Excel and Project)
This will help me a lot. I manage 4 to 5 software projects and constantly ask to provide updates. Few suggestions below:
1. Can we add key projects achievements/accomplishments box since the last update.
2. A link where it shows history of key achievements/accomplishments since the project started.
3. Project status - Red, Green, Yellow
Look forward for second part.
We have a need/use for such a dashboard/gantt chart . . . I've created something to this affect, but I like yours much better! Our needs include notifications about past-due items, assignments (1st, 2nd, secretary). Then we also categorize by larger buckets of responsibility and budget. I would love to send you what we've done--I'm sure it's in need of help. It's also HUGE in size 🙁
Exactly what I'm looking for! Can't wait for this to be finished! What's the ETA? 🙂
Chandoo,
I can't wait for the next part...
Some recommendations, which might be helpful for top level mnmgt:
-There is a high number of developed performed over the time. Some of them gets closed hopefully. The project team can learn a lot from these. The board usually is interested in the projects closed in the last period and the open projects. A sorting option would be useful what status to show on the dashboard.
- It is also interesting how much revenue these projects will generate. Maybe a selling funnel could be implemented to show how much is in RFQ, then in quote phase, how much is in design phase, how much in process design or validation and how much is just before launch.
- There are industries where the projects have given phases/milestones and showing the GYR status of the milestones for each project on one page is also useful to see how healthy the project management is.
~Attila
Hey Chandoo, Exactly what I am looking for! Have used your previous ones, and waiting for improvements! Looking forward to it!
Jo
Great and very useful topic, Chandoo.
I second Mirko's comment - a scalable solution that can handle up to 50 projects would be immensely useful, especially if the 'tabs' at left can include conditional formatting to highlight ones that are overdue, over budget, pending review or completed.
Looking forward, like everyone else, to Part 2.
i cant wait for the final outcome of the dashboard.... since u do an interactive dashboard, i'm interested to see how u balance the info in the print out version and the interactive version.
My priorities on each project are:
Scope
Delivered Product (or ready for delivery) to date
Budget Forecast and Projected finish
What is needed, calls to act
I am looking forward to part 2! I would like to see resource capacity on the main dashboard. We have a formula we use for our teams, based on all the projects.
Can't wait to see the rest of this topic, any idea when part 2 will be published? Very relevant correlation to new business development at my workplace!
Thanks RSE
Chandoo,
Great idea!! I agree with comments about scaling and would like to add one more. The need to have multiple team members contribute to the detail that sums up to make the dashboard. How can we collaborate in the 'cloud'?
Regards,
Asmar
Any idea when we will see part 2? Its been awhile, looking forward to it!
Thanks
GG
[...] days ago, we learned how to design a project portfolio dashboard. The next part (which will be posted on Monday) talks about how to create this dashboard using [...]
This is going to be a great topic to follow. I am eager to see how this might be applied to a single project involving several hundred work orders, each with their own schedule (each schedule with a few key sequential milestones to be tracked).
[...] this 2 part tutorial, we will learn how to design a project portfolio dashboard. Part 1 discussed user needs & design. Part 2 will show you Excel implementation. Project Portfolio [...]
Hello,
very good dashboard! We would be interested in purchasing your Project Portfolio Dashboard and using it in our organisation.
However, there are some questions about it.
1. Is it easily customizable? Can an average Excel user without programming skills change/add own sheets and features?
2. The list of projects looks good. But how can we see an overall time-line where all the projects are present (a kind of Ghantt Chart where there are projects/programs instead of single activities)? Is it possible to add such a visualisation?
3. How can we collect the projects into programs? Is it possible?
Thank you!
Best regards,
Denis
Hi Chandoo. Love your work and tips. Well done.
My challenge is to set up a portfolio view of all project implementation dates. I need this to track my I.T portfolio of some 50 projects to ensure that we do not have conflicts on weekends when we are implementing, as well as ensuring we are implementing projects on Application or Infrastructure weekends as they should be. (Sort of like holiday LoVs, but with App and Infra weekends instead). I need to be able to track multiple implementations for each project (in case the project spans across multiple implementation weekends). I tried using your portfolio dashboard as a starting point, but I got bogged down changing everything. Any thoughts or advice? I need it to look professional and be used by my PCO for entry...
Thanks for the love Chris. Interesting question. I think you can make a simple gantt chart with all implementation schedules and then look for conflicts. That might be better than using the complex portfolio template to see this.