Excel Project Plan

This isn’t strictly a CRM/D365 post, but I think it could provide some assistance for planning CRM related projects, so I thought I’d share.

Any good CRM project, whether that be a new deployment or a small change, requires planning to ensure it is effective; the 5 P’s cliché “Proper Preparation Prevents Poor Performance” exists for a reason.  I am aware that plenty of people use Microsoft Project or use D365 Project Service Automation (if you want to learn more about this I’d highly recommend reading Antti Pajunen‘s excellent blog posts about PSA), however I am also aware, from my experience of working in small companies, that the licence costs for these products can be prohibitive.

A Simple* Solution

Any company that utilises the Microsoft Office technology stack as part of their business will have access to Excel, and therefore they’ll be able to utilise the vast array of templates that Microsoft have made available to help them in their business.  I’ve used many of them in the past, and continue to do so today.

There are many Project related templates available for Excel, and I recently saw the Agile Gantt Chart template.  This template is great because it provides a decent foundation for a Gantt chart, but there a number of areas I felt it was lacking, so I’ve modified it to try and make it more suitable for my purposes.

My Template

My concerns with the template available from Microsoft are:

1. There is no ability to automatically schedule task completion dates
2. There is no ability to include predecessors for tasks
3. There is no ability to effectively resource manage tasks

With all of this in mind, I thought it would be a fun task to see if I could implement some improvements.

I’ve included a link below to download my version of the template.  The key features I’ve added are as follows:

Activities are added by:

1. Selecting a Component from the drop-down selector in the Component column
   1. The Component drop-down is populated from the Component Column in the High-Level Summary Dates table on the Project Summary worksheet
2. Manually inputting an Activity description in the Activity Column
3. Selecting a Task from the drop-down selector in the Task column
   1. The Task drop-down is populated from the Task Column in the Mid-Level Summary Dates table on the Project Summary worksheet
4. Selecting a Category from the drop-down selector in the Category Column
   1. Goal marks the Activity with a Goal marker on the Gantt chart
   2. Milestone marks the Activity with an Activity flag on the Gantt chart
   3. On-Track, Low Risk, Med Risk and High Risk format the cells on the Gantt chart in accordance with the format on the Legend at the top of the sheet

 

Start Dates are calculated as follows:

1. Each Activity starts on the End Date of the preceding Activity in the list, unless:
   1. A Predecessor is selected by inputting the ID of the predecessor in the Predecessor column; and/or
   2. A number of “Lag Days” in working days is input in the Lag Days column
   3. An Actual End Date is entered for either the preceding task or the predecessor

 

End Dates are calculated as follows:

1. The estimated effort in Working Days is input into the Effort (Working Days) column
2. Responsibility for the task is allocated to a person using the drop-down selector in the Responsible column
   1. The Responsible drop-down is populated from Name column in the table on the Project Personnel worksheet
3. The Task Duration is automatically calculated as the Estimated Effort / Effort Profile (from the Profile column in the Project Personnel Sheet), and is rounded up to the nearest ¼ day
   1. E.g. a task with an estimated effort of 1 day, allocated to a person with an Effort Profile of 50%, would have a Task Duration of 2 days
4. Any holidays to be accounted for are documented in the Holidays table on the calcs worksheet
5. The End Date is therefore Start Date + Task Duration (in working days), and ignores any holidays

 

If you want to use this template you can Project Plan Template

Let me know if you find it useful!