This free Excel Template allows you to track projects and calculate your effectiveness in completing them. I find it applicable for scenarios where all the projects go through the same set of pre-defined stages in sequence. For example, all building projects may have three stages: plan, design and build. Once planning is completed, designing begins; and after designing is completed, building begins. We’ll track the completion of each stage of each project.
The template is very simple to use and it can provide visibility to how long it takes for your projects to be finished. It can also help identify where the bottlenecks are in your project cycle and lets you view trends in terms of time each stage takes.
The use of the template requires Microsoft Excel for Windows (2010 or above) or Microsoft Excel for Mac (2011 or above).
Download the free project pipeline tracker template for Excel from Dinesh Natarajan Mohan.
The template has only three visible sheets:
- Data, where you enter your input data and see a summary of metrics;
- Active Pipeline, which shows projects still in the works, and where the output is automatically calculated; and
- Report, a rundown on monthly status, which is also automatically generated.
More about each to follow!
Step 1: Enter Stages
By default the template has six stages (including the final closing stage).
Figure 1. Here’s where you enter up to six stages in your project.
You can rename those stages to meet your business needs. Also, you can remove the stages you don’t need. Start at the bottom, click on the stage name and press the Delete key.
Step 2: Enter Projects
Enter each project in a separate row in the Projects table.
Figure 2. The list of projects and their information.
Enter the Project ID and Name, then, enter the Start Date of the project. The next six columns are the dates of completion for each stage of the project.
Note that you shouldn’t rename the Stage DT fields.
Figure 3. Additional fields to track in your projects.
However, the two columns, Type and Owner, are optional and can be used to store other information as you need. These can be renamed.
The last four columns in the table, shown in Figure 4, are calculated and should not be edited.
Figure 4. Calculated fields.
- Current stage. Based on the dates entered for each project, the current stage will be displayed. If the project has completed the final stage, then it will display, “Completed.”
- Completion date. If the project has completed the final stage, then that date will be displayed.
- Duration (Days). If the project is completed, this will show the duration from Start Date to Completion Date. If the project is still in progress, this will display the duration from Start Date to Today.
- The template checks for a couple of errors. If the dates entered for each stage aren’t sequential or if there is a date missing in between stages, this field will display, “Error.” Error projects aren’t included in metric calculations.
Completed Projects – Summary
We can see an instant summary of the completed projects as we enter data. That includes the number of completed projects and the average duration for each.
Figure 5. A summary view of completed projects.
We also see how long each stage takes to complete.
Active Pipeline – Summary
The number of projects that are currently being worked on and how many projects are currently in each stage are shown. The data page shows a summary of the active pipeline, which displays the number of projects in each stage of development.
Figure 6. The active pipeline.
The Active Pipeline sheet shows the same information, along with additional details, ID, Type and Owner fields.
Figure 7. Active Pipeline Report
Even if you rename the fields and use them to store other types of information, they’ll be displayed in this Active Pipeline sheet. Thus, it can be easily modified to your specific business needs.
The Report Sheet
The Report sheet presents the metrics — Projects Completed, Average Project Duration and Average Duration for each stage — for the past 12 months.
Figure 8. The monthly report table.
The information is also presented visually so that you can see how the duration varies month over month for each stage.
Figure 9. Monthly project trends.
Editing the Template
To prevent accidental editing of formulas, I’ve locked the Active Pipeline and Report sheets with this password:
Indzara
You can unprotect them and make edits if needed. (To learn how to unprotect Excel sheets, you can refer to this article on my company blog: “Unprotecting Sheets in Excel.”
The Data sheet isn’t locked. You can edit the shapes and charts as needed.
I hope that you find value in this Excel template that allows you to track projects and calculate effectiveness in their completion. If you develop a modification for the template that others may find useful as well, I hope you’ll post instructions below in the comments to share with the MPUG community.
A version of this article originally appeared on Dinesh Natarjan Mohan’s blog.
Image Source
