MS Project: True Day Rate for Resources
For the majority of project plans, labor resources accrue cost based on their standard rate which is often expressed as $/hr. The labor cost is calculated by Project as:
Labor Cost = Work [hours] x Standard Rate [$/hour]
As a side note, even though a user can enter cost rate data in multiple ways, (e.g. rate/minute, rate/hour, rate/day, etc.), internally Project stores all cost rate data as rate/minute. For example, let’s say a user has entered a standard rate of $100/hr, (internally $1.66/minute), for a particular resource. If that resource works for 1 hour on a task, the cost will be $100 and if the resource works 10 minutes on a task the cost will be $16.66.
However, in some countries and for some types of plans, resources are paid on a true hourly, daily, weekly or monthly rate. To establish a defining term, let’s call it a day labor rate even though the rate period may be something other an a day. For example, a contractor charges $1000/day. It doesn’t matter if the contractor works 1 hour or 12 hours on any given day, the cost will be $1000 for that day. Without customization Project cannot cost a plan using this type of labor rate. So how can a user incorporate a day labor rate into their plan? One approach might be to simply treat the contract as a fixed price and use the Fixed Cost field adjusted for the total span in days for each task. Simple, but maybe not desirable for various reasons. Another approach to costing a plan with resources using a day labor rate is through VBA.
In developing a macro to calculate a day labor rate there are several things to consider
The normal Cost field cannot be used since it is tied to the normal resource standard rate. Therefore an extra cost field (e.g. Cost1) might be a good choice, keeping in mind that there are separate Cost1 fields for tasks, resources and assignments (see: http://social.technet.microsoft.com/wiki/contents/articles/31991.ms-project-data-types-task-resource-assignment.aspx)
Since the resource rate is based on the number of days, or part days, the total cost can only be summed up at resource level. It may not be practical to define a task based cost for that resource. For example, let’s say the resource works on task A for a day and a half. In the middle of the second day, the resource starts working on task B which is completed the following day. The resource worked a total of 3 days so the cost would be three times the day-rate. At resource level, the cost is straightforward but what about at task level? The rate is per day so how should the cost be allocated at task level? Does task A show a cost of 2 days and task B a cost of 1 day, or should the day-rate be apportioned to the hours associated with each task assignment (e.g half the total cost on task A and the other half of the cost on task B)?
If tracking using baseline data is required a custom field such as Cost1 cannot be saved as a baseline using the Set Baseline function. Two options are available. First, the macro code can linearize and set the baseline data using Cost1 values, or instead of using an extra cost field, the Fixed Cost field may be a viable option although the code will have to translate the resource costing into the Fixed Cost field which is a task field. The advantage is that fixed cost values are saved when a baseline is set.
If a resource using a day labor rate works multiple tasks during any given day period, care must be taken to avoid “double charging”. The resource’s assignments must be broken into daily “packets” instead of individual assignments as is the case with normal resource costing.
Example of a VBA approach
The simple project file below shows 5 tasks spanning a couple of weeks with two day-rate resources assigned. Resource “joe” has a day-rate of $100/day and resource “bill” has a day-rate of $50/day, both entered into the Standard Rate field on the Resource Sheet. For comparison, the Gantt Chart view shows the normal Cost calculated by Project and also the Cost1 total using a macro. Note that at task level normal cost is shown for each task, but for the day-rate calculation the total cost is only displayed at the Project Summary Task level, no attempt was made to apportion costs at task level.
The Resource Usage view shows the total day-rate cost of each resource and also the total number of days used to determine the resource cost. There is no timescaled data for extra fields so only the work values are shown for each day.
The macro that produced the above day-rate costing is shown below. It demonstrates the basic structure to create day-rate cost data and does not address day-rate cost at task level, nor does it address setting a baseline for the day-rate values. Those are left as an exercise for the reader.
Option Explicit
'This macro calculates cost based on a true resource day rate
' Resource Sheet must show resource rate per day (e.g. $100/da)
' total resource cost is written to the Resource Cost1 field
' total number of cost days for resource is written to the Resource Number1 field
' any resource assignment day is treated as a full day (e.g 1 minute=1 day)
' multiple assignments on any given day are treated as a single day (no double booking)
' macro code by John Project 3/14/15 at 9:26 AM (pi day)
Sub DayLaborRate()
Dim DaPd() As Boolean 'paid day tracker for each resource
Dim a As Assignment
Dim r As Resource
Dim Da As Single, RDays As Single, DaCnt As Single, TotProjCost As Single
Dim PR As Single
Dim RR As String
Dim AssDays As TimeScaleValues
Dim ADay As TimeScaleValue
'loop through all resources
For Each r In ActiveProject.Resources
DaCnt = 0
'find total time span in calendar days for this resource,
' set array size accordingly
RDays = DateDiff("d", r.GetField(pjResourceStart), r.GetField(pjResourceFinish)) + 1
ReDim DaPd(RDays)
'strip pay rate string of currency symbol and rate period (i.e./da)
RR = r.PayRates(1).StandardRate
PR = CSng(Mid(RR, 2, InStr(1, RR, "/") - 2))
'loop through all assignments for this resource,
' count each working day and eliminate duplicate days
For Each a In r.Assignments
'find offset, if any, for this assignment compared to resoure's
' earliest assignment (this sets the index for the array)
Da = DateDiff("d", r.GetField(pjResourceStart), a.Start)
Set AssDays = a.TimeScaleData(a.Start, a.Finish, _
pjAssignmentTimescaledWork, pjTimescaleDays)
For Each ADay In AssDays
'only look at days with work assigned
If ADay.Value <> "" And ADay.Value > 0 Then
'only count day if not already counted
' keep track of "counted days" in array
If DaPd(Da) = False Then
DaPd(Da) = True
DaCnt = DaCnt + 1
End If
Da = Da + 1
End If
Next ADay
Next a
'write number of paid days for this resource and sum cost for each resource
r.Number1 = DaCnt
r.Cost1 = DaCnt * PR
TotProjCost = TotProjCost + r.Cost1
Next r
'finally write total cost to Project Summary Task
ActiveProject.ProjectSummaryTask.Cost1 = TotProjCost
End Sub