Increase performance using variance thresholds in Office Project 2007
By Bonnie Biafore
Comparing cost and schedule variances to set project thresholds is a great way to track performance and obtain early warnings about slippage and overruns. But, before you can see if you're ahead of the game or behind the eight ball, you should set up variance thresholds in your project schedule and budget. Microsoft Office Project 2007 can calculate cost and schedule variances for you, but custom fields are just the ticket for storing thresholds and calculating variances versus thresholds. In this article, you'll learn how to set up schedule and cost thresholds in Office Project 2007 and Microsoft Office Excel 2007 to compare variances to those thresholds. You'll also learn how to highlight variance status with visual indicators in Office Project 2007 and Office Excel 2007.
Available in this article …
Calculate variances in Office Project 2007
Office Project 2007 can calculate variances and project performance, however, you have one crucial task to complete before you can make these calculations: save a baseline for your project. This foundational detail is the plan you build for schedule, cost, and other project measures, to which stakeholders agree. Without this, Office Project 2007 doesn't have the targets that you want to hit, and therefore can't send an alert if your project is off track.
Use earned-value analysis to measure performance
One challenge is that the basic variance fields make it difficult to identify which tasks are on track or in trouble. A percentage is much easier than dollar amounts or date variances, because a percentage works regardless of how far the project has progressed or how many times that baseline has been revised.
But, what performance ratio helps you evaluate project performance? As you know, comparing actual values to estimates can be deceiving. Suppose your project has consumed half of its estimated duration and budget. Whether that's good news depends on how much of the project work is complete. Earned-value (EV) analysis makes your progress clear by comparing estimated and actual values to the amount of work you've finished.
Before looking at the EV ratios to track performance, it's important to understand the underlying measures:
Budgeted cost of work scheduled (BCWS) is the baseline cost through the status date for tasks scheduled in your project plan: how much you planned to spend by the status date, whether or not it's complete. For example, if the cost of tasks to be completed by September 30, 2008, is $10,000, BCWS for that date is $10,000.
Actual cost of work performed (ACWP) represents the costs for the work performed through the status date, regardless of what has been completed. ACWS on September 30, 2008, is what you've spent as of that date.
Budgeted cost of work performed (BCWP) is EV, because it measures the cost you estimated for the work completed, or earned, through the status date. For example, if the budgeted cost for completed tasks is $15,000, BCWP is $15,000, if you finished those tasks early, late, or right on time.
Use earned-value ratios to compare to thresholds
EV analysis uses several ratios to evaluate schedule and cost performance, including schedule variance (SV), cost variance (CV), schedule performance index (SPI), and cost performance index (CPI). Office Project 2007 ratio-calculating functionality and other new features are the best tools for comparing to thresholds:
EV cost variance (the CV field in Office Project 2007) shows the difference between the estimated and actual costs of the work you've completed (BCWP minus ACWP). If CV is negative, the actual cost is greater than the budgeted cost, and the task is over budget.
Cost variance percent (CV%) is the EV cost variance as a percentage of the estimated cost of completed work: [(BCWP – ACWP) / BCWP] X 100. For example, suppose that BCWP is $10,000 and ACWP is $12,000. CV equals $10,000 – $12,000, or –$2,000. The completed work costs $2,000 more than you budgeted. Thus, CV% equals (–$2,000 / $10,000) X 100, or –20%.
CV% is ideal for comparing to your CV threshold.
EV schedule variance (the SV field in Office Project 2007) shows the BCWP minus the BCWS — the difference between the estimated cost of work completed and work you planned to complete through the status date. If the SV is negative, you finished less work than planned and are behind schedule.
Schedule variance percent (SV%) is the EV schedule variance as a percentage of the budgeted cost of work scheduled: [(BCWP – BCWS) / BCWS] X 100. SV% shows the schedule variance as a percentage of the work scheduled. For example, consider the same BCWP of $10,000 with that of $9,000. CV equals $10,000 – $9,000, or $1,000. The scheduled work cost $1,000 less than the completed work, meaning you completed more work than you planned and are ahead of schedule. SV% equals ($1,000 / $10,000) X 100, or 10%.
SV% is ideal for comparing to your SV threshold.
Compare variances to thresholds
Traffic lights keep things simple: green means go, yellow is caution, and red means stop. Project thresholds are project traffic lights. By setting thresholds to indicate if your project is running smoothly, hitting a few bumps, or heading for problems, you can see where your project stands and decide what actions are needed.
When determining threshold values, you are defining the cut-off points that delineate green, yellow, or red lights for project performance, based on schedule and cost variances.
For example, a threshold of 5 percent over budget or behind schedule might be the maximum acceptable performance. A 10-percent threshold would indicate it's time to take corrective action. If the ratio is between 5 and 10 percent, you need to keep a close eye on the tasks to make sure they don't get any worse.
Make variance data easy to interpret in Office Project 2007
If you include columns for CV% and SV% in an Office Project 2007 table, you can watch for negative numbers that show you tasks are behind schedule or over budget. However, the ideal solution is to determine which tasks to watch and those to act upon.
Graphical indicators in Office Project 2007 make these situations stand out. For example, if CV% or SV% is less than –10 percent, the cost or schedule has exceeded your threshold, and you see a red light.
To display traffic signals for cost performance based on your predetermined thresholds, perform the following steps:
Create a custom field that shows CV% as a graphical indicator: From the Tools menu, select Customize, and then select Fields.
In the Customize Fields dialog box, in the Type drop-down list, select Number.
Select a custom number field, such as Number 1.
To give the custom field a meaningful name, click Rename. In the Rename Field dialog box, type a name, such as Cost Var%. Click OK.
To set the custom field equal to CV%, click Formula. In the Formula for Cost Var% dialog box, click Field, point to Cost, and then select CV%. Click OK.
In Values to display, click Graphical Indicators.
In the Graphical Indicators for Cost Var% dialog box, define the first test to check for CV% values that exceed your threshold:
Click in the first Test for 'Cost Var%' row; from the drop-down list, select is less than.
In the first Value(s) row, type –10 (do not use a percent sign).
Click in the first Image row; from the drop-down image column, select the red circle, because CV% less than –10 means that the task is more than 10% over budget.
Note If you use the same thresholds for all tasks, you can define the test with the threshold numbers. If you want variation from task to task, assign threshold values to other custom fields, and then in the Value(s) column, select a threshold.
In the second Test for 'Cost Var%' row, select is greater than. In the corresponding Value(s) row, type –5; and in the Image row, select the green circle.
In the third Test for 'Cost Var%' row, select is greater than or equal to. In the corresponding Value(s) row, type –10; and in the Image row, select the yellow circle. Click OK.
Note Because Office Project 2007 processes tests in order, the third test catches values that are greater than –10 but less than –5.
Note Displaying traffic signals for schedule performance is similar, except that you use the SV% field instead of CV%
To display the graphical indicators in a table in Office Project 2007:
From the View menu, select Table:, and then select More Tables.
In the More Tables dialog box, select a table and click Apply.
Right-click in a column heading.
From the shortcut menu, select Insert Column.
In the Column Definition dialog box, from the Field Name drop-down list, select a custom field. If referring to the previous example, select (Number 1) Cost Var%, and then click OK.
Note You can create a new table to show customized variance measures. To do this, from the View menu, select Table: and then More Tables. In the More Tables dialog box, click New. In the Table Definition dialog box, specify the table name and the fields to include in the table.
The column shows the status of your tasks with green, yellow, or red lights, as shown in Figure 2.
In Figure 2, you can see the difference between EV and basic variances. For example, the CV for the Design Deck task is a positive number ($60.00), because the actual cost is greater than the baseline cost. However, CV% is a negative value (–6%). Both variances indicate that the cost is over budget.
Avoiding slippage and overruns
You can use Office Project 2007 to calculate schedule and cost variances and evaluate your project's performance. Graphical indicators make it easy to see which tasks are on track and those that need your attention.
Unfortunately, project stakeholders and team members often don't understand the measures that are second nature to project managers. To learn how to transform this variance information into a form that other audiences can understand, see the Work Essentials article Tips for communicating project variances.
About the author Bonnie Biafore is a Project Management Institute (PMI)–certified Project Management Professional (PMP). She is a consultant, trainer, speaker, and award-winning author of several books about investing, personal finance, and project management, including On Time! On Track! On Target! Managing Your Projects Successfully with Microsoft® Project (Microsoft Press, 2006).