Author: Raphael Santos
Raphael Santos is a PPM enthusiast with expertise in providing Microsoft Project, Project Online, and Project Server solutions. He has worked in several projects to implement PPM tools, including projects located in Latin America (Brazil, Peru, and Argentina), in the United States, and in Africa. He is also a trainer with more than 10 years of experience teaching users how to use Project Management tools in a more productive way. In 2016, Raphael was awarded the MVP title by Microsoft in recognition of his contributions to the Project Management community. Raphael is a PPM Consultant at Sensei Project Solutions, a certified Microsoft partner specializing in project and portfolio management deployments. Sensei offers a complete set of services to help organizations succeed with their Microsoft PPM deployments. Services include full implementation and training as well as pre-configured solutions and report packs. Visit senseiprojectsolutions.com or contact info@senseiprojectsolutions.com for more information.
Creating a Risk Matrix Report in Project Online
When using Microsoft Project Online to plan, manage, track and control projects, project managers can benefit from using collaboration capabilities that are available in the platform. For each new project you start, there’s the option to set up a Project Site in which project artifacts — such as risks, issues and documents — can be created, managed, tracked and shared with people who are involved in that project initiative. The risk management process involves taking proactive steps to identify and track events and/or conditions that have the potential to affect future planning for a project. Microsoft offers an useful article on the best practices for managing risks in Project Online. Once project managers and team members start recording and tracking the risks that have been identified, the organization might start thinking about creating a report to help people make sense of all the risks that exist on the portfolio. In this article, I share how I create risk matrix reports in Project Online using Microsoft Excel. Step 1: Retrieve Data from Project Online First, use Microsoft Excel to retrieve data from Project Online. Open Microsoft Excel. On the Data tab, click From Other Sources | From OData Data Feed. You can use the following URL string to retrieve the data that will be used on the report: https://<YOURCOMPANYTENANTNAME>.sharepoint.com/sites/pwa/_api/ProjectData/Risks() Follow the wizard until you’re prompted on how you want to view the data in your workbook dialog. At this point, I would recommend bringing the data into a PivotTable Report. In this example, I have organized my PivotTable to display the following columns: Project Name, Risk Title, Impact and Probability: Step 2: Define the Criteria for the Layers A risk matrix report combines two dimensions, impact and probability, to determine whether the risk is sufficiently controlled. Thus, the second step is to create the criteria for the layers we’ll be using in the report. In this example, I’ve created nine layers with the following nine combinations: High impact and low probability; Medium impact and low probability; Low impact and low probability; High impact and medium probability; Medium impact and medium probability; Low impact and medium probability; High impact and high probability; Medium impact and high probability; and Low impact and high probability. Notice that the layers will be very important, because they’ll be used as the reference for counting the risks according to each possible combination. In Excel, create the layers as follows: Step 3: Create the Report Once you have created the layers that will be used on the report, in Excel add a new tab. Create your risk matrix table as follows: You’ll then have to use the COUNTIFS function to count the number of items in accordance with the combination of impact and probability. Use the screenshots below as a reference: Place the cursor in the cell that represents the high impact vs. low probability combination. Start the COUNTIFS function. As the first criteria_range, use the Impact column from the risks list tab: While still in the COUNTIFS function, go to the next argument (criteria1). Here you need to determine which criteria will be applied to the first range that has been selected. As you use the Impact column, you’ll need to determine the criteria by using the information that has been organized in the layers: This stipulates that for each row in the impact column, Excel will only consider values that are greater than or equal to 7. While still in the COUNTIFS function, move to the next argument, which will be represented by the criteria_range2. This time you will select the Probability column: Finally, you need to determine the criteria to be applied, which will be available in the layers combination: You can close the function to see the result: Now, repeat the steps for each possible combination in the risk matrix table to get your result: The table above shows the distribution of all the existing risks that have been created in the existing portfolio, combining their impact and probability. Through this matrix you can easily identify how the risks are distributed. For example, we can see that the highest proportion of risks is concentrated in an area with medium impact and low probability (36 items), while the second highest concentration of risks hase medium impact and medium probability (20 items). Layout Adjustments and Final Considerations To make effective use of the available data, you can create additional visualizations to increase the understanding of your report. Here are a few suggestions: Create charts to consolidate the information, such as “Risks by Category” or “Number of Risks Assigned by Resource.” Add slicers to the report to allow people to filter the data. Here I used a slicer for “Risk Status” and another for “Project Name.” Add conditional formatting to the risk matrix table, so that when the number of risks in each cell is equal to zero, the font receives the same color as the fill (to make the number zero “disappear,” which, because it’s not needed, is a good thing). The final layout might look like this: Remember that you can change the criteria for the layers to better adjust them to the risk management process that is in place in your organization. Image Source
Creating a Portfolio KPI Report in Project Online
*Please note: the PMI® PDU credit for this session is not “mpugwebnlearn052417” as indicated in the video. The correct PDU is: mpugweblearn052417 Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Download the files used in this session. Event Description: Being able to report accurate data, providing real-time information about the health of the entire portfolio of projects, is one of the most wanted features Executives and PMO members are looking for when they consider acquiring a PPM tool such as Microsoft Project Online. It is key for any organization that deeply depends on projects to run their business to understand and evaluate the performance of their portfolio in an easy and quickly way, so they can take strategic decisions based on live data. In this webinar presentation, Raphael Santos will demonstrate all the steps required to build a KPI report with data coming from Project Online, so you can rock your next project status meeting. Presenter Info: Raphael Santos is a PPM enthusiast, with expertise in providing Microsoft Project, Project Online and Project Server solutions. He has worked in several projects to implement PPM tools, including countries in Latin America (Brazil, Peru and Argentina), in the United States and in Africa. He is also a trainer, with more than 10 years of experience in teaching users on how to use Project Management tools in a more productive way. In 2016, Raphael was awarded with the MVP title by Microsoft, in recognition to his contributions in the Project Management community. Raphael works at Sensei Project Solutions, a company that offers a set of services to help an organization make their Microsoft PPM deployment successful, including full implementation and services, training as well as pre-configured solutions and Report Packs. You may contact him at raphael.santos@senseiprojectsolutions.com Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]
How to Create a Portfolio KPI Report in Project Online
Back in 2015, my colleague Nate Auchter wrote a great article in which he demonstrates how to build a key performance indicator report in Project Online using Microsoft Excel. (The full article can be found on the MPUG website here.) While Nate’s article uses a subjective custom field to create the KPI, this article places the focus on creating custom columns with data retrieved from Project Online to calculate and display the KPIs — so users would be able to evaluate the performance of the entire portfolio of projects based on live data. Initial Step: Define KPI Criteria The first step in building a KPI report is to define which is the criteria that will be applied to the data. In this example, we evaluate the projects by three measurable metrics: schedule, work and cost. The main goal will be to discover the percentage of variance for the three metrics and then display a collection of stoplights based upon the results. The criteria will be the following: Retrieve Data from Project Online To get started building our report, the initial step is to retrieve the data out of Project Online and bring it to Microsoft Excel. We’ll use two data connections from the OData feed: Projects and Project Baselines. Follow these steps: Open Microsoft Excel. On the Data tab, click From Other Sources | From OData Data Feed. To get only the fields and projects that will be used in the report, use the following query by copying and pasting it in the data field location. Don’t forget to replace the <YOURCOMPANYTENANTNAME> text with the name of your company’s tenant): https://<YOURCOMPANYTENANTNAME>.sharepoint.com/sites/pwa/_api/ProjectData/Projects() ?$filter=ProjectType ne 7&$select=ProjectId,ProjectName,ProjectOwnerName,EnterpriseProjectTypeName, ProjectStartDate,ProjectFinishDate,ProjectDuration,ProjectCost,ProjectWork,ProjectFinishVariance, ProjectCostVariance,ProjectWorkVariance,ProjectPercentCompleted Click Next. At this point, you might get prompted to insert your credentials to authenticate in your Office 365 tenant. Inform your credentials to proceed (if applicable). In the next dialog, select the table that contains the data we want — in this case, the “Projects” table — and click Next. In the next dialog, you can give a friendly name to your data connection so it will be easy to identify and use in the future. After doing that, click Finish. Finally, when finishing the import process, select the Only Create Connection option. The steps above will pull the selected data from the Projects table. Now you need to retrieve the complementary data, which is stored in the Project Baselines table. Follow the same steps we took previously, changing the query to the following: https://.sharepoint.com/sites/pwa/ _api/ProjectData/ProjectBaselines() ?$filter=BaselineNumber eq 0 & $select =ProjectId,ProjectBaselineCost,ProjectBaselineDuration,ProjectBaselineWork Manipulate the Data Even though we have all the data required to build the report, at this moment there’s not much we can demonstrate with it. To make the data meaningful we need to manipulate the data connections by establishing a relationship between them and creating some calculated columns. All of this can be accomplished through Power Pivot. In Microsoft Excel, on the Power Pivot tab, click Manage: The first action to be performed is to create a relationship between the two tables. Once Power Pivot is launched, click on Diagram View: You will see the two tables with the data that has been retrieved from Project Online: To establish a relationship between them, on the Design tab, click Create Relationship: On the Create Relationship dialog, first select the Projects table and then the ProjectId field. Then, select the ProjectBaselines table and the ProjectId field and click OK: After creating the relationship, on the Home tab, click Data View to return to the visualization of the data. Once the relationship between the tables is established, your next action will be to create the custom columns that will calculate the percentage of variance for schedule, cost and work. In Power Pivot, on the Design tab, click Add: First, we’ll create the custom column that calculates the percentage of finish variance. Use the following expression, which you can once again copy and paste: =IF(RELATED(ProjectBaselines[ProjectBaselineFinishDate])=0,-100, [ProjectFinishVariance]/RELATED(ProjectBaselines[ProjectBaselineDuration])*100) After creating the custom column, you can right-click it and rename it to “Percent Finish Variance” (or another name of your preference). Now you’ll need to create two more custom columns. Follow the same steps, using the expressions below: Percent Cost Variance =IF(RELATED(ProjectBaselines[ProjectBaselineFinishDate]) =0,-100,IF([ProjectCostVariance]=0,0,[ProjectCostVariance] /RELATED(ProjectBaselines[ProjectBaselineCost])*100)) Percent Work Variance =IF(RELATED(ProjectBaselines[ProjectBaselineFinishDate]) =0,-100,IF([ProjectWorkVariance]=0,0,[ProjectWorkVariance] /RELATED(ProjectBaselines[ProjectBaselineWork])*100)) Once all custom columns are created, you can close out Power Pivot and return to Microsoft Excel. Build the Report Once we have all the data that we need, we can start building the report. In Microsoft Excel, click Insert | PivotTable. Keep all the selections that were offered by the wizard and click OK. Next, in the contextual PivotTable Tools section in the Ribbon, click Design | Report Layout and select the Show in Tabular Form option: In the same contextual menu, click Design | Subtotals | Do Not Show Subtotals. Now, add the fields that will be part of the report to the pivot table. Drag and drop the fields to the pivot table areas as follows: Rows area: Project Name Project Owner Start Date Finish Date Values Area: % Finish Variance % Cost Variance % Work Variance You can also remove the unneeded subtotals and apply formatting to the fields in the pivot table report based on your preference. The result might look like this: As we want the percentage variance fields to display the stoplights accordingly to their values, we need to apply conditional formatting to them. Select one value in the % Finish Variance column and click on Conditional Formatting | New Rule: The rule will be applied as follows: In the Apply Rule To option, select All cells showing “% Finish Variance” values for “Finish Date.” In the Select a Rule Type section, keep the Format all cells based on their values option selected. In the Edit the Rule Description section, do the following: On Format Style, select Icon Sets. On Icon Style, select one option that displays for possible icons (any of the available ones) and check the Show Icon Only option. On Display each icon according to these rules, you will have to apply the following configuration: The result will look like this: After applying the rules, click OK to see your results. You then need to create the same rules for the two other custom columns (cost and work). The final result will look something like this: Finally, you can make your report look better by adding slicers, title, logo and legend: Remember that you can adapt the rules that have been used in this example to better align the icons to your company’s standards. Once you’re ready to go, you can upload the report to your company’s Project Online instance and make it available to the appropriate users. Do you have your own techniques for reporting KPIs? Share them with the rest of the MPUG community in the comments below!
5 Lessons on Microsoft Project Resource Engagements
In June 2015, when Microsoft first talked about the new resource engagements feature for Project Online and Project Server, there was great excitement in the project management community. We were finally going to have a formal way of managing the negotiation between project managers and resource managers by specifying the amount of work and time a resource could be associated with a project. More than a year has gone by since the new capability was introduced, and we think it’s a good time to share five lessons on how to maximize the use of this feature. Lesson #1: Set Human Resources to Require Approval for Assignments While not all resources in the organization require an approval before they can be assigned to tasks, at the least, it’s recommended that the high-demand human resources are set to require approval. This setting is beneficial for both project managers and resource managers, as they will have to work together to agree “in the calm and quiet of planning” on the utilization of resources. To set the resource to require an approval, follow the steps below. (You’ll need to have the right permissions in order to perform this action.) Setting this flag ensures the availability of these key resources is managed through Resource Engagement,. And it should significantly reduce the instances of multiple projects using the same resources and “burning them out” due to over-allocation. Lesson #2: Project Managers Must Submit Multiple Engagements It used to be that when using the “old” resource plans and a project manager needed more than one resource with the same role to work on a project, it was common practice to set the units of the generic resource to match that need. This way, if we needed two business analysts for the month of July, we could create a resource plan and set the work units for that month with 320 hours (or simply set the work units as “2 FTEs”). With resource engagements, the scenario changes. Now, if we need two business analysts for July, we recommend creating two separate engagements, each one representing one resource specifically: Why? Because the resource manager won’t be able to assign two named resources to a single engagement proposed at 200 percent. In other words, the resource manager can’t split an engagement in two. So, the ideal scenario is for the project manager to create one engagement for each needed resource. Then, the resource manager will be able to assign a resource to each request individually. The same scenario applies when a project manager needs to extend an engagement because the dates on the project have changed. Let’s say you have two business analysts committed for the month of July, but the project gets into execution and for some reason is delayed and you’ll need to extend the engagement for the two first weeks of August. We recommend creating (and submitting) a new engagement representing the request to extend these resources. This engagement alerts resource management to the change, and you can work with them through resource engagements to find a staffing solution supporting both your project and any other projects the business analysts were scheduled to work on at the beginning of August, as shown in this screenshot: Lesson #3: Human Resources are Added to Your Team, But Aren’t Assigned to Tasks When you create a new project in PWA, the initial project schedule may also have the generic resources assigned to some tasks in your schedule (primarily when the enterprise project type has a project template assigned to it). These generic resources are the perfect starting point for creating your initial resource engagement requests; however, it’s VERY important to know when a resource manager accepts the proposed engagement and commits it with a human resource, they won’t actually replace the generic resources on the assignments. So, even though you have the human resource available to work on your project, the replacement process for the tasks will have to be done manually. Lesson #4: Committed Human Resources Are Not Changeable When a resource manager replaces a generic resource with a human resource and commits to a resource engagement, he or she won’t be able to change the committed human resource. If the person who has been given to the project manager isn’t appropriate and needs to be replaced, the original engagement needs to be deleted and a new one has to be created and submitted for approval. When editing a committed engagement, project managers will be able to change Start and Finish Dates, as well as the Units or Work that have been committed — but won’t be able to change the actual committed resource, as shown here: Lesson #5: Violations of Committed Engagements When all the Resource Engagements have been negotiated and approved and the planning phase has ended, the project moves into execution. Once in execution, we can experience changes in the resource requirements for a number of reasons. If any change in resource usage violates the committed engagements, then Microsoft Project will inform you with some wonderful new icons in the “Indicators” column. There are three main ways that the engagement rules can be violated: You can use the “Engagement Inspector” to identify what is causing the engagement to be violated, as well as to apply the actions to solve it: Scenario 1: A resource that requires an approval is assigned to a task, but no engagement has been committed: Scenario 2: The assignment is outside the boundaries of a committed engagement: Scenario 3: The work required for the assignment is beyond the work approved on the engagement: 4 Final Observations To wrap up this article, we offer these four general comments and observations on the use of resource engagements: A version of this article originally appeared on the Sensei blog. Image Source Steve Caseley, is a PMP, PMI-ACP, a Scrum Master and a Principal Consultant. Steve has worked in the project management field for over 25 years, and has a wealth of practical experience in successful project delivery. As a result, he has many battle scars, but none have been fatal. Over the years, Steve has helped a wide range of companies implement PPM Systems and Best Practices and has practical, hands-on PM experience in a wide range of industries, project types and sizes. Steve’s passion is working with organizations to improve overall project delivery efficiency through the implementation and adaption of Industry Best Practices in Project and Portfolio management and implementation of effective PPM tools supporting these best practices. Steve has presented at international PM conferences and Microsoft Project User Groups over the years.
2 Ways to Manage Resource Availability in Microsoft Project
When starting to plan the development of a new project, one important task for the project manager is to identify the roles and responsibilities required to deliver the project based upon the agreed scope. You also will need to have a clear understanding of the availability of the resources that play roles throughout the project lifecycle. So it’s frustrating to discover during the execution of the project that a key member of the team isn’t available in the next week due to vacation — which was already planned more than six months ago. To avoid problems with the resources not being available to perform the tasks under their care, it’s important to manage information about resource availability, a job that can be handled in Microsoft Project. Options to Manage Resources Availability You have two options to manage resource availability in Project: We’ll use the example presented in the schedule below to make simulations and discuss the pros and cons for each option. Changing the Resource’s Availability When you change the availability of a resource in Microsoft Project, you are basically telling the software that during a specific period that person’s availability will be reduced. When making this change, it’s possible to identify the tasks assigned to this resource during the period of his or her unavailability because they’ll be marked with the “over-allocated” indicator. Let’s say that you have been informed that the resource Walt Nickel will be on vacation from 11/02/2015 to 11/13/2015. Based on that information, you need to “inform” Project of Walt’s unavailability. Navigate to the Resource Sheet view. Then double-click the resource Walt Nickel to display the Resource Information dialog. Look at the Resource Availability area. There you can configure the periods in which Walt isn’t available or in which his availability will be reduced. Change his availability as follows: In the Available To field on the first line, type 11/01/2015, which is the last date for which Walt will have 100 percent of availability prior to his vacation. On the second line, in the Available From field, type 11/02/2015, which is the starting date of his vacation. Then, in the Available To field, type 11/13/2015, which is the date Walt’s vacation ends. After that, in the Units field, type 0% to make Walt unavailable for this period. On the third line, in the Available From field, type 11/14/2015, which is the date Walt will be fully available to work in the project again. In the Units field, type 100%. Click OK. You will instantly see that Project indicates that Walt is over-allocated, as the period of his vacation conflicts with some tasks that have been assigned to him: There are many different ways you can see over-allocated resources in Project. In this example I switch to the “Resource Usage” view to see the tasks that were affected by Walt’s unavailability: You can also view the over-allocation using the Team Planner or Resource Graph views, or through Project’s reports. The positive side of changing the resource’s availability is that by understanding when and where the resource is unavailable, you can take proactive actions to reorganize the schedule (for example, reassigning tasks to other resources when possible), in order to reduce the impact in the project. On the other hand, you’ll also have to take care of the corrective actions manually; for big schedules this can be challenging. Changing the Working Time The second option for changing the resource’s availability is to configure the person’s working time. This option allows you to set restrictions to a resource’s calendar, creating nonworking time for a specific period. When using this approach, instead of informing you that the resource is over-allocated, Project will move the tasks along and assign them to the next date when the resource is available to work. For example, let’s say you’ve been informed that the resource Nate Auchter will be on vacation from 10/13/2015 to 10/16/2015. Based on that, you need to tell Project of Nate’s unavailability. Navigate to the Resource Sheet view. Then, double-click the resource Nate Auchter to see the Resource Information dialog. However, instead of changing Nate’s availability, let’s change his working time. Click the Change Working Time button. In the Change Working Time dialog, create an exception to Nate based on his vacation, as follows: Click OK and OK. You won’t see any signal of over-allocation for Nate. However, notice that a task assigned to him, “Interviews with Project Managers and key users” was changed automatically by Project, due to Nate’s unavailability. The task was previously scheduled from 10/13/2015 to 10/26/2015, and now it is scheduled from 10/19/2015 to 10/30/2015. Consequently, the schedule, which was planned originally to finish at 11/30/2015, is now finishing at 12/04/2015: The positive side of changing the resource’s working time using this method is that Project will make the changes to reorganize the tasks automatically based upon the resource’s availability. However, sometimes it can be hard to understand where the changes have been made and how they impact the schedule. Now that you know the two available options to control your resources availability, it’s up to you to decide which one best meets your needs. Have another approach for handling resource availability? Share your ideas with the MPUG community in the comments below. A version of this article first appeared on the Sensei Project Solutions blog.
Create Beautiful Reports on Project Desktop with Tools Other Than Just Project!
Project Management Institute (PMI)® Professional Development Units (PDUs): This Webinar is eligible for 1 PMI® PDU in the Technical category of the Talent Triangle. Event Description: Even though Project Pro for Office 365 is available through a monthly subscription, there are many companies and individuals who still use old versions of MS Project (Project 2010, Project 2007 and – believe or not – Project 2003) to track their schedules. All these people sometimes struggle on trying to create reports to quickly understand their project performance, once the reporting mechanism in old versions of the software is not as good as the mechanism that was introduced with the launch of Project 2013. What most people do not know is that it is possible to export data from MS Project to a MS Access database. By doing this, we can then connect to the database with MS Excel and create beautiful, interactive dashboards, getting quick insights of the key project information. Also, this approach provides the user with the ability of keeping the report up-to-date, and also to reproduce the report for other projects if needed. The key take-aways of this presentation are: Knowing how to export data from Project to a MS Access Database Knowing the information that is exported and how to manage it Knowing how to use MS Excel to connect to the database Creating a beautiful, interactive status report Keeping the report up-to-date Using the report for other schedules Presenter Info: Raphael Santos is a PPM enthusiast, with expertise in providing Microsoft Project and Project Server solutions. He has worked in several projects to implement PPM tools, including countries in Latin America (Brazil, Peru and Argentina) and Africa. He is also a trainer, with more than 10 years of experience in teaching users on how to use Project Management tools in a more productive way. Raphael works at Sensei Project Solutions, a company that offers a set of services to help an organization make their Microsoft PPM deployment successful, including full implementation and services, training as well as pre-configured solutions and Report Packs. You may contact him at raphael.santos@senseiprojectsolutions.com Have you watched this webinar recording? Tell MPUG viewers what you think! [WPCR_INSERT]
Increase Your Schedule Understanding with Smart Project Indicators
When using Microsoft Project to track their projects, project managers want the ability to identify any issues that may harm their schedules. One of the best ways to achieve this is through the use of graphical indicators. By default Microsoft Project offers an out-of-the-box graphical indicator called “Status Indicator,” which presents the current status of a task (learn more about this field). In addition, you can create your own custom indicator, based on a formula of your choice. In this article I show you how to create a new graphical indicator for the tasks, based on their finish variance and critical path. Choosing the Criteria The challenge when creating a new schedule indicator begins in choosing the criteria. If your company is going to use the traditional stoplights, what should be the rules applied to the indicator in order to show the red, yellow and green colors? The most common answer is to use the percent of variance. In this case, if a barrier is broken, then the indicator changes its color automatically. One common practice is: The use of rules based on the percent of variance is a good way to see how much the task is slipping compared with what was planned. To create a custom field that calculates the percent of variance in your schedule, follow these steps: 1. In the ribbon, click on Project | Custom Fields. 2. In the Custom Fields dialog, select the Number Type. Select a number field that hasn’t’ been used and click Rename. In this example, I call this field “% of Finish Variance”. Click OK. 3. In the Custom attributes area, click on the Formula button. This will launch the editor dialog, where you can create the formula you want to use. In this example, you can customize the field by typing the following formula: IIf([Baseline Finish]=ProjDateValue(“NA“) Or [Milestone],0,[Finish Variance]/[Baseline Duration]*100) This formula has a simple syntax. If the Baseline hasn’t been saved ([Baseline Finish] = “NA”) or if the task is a milestone, then the percent of finish variance will be equal to 0 (zero). Otherwise, Project has to calculate the number of slipped days ([Finish Variance]) over what was initially planned ([Baseline Duration]). 4. In the Calculation for task and group summary rows area, choose the Use formula option. Click OK to finish the creation of the custom field. Now, let’s look at the picture below. It shows a project plan to implement Project Server 2013: Once your schedule is revised and ready, you can save its baseline. After that, you’ll have to insert the custom field you created into the Entry Table: Now let’s say that a resource calls you to report that task number 3 — Prepare infrastructure — needs one more day to be completed. So, instead of five days, you’ll log six days as the duration for this task. The result will be: After changing the duration of the task from five days to six, it’s now possible to see that the field, “% of Finish Variance,” is showing the number 20, which represents the percent of variance for this task based on what was planned (one day of variance over five planned days of duration). If we were following the criteria proposed for the graphical indicators, it would show the red icon (percent of variance greater than 10 percent). However, even though the task has a high percent of variance, this variance is not yet causing impact on the overall project. The reason for this is that this task is not in the critical path (in other words, this task still has space to be delayed without delaying the project’s finish date). Let’s look at another scenario. You receive a call from your customer, who says that task number 6 — Interviews with Project Management Key users — won’t start on August 10, as planned, because there’s an important meeting that the PMs need to attend, so they won’t all be available for the project work. If you change the start of the task to the new proposed date — Tuesday, August 11, the result will be: Now you can see that the change made in this task represents 10 percent of variance over the planned finish date. Considering the proposed criteria for the indicator, it should show the yellow icon (percent of variance between one and 10 percent). However, the change on this task has an impact in a considerable number of tasks in the schedule, including the project finish date. Now the project has a finish variance of 2.7 percent because this task is part of the critical path. The point is that even if the task has a minimum percent of finish variance, we should take a closer look to see if it’s on that critical path, which means the variance will affect the entire project. At this point, we could consider creating a graphical indicator based on the following criteria: Creating the Critical Path Indicator To create the custom graphical indicator, follow these steps: 1. In the Ribbon, click on Project | Custom Fields. 2. In the Custom Fields dialog, select the Text Type. 3. Select a text field that hasn’t been used and click Rename. In this example, I call this field, “Critical Path”. Click OK. 4. In the Custom attributes area, click on the Formula button. This will launch the editor dialog, where you can create the formula you want to use. In this example, you can customize the field by typing the following formula: IIf([Baseline Finish]=ProjDateValue(“NA”),”No Baseline”,IIf([Finish Variance]>0 And [Critical],”Critical Finish Variance”,IIf([Finish Variance]>0 And Not [Critical],”Non-critical Finish Variance”,”No Variance”))) 5. In the Calculation for task and group summary rows area, choose the Use formula option. 6. In the Values to Display area, click in the Graphical Indicators button. After that, you can choose the icons you want to show, as seen below: 7. At the left upper corner of the dialog, select the options, “Summary rows inherit criteria from nonsummary rows” and “Project summary inherits criteria from summary rows.” 8. Click OK, and then OK again to finish the creation of the graphical indicator. After creating the graphical indicator, you will have to add it to the Entry Table: By combining the percent of finish variance for the task with the overall impact this finish variance is causing on your schedule, you’ll gain a much better understanding of your project. A version of this article originally appeared on the Sensei Project Solutions blog. Image Source
Calculating the Percent of Variance in Microsoft Project
Have you ever wanted to know the impact of a given variance in your schedule percentage-wise? For example, let’s say you have a finish variance of five days in a task that was originally planned to be executed in 20 days. If we calculate the variance of the task (5 days) over what was planned (20 days), the answer will be 25 percent. In this article I show you how to calculate that and present it in Microsoft Project. As a by-product, you’ll also learn how to create simple formulas that help you customize Project to better fit your needs. Three Tips for Getting Ready If you’re not familiar with creating custom formulas in Microsoft Project, I recommend that you have a look at this free, awesome 11-page guide from MPUG that shows the required steps when creating your own custom fields and graphical indicators. When creating my own custom fields, I like to make sure that some pre-requisites are in place to ensure that the field is calculated properly: Make sure that the schedule has been baselined; Make sure that the task is not a milestone; For formulas that are based on cost, make sure that the task has a planned cost; and For formulas that are based on work, make sure that the task has a planned work. To understand how to set a baseline in your project, read this MPUG article by Sensei’s Kenneth Steiness on baselining best practices. Calculating the Percent of Variance for the Finish Date Let’s get started with dates. Once you go through all the steps of building your schedule and saving a baseline in Microsoft Project, do this: Click on Project | Custom Fields. Select the Number Type and then select the Number1 field (or the first available field if the Number1 is in use). Rename the field to % of Finish Variance. Click in the Formula button. Now you should be seeing the Formula for % of Finish Variance dialog box. There you will have to type your custom formula. To facilitate this step, copy and paste the syntax below: IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”),0,[Finish Variance]/[Baseline Duration]*100) Click OK. You will see a message from Microsoft Project saying that now this field will be using the calculated expression you inserted. Click OK again. In the Calculation for task and group summary rows, select the Use formula option. Click OK to finish the creation of the formula. Now, let’s have a look at the syntax to understand the formula that you’ve entered. Let’s start with this section: IIF([Milestone] OR [Baseline Finish]=ProjDateValue(“NA”),0, First, this expression is checking to see if the task is a milestone. This comparison is important because, in general, when a task is a milestone, its duration is equal to zero, so we don’t divide any value by it. Second, the expression checks to see if the value inside the Baseline Finish field is equal to “NA” (not available). If one of the two statements is true, then no calculation has to be made. [Finish Variance]/[Baseline Duration]*100) If the task is not a milestone and has been baselined, the formula calculates what the percent of variance is and then multiplies the result by 100 to convert it to a whole number. Now, add the custom field to your table: After setting the baseline for the project, try to make a change in one or more tasks in order to discover how this affects your schedule. In the example below, I’ll change the duration of the task number 6 (Interviews with Project Management Key-users) from 10 to 11 days. The result will look like this: It’s now easy to understand that the modified task has a percent of variance of 10 percent (1 day of variance over 10 planned days), while the impact in the whole project is 2.27 percent. (We can also see the impact in all the tasks that are related to the modified one.) Calculating the Percent of Variance for Cost In order to create a custom field to calculate the percent of variance for cost, you will have to follow the same steps that were taken previously. However, the syntax of the formula will have a slight change, as follows: IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”) Or [Baseline Cost]=0,0,[Cost Variance]/[Baseline Cost]*100) Be aware that, as pointed out, now we’re also examining whether the task has a planned cost before making the calculation. Calculating the Percent of Variance for Work The percent of variance for work will be very similar to that used for cost. The syntax will look like this: IIf([Milestone] Or [Baseline Finish]=ProjDateValue(“NA”) Or [Baseline Work]=0,0,[Work Variance]/[Baseline Work]*100) There you have it — a simple way to find out the percent of variance across time, cost or work in your project using a basic formula. Image courtesy of Photosteve101 — CC 2.0