Create a P6 Project Dashboard in Excel

Tutorial Files

• Excel Spreadsheet (Excel 2007+): PTuts_Project_Dashboard.xlsm – 1.5Mb

One of our previous site manager told us (the project control team), that if we bring to him another report that have more than 1 page, he will just throw it away (sic), actually managers care only about the big picture, and don’t bother reviewing every small details. The purpose of this tutorial is to create a relatively good looking Excel Project dashboard with the most relevant graphs and indicators on it.

What can P6 Professional do?

Let’s see what P6 offers in this regards (here P6 refer to professional, EPPM has a whole stacks for reporting and dashboarding). P6 has a very rudimentary graphics reports, and the bundled report writer is a limited piece of software, but that’s fine, P6 is scheduling software not a reporting engine.

In that case, we do expect that P6 offer an easy way to query the data from the database, so we can generate all those fancies graph and reports, and that’s the real issue here:

• P6 does not store the period actual progress by default, you have to use store period performance, unfortunately financial periods is defined at the database level, it is not project specific ; for example if you have two different projects with different cut off days, you are out of luck. (Unless you create a different database)
• Even if you want to use the store period performance, when the project progress and the initial quantities changes, and those engineer on site start changing the previous recorded progress, because that supervisor were exaggerating the figures, then maintaining the progress in P6 will be a time and hard consuming task.
• The spread data are not stored in P6 database, they are calculated on the fly by the client software (summaries project work only at the WBS level not the activity level),

So if you are using P6 professional and you need to query the spread data at the activity level, your only solution is to use the SDK, which is a clunky piece of software.  And if you are not an expert on the P6 database schema you may end up corrupting your data. (For the record the API was removed from P6 Professional, it is only available now in P6 EPPM)

What you should Use?

• If you are working with a big contractor, probably you are using a proprietary in-house database to manage progress or EPPM, and you do have access to a all kind of live dashboard reports.  So this tutorial is not only not useful but highly not recommended. I would not imagine the frustration of Project controls manager when their team starts using offline systems 🙂
• If you are very comfortable using the P6 SDK, and the limitation of financial period doesn’t particularly bother you then fine, query the data from P6 Professional and use your favourite reporting software to generate your Dashboard ( Crystal reports, Excel, Tableau ….. ).
• If you are not an expert in the P6 database schema, and your project has a lot of quantities revisions, then Excel (if properly used) can be a very good and convenient tool.  Many professional hate it because many planners tend to create very complicated spreadsheets that no one understands except for them.

How the excel Project Dashboard workbook is organized

1. This workbook is not P6 specific, as the actual values per period are store in Excel, you can use any scheduling software you want.
2. The VBA macros are used only to fill down formula.
3. We use ID as a reference here, it can be the schedule Activity ID, or Bill Item ID.
4. We’ve already shown you how to move from Activity ID to Pay Item.

The workbook is organized on 5 sections.

1. Dictionaries: data that you input only once when you start your project (WBS, Non working days, reporting Period, Commodities definition).
2. The project baseline.
3. Progress data: the percent complete of activities and the spent hours by reporting period.
4. Data: it pulls data from the sheets and presents in a database friendly format (values are listed in column, with the first row as header).
5. Output: the Dashboard.

What is inside every tab of the Project Dashboard spreadsheet

WBS

Copy your WBS dictionary here.

WBS_Split

This sheet will use the WBS dictionary to split the WBS by level; it is used for the Actual sheet, as we need to organize the activities by

WBS level

In a previous tutorial, I showed an “ugly” solution how to split the WBS level, but for this tutorial, I am using a much more elegant solution (if you want to see an example of beautiful solution check this one)

Commodities

Here we group the activities by major commodities, if you group Activities they have to share the same Unit of measurement.

Holidays (Optional)

You list all the non working period of your project (weekend, holidays  …); it is used only if you want to create the spread in Excel

Period

Your reporting period, in our case we used Friday as a cut off.

Baseline

We use the planned dates from P6 and the non-working period to generate the planned values spread. It is really useful if your schedule is not resource loaded, or if you want a quick way to have a first look at how the overall curve will look like.
Obviously you can later overwrite those values and use your P6 spread, you need only to normalise your data. (0- 100%).

Spent_Hours

here you input the spent hours (the actual burnt hours by direct labour), notice we record the spent hours at the project level, then we prorate the data based on the progressed activities on the reporting period, it is not very accurate as the best way would be to record at the activity level or at least the commodity level, but getting this kind of information is always “problematic with the field People”

Actual

All the progress figures are input in this sheet.

Suppose you want to update progress for the period 20 Jan 2012,

1. Select 20 Jan 2012 in the drop list menu.
2. Copy and paste the values from the previous period (the actual is cumulative).
3. Update the % complete for every activity that has progress in this period, the % complete is always between 0 and 100 %.
   • The % field is “installed quantities/ Quantities at completion”.
   • The quantities at completion are your best available estimation at the time of your update. In most cases the initial quantities in the BOQ are just estimation and have to be updated once the work has started.
   • It make sense once the activity is 100 %, the quantity at completion is frozen.
4. Select if the earned value is based on the current quantities or the Baseline Quantities, notice the planned values are always based on the baseline.
   • Although the Overall percent completed based on the current is more accurate, many clients prefer to use the original baseline quantities unless they release a new revision.
   • Note: the overall percent complete is a subjective figure as it depends on the budget quantities, but the installed quantities are a fact and they are independent from the budget.
5. Click on calculate.

Data

All the data from other sheets are grouped here, when you group your data into columns, you can easily and quickly create your reports and charts.

To reduce the size of the excel file, when you close the workbook there is a VBA macro that deletes all the records except the first two rows. (They are generated every time you click on calculate in the “Actual” sheet)

Dashboard

• Overall Progress planned vs. actual.
• Main commodities planned vs. actual.
• Productivity curve.
• Tabular report Progress Planned vs. Actual by WBS.
• Tabular report Quantities Planned vs. Actual by major commodities.

In order to group many reports in one Page, a good trick is to create your reports in a separate tab then copy and paste it on the dashboard using “Paste Picture Link”.

Wrap Up

Although P6 Professional is hugely used planning software, some very important features used daily by planners are missing, the reporting capabilities are very limited, and it is very difficult for the average users to mine P6 database (Especially spread field) to extract useful data.

Recently Oracle has added the extended schema to P6 database but unfortunately it is available only to EPPM users.
the limited functionalities of store period performance, the non ability to define financial period per project  and the complicated use of  SDK ( specially for a production system), all that have an unfortunate result : a very powerful Database with a excellent business rules but limited capability to generates good and useful reports.

I really hope that Oracle continue improving P6 Professional and not concentrating only on EPPM, because there will be always a huge market for standalone users.

Things for you to try

Excel is by far the most used software to manage data, it can save you a lot of hours if automate your workflow, and if you want your complexes spreadsheet to be used and understood by others, you can use very simple steps.

• Separate data from reports.
• Always write your datasets as a database, first row as header and the rest values.
• Add a help section where you document how your formulas work.

Here are some must-know functions to learn if you want to create awesome reports in Excel

• Countif: check if the item is unique (the bill item should be unique).
• VLookup: (even better index/match) to look up values.
• Sumifs: for calculating sum with conditions.
• Offset ( just Google it , it is awesome)
• Named range
• How to record a VBA macro