Previous Article in this Series
Lesson 6 - Track Project Progress
You have planned the project meticulously. You also have been tracking it religiously and updating it regularly. But the other stakeholders also need to know it. Project reports are not simply some data put in some document and sent across to the various stakeholders. You must understand the needs and expectations of each stakeholder and create reports that they can easily and quickly understand, and get a feel for the project’s progress and health.
- MS Project has many useful built-in reports that can be accessed by clicking the “Report” menu. You are encouraged to explore these options. For the purpose of this tutorial, generating manual visual reports based on the data provided by MS Project and the power of MS Excel charting, will be described.
- Save the project plan each week and follow a unique, consistent naming convention. One good way is to append the week number to the filename (i.e., [Project name]_Wk[Week number].mpp). For example, we will save the file showing progress from March 10 through March 14, 2008 as “Demo for BKM_Wk11.mpp”.
- The first step in reporting is to know who the report is meant for. A technical person in the client’s team may need a certain set of data while a higher level manager or an executive may need an overview that demonstrates in a concise and simple way the health of the project.
- Once you understand the stakeholder’s need, identify the data you must present in a visual format. Utilize MS Excel’s powerful charting features for the purpose.
- Then, create a table in Excel where all this data will be put together and managed as the project progresses.
- Fill the data in the table by collecting it from MS Project.
- Chart it and make the charts visually appealing so that the information you wish to convey stands out.
- Create a PDF report from all the compiled information.
Now we will demonstrate the steps outlined from 3 through 8 above.
- In this example, we will report the following on a weekly basis:
Create a table in Excel as shown in Figure 21. The data to be filled in will be obtained from MS Project and will be shown next.
- Estimated person days required to complete the various phases and the project
- Progress to completion of the various phases and of the project as a whole
- The variation in the estimated completion date of the phases and of the project as the project progresses
- Summary of the status of tasks defined in the project plan
- Status of milestones defined in the project plan
Figure 21: Compiling data in Excel
Figure 22 shows the data to be collected from MS Project to arrive at the effort estimates. In MS Project, click on “Resource Usage” under “View” menu to get this information. This information is filled in rows 3 through 8 each week in our example.
Figure 22: Entering resource usage (effort) data in Excel (Bottom) using MS Project (Top)
Total effort (data in row 10 in Excel) is calculated by summing the data in rows 3 through 8. Total person-days (data in row 11) is calculated by dividing the value in row 10 by 9 (9 being the number of hours in a work day in our example).
Completion percentages required in rows 13 through 15 can be obtained from the bar text in the Gantt chart as shown in Figure 23.
||Note: If resources work different number of hours a day (different resource calendars), the calculation in row 11 must account for that.
Figure 23: Obtaining completion percentages from Gantt chart bars
Completion percentages can also be easily obtained by adding the relevant column in the Gantt chart view. This is achieved by right clicking on a column header where you want the new column inserted and selecting “Insert Column”. Select the metric you wish to track in the window that opens as shown in Figure 24.
Figure 24: Insert column to track % complete
The resulting project window will show the “% Complete” values as shown in Figure 25.
||Note: Any metric or any number of metrics can be tracked using the same steps
Figure 25: % Complete values displayed in the project plan
Completion % and dates are filled in rows 13 through 15 and in 17 through 19, respectively, as shown in Figure 26.
Figure 26: Completion metrics filled in Excel (Bottom) from MS Project (Top)
The status of tasks to be filled in rows 21 through 24 of the Excel are obtained using the built-in MS Project reporting feature. To access it, click on “Reports” under “Report” menu. Select “Overview…” in the “Reports” window, which opens the “Overview Reports” window. Select “Project Summary”. The report and the required data are shown in Figure 27.
Figure 27: Task status values in Excel (Bottom) from MS Project report (Top)
Milestones status is obtained the same way as Project Summary in the above step.
The data in the Excel file is charted as shown in Figure 28, with comments for the report.
||Note: The content in the first page of the milestones report depends on the width of the columns in MS Project. As we are interested only in the Finish Date of the milestones, we have adjusted the column widths to display only up to the Finish Date in the first page of the report.
Figure 28: Visual presentation of the data
A drawback of MS Project is that the reports cannot be saved or copied. Only the “Print” option is available. Another drawback is that the printer selection cannot be changed in the window that opens on clicking “Print” in the report. The workaround is to open the print window in the project plan and select “PrimoPDF” and close the window without printing. Then, the print window that opens in the report will use “PrimoPDF” as the printer and you can print page 1 (unless you need other information as well) as a PDF file.
For our reporting purpose, we will select “PrimoPDF” as the printer while printing the Excel Charts, Data and the Milestones report.
After these 3 sheets (Excel Data sheet, Excel Charts sheet, and MS Project Milestones report) are printed as PDF files, PDFill PDF Tools application can be used to rotate, crop, and merge the PDF files, and add watermarks, etc. The features of the application are shown in Figure 29.
||Note: In the case of Excel 2007, an Office 2007 PDF and XPS add-in can also be downloaded from Microsoft’s website that allows saving the Excel sheet or entire workbook as a PDF file.
Figure 29: PDFill PDF Tools for working with PDF files
You are ready to send a professional report to the stakeholders.
As mentioned earlier, some of the steps in this tutorial can be performed in alternative ways, other tools can be used and you may even find an alternative process that makes doing the above a bit easier.
However, it is our belief that you will find many tips presented here useful.
We certainly will appreciate your feedback on the tutorials that will help others and also help us improve them further.
Have fun with MS Project and success on your software development projects!