Often, we see that project timing and project resourcing are managed in Microsoft Project, while project finances are kept in Microsoft Excel. Using the financial governance capabilities in Project is often an unexploited area. If you think about it, this is a waste. Assuming you add rates to resources in Project, that gets you halfway; Project can calculate anything for you directly from your schedule.
The trick here is to keep the right balance between the amount of input you do to get valuable output. Remember, Project isn’t meant to be accounting software; it’s a modeling tool that should deliberately simplify the reality. Accountants aren’t permitted to simplify reality. Neither will Project serve as an invoicing system.
Learn more from Sander Nekeman on this topic in his MPUG webinar, “Get rid of your separate Excel sheets: Use your MS Project schedule for project financials” available on-demand.
In this article, I’ll lay out an approach for using Project to enter a project budget for main cost types and compare planned cost against actual cost. The work will also predict eventual cost for the project based on actual progress. Plus, it will provide burn-down charts and earned value metrics to qualify the project’s performance — all the things required to be able to perform quality cost control.
Cost Management Terms in Project
You’ll have to understand the basics of several cost management terms to accomplish the work I cover in this article:
- Forecast: The projected cost for all past, current and future tasks. In other words, the predicted cost for the project. This is called “cost” in Project.
- Actual: The costs that have already been made or for which the obligation to pay is already made. This is called “actual cost” in Project.
- Remaining: All costs that are still to be incurred because they’re related to tasks that still need to be done. This is called “remaining cost” in Project.
- Budgeted costs: The amount of money you’re allowed to spend due to agreements made with your client, boss or stakeholders. There could be an agreed-upon tolerance level for deviating from this budget. (In this article, I use 10 percent as the tolerance.) This agreement would be well-documented, of course. This is called “budget cost” in Project.
- Baseline costs: At the point of accepting the project, you set a baseline, a copy of the schedule that adheres to the time, budget and scope constraints for the project. Baseline costs reflect the “forecasted” costs at that time the project is set up. This is called “baseline cost” in Project. The actual and forecasted costs can be compared to the budget as well as to the baseline costs.
How To Set Up a Project Budget
To set up a budget, you need to do three things:
- Create the budget resources in your file.
- Assign these budget resources to your project.
- Enter the amount of your budget per budget type.
This isn’t a difficult process, but the need for doing things in the right order and using different views for these steps can make it a bit confusing.
A budget resource is one of the four resource types and as such can be created in the resource sheet. Make sure you do this in the right order. Otherwise, it won’t work:
- Enter the new resource into the sheet and mark it as resource type “Cost.” I recommend using the naming convention:
Budget <budget category>
In other words: “Budget Internal Hours,” or “Budget Hardware” or “Budget Travel.”
- Use the budget category field to indicate the budget category. This will enable you to group on this field in order to see a nice summary of your costs against the budget by budget type.
- Double-click your new resource. In the resource information window, check the box before Budget.
- To assign any of these budgets to your project, you have to assign them in the Gantt chart to the project summary task, just like you assign any other resource to any regular task. If you don’t see the project summary task, go the layout tab and check the box on the right before Project Summary Task.
- To enter the amounts for the different budget types, go to the Resource Usage view, add the column “Budgeted Cost,” and type the amount on the line that has your Project’s name. You’ll see that the budget is being spread equally in time-phased increments over the length of your project. You could deviate from this equal spread, for instance, by applying the month’s timescale and adjusting the monthly amounts as you wish. Obviously, make sure that the total is still correct.
At this point the budget has been correctly entered!
How To Enter Costs for Resources
Project “knows” multiple cost types and each of these cost types requires its own way of being entered into the schedule. This illustration shows how the different cost types are distinguished:
Let’s go over each one.
- Work costs at a “standard rate”: William works 40 hours on a certain task and does this for a standard rate of $60 per hour. This standard rate could also have a standard overtime rate, which is applied for every hour worked over the standard number of work hours this person’s work week contains.
- Labor costs at a changing rate over time: Ruben has a rate of $60 per hour, but this rate will change to $70 per hour after his promotion on January 1.
- Material “variable” costs: These materials are used in the project and consumed per unit, such as sandbags or meters of cable.
- Material “fixed” costs: These materials have a cost per use unrelated to the number of units or number of hours of usage. An example is the transport costs for the rented concrete mixer. You pay it once regardless of how many days the mixer will be used. The daily rental for the crane, however, would be considered a material variable cost.
- Fixed resource costs: This is a resource you use only once. It isn’t actually a material used in the project. Often this category is called “out-of-pocket”‘ costs — travel tickets, training location costs or “pizza-Friday.”
- Fixed task costs: This would be the fixed-price consultancy task or a fixed-price outsourced part of the project. The price can’t change based on more hours and is always directly related to a certain task in the project.
When To Take the Costs
In the resource sheet you’ll also find the column “Accrue At.” With this you can determine when the costs will appear as actuals in your cost reports. For work resources, for instance, the obligation to pay the expenses accrues when hours are worked. In this case, you would select the default option, “Prorated.”
Another situation is the purchase, for example, of three PCs used for a certain task. Once you start using these PCs, you incur the costs (possibly even sooner, at the moment of purchase). Regardless the duration of this task, if you incur the costs at the beginning, you should select the option “Start here.”
The option “End” is used for a task that has a fixed price or “no cure no pay” assignment. The obligation to pay for this starts up once you have verified the work was done correctly (or the “cure” has completed).
How To Compare Costs to Your Baseline
With the (custom) view “Cost vs Baseline,” you can learn from your actual costs against baseline. The smileys will tell you how your cost is doing compared to the baseline cost.
- Green smiley: Value hasn’t changed.
- Amber smiley: Value has increased, but not more than 10%.
- Red smiley: Value has increased by more than 10%.
- Green minus symbol: Value has decreased, but not more than 10%.
- Amber minus symbol: Value has decreased by more than 10%.
Hovering over the symbol will show you the result of the calculation: cost/baseline cost. Notice that this is also done for summary tasks.
A value higher than one means that the current cost of activities is more than previously estimated for the project: We have lost money!
A value between zero and one means that the current cost of activities is less than previously estimated for the project: We have earned some buffer!
Although a value between zero and one is, of course, good news for the project, we also consider this to be an estimate that wasn’t so good. After all, those funds could have been freed up for other tasks or projects earlier.
The Cost Overview Report
The cost overview report shows four areas with cost information about your project.
The upper left corner of the report shows the forecasted cost (“Cost”); this is the actual cost plus the remaining cost for the entire project. It also shows the remaining cost separately as well as the percentage completed of the project. Note that this percentage refers to the duration that has passed. It doesn’t say anything about the work that has been done, actual cost being incurred or the actual physical product being finished.
The “Progress Versus Cost” chart displays whether your project is over budget or not. If the blue line (for cumulative percent complete) is below the orange line (cumulative cost), your project may be over budget.
The “Cost Status” table shows actual cost, remaining cost, baseline cost, cost and cost variance for every top-level task (outline level 1). These are your main deliverables in this case. Here we can see, for example, that the final assembly cost $200 over baseline and the preparation cost $998 less than we have baselined.
The “Cost Status” chart is a compilation that shows the actual cost (orange) and remaining cost (blue) in stacked columns for each top-level task, as well as a line for baseline cost. In the example, preparation costs came in below baseline cost (actual cost + remaining cost vs. baseline cost), and that the preparation is almost finished.
Resource Cost Overview and Cost Overview of Tasks
The reports “Resource Cost Overview” and “Task Cost Overview” provide useful graphs and a clear overview of the costs of the work in progress.
To open the reports click the Report tab, select “Costs” from the dropdown menu and chose Resource Cost Overview and Task Cost Overview.
The Resource Cost Overview shows three areas with information about the cost of resources in your project.
The “Cost Status” chart displays the actual cost (blue) and remaining cost (orange) per resource in stacked columns, as well as a line for baseline cost. You can see in the example that the resource Edwin is expected to cost the most, but that he costs less than the baseline cost. The reason for this could be a lower rate, fewer hours or a combination.
The “Cost Distribution” pie chart shows how costs are spread among different resource types (work, material and cost).
The “Cost Details” table shows the actual work, actual cost and standard rate (actual cost = actual work x standard rate) for each resource in your project team.
The Task Cost Overview also shows three areas, but this time with information about the cost of tasks in your project.
The “Cost Status” chart is a combination chart that shows the actual Cost (blue) and remaining cost (orange) in stacked columns for each top-level task as well as a line for baseline cost. This has the same elements as the chart shown in the cost overview.
The “Cost Distribution” chart displays how costs are spread among tasks based on the Status field. “Complete” means the task is done. “Future Task” refers to tasks with start dates greater than status date. “On Schedule” shows tasks where the time-phased cumulative percent complete spread to at least the day before the status date. And “Late” are the tasks where the time-phased cumulative percent complete doesn’t reach midnight on the day before the status date
In the example you can see that the biggest chunk of costs is for future tasks.
The “Cost Details” table shows fixed cost, actual cost, remaining cost, cost, baseline cost and cost variance for each top-level task of your project. Cost = actual cost + remaining cost. Cost variance = cost – baseline cost. Also in this table you can clearly see that the final assembly costs $200, more than baselined, and preparation came in $998 under baseline.
Earned Value Management
If you set a baseline and keep track of your tasks, you can use the earned value management report in Project. Earned value analysis is a method to determine the progress of your project and predict what the final costs and the possible finish date will be.
Earned value is calculated on these details in your schedule:
- Hours worked;
- Budget spent at a certain point (the status date); and
- Value delivered by the project so far (earned value).
For example, say I have 10 paintings to produce in 10 days. I sell them at $100 each. At the end of day five, I’ve only finished only three paintings.
- Day 5 is the status date.
- The value the project has delivered is $300.
- Assuming that each painting takes an equal amount of time to produce, after 10 days the earned value will be $600.
- At this pace, finishing all 10 paintings will take around 16-17 days.
Let’s use that example to set up the schedule for earned value management. I have to make 10 paintings (scope) in 10 days (duration). We sell them at $100 each (cost information). At the end of day five (status date) I have finished three paintings (progress information).
Earned Value Management Reporting
You’ll find the Earned Value Report in the reports tab under costs. It covers three new fields:
- EAC: Estimate at completion. If you extrapolate the current progress against the baseline to the end of the project, this will be the total costs of the project (in my example, 16-17 days for 10 paintings at my hourly rate).
- ACWP: Actual cost of work performed. This type of field shows costs incurred for work already done on a task, up to the project status date or today’s date. When a task is first created, the ACWP field contains 0.00. As progress (percentage of completion or actual work) is reported on the task, Project calculates the actual cost of work performed (ACWP). This is the cost of actual work plus any fixed costs for the task to date.
- BCWP: Budgeted cost of work performed. ) This type of field contains the cumulative value of the percent complete for the task, resource, or assignments multiplied by the time-phased baseline costs. BCWP is calculated up to the status date or today’s date. This information is also known as earned value. A variation on this is BCWS — budgeted cost of work scheduled.
- CPI: Cost performance index. This type of field shows the ratio of budgeted (or baseline) costs of work performed to actual costs of work performed up to the project status date. The time-phased version of this field shows values distributed over time.
- SPI: Schedule performance index. This type of field shows the ratio of the budgeted cost of work performed to the budgeted cost of work scheduled (BCWP/BCWS). SPI is often used to estimate the project completion date. The time-phased version of this field shows values distributed over time.
Earned Value over Time
The graph “Earned Value over Time” shows the earned value of the project based on the status date.
If the actual costs (blue) are higher than the earned value (orange), the project budget has been exceeded. For example, I have painted three paintings for a higher hourly rate.
If the scheduled value (gray) is higher than the earned value (orange), the project is behind schedule. For example, I have finished fewer paintings than I expected to have on this (status) date.
Variance over Time
The graph “Variance over Time” shows costs and efficiency variances for the project based on the status date.
If cost variance (blue) is negative, the project exceeds budget. For example, I have painted three paintings for a higher hourly rate.
If the schedule variance (orange) is positive, the project is behind schedule. For example, I have finished fewer paintings than I expected to have on this date.
Indices over Time
The graph “Indices over Time” shows the performance index for cost and schedule based on the status date. The greater the performance index, the better the project is on schedule (SPI) and the more costs are saved (CPI).
The concepts I’ve covered may come across as complex, but once you begin working with them, you’ll sort it out in no time because they’re fairly straightforward and just require a bit of practice. I advise you to tune into my MPUG webinar, which takes place on November 2, 2016 at noon Eastern time. If you miss the live broadcast (which gives you an opportunity to ask questions), you can watch the recording on demand.
In the meantime, consider how you can get more out of your Microsoft Project investment by using the schedule data you’re already tracking to keep an eye on financial and budgeting data related to your project as well. All you need is the will to automate work and save time.
A version of this article originally appeared on LinkedIn here.
Image Source: Chris Potter
