April 22, 2012 2 Comments
Many project teams spend time talking about project metrics up-front, but then have weak follow-through on actual implementation of the metrics. The one metric tracked within most project offices is the overall status (Green, Yellow, Red), but even then it is often times not supported by factual project performance data. There is data captured throughout the project life cycle that can be presented to provide a better picture of overall project performance.
SharePoint 2010 provides features that enable project teams to expose project data in the form of easy to consume project metrics. The Status List in SharePoint is used to implement project metrics on your project site that are updated as project data is maintained throughout the project life cycle. As with any tool, the hard work lies in defining the metrics and business rules, not adding the metrics to the Status List. This blog provides several tactical tips on how to use the SharePoint Status List to implement project metrics that help measure and improve project performance.
5 Tips to Create Meaningful SharePoint Key Performance Indicators (KPIs)
The following represent several “how to” type tips for creating project metrics within a Status List on your SharePoint 2010 project site.
1. Define what to measure – In many cases the project office will have established standards or guidelines around project metrics utilized to measure project performance. As with all project management best practices you want to select the critical few metrics that will make the biggest impact on project performance. In addition, it is best to focus on metrics that can be produced as a by-product of the project information required to effectively manage the project. I focus on the following best practices areas from a measurement perspective:
- Schedule Performance – This metric can be derived from the project schedule using earned value, or based upon the schedule variance on key milestones (as a percent of the total duration for the milestone).
- Cost Performance – This metric is best calculated from the project budget information, either using earned value or based upon budget variance (be careful with budget variances that do not take into consideration some flavor of earned value in the estimate to completion (ETC) calculation).
- Impact of Change – What impact has approved changes had on the schedule and cost performance of the project? This metric should come straight from the project change control tracking tool.
- Risks and Issues – This metric is utilized to project the potential impact of high risks and issues on the future performance. This metrics should also be a fairly straight forward extract from the risk and issue tracking tool.
- Overall Project Status – Some project offices calculate the overall project status (Green, Yellow, or Red) as an aggregate of other project metrics. In most cases, I find it most effective for the project manager to assign the status during the weekly status process as a reflection of the cost and schedule performance, and in the overall health of the project (including trending of the project’s risk profile).
2. Establish the data source – Once you have defined the project metrics, you need to establish the source of the data that will be utilized to update the metric within you SharePoint Status List. The two sources that I utilize are Excel Spreadsheets stored in the document library on your project site (e.g., Cost Performance or Schedule Performance), and a SharePoint List utilized to track specific project information on your project site (e.g., Risks/Issue and Change Control).
- Excel as the Source – There are a couple steps required to expose data from Excel spreadsheets within the KPI. As previously mentioned the first step is to save the spreadsheet within a document library on your project site. The second step is to utilize the name manager function within Excel to identify the specific cell or range of cells that will be accessed from SharePoint.
Excel’s Name Manager utilized to identify the specific cells to be accessed by SharePoint.
Establish the location and named area when setting up the KPI in the Status List.
- SharePoint List as the Source – If the information for the KPI is maintained within a SharePoint list, you select the specific list and view (I discuss list views under point #4) from the Site Content displayed.
Select the SharePoint List and View when setting up the KPI in the Status List.
3. Define the parameters – After identifying the source of the data for the KPI, you need to define the parameters that establish the assignment of the metric rating (Green, Yellow, Red). First, establish whether “higher” or “lower than the target value is better for the KPI. Then specify how to determine if the metric is Green, Yellow, or Red.
- Excel as the Source – The definition of the targets of Green and Yellow are maintained within the Excel Spreadsheet, and you specify these fields (as Named Areas) when setting up the parameters for the KPI. In this example the values in the spreadsheet contain earned value targets for Green (1.0) and Yellow (.90).
- SharePoint List as the Source – You have options on how to define the KPI from a SharePoint list. It can be based upon a count of the items in the List View, or calculated based a specific field in the List. In the example below the schedule impact was defined based upon the sum of the schedule impact field in the approved change request view of the Change Request Tracking List. In this example the KPI is GREEN if equal to or lower than 10 days total impact, and YELLOW if less than or equal to 20 days (and RED if over 20 days).
4. Create the List Views – When using SharePoint Lists as a source of KPIs in the Status List, you must make sure the List Views are established that support the KPI. This point is best explained with the example below.
- Impact of Change – If the metric is utilized to communicate the impact of approved change requests on the Schedule, the List View must be filtered to only include changes with “Approved” status. In addition, the view must include any fields that will be utilized to compute the KPI (in this case “Sched Impact” field).
5. Use the Metrics – Now that you have gone to all of the effort to create the project metrics on your project site, make sure you use them to effectively and proactively manage your project. You should understand what caused a KPI to change from GREEN to YELLOW or RED (e.g., a new High Risk, or a slippage in the schedule), and initiate the corrective actions required to move the KPI back to GREEN. In addition, I find it a best practice to include these KPIs in your project status report, as well as corrective actions for YELLOW and RED metrics. Use of the SharePoint Status List to measure and communicate project performance also enables the ability to produce an on-demand / on-line project status report.