Generating an Earned Value Visual Report

Visual reports are a major new feature of Project 2007. If you completed the section “Generating Visual Reports with Excel and Visio” in Chapter 12, you’ve worked with some visual reports. One of the Excel visual reports available in Project, the Earned Value Over Time Report, generates the classic “S-curve” line chart that graphs cumulative values over time for the three key indicators associated with earned value analysis. They are:

• Actual Cost of Work Performed (ACWP), labeled in the visual report’s legend as AC.

• Budgeted Cost of Work Scheduled (BCWS), labeled as Planned Value.

• Budgeted Cost of Work Performed (BCWP), labeled as Earned Value.

In previous versions of Project, to generate a similar chart in Excel, you used an add-in named Analyze Timescaled Data in Excel. In Project 2007, this functionality is provided by the visual report. As with all Excel visual reports in Project, you end up with the chart as well as the Excel PivotTable on which the chart is based that you can modify as you wish.

Important 

If the computer on which you are now working does not have Excel 2003 or later installed, you cannot complete this exercise. If this is the case, skip ahead to the “Key Points” section.

In this exercise, you generate an Earned Value Over Time Report.

1. On the Report menu, click Visual Reports.

The Visual Reports dialog box appears:

This dialog box groups visual reports in a number of ways: all reports, only Excel or Visio reports, and task, resource, or assignment details (divided into summary and usage reports). The dialog box includes a simplified preview of the type of graphic (chart or diagram) associated with each visual report. You can click the various tabs in the dialog box to see how the visual reports are organized if you wish.

2. Click the Assignment Usage tab.

3. Click Earned Value Over Time Report.

4. In the Select level of usage data to include in the report box, make sure that Weeks is selected.

This setting determines the time increment that will be included in the Excel PivotTable and charted in the line graph.

5. Click View.

Project generates the data required by this report, launches Excel, and creates the report. Next, you’ll adjust the PivotTable outline to change the level of detail shown in the line chart.

6. In Excel, click the tab name of the Assignment Usage withEV sheet.

This is the PivotTable on which the earned value chart is based.

On the PivotTable Field List, you can see all of the fields for which timephased data was exported from Project. You can add any of these fields to the PivotTable by clicking them.

To show more detail in the earned value chart, you’ll expand the PivotTable outline.

7. Click the plus signs next to the Q1 and Q2 values.

The PivotTable expands to show the weekly values you exported from Project.

8. Click the tab name of the Chart1 sheet.

9. In Excel, adjust the zoom setting such that the entire chart is visible.

Because you set the status date to June 20, Project reports zero values after that date, causing the lines in the chart to return to zero after week 23 of the project’s overall duration.

Now the earned value chart shows the level of detail we want. The key to reading this type of chart is to locate earned value and actual cost as they relate to planned cost. Recall that the planned costs represent the cumulative amount of money you planned to spend at any given time. Where the earned value line falls below the planned value line around week 20, you see the project effectively fell behind schedule. The actual cost (AC) line, when it is below the planned value line, tells you the project was running below budget. Around week 21, however, actual cost shot well above budget. As the earned value indicators you saw in Project suggested, the project has been underperforming in terms of both schedule and cost indicators. The earned value chart helps you visualize the specific trends that are driving project performance and hopefully allows you time to address these issues through the remainder of the project.

10. Close Excel without saving the workbook.

11. In Project, click Close to close the Visual Reports dialog box.

Close the Parnell Film 18 file.