Keep Track of Project Milestones with This Excel-based Tool


The heart and soul of project management is tracking milestones and reporting progress to the team at large. True, a lot of homework needs to go into setting those milestones, but even that process must be carefully monitored for deliverables and dates, or chaos will set in and your project will get off track.


Our partners at Info~Tech Research Group have developed two simple tools to help you track your project and summarize your progress. "Simple," of course, must be taken in context-these tools aren't as sophisticated as full-blown applications like Tom's Planner or MS Project, but they are fairly comprehensive, as far as spreadsheet-based tools go.


Today, we'll show you the data-entry and core project tracking functionality to be found in the Project Planning and Monitoring Tool, the Excel-based core of this tracking system. Tomorrow we'll take a look at the reporting features of this spreadsheet, as well as its Word-based companion, the Project Milestones Report Template. Both these tools are available free to IT Business Edge members here in the IT Downloads library.


The Project Planning and Monitoring Tool spreadsheet comes in two versions: one with a macro to support some minor functionality, and one without the macro. Pick the one that best suits your shop's security culture -- the core functionality is the same in both versions of the tool.


The spreadsheet is built around four functional tabs: Data Entry, Task Monitoring, Issue Log and Reporting. We'll cover the first three today, and take a look at the reporting capacities tomorrow.


In the Data Entry tab, you'll be asked to enter some core assumptions that will define the project. As you can see in the figure below, these assumptions include the obvious, such as start and end dates, and the not so obvious, such as holidays and other unavoidable time-off dates.



The tab also asks you to input the names of project team members and stakeholders. As with the rest of the data entered in this tab, these team member entities will be used as the basis for the task Monitoring tab, which you can see in the figure below.



This tab, which presents information in the standard Gantt format, asks you to assign presumed start and end dates, as well as owners, to action items for your project. As you can see in the sample image, you will want to manage preliminary tasks, such as cost-benefit analysis, as part of overall project. It's all part of getting it done.


The Gantt chart is laid out by daily units, and color-coded by projected work day and presumed time off. Another column throws up red flags when a task goes past its projected complete date. Of course, there's also space for comments.


The Issue Log tab, which takes the form of a simple table, completes the ongoing project monitoring tool set of the spreadsheet.


As we mentioned earlier, this tool is relatively simple, by PM standards. It does not include capabilities for assigning percentages of resources to tasks, or creating contingencies between deliverables-for that kind of functionality, you will probably need to look at the numerous dedicated PM software packages on the market.


However, the tool's simplicity does make it a great way to communicate the overall status of the project to the full range of stakeholders. The accounting manager probably does not care that Joe, your Python developer, unexpectedly became unavailable for a week-they just want to know if stuff is getting done on time. Tomorrow, we'll look at how the Project Planning and Monitoring Tool and the Project Milestones Report Template work together to provide a simple, clear reporting platform that you might even want to consider as a supplement to your more formal internal project management reports.

Subscribe to our Newsletters

Sign up now and get the best business technology insights direct to your inbox.


Add Comment      Leave a comment on this blog post
Dec 30, 2011 7:37 PM Chris Thomas Chris Thomas  says:

This is great, but how do I add more weeks to cover a year and show additional holidays? When I add holidays, they are are not showing up?

Jan 9, 2013 11:54 PM Joe Joe  says:
If you'd be interested in a Web based, color-coded milestone dashboard view that users can share (as well as email to each other, or export to Excel), take a look at ours: http://www.trackersuite.net/scrn_milestones_dashboard.html The projects within the dashboard can be filtered by an array of values, including program, type, status, priority, manager and more. If you'd like a hands-on evaluation, you can register for immediate access to a free demonstration. Reply
Jan 13, 2018 3:35 AM mike mike  says:
would like to know how this works and maybe I could adopt. Reply

Post a comment





(Maximum characters: 1200). You have 1200 characters left.




Subscribe Daily Edge Newsletters

Sign up now and get the best business technology insights direct to your inbox.

Subscribe Daily Edge Newsletters

Sign up now and get the best business technology insights direct to your inbox.