My Settings #05: Schedule Roll-up in Microsoft Excel
In more than a decade of working as a Project Planner, I have come to the realisation that Project Managers rarely read any report that exceeds 2 pages and this includes project schedules, unless they are looking for a specific information in the report. This post is about an easy and cost effective way to prepare a simple but very effective one-paged summary schedule that will always grab a manager's attention as it can fit into a presentation slide.
As explained in my last post about producing summary schedules in Microsoft Project & Primavera P6, I'm a big fan of Level 1 schedules in Microsoft Excel. My preference for Excel is because doing it in P6 or MS Project does not produce the desired output as you only have the outline levels headings (WBS) as activity description and there are times that these headings are not descriptive enough to cover all the activities under them.
Also, P6 or MS Project schedule roll-up does not allow one to emphasise a lower level activity in the roll-up without changing the WBS structure or defining activity codes or custom fields. Recent versions of P6 have the TSLD which can be used to produce one-paged schedules with emphasis on schedule logic but I still prefer using Excel for the simplicity and flexibility it offers.
With Excel, you have total control over the structure of the one-paged summary schedule as it is not constricted by WBS or activity codes or custom fields structure. Like I said in the opening paragraph, Excel based one-paged summary schedules are best suited for presentations to project stakeholders, project sponsors, project directors or project teams. In addition to the flexibility of activity grouping, some other advantages of these Excel based summary schedules are:
Will always fit into a single page as you control what is displayed
They are usually aesthetically appealing as you can format the Gantt chart using different colours & shapes
You can fit more than one activity bar or milestone on the same row
Anyone can produce or update them, no specialist software knowledge required
Cost effective as no additional software licence is required
Assuming we have the 3-paged P6 Level 3 schedule shown below from which we need to prepare a summary schedule for the Project Manager.
The P6 summary schedule will look like this.
While the Excel summary schedule will look this.
Comparing the P6 summary schedule and the Excel summary schedule, you would see that the Excel summary schedule conveys more project information than the P6 summary schedule and it will look better in a presentation or a report than the P6 summary schedule.
Though there are advanced macros enabled Excel summary schedules which will automatically draw & accurately position activity bars and milestones to scale, the one shown in the image above is a basic one which requires manual positioning of activity bars and milestones. Despite the manual functionality of this spreadsheet, it only requires around 30 minutes or so to update and considering the effectiveness of the summary schedule, I would want to think it will be 30 minutes well spent.
