Export project data for future effort estimation

Learn how to tweak your estimation matrix even further by analyzing the project data from your Microsoft Project schedule.

In my previous column, I explained how to use project data to develop a better estimation matrix. The key benefit of tracking the actual project effort and comparing it to your original project estimates is that you can refine your estimation matrix for future analogous top-down estimation. Even Agile enthusiasts would value this approach for their next round of Planning Poker, as the effort estimation is based on prior experience for similar types of work.

I provided a very simple estimation matrix based on the common RICEF deliverables of reports, interfaces, conversion, enhancements, forms, and screens in Figure A. You can tweak this matrix even further by analyzing the project data from your Microsoft Project schedule. If you follow the eight steps outlined below, you'll be crunching numbers in a few minutes. Figure A

Click image to enlarge.

Step 1: Configure your estimation table

I could write several articles on how to effectively customize Microsoft Project with your own tables, view, and maps; however, it's easier if you download this sample Microsoft Project file (Sample Schedule with myEstimates.mpp) and copy the relevant objects into your Global.mpt file.

After you download the sample schedule, follow these steps:

  1. Open the Sample Schedule with myEstimates.mpp file.
  2. Select Tools | Organizer.
  3. Click the Tables tab, click myEstimateComparison table, and click the Copy button. This will copy this custom table to your PC, where you can use it for other project schedules.
  4. Click the Fields table and follow the same steps to copy the Estimation Category, Task Complexity, Task Category, and Include Estimation fields to the Global.mpt file. These fields map to Text28, Text29, Text30, and Flag20, respectively. If your project schedule currently uses these fields for other purposes, you'll need to customize the fields using the Tools | Customize feature in Microsoft Project.
  5. Click the Maps tab and copy the myEstimates map into the Global.mpt file.

Step 2: Assign Task Category to tasks

Once the project fields, table, and export map are copied to your local PC, open your project schedule. Click the Gantt Chart view and switch to the myEstimateComparison table. The custom table was created to replicate key Microsoft Project fields without modifying the core Microsoft Project table structure.

Follow these steps to switch tables:

  1. Select View | Table.
  2. Select the myEstimateComparison table.
If the table doesn't appear in the menu items, click the More Tables button and select the table. You should see a table similar to Figure B with blank values. Figure B

Click image to enlarge.
The default Task Categories field includes these values: Report, Interface, Conversion, Enhancements, Form, and Screen. If you want to modify these values, simply click Tools | Customize | Fields and modify the Custom Attributes (Figure C). Figure C

Find the key task you want to track in your project schedule and assign a task category from the drop-down menu.

Step 3: Assign Estimation Category

The Estimation Category field includes Requirements, Test, Code, and Deploy valid values; you can modify these values to accommodate your systems' development lifecycle. The Estimation Category field is used to identify the time spent in specific phases for key deliverables.

Step 4: Assign Task Complexity

The Task Complexity field consists of low, medium, and high values. Depending on your estimation approach, you may want to add values for smaller or larger work.

Step 5: Set the Include Estimation flag

The Include Estimation field is used as a filter so you can view all the key tasks or deliverables you want to track in Microsoft Project or in a spreadsheet. You need to set the Include Estimation flag to Yes or No.

Step 6: Repeat for each of the RICEF deliverables you want to track for future estimation.

Step 7: Export to Excel using the myEstimates map

Exporting data from Microsoft Project to Excel or a different format is easy to do. Since you already have the myEstimates map, you can save the file as an Excel export with just a few clicks.

  1. Select File | Save As.
  2. Select Excel Work Book (*.xls), enter a filename, and click Save.
  3. When the Export wizard dialogue box appears, click Next.
  4. Click Next.
  5. Select the Use Existing Map radio button.
  6. Click the myEstimates Map (Figure D).
  7. Click Finish.
Figure D

Step 8: Export to Excel using the myEstimates map

Open the exported data in Excel, and you can filter by the estimation columns to further analyze durations. The translation from the raw Microsoft Project data to your estimation matrix only takes a little bit of data manipulation. Programmers will be tempted to write macros to update the estimation spreadsheets, although I recommend project managers inspect the duration and effort data and make decisions on where to adjust their estimation matrix.

Get weekly PM tips in your inbox TechRepublic's IT Project Management newsletter, delivered on Wednesday, offers tips to help keep project managers and their teams on track. Automatically sign up today!