Project Portfolio Dashboard in Excel [Part 2 of 2]

Project Portfolio Dashboard in Excel [Part 2 of 2]:
In 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 Dashboard Pack is now available.

Click here to get your copy.

Background: Designing a Project Portfolio Dashboard

As discussed in part 1, the biggest challenge when it comes to designing project portfolio (program) level dashboards is that, End users want it very concise yet powerful.
We have identified important needs of our end users & come up with a mock up design that meets all these. Refer to part 1 for that discussion.

Final Implementation – Project Portfolio Dashboard

First lets take a look at the finalized dashboard implementation. Click on it to enlarge.

Construction of Project Portfolio Dashboard

Design philosophy for the dashboard

First let us understand the design philosophy for this dashboard, because that is what drives all the Excel work. Here is a mind map that explains how I approached the design of this dashboard.

Data Entry

Majority of data in this dashboard is captured using Excel Tables. This has several advantages:

• Users can easily add more rows of data without worrying about the formulas.
• Formulas are self-explanatory, thanks to structural references.
• Data entry is easy, thanks to banded rows, headers & table styles.

There are 6 important tables:

1. High level project details table
2. People details table
3. Each project’s plan details go in to a table, named plan1, plan2…plan10
4. Risks table
5. Issues table
6. Holidays table

Calculations

Explaining each and every formula in this would take us until next years Christmas. So I will highlight key formulas & challenges faced:
Fetching relevant project plan from all plans:
This was one of the trickiest things. Since each plan has its own table, getting selected project’s table is necessary to drive all calculations. This is how its done.

1. Define lstPlans as a list of all tables =plan1,plan2…,plan10
2. Use INDEX to fetch one of the tables from this array like this =INDEX(lstPlans, activity-row-number, column-number, plan-number)

Sorting Projects:
This is done using 3 steps:

1. Extract relevant data for all projects based on sort criteria (for example, sort by done % means we need done %s for all projects)
2. De-duplicate this data by adding a small running fraction to them
3. Sort using RANK formula

This is essentially same technique Robert taught us in 2008 in KPI Dashboards article.
Showing Daily, Weekly or Monthly Gantt view:
This is achieved by using below logic:

1. For Daily gantt, see if date in the column is between start & end dates (more: Between formula in Excel)
2. For Weekly gantt, see if the week start & week end in the column fall between start dates’ week start and end dates’ week end. (more: Date overlap in Excel)
3. For Monthly gantt, see if the month’s start & end in the column fall between start dates’ month start and end dates’ month end.

Other important formulas:

• WORKDAY for all date related calculations so that holidays & weekends are omitted
• INDEX for all dynamic ranges so that dashboard remains responsive.
• No UDFs .
• Very few array formulas so that users can understand what is going on.
• Structural references as much as possible so that formulas are readable, editable & dynamic. [More on Excel tables]
• Used lots of named ranges to keep formulas readable.

Dashboard Display

This dashboard display follow box layout with simple colors, easy charts, picture links & lots of conditional formatting goodness.
To understand the important Excel features used in this, see below image & following list.

1. Hyperlinks: for accessing other parts of the workbook & data
2. Boxes & Text boxes: to show data & provide layout.
3. Thermometer chart to show budget vs. actual performance
4. Simple Column charts to show distribution of values
5. Combo boxes for selecting sort & view options
6. Scroll bars for seeing more
7. Conditional formatting for icons, highlighting & gantt chart
8. Picture links to embed project summaries & gantt chart views
9. Option buttons to select a particular project

Dashboard Usability

Color Scheme:
Use default Office 2007 (2010) theme. This provides very good contrast, excellent color mix & does not surprise many people.
Fonts:
Only 2 fonts are used thru out dashboard. Franklin Gothic Book for content & Franklin Gothic Demi for headers.

These fonts are specified in dashboard’s theme so that they apply by default when opened in any computer.
Printable:
The dashboard is optimized for print. All form controls & links are disabled for printing. This ensures that you get a clean printout with just data & charts.
Tip: You can disable printing for any object by right clicking > format > properties and un-checking the print option.
Macros:
Since the workbook uses macros, I have added a warning message that shows up when macros are disabled. A technique I picked up from Mike Alexander.

How this dashboard works [Video]

Since all this explanation might not do justice to the work, I made a short video [12 mins] explaining how the dashboard works. See it below:

Download Project Portfolio Dashboard

Now you can get this and 4 other project portfolio management templates (including simplified portfolio dashboard, time line chart, gantt chart templates). All these files are easy to use, beautiful to present, fully customizable, unlocked and designed to make you awesome.
Click here to get a copy of the project portfolio portfolio dashboard.

• Download locked version of this dashboard and see how this works – Excel 2007 & above.
• Download unlocked version of this and more templates.

How do you like this dashboard?

I really enjoyed making this dashboard. It was challenging & entertaining experience for me. I think the final workbook summarizes performance of a bunch of projects in a concise yet powerful way.
What do you think? Do you like this dashboard? Do you often work with project portfolio / program level dashboards? How do they look and behave? Please share your feedback, suggestions & ideas using comments.

Thank you for your suggestions & feedback

Thanks everyone who sent suggestions thru emails & comments. I feel very happy about the way this dashboard has turned out. Thanks for your continued support for Chandoo.org.
PS: Go ahead and pick up Project & Portfolio Management template pack today, because you want to be awesome.