Tracking Actual Costs in Microsoft Project 2010 (Part II)

Continuing in the thread of documenting more on Actual Cost calculations than you ever wanted to know, let’s look at what happens when we manually edit the Actual Cost for a task on which a resource is actually assigned.

For Those of You Just Joining Us…

For review, what we identified in the last post is that each task appears to come by default with two assignments (even when no resource has been assigned), i.e the assignment when there is no assignment – perhaps demonstrating another of those peculiar intersections of Zen and Microsoft Project. 

One of those non-assignments tracks the task Fixed Cost, and one of those non-assignments tracks variable costs.  When there are no variable costs, that second assignment appears to do double duty by tracking adjustments associated with manually editing the Actual Cost field.

Below is an Access query against an MDB file derived from an MPP file with a single task and no assignments. See the two non-assignments, one not assigned to a resource called “Task’s Fixed Cost” and the other not assigned to “Unassigned?”

image

Tasks with Single Assignments

Now let’s take a look at what happens when we actually do add assignments.  Again we’re using my basic project schedule for illustration purposes.

image

For comparison purposes, here’s what my Access query looks like against that project.

image

Now let’s assign a work resource to the task.  I will set the resource cost to $1,000/day.  I also set the resource name as “Work Resource.”

image

Going back into Access, that looks like this:

image

Note that I now have a new unassigned assignment against the project summary task.  Not quite sure what’s happening there, but maybe I’ll come back and take a look at that in a later post.

Going back into Project, I make sure that the Actual Costs Are Always Calculated by Project option is unchecked.

image

Now I manually set the Task Actual Cost to $5,000.  Let’s take a look at what that does in Project.

image

First off, we see that the Actual Cost was allocated to the Work Resource.  I now see an Actual Cost of $5,000 on the resource – although the amount was not decremented against the Remaining Cost, so now I have a Total Cost of $15,000.  Fixed cost is still $10,000, so we can see that Fixed Cost + Variable Cost = Cost, or $10,000 + $15,000 = $25,000.

But let’s say that I don’t want the Total Cost to go up.  I want the Total Cost to remain the same.  Perhaps work was performed ahead of schedule.  Perhaps my resource incurred extra costs.  For whatever reason, I want to manually adjust my Actual Cost, but keep my Total Cost at the original $20,000 estimate.  So I change Total Cost to $20,000.  Interestingly enough, the Fixed Cost was decremented to $5,000, and the resource Cost remains at $15,000.

This means that any adjustment to the Task Actual Cost gets applied to the Resource Cost, whereas an adjustment to the Task Total Cost gets applied to the Task Fixed Cost.

image

Drop it into Access and here’s what I see.

image

This is where things get interesting.  Let’s try this on a task with no Fixed Cost.  I again assign my $1,000/d resource.

image

I set the Actual Cost to equal $5,000 and reset the Total Cost to equal the original $10,000.  That yields this:

image

See how the Fixed Cost is now suddenly showing a negative number?  That’s a bit weird.  The numbers all kind of work out, although now my resource is showing with a cost of $15,000 whereas before he only had $10,000.

Going back into Access, we see this:

image

So again the Actual Cost is added to the Remaining Cost for the resource.  When we modify Task Total Cost back to the original value, the offset is booked to the Task Fixed Cost, i.e. the reduced Task Total Cost appears as a negative value in the Task Fixed Cost.

Preliminary Conclusions

Based on these experiments, I might draw the following conclusions about projects where the setting Actual Costs Are Always Calculated by Project is unchecked.  The following assumptions are made based on a process where the user manually enters (or imports) Actual Cost data and then manually enters (or imports) Cost data to correct for the offset, i.e. a two step process.

  1. The Task Cost field will be accurate.
  2. The Task Actual Cost will be accurate.
  3. The Task Remaining Cost will be accurate.
  4. The Task Fixed Cost field will be incorrect, as it is adjusting for the Actual Cost number that was manually entered.
  5. The Resource Cost field may be incorrect, as it will include the Actual Cost manually entered plus the Remaining Cost.
  6. The Resource Remaining Cost field may be incorrect, as it will include the Actual Cost manually entered plus the Assignment Total Cost.

