Reporting Schedule Performance with MS Project

You have a feel for whether your project is on schedule or not because you are the project manager and you have your ear to the ground. Slipping the beginning of QA testing or a key iteration end date will tell us that we are behind schedule, but does meeting those key dates mean that we’re on schedule? Does being behind schedule on non-critical path activities mean we’re still on schedule? Obviously, when activities on the critical path are behind schedule, the project is at risk of delivering late but is that the only good indicator of schedule health? The project manager should be able to answer all these questions and convey the information to stakeholders in a form they can understand.

Schedule Performance, or SP, is the yard stick chosen by the Project Management Institute to measure a project’s performance to schedule. Anyone who has completed a PMP course, or any other PMP exam preparation training, lately will recognize this acronym. It is described thoroughly for you in the PMBOK in the Cost Management knowledge area. The PMBOK approach to performance measurement is taken from Earned Value Management (EVM) technique. There are 2 specific measurements that fall in the Schedule Performance area: Schedule Variance (SV) and Schedule Performance Index (SPI). Schedule Variance indicates how far ahead of or behind schedule the project is in absolute terms. Schedule Performance Index is a comparative indicator, indicating whether the project is ahead of or behind schedule and by how much with either a percentage or number. A percentage greater than 100% tells you the project is ahead of schedule, a percentage less than 100% tells you the project is behind schedule. Whole numbers work similarly with 1.0 indicating a project on schedule a number less than 1.0 indicates a project behind schedule, and a number of greater than 1.0 indicates a project ahead of schedule.

Earned Value Management deals strictly in terms of cost, even when measuring performance to schedule. This means that all the metrics used to measure schedule performance are cost based. MS Project supports the Earned Value Management approach so opinion on this seems to be unanimous, however I still prefer to gauge schedule performance in terms of time, after all when the executive sponsor wants to know whether our project is on schedule or not they aren’t interested in hearing how many dollars we’re ahead or behind! Bear with me and I’ll show you some tricks that will let you convert the cost based information to a time base.

The Schedule Performance indicators compare two absolutes: the work actually performed and the work that was scheduled to be complete by the current date. These numbers are referred to in the PMBOK as the Budgeted Cost of Work Performed (BCWP) and the Budgeted Cost of Work Scheduled (BCWS). Budgeted Cost can be expressed in non-monetary terms, such as units of time which is the approach we’re going to use here. Your source for this information will be the project management tool you use to break the project work down and maintain the schedule. The single most common tool in use today is Microsoft’s MS Project. So the problem is, how do we convert cost based metrics to a time base?

The first method I’m going to describe will use the Earned Value report available from MS Project. To use this report you must have: saved a baseline of your project, provide actual start and finish dates for all activities, and provide the pay rates for all project resources. The first two criteria aren’t that difficult to meet. You should always save a baseline for your project and update that baseline whenever an approved change request changes the project plan. Supplying actual dates requires a little more diligence. You should update your MS Project file on a daily basis setting actual start dates for activities that started on the previous day and actual completion dates for activities that completed on the previous day; update the % complete field too while you’re at it. Supplying rates may be a little more tricky. To track cost information accurately you need to get the loaded labor rates for all the resources on your project. This figure is usually expressed as an hourly rate that includes a portion of the organization’s fixed costs (real estate, heating, electricity, etc.) as well as salary and benefits. Loaded labor rates will probably be broken down by employee category, such as grade; you will have to organize the resources into groups according to this classification if that’s the case. MS Project’s Earned Value report will provide all the information you need to perform your calculations: SV = BCWP – BCWS, and SPI = BCWP/BCWS (x 100 for percentage).

For those who don’t have access to financial information, you can still use the Earned Value report to provide schedule performance information. Use a rate of $1.00 per hour for all the resources, this will have the effect of equating hours of effort to $ of cost on a one to one basis. Expressing the SV in hours requires you to strip off the $ while expressing the SV in days or weeks will require you to apply the appropriate divider (divide the SV by 8 for an 8 hour work day or by 40 for a 40 hour work week). The SPI figure doesn’t need any modifications since it is a raw number. The report will provide this information as of a status date. The default for this date is today’s date unless you change it. To change it to last Friday for example, click project from the menu bar at the top of the page, click on project information in the resultant popup and then enter the date you want in the Status Date box.

You don’t have to track all this information in MS Project to be able to determine your project’s SV or SPI. Excel was created to allow users to manipulate data and you can export the data you need to an Excel spreadsheet and perform the necessary calculations there. To create the Excel spreadsheet you need you’ll have to export the MS Project file to an Excel file. This is done by selecting the Save As function from the File menu. The resulting popup window allows you to choose the file type you want to create. Choose Excel Workbook. Choosing this option will pop up the Export Wizard window. The wizard lets you map the MS Project fields you want to export and the name of the Excel column that will hold the field. There are several fields that are mandatory for our purposes: task id, task name, start date, finish date, duration, percentage complete, and outline code. You can choose additional fields if you want to see the resource assigned to the task for example.

The task duration and % complete fields are the ones you need to do your calculations, but you’ll need to do some editing first. Eliminate all the summary tasks from the spreadsheet using either the task id or outline code. Summary tasks are bolded in MS Project but unfortunately they don’t remain bolded when exported to Excel which is why you must use the task id or outline code. To use the outline code, assign a unique code to each task and another to the summary tasks, then strip out everything except tasks. To use the task id identify the hierarchies in the spreadsheet (e.g. parent task 1.2.5.1.2 and child tasks 1.2.5.1.2.1 and 1.2.5.1.2.2), then strip out all the parent tasks. Now strip out the time unit modifier from the duration field. There are several ways of doing this; try substituting a null (not a blank) for the word days and the same for the word day. Now you need to convert all the figures in the duration column to numbers.

Once all the excess information has been stripped from your spreadsheet you’re ready to do your calculations. The time equivalent of the BCWS is calculated by adding all the task durations that were planned to complete on or before the status date. The BCWP is found by multiplying the % complete by the duration of every task in the sheet. SV is still BCWP – BCWS and SPI = BCWP/BCWS (x 100 for a percentage).

Note that the above Excel calculations discount work that is ongoing from the BCWS count. You should include this information as a disclaimer on any schedule performance reports you communicate. If missing this work will skew the results too greatly, there is a fix which can be applied using the spreadsheet. Make a copy of your spreadsheet to do the calculations. Mask all tasks which have start finish dates in the past and all tasks that have start dates in the future; this leaves you with tasks which should have started by the report date but are not scheduled to finish until some time in the future (i.e. ongoing work). Now substitute the report date for the finish date. This will allow you to calculate the amount of work that should have been completed on the task by the report date (BCWS). Convert the dates to numbers in the start and finish cells by selecting the Number option. This will allow you to subtract the start date from the report date which gives you the amount of work (in days) that should have been completed by the report date. This is work that was scheduled to be done and should be added to your BCWS figure. Try doing this calculation if you’re not sure whether you need to do it or not. If the work is more than 1% or 2% of the BCWS figure, you should add it.

Organizations that use the Earned Value Management methodology should have all the information you need available, including loaded labor rates which are key to the EVM calculations in MS Project. You’ll need to invest the time required to maintain the additional information but the payback is the ease with which you can report any performance information you need. Use the methods I describe above as work-arounds when the cost information isn’t available or your stakeholders prefer to hear about the project in terms of time rather than dollars. Your ability to understand the concepts behind EVM if you read the PMBOK. Project managers who are certified PMP will already understand these concepts. Take a good PMP course or any other PMP exam preparation training and get yourself certified if you haven’t done so already.