Running and Analyzing the Project Status Report

Help for running and analyzing the project status report in the project planning and management template for Excel

User Guide Navigation:
Business Spreadsheets User Guides: Project Planning and Management Multiple Regression Analysis and Forecasting Investment and Business Valuation Real Options Valuation Portfolio Optimization Portfolio Performance Tracking Customer Invoicing
Project Planning and Management Help Topics: Defining the Project Resources Defining and Entering Project Tasks Creating the Gantt Schedule Running and Analyzing the Project Status Report Participant Emailing and Workbook Generation

The Project Status Report can be run at any time to gain an insight into the current progress status of the project and identify risks in order to take necessary corrective action. The date for which the project status report is required is specified in the 'Resources' sheet. The project status analysis is broken into four main areas: Earned Value Analysis, Risk Analysis, Project Timing, and Completion Analysis.

Earned Value Analysis

Earned value analysis compares the planned resources to the projects time-based schedules and costs, to constitute a cost and schedule measurement baseline. The three main objectives of earned value analysis are:

To establish effective internal cost and schedule management control systems.
To monitor the project in real time and anticipate possible slippage.
To enable the manager to get timely data for determining unit type contract status.

Earned value parameters are calculated for each unit type and capital costs in order to calculate a Cost Performance Index and Schedule Performance Index for each component and for the project as a whole.

When running the Status Report, use is made of both budgeted and actual unit costs for the earned value analysis. If zero unit costs are detected, an option to base earned value on Hours rather than Value is given. This enables the earned value analysis to still be run on projects for which costs are not quantified (e.g. internal labor costs).

Three basic project performance measurement variables are required to perform the earned value analysis. These are:

BCWS – Budgeted Cost of Work Scheduled. This is the portion of the budget that corresponds to the work that has been scheduled to be done so far for the unit type. This is calculated by multiplying the number of units scheduled (as defined by the auto-timing function) by the budgeted unit cost.
BCWP – Budgeted Cost of Work Performed. This is the portion of the budget that corresponds to the work that has actually been performed for the unit type. This is calculated by multiplying the number of units completed by the budgeted unit cost. The BCWP can be considered as the worth of the work completed and is often referred to as Earned Value (EV). Conceptually BCWP is the BCWS multiplied by the percent complete.
ACWP – Actual Cost of Work Performed. This is the amount of money that has been expended so far in performing the work for this unit type. This is calculated by multiplying the number of units completed by the actual unit cost (specified in the 'Tasks' sheet).

From these three performance measures, two variances can be calculated:

Cost Variance: BCWP – ACWP. This represents the difference between what has actually been spent and what should have been spent. A negative cost variance indicates that costs are over budget for the work that has been performed.
Schedule Variance: BCWP – BCWS. This represents the difference between the project value of the project that has been earned and the project value that was scheduled to be earned. A negative schedule variance indicates that amount of project value that has fallen behind the schedule – i.e. that amount that should have been earned but has not.

The three performance measurements are also used to calculate the Cost Performance and Schedule Performance Indices, that provides a more standardized analysis of project cost and schedule performance.

CPI – Cost Performance Index: BCWP/ACWP. This represents the value earned by each dollar (or currency unit) spent. It gives an indication of whether is project is running over or under the budgeted costs. A CPI of less than 100% indicates that the project is over budget as the value of work produced is less than the amount spent.
SPI – Schedule Performance Index: BCWP/BCWS. This represents the amount of work that is completed relative to work that was scheduled to be completed. It gives an indication of project performance relative to the planned schedule. An SPI of less than 100% indicates that the project is running behind schedule as the number of units completed is less than the number of units that were scheduled to be completed.

Period 'snapshots' of both the CPI and SPI for the project can be taken at the bottom section of the Status Report. The Historical Project Performance chart plots the CPI and SPI at the periodic snapshot intervals, to give a clear view on the trend of the project performance since the start date. This trend can be used to view the reaction of the project to actions taken and the plan can be revised in response to the direction and intensity of the trends.

Clicking on the 'Take Snapshot' button will append the CPI and SPI values to the Snapshot History table as at the status report date, and update the chart accordingly. Snapshots can be removed by selecting any cell for the snapshot and clicking the 'Remove Selected Snapshot' button.

Cost Risk Analysis

The Cost Risk Analysis section summarizes the project's total operating and capital costs with projection and recovery calculations derived from the performance indexes. The analysis consists of three calculations:

The Budgeted Project Costs, and Budgeted Project Costs with Risk Contingency are extracted directly from the project's budgeting data in the 'Resources' sheet.
The Projected Project Costs are calculated by dividing the Budgeting Project Costs (without contingency) by the CPI index. This represents the expected cost of the overall project if cost overrun or under run continues as it has so far. If this is higher than the Budgeted Project Costs, it should be compared to the Budgeted Project Costs with Risk Contingency to determine whether there is enough contingency to cover the expected overrun.
Recovery Costs represent the costs that will need to be incurred to complete the project in the originally specified time by added additional resources. This is calculated by dividing the Budgeting Project Costs (without contingency) by the SPI multiplied by the CPI, and then subtracting the Budgeting Project Costs.

Project Timing

The Project Timing section calculates the expected duration of the project and compares this with the planned end date. The key information displayed here are:

The Projected Duration. This is calculated by dividing the planned duration by the SPI and represents the total number of days required to complete the project, assuming schedule performance is held constant.
The Project Deadline. This is copied directly from the project deadline date in the 'Resources' sheet.
The Predicted End Date. This is calculated by adding the Projected Duration to the original start date specified in the 'Resources' sheet.
Overrun/ (Under run). This represents the number of days after the deadline required for the project to be completed. This is calculated by the number of days between the projected end date and the project deadline.

Completion Analysis

The completion analysis section displays the number of units budgeted, scheduled, and completed for each unit type. The number of units completed is then compared to both the budgeted number of units from the 'Resources' sheet and the total scheduled number of units calculated by the 'Auto-Timing' function in the 'Tasks' sheet. These comparisons are used to determine number of Remaining Units to be completed and Percent Complete against both the budget and schedule.

This analysis can be used to give an overall view of project completion and isolate the unit types which may be holding back progress. It also allows the identification of any discrepancies that might exist between budgeted and scheduled units required to complete the project.

Screenshot: Running and Analyzing the Project Status Report

Back