EVMS Implications

Thinking through the implications of this phenomenon on EVMS calculations, I find that it looks like EVMS will no be impacted.  Here’s a quick rundown:

 

  1. BCWS (PV) – is calculated based on the baseline cost values.  Presuming that the project is in fact baselined prior to manually editing Actual Cost fields, this value should still be valid.
  2. BCWP (EV) – is also calculated based on the baseline cost values.  Presuming that the project is in fact baselined prior to manually editing Actual Cost fields, this value should still be valid.
  3. ACWP (AC) – In theory this could be impacted by these values, insofar as the negative value in the Fixed Cost field may offset the Actual Cost incurred during the performance of the task.  In reality, this does not seem to be the case.  When I plot the projects out using the EVM Over Time Report, the ACWP appears to work fine.  I don’t see any of the negative values appear in the report.  It would appear that there is some logic built into the tool to preserve the ACWP values on tasks marked complete with negative Fixed Costs.  If needs must, I may revisit that specific mechanism in a future post.

Next up….how does this phenomenon impact tasks with multiple resource assignments?

Advertisements
Tracking Actual Costs in Microsoft Project 2010 (Part II)

15 thoughts on “Tracking Actual Costs in Microsoft Project 2010 (Part II)

  1. Robert says:

    I love this post. It if anything has helped me validate what I have done in the past. Let me run something buy you. On many of the IT project I conduct Project Controls on we report monthly (Gov. contracting stuff). For the most part I could care less who is doing the work (person wise). So many of the cost we receive from other contractors are based on their manning management. I apply cost to a task via a resource with a per hourly rate derived from the total cost provided by the contractor, This does spread the cost correctly, as the contractors do validate the data. I would like to have MS Project calculate actuals every week but, at the end of the month I would like to apply the adjusted actuals based on monthly finical reports. I would think this could be done via the Fixed Cost Column. What is your take on this matter?

    Again thanks for the blog.

    Rob

    1. You could probably do it via Fixed Costs. One thing I’ve started doing is using material resources for this purpose. So I have a material resource for Contractor 1. I set the Standard Rate to $1/unit, then assign the task at X units of the resource – thus giving me the estimate for the contractor (or X units/day or whatever). When I apply actuals, I actually apply them to the Actual Work column (it works for some reason), and have Project calculate Actual Cost for me.
      That being said, Fixed Cost also works – as long as you work through the issues of compensating for Actuals booked during the progress of the task. That first approach with the material resources may be easier, but I would have to think through it.
      In fact, that might also be the solution to your third question. If using material resource, in theory you could calculate each week, then at the end of the month, go into the Resource Usage view, zoom out to the month, and enter Actual Work coresponding to the Actual Cost booked. Project will calculate the Actual Cost for you based on the unit cost of $1 (i.e. 10 hours X $1/hour = $10).
      Probably a longwinded solution, but it seems to work….probably some simpler solutions available I would guess.

      1. Andrew, thanks for your very thorough approach top the subject. I have found another possible solution to this issue which I’d like your take on. If you:
        1. Untick as you suggested above “Actual Costs are always calculated…”, and
        2. Makes sure under File\Options\Advanced you select “Default EV method” as Physical % Complete (otherwise you get the problem you mention above)
        Then,
        3. Add a “dummy” task at the level of your Control Account called “CA Code Entry”
        4. You can then add the Account system “Actual Costs” to that task in Task Usage view, and the result is that Actual costs are not calculated as you suggest above but roll up to the Summary Actual Costs for the EV calculations.
        This of course assuming that you use the actual hours worked against each task by each resource using the Assignment Form in say Resource Usage view.
        This seems to be the only way I know to get Accounting Actual accrued costs to interact with Project 2010. What do you think?

  2. Shannon says:

    Hello… I use MS Projects for scheduling 7 subcontractors in the substation construction utility industry. They each get a standard rate, OT rate, subsistence and auto rate per day, and mileage reimbursement that varies. I need help recording actual costs and forecast costs using MS Projects on a weekly basis. I could manually enter in costs that I have pulled from QuickBooks, but I want MSP to calculate it for me. Is this even possible? My goal is to have accurate actual costs calculated and recorded per employee, per job AND be able to accurately forecast costs per employee, per job for an expected duration. Please help! admin@ktinspection.com

    1. If you’re trying to integrate with something like Quickbooks, I’d say you might try a couple of options: drop QB into Excel and figure out a way to merge from there (ref. the Excel Import Wizard), or write some VBA to go out to an Excel sheet and import the various figures. The latter would be a bit of work, but not too strenuous for somone familiar with Project and VBA.

  3. Ravi Ramachar says:

    I liked this post. I use MS Project for scheduling and tracking projects. I have a construction project going with a fixed price contract. The payment schedules are identified for Every Milestone. The actual costs have started to increase owing to small changes in specs, abnormal price hike in materials, etc. Tracking using MS project, I have defined a cost resource for the contractor (different costs applicable for different milstones, besides the number of workers at any point of time is warying). I am interested in computing EVM for my project. Could you help me with some tips?.
    Can EVM be applied for fixed price contracts in the first place?

  4. Well, I’d check the EVM guidance, but in theory you have an internal cost and an external cost – and calculating EVM off the internal cost in a fixed price contract is no different than any other EVM exercise. I’m just not sure how you’d go about presenting that to your customer. If the margin within the Fixed Price contract is transparent, it wouldn’t be a challenge.

  5. Tammo Wilkens says:

    Andrew, this is a great post, thanks for sharing. I was wondering if there was a way to export and then import the time-phased data, such as the daily Actual Work field? The ultimate goal is to get the actual hours data from the time tracking system and import it into Project. I have not found a way to get to the periodic (time-phased) data.

    I have done a Copy/Paste from the Task Usage table in Project into Excel, updated the Actual Work values, and then Copy/Pasted it back into Project. This works but is way too risky though as the import would have to go into the exact same layout/filter/grouping etc to map the pasted data correctly.

    So, Im looking for a more direct import means, even if the time tracking has to create the data in Excel format. Prefer Excel as more users are familiar with it than Acces. Also prefer to use
    VBA only under “duress” 🙂

    Your thoughts would be appreciated.

    1. Take a look at the Visual Report options for exporting. That creates a local cube of data in Excel.

      For importing – I’m pretty sure you would have to either copy/paste or use VBA. You could in theory finagle it through the use of status dates, regular updates, etc. – as this would force Actual time into the correct time period – but it’s a lot of work to try to maintain.

  6. Alex says:

    Thanks Andrew. This may be a fundamental question, and I’m not sure.

    When requesting updates from my team, they give me a %complete, which automatically updates actual work, then acutual costs. Here’s what i can’t fingure out: Let’s say 100hrs is estimated ($100). Halfway through the project, the team indicates they are 25% complete, but have incurred 75hrs ($75). When I enter 25%, actuals hours is automatically changed to $25. How do I update %complete indepdendently from hours incurred?

    In context of Earned Value, I’m trying to get:
    EV = 25
    PV = 50
    AC = 75

    This is driving me nuts! Appreciate your help!

    1. It depends, but you should probably just be entering Actual Work and Remaining Work in your updates – then letting the system calculate % Complete. Hence, your AC would be 75 and your EV would be 25 – which is your goal.

  7. S Jain says:

    Do the above apply to MS Project 2013 too? For a task with fixed cost only marked 100% complete, I am getting a large negative AC, small positive values of EV and PV. The AC is -2.447M while the actual cost is 2.324M! Thought that AC should equal actual cost for a 100% complete task but that’s not how it is working out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s