How to Track Actual Costs in Microsoft Project
I remember an incident in college when my old roommate, Neil, came back to the apartment and announced his new data entry job. In his words, his new boss pointed at a stack of paper, then at the computer and asked him to “make them one.” Well, that’s what I am looking at right now, except instead of a pile of paper and a computer, I am looking at a LOB accounting application and the Microsoft Project desktop application.
First order of business: in true epistemological fashion, identify what I know, what I don’t know, and what I think I know about how Actual Cost is calculated within Microsoft Project. It turns out that there’s actually quite a bit that I didn’t know about the topic – or that I thought I knew which was actually wrong….hence the next couple of blog posts.
Review: Fixed vs. Variable Costs
Let’s kick this discussion off with a review of how costs are calculated in Microsoft Project. Essentially, there are 2-5 task level fields that need to be considered:
- Cost (also sometimes displayed as “Total Cost”)
- Fixed Cost
- Actual Cost
- Remaining Cost
For what it’s worth, note that Actual Fixed Cost is a field that is available in the Task Usage view but not on a Task view. Not sure why, but ok.
The rough calculations may be summed up as:
- Actual Cost + Remaining Cost = Total Cost
- Total Cost – Fixed Cost = Variable Cost (which is not an available field), where Variable Cost is the cost associated with the resource assigned to the task if there in fact is an assignment….
So the key takeaway from this discussion is that Variable Cost + Fixed Cost = Total Cost. The corollary to that statement, is that if Variable Cost equals 0, then Total Cost – Fixed Cost also equals 0. That statement is not true…well, sort of not true in some specific circumstances.
Next, and this honestly was news to me, there are actually some configuration options which affect how Actual Cost is tracked. In fact, there are three of them:
- Actual Costs are always calculated by Project – by default, this is turned on. When this option is checked, actual cost is calculated by Microsoft Project. In order for the user to manually enter Actual Cost data, the task would have to be 100% completed. If the option is unchecked, users may enter an Actual Cost at any point in the task prior to completion. This option is of particular interest to me as my tentative goal is to integrate this field in some fashion with an accounting application, i.e. to import the Actual Cost data from the external application.
- Edits to Total Cost Will Be Spread to the Status Date – This will be the subject of its own blog post, so stay tuned (for more Actual Cost excitement)!
- Default Fixed Cost Accrual – which really doesn’t impact Actual Cost, but it does impact how Fixed Cost is allocated across the task, so probably worth mentioning. This field sets the default for all new tasks, i.e. if I set the Default Fixed Cost Accrual to Start, then all new tasks created will default to the Start setting. Existing tasks will not be impacted.
For this and the next post, we’ll take a look at what that first option does. To illustrate this, I have created a simple project with two tasks. Each task is 10 days in duration and has a Fixed Cost of $10,000. The project is baselined.
Actual Costs Are Always Calculated by Microsoft Project is turned on.
When I try to edit the Actual Cost, I find that I cannot. The field is locked while the task is in progress. If I mark Task 1 as 100% complete, the Actual Cost field will autopopulate with the Fixed Cost value I entered at the beginning.
Once the task has been set to 100% complete, I can manually go in and revise the Actual Cost for the completed task. In this case, I change the Actual Cost from $10,000 to $15,000. Note that the total Fixed Cost for the task is also adjusted upward to $15,000.
So far so good. Everything is functioning as it should.
But what happens if you wish to update the Actual Cost in real time before the task is actually completed? This would be the case if you (1) had tasks that extended longer than a specific reporting period or (2) wanted to disassociate the in-progress task from the automated calculation (which is essentially % Complete X Fixed Cost).
Let’s uncheck the Actual Cost calculation option.
The first thing we notice is that modifying % Complete does not in fact update the Actual Cost field.
…but I can manually edit the Actual Cost field at any stage in the task progress. Hypothetically, I might do this if the Actual Cost for the task was actually being pulled from another application or spreadsheet. In the below example, I set the Actual Cost to $7,500. See how the different fields are calculated.
What’s worth noting is that the Remaining Cost is not decremented as I might have expected. In this case, we had a Cost of $10,000 and an Actual Cost of $7,500. I would expect the Remaining Cost to go down to $2,500.
That is not the case. Instead, the Total Cost is increased to $17,500, i.e. the Fixed Cost + the Actual Cost. What’s also noteworthy is that the Fixed Cost remains at $10,000. The Total Cost field should display Fixed Cost + Variable Cost, where the Variable Cost is the cost associated with a resource assigned to the task. But I don’t have a resource assigned to the task, so where is that cost being stored? Where is the $7,500 within the system? Call out the detectives (or since this is Texas, the horse-mounted posse), we have a missing value….surely, the varmint couldn’t have gotten very far.
To hunt down our elusive value, we fall back on an old trick that I’ve used in the past to troubleshoot schedules and/or figure out what’s going on behind the scenes. We export the project file to an Access database, thus exposing some of the internal calculations to further scrutiny.
To export to Access, select the Visual Report option under the Project tab. Select the Save Data option in the resulting dialog box.
Once I have the data in Access, I can build a quick query to look at the project assignments.
The query yields something like this:
Interestingly enough, I see assignments, even though I definitely have no resources in the schedule and certainly have not assigned resources to any tasks. My conclusion from this is that behind the scenes, the costs for tasks are actually tracked in phantom assignments. Those assignments lurk under the radar and are responsible for the behavior observed in the Fixed Cost fields when the Actual Cost is manually entered.
Hence, when I entered $7,500 as the Actual Cost for Task 1, I can see that was recorded in the “Unassigned” Assignment. This then explains where the Total Cost of $17,500 comes from, $10,000 from the Fixed Cost and $7,500 from the “Unassigned” Assignment Variable Cost gives me $17,500..