Within this and the following posts I want to focus on Earned Value Management (EVM) and how this project management & controlling instrument can be implemented in Standard Dynamics AX. The next subsection will give you a brief introduction into the main pillars of the EVM technique but cannot provide you with a comprehensive overview. If you are interested in more details about EVM, you can start digging into details by having a look at the following website.
A. Earned Value Management – Background
If your company is working on long-term projects, a major issue from a finance & controlling perspective is how to check whether those projects are on time and within budget. A simple “actual vs. budget” comparison is not able to provide you with this kind of information. That is because budget overruns can simply be caused by the fact that more work than originally planned has been executed causing the cost overrun. The next screen-print exemplifies this issue.
What is needed is thus an instrument that is able to incorporate a measure for the work accomplished, i.e. a measure of the “value earned”. Once you know this value you can compare it with your original planned value to calculate the so-called schedule variance that gives you an indication of whether your project is still on, ahead or behind schedule. (Please see the next screen-print for an example).
In addition, a comparison of the earned value with your actual costs results in the so-called cost variance that gives you an indication of whether your project is above or below budget. (Please see the next screen-print for an example).
Combining all three measures (actual, planned and earned value) into one single chart (as illustrated below) does thus equip you with an instrument that allows you controlling how projects actually run.
Within the next session I will provide you with details of the sample data that I used for the subsequent earned value management analysis in Dynamics AX.
B. Sample Data
The example used in the following is based on the project of constructing a house that starts in January 2016 and ends in May 2017. As per agreement with the customer, three (fixed price) payments have to be made:
- The first one in February 2016 for $350k
- The second one in July 2016 for $1250k and
- The last one in June 2017 for $400k
Those payments and the dates when the customer needs to make the payments are included in the customer invoice plan shown on the right-hand side of the next screenshot.
What you can also identify from the screenshot above are the planned and actual costs for the different construction phases. For reasons of simplicity, I only considered hour related expenses in the first line of each stage and ordinary vendor related expenses in the second line of each stage. As an example, in the first construction phase (“ditch for the foundation”) people need to work for 800 hours. With a cost price of $50/hour total costs of $40000 arise. In addition to those hourly expenses, a digger needs to be rented for the same 800 hours. As the digger costs $250/hour total costs of $200000 arise that add up to a total of $240000 for the first construction phase.
Based on the project plan, total costs of $1218000 should accumulate for the whole project. However, due to some price changes and additional (unplanned) work actual costs of $1329500 arose.
The next screenshot shows you the earned value and variance calculations that I did in MS Excel for the sample data illustrated above.
The important figure for the whole earned value (EV) calculation is the percentage of work completed (POC) that is multiplied with the planned value figure to arrive at the EV which is used for calculating the cost and schedule variances. In line with the AX calculation principles, the POC is calculated as follows:
Based on this formula a POC of 9,7% for the first stage results.
For the second construction phase a POC of 33,24% results.
With a POC of (rounded) 9,7% an earned value(EV) of $118088,64 can be calculated for the first construction phase (ignoring rounding differences).
The next graph summarizes the earned value analysis for the sample data and compares the calculated earned value measure with the actual and planned values.
Note: Please note that there are many alternative options available for calculating the POC. Using the actual hours worked and comparing them to the expected future hours of work that need to be done is only one possibility that is nicely supported by Standard Dynamics AX. If you are running projects where the majority of your costs are caused by expense or item transactions, this approach for calculating the POC might possibly not be the right one for you. Yet, the major concern in this post is not how the POC is calculated but rather how the project accounting engine can be setup in order to get the EV information posted on ledger accounts for subsequent analysis.
… to be continued in part (2)