Sunday, 12 June 2016

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:

  1. Will always fit into a single page as you control what is displayed
  2. They are usually aesthetically appealing as you can format the Gantt chart using different colours & shapes
  3. You can fit more than one activity bar or milestone on the same row
  4. Anyone can produce or update them, no specialist software knowledge required
  5. 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. (pdf file available here)



The P6 summary schedule will look like this (pdf file available here)
While the Excel summary schedule will look this (pdf file available here)

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.

If you need a copy of my Excel summary schedule template, leave a request using the contact form and I will send it to you.

Have a great weekend.

13 comments:

  1. Once again, you have a great article here. Quite timely for me as well because of a current need that requires summary presentation of the milestones.

    ReplyDelete
  2. Thanks Jerome for the Post .. i am facing the same problem with the output of Primavera , every time i give an output to the project stakeholders (client , the engineer , my team ) i need at least 30 minutes to explain and discuss the print out
    i appreciate if you send me copy of the excel sheet
    thanks in advance
    A.almasry@hotmail.com

    ReplyDelete
  3. I'm glad you found the post useful. Check your mailbox, I've sent the Excel file to you.

    ReplyDelete
  4. Hi Jerome,
    Appreciate your enthusiasm in sharing your experience and knowledge, not to mention having to have an excel copy of the above. Kindly send to paul71ml@gmail.com. Many thanks to you.

    ReplyDelete
    Replies
    1. Hi Paul,

      Thanks for your comments. Spreadsheet has been sent to you.

      Jerome

      Delete
  5. Very useful info. Thanks. May i request for a copy of the excel template.

    ReplyDelete
    Replies
    1. Art,

      Send your email address using the contact form.

      Jerome

      Delete
  6. Hi Jerome,

    Please can I have a copy of the excel form and totally appreciate all your support.

    Clem

    ReplyDelete
  7. Clem,

    Check your inbox, template sent.

    Jerome

    ReplyDelete
  8. Hi Jerome,

    Useful article especially as I am needing to show more useful Level 2 and above summaries for projects with many Activities. Have already used Excel to make the large projects more readable for my Project Managers so agree it is a useful tool.

    Would you be able to send me a copy of your excel template. chris.drury@mcnealybrown.co.uk

    Chris

    ReplyDelete
  9. Hi Jerome,

    Very useful articles. Could I please get a copy of the excel template? chris.drury@mcnealybrown.co.uk

    Chris

    ReplyDelete
    Replies
    1. Chris,

      Check your inbox. Template sent.

      =jerome

      Delete