Getting to Earned Value Analysis in Microsoft Dynamics AX 2012 R3
In working with professional services organizations to help themimprove their project performance through the implementation of the Microsoft Dynamics AX Service Industries ERP system, we run across a number of tools and methods that our clients’ project managers use to manage their projects.
One of the more popular tools used by more advanced project managers is the use of “earned value”, which helps project managers quickly determine if their projects are on track in terms of both budget and schedule performance so that they can make adjustments to how they’re delivering the project. In this blog article, we will explore how we can track the information needed to report earned value in Microsoft Dynamics AX 2012 R3, as well as illustrate one approach that we used to analyze this information.
The premise behind earned value analysis is the need to time-phase the capture of critical project-related data so that you can see how your project is trending relative to how it should be at any given point in the project. The primary components of earned value that need to be tracked are as follows:
• Planned Value (PV) – Planned value, or PV, is the baseline budget that has been established for the project. This budget is time-phased out over the life of the project to provide a benchmark upon which to compare actual costs and earned value over time.
• Actual Costs (AC) – Actual cost, or AC, illustrates the actual project costs that have been incurred to date as of the point in time that you are doing your earned value analysis. Some organizations may look at this from the revenue angle, or their customer’s cost, instead of their internal costs depending on how they manage their projects.
• Earned Value (EV) – Earned value, or EV, represents the portion of work that you’ve stated has been completed, or “earned”. There are many methods for determining how much value has been earned, ranging from simple % complete methods, to different distributions of earned value as work is started or completed (0/100, 50/50), and other models.
When analyzing earned value for a project, the project manager compares the earned value (EV) to the actual costs (AC) to understand how the project is tracking relative to budget at that point in time, and compares the earned value (EV) to the planned value (PV) to understand how the project is performing in terms of the schedule. Most project managers will use a 3-line graph to plot out the PV, AC, and EV to visually represent the relationship between these values and to see how the project performance is trending over time. There’s a great Wikipedia article that provides more details on the specifics of these measurements and some good examples at the following link: http://en.wikipedia.org/wiki/Earned_value_management.
Now that we’ve established what earned value is, let’s talk about how we can get there with Microsoft Dynamics AX 2012 R3. Dynamics AX does not natively support earned value management “out of the box”, however through the use of SAGlobal’s ProjAX (now known as Projects+) solution for Microsoft Dynamics AX and some crafty Excel-based reporting, we can capture and present the information necessary to develop an analysis of earned value. Here’s an example of how we recently addressed this for one organization.
• Planned Value (PV) – Baseline – To represent planned value, or the baseline, we used the original budget or forecast for the project. In order for this baseline to have meaning, the forecast must be time-phased out over the life of the project. To do this, we used the role-based resource forecasting module in our ProjAX (now known as Projects+) solution. This module allows you to build a time-phased resource plan for the project, done typically at the role level in weekly buckets, which then generates a time-phased forecast based on the underlying cost and rate tables for each role or employee, which produces a weekly forecast for the project. To create a snapshot of the baseline, or PV, in this example we simply built out the initial resource plan, which drove out a forecast, and we saved that as a forecast model called “Baseline”.
• Planned Value (Forecast) – To provide a mechanism for project managers to be able to update the forecast as necessary throughout the project, we then used a separate forecast model that we called the “Operating” model to capture any ongoing changes to the resource plan and forecast as the project is being executed.
• Actual Costs (AC) – Dynamics AX will automatically capture actual costs for labor, expenses, and materials based on the project transactions that are flowing through the system. In the analysis shown below, we focused on labor only, but this could in theory also contain other transaction types such as any expenses or material costs.
• Earned Value (EV) – As discussed above, there are many ways that organizations may choose to calculate or “earn” value in their projects. The simplest way of doing this with standard functionality in Dynamics AX is the use of % complete to represent the value that has been earned. The PM can periodically update the % complete in the WBS in Dynamics AX, which is then used as an input to the billing and revenue recognition processes. Dynamics AX stores the % complete for each invoice period, so as long as your invoicing frequency is aligned with your earned value analysis frequency, then this need is supported out of the box. If not, you’ll need to create some tables that will commit and store the % complete updates more frequently, and independent from the billing process.
So after putting this model in place in Dynamics AX, the next step was to figure out an easy way to extract this data for reporting and analysis purposes. Using some Excel magic, SAGlobal Solution Architect Jan Sennov was able to create the report below in Excel using the Excel plug-in for Dynamics AX that provides bi-directional integration with Dynamics AX. Simply by building an Excel query that extracted data about the forecast models (baseline and operating), the and percent complete and plotting the data out over time by week, he was able to then create the 3-line graph shown below that is nearly identical to those used in a formal earned value analysis. Of course, this was just an exercise to prove that this is possible, so in production it would need to be flushed out a little further.
In the above graphical report, the orange line on the top represents the planned value (PV) for the original baseline budget. The blue line on the bottom represents the actual costs (AC) that have flowed into Dynamics AX up to the current point in time, and then extends into the future in a lighter blue line that represents the operating forecast for the project into the future, basically ending at an “estimate at completion” or EAC. And then the grey line in the middle represents earned value (EV) or the percent complete for the project over time.
In looking at the above graph, we can see that the earned value (EV) is greater than the actual costs (AC) incurred as the project has gone along, meaning that the project is performing well from a budgetary standpoint (budget variance). However the earned value (EV) is below what was planned (PV) as time has gone along, meaning it is somewhat behind schedule (schedule variance). While this is the case, the delta between EV and PV over time is shrinking, which indicates that the project is trending in the right direction in terms of schedule performance. This illustrates why the time-phasing of this information is important in terms of understanding the trending aspect of the project’s performance.
Note that at the bottom of the graph, it is also calculating a Schedule Performance Index (SPI) and Cost Performance Index (CPI) which are two common outputs of earned value analysis that give more of a metric that shows how the projects are performing in terms of schedule and budget performance respectively.
As you can see, it takes a specific approach to capture the data required to analyze earned value in Microsoft Dynamics AX, so there are some caveats about how your project managers would need to use the system to facilitate this analysis. But it is definitely possible to get there using our ProjAX (now known as Projects+) solution for Dynamics AX and some creative Excel reporting.
For more information regarding SAGlobal and Microsoft Dynamics AX ERP solutions for A/E/C firms, please visit www.saglobal.com
Click here for the PDF version of: Getting to Earned Value Analysis in Microsoft Dynamics AX 2012 R3
About the Author
Andy Yeomans is Director of Business Development for SAGlobal, where his role is to help project-driven organizations evaluate and select ERP software systems to better manage their finances, projects, clients, and resources. Andy was instrumental in developing the organization's Microsoft Dynamics 365 industry solutions and practice area for the AE & Environmental industry. Andy has over 17 years of ERP experience and has helped dozens of AE & Environmental firms evaluate, select, and implement ERP software systems for their business.