Authors Note: I've received a lot of emails recently regarding the use of Microsoft Project and earned value management.  If you are new to building a project schedule, you will want to check out the MS Project Tutorial on How to Effectively Build a Project Schedule in 10 Easy Steps as it builds a project project schedule that  can use earned value metrics.  The following article provides the high level steps to calculate the earned value metrics in Microsoft Project. Enjoy -A. Makar.

You've just returned from your Friday afternoon lunch and started to check last night's sports scores when boss stops by your desk. You deftly hit Alt-Tab keys to switch to an open Excel spreadsheet as your boss starts talking about his latest training class. He just returned from a project cost control seminar and wants you to apply earned value analysis to your project. He wants to see your current SPI and CPI calculations on your next status report

You remember reading about earned value in the PMBOK book that sits high on your office bookshelf, but have no idea on how to get started. You know your budget and actual costs to date, but calculating earned value remains an enigma. Don't worry. By following this article, you'll be calculating earned value, SPI, CPI and other earned value metrics with a few mouse clicks and some third grade math.

In order to apply earned value to your project plan, you will need to complete the following six tasks:

1. Enter Resources with hourly bill rates in the Resource Sheet view
2. Assign resources to tasks in the Gantt Entry view
3. Save the Project Baseline
4. Record Actual Start/Finish and Actual Work each week in the project plan
5. Review EVA metrics using the Earned Value views and reports
6. Calculate Schedule Performance and Cost Performance indices
   

The Resource Sheet View

The resource sheet view allows project managers to define the different type of resources available to a project. MS-Project tracks work resources and material resources. Work resources are the people and equipment that consume time to accomplish project tasks. Material resources are the consumable supplies, such as steel, concrete and other construction materials that are used to complete the project tasks. Most software projects are based on work resources with different costs rates. Figure 1 depicts all the team members assigned to a project with their respective hourly cost rates.

Figure 1 Resource Sheet

The resource costs need to be entered to calculate the project's planned value (PV) or budgeted cost of work scheduled (BCWS). In this example, Customer Cindy was assigned a $0.00 dollar amount since the client isn't being charged for their participation in the project. If you are managing an internal IT project and are tracking costs for both IT and functional resources, assign a standard rate as appropriate.

Assign Resources to Tasks

After you've established your pool of resources and their individual costs, the next step is to assign resources to all your tasks in the Gantt Entry view. There are several ways to assign resource in MS-Project and the most direct method is to click on the Assign Resource Icon in the Resource Management Tool bar and assign the appropriate people to the task. Figure 2 identifies the Assign Resource Icon and the Assign Resources dialog box.

Figure 2 Assign Resources

If you are applying earned value to an existing plan, the resources should already be assigned and the costs for each task are automatically calculated when the standard rate was assigned. After the resources have been assigned, MS-Project calculates the task's cost. To view the current costs, follow these steps to use the combination Task Entry and Task Usage view.

1. Click on the Task Entry View icon in the Resource Management toolbar or click on the Gantt Chart view and select Split from the Window menu
2. Click anywhere on the lower window pane to activate it
3. Select the Task Usage view in the lower window page
4. Select View – Table and select the Cost table.
5. Click on any task in the upper window pane to view the cost information.

Figure 3 depicts the total cost of the project in the combination Task Entry and Task Usage view.

Figure 3 Project Cost

Try selecting various tasks to view the total cost. In the EVA Example.mpp template, click on the "Code Screen A" task. The total cost of the developing the code for one screen is $3200 since the developer, ASP Alex, bills $40.00 per hour for 80 hours of work. Understanding the actual cost to deliver a task provides better awareness of major project deliverables and their complexity. It allows the project manager to manage each task as a budget item in their portfolio instead of just a list of tasks.

Managing the costs at the task level helps improve productivity by planning for quality team meetings. In the EVA Example, if all team members meet on a weekly basis for 1 hour, it will cost the project $530.

If you were going to meet with a consultant who charges $500 per hour, wouldn't you want to adequately organize and prepare for this meeting? Understanding the costs of operating and managing the project will help avoid costly meetings and motivate the project manager to plan for productive and effective meetings.

Once you've reviewed the total project costs, verify the total project amount does not exceed your total project budget. Project budgets are often estimated at a high level without the benefit of a detailed bottom-up estimate. If the project's cost is greater than the original budget, a funding supplement or scope change may be required.

Save the Project Baseline

After you've built your plan, assigned resources and confirmed the budgeted costs, the next step is to baseline the project plan. Saving the project baseline is an important step to measure and manage your project's performance. To save the project baseline, follow these steps:

1. Select Tools – Tracking – Save Baseline
2. The Save Baseline Dialog box appears and select the Save Baseline radio button
3. Select the Entire Project radio button to baseline the entire project plan or select the Selected Tasks radio button to baseline any selected tasks.
4. Click OK

The Baseline Start, Baseline Finish, Baseline Work, Baseline Cost and Earned Value fields will be populated. If your project is in process and you haven't established a baseline, you'll need to baseline any future tasks and manually edit the baseline data for completed and in-process tasks. Saving the project baseline before project execution avoids these manual-editing procedures. For more information on the project baseline, refer to the upcoming Lessons Learned with Microsoft Project #6: Baseline the Project Plan in our series.

Record Project Actuals

Once the baseline is established and the team begins working on the plan, tracking the actual costs and start and finish dates will be helpful in calculating the project's earned value. Tracking the Actual Start and Actual Finish dates and comparing them to the Baseline Start and Baseline Finish dates will be used to determine any schedule variances. Tracking the Actual Work and comparing it to the Baseline Work will be used to determine any cost variances. After you've entered your project actual dates and hours for the week, you can view the earned value information.

Review the EVA view and reports

It may seem like it took a lot of effort to reach this point, but if you've established your project baseline and are tracking project actuals, the earned value information is just a few mouse clicks away. MS-Project uses the Earned Value, Earned Value Cost Indicators and Earned Value Schedule Indicators tables to display earned value information.

To view the Earned Value table:

1. If you are using a combination view, select the window pane to display the earned value information.
2. Select View – Table – More Tables menu item.
3. The More Tables dialog box will appear and you can select from a variety of tables.
4. Select the Earned Value table and Click OK.
5. The current earned value information will be displayed for each task.

Figure 4 displays the Earned Value table for each task in the project plan.

Figure 4 Earned Value table

The Earned Value table contains eight earned value fields to represent the project's earned value.

The Earned Value data is calculated based on the current date. As of 3/4/2005, the project had planned to complete $23,570 dollar of the planned budget. This is the budgeted cost of work scheduled or the planned value for the project. The project actually completed $19,575 dollars of the project budget. This is known as the earned value or budgeted cost of work produced. Finally, it actually cost the project $23,170. This is known as the actual cost of work produced or actual cost.

In percent complete terms, you had planned to complete 65 percent of the project. However, you only completed 54 percent of the project and have spent approximately 65 percent of the project budget. The percentages are calculated by dividing the respective BCWS, BCWP, and ACWP by the original project budget or BAC (Budget At Complete).

By examining the SV (Schedule Variance) and CV (Cost Variance) columns, you can quickly determine if the project is on schedule in terms of project plan performance and budget. If the SV and CV columns are zero then the project is on track and on budget. Schedule and cost variances need to be monitored throughout the project to keep the project in control.

The EAC column provides an estimated cost at the completion of the project. If the project continues to perform with these cost and schedule variances, the actual cost of the project will be $40,800. Remember the original project budget was $35,905 and total project cost variance will be $6,594. Your customer and a contingency budget may be able to fund the cost overrun, but the VAC (variance at completion) should be reviewed with your customer to manage the budget and scope accordingly.

The Earned Value report in MS-Project will also display the earned value for each task in the plan. To run the report:

1. Select View - Reports
2. Click on the Costs icon and click select
3. Click on the Earned Value report and click select.

The earned value report and table provide the objective metrics you need to assess the project's health. By reviewing the SV column, you can quickly identify the tasks that are running behind schedule. The next step is to calculate the schedule and cost performance indices for your status report.

Calculate Schedule Performance and Cost Performance indices

The schedule performance index (SPI) is a measure of the actual project progress to the baselined schedule. The cost performance index (CPI) is a measure of the project's earned value compared to the actual costs incurred. The indices are calculated using the follow equations:

SPI = EV / PV

CPI = EV / AC

In our project example, the SPI is $19,575 / $23,570 or .83 and the cost performance index is

$19,575 / $23,170 or .84. Both calculations are less than 1 and indicate the project is behind schedule and over budget. The project manager needs to address these variances to improve the performance of the project. During a project execution, a project can often run behind schedule or over budget for a given time period. The schedule and cost performance indices help to summarize the objective performance of the project and should be included on the project's status report.

Summary

In six easy steps, you've been able to calculate your project's earned value. The actual earned value calculations only require a few clicks of the mouse, but the good project management processes of tracking actual performance against the project baseline requires discipline. Project managers need to consistently measure performance in order to successfully manage their projects. Implementing earned value reinforces the need to manage against a baseline project plan and track project variances.

MS-Project's earned value calculations are based on the status date in the Project Information dialog box and are compared against the project baseline. If a status date is not set, MS-Project uses the current system date in the EVA calculations. If the plan wasn't updated for three weeks and the project manager viewed the earned value information, the calculations would be further off since three weeks have past since the last review. Remember, earned value is a point-in-time evaluation of the project. If you want to experiment with the EVA Example.mpp file, change the dates, rebaseline and simulate the actual hours. You will see a different EVA calculation based on the project's progress.

Incorporating these objective metrics in your status report will help guide the project discussion around schedule and budget in addition to the subjective project status narrative. In the next article, I'll review some other useful earned value calculations. In the meantime, hit Alt-Tab to finish checking your sport scores and then start applying earned value to your project.

Attachments:

File	Description	File size
EVA Example	EVA Example	317 Kb