Consultants often need to calculate separate pay and billing rates for their projects. For example, when an organization such as a software company is doing work for a customer, it may want to track the difference between how much it pays a resource to do an hour of work and what it charges a customer for that same hour. Consulting organizations, contract software development shops, and even some internal IT support organizations need to track this “profit margin” for individual projects. This article will look at how you can use Microsoft Project to track these costs in a way that will allow for differing rates per resource. Our technique will track the costs down to the assignment level.

Setting up the tables
The first step is to set up the Cost Rate Tables. We’ll use a macro that requires us to employ Tables A and B. Table A will be for the pay rate for our resources—that is, the rate that we, as a company, pay the resources per hour to work for us. Table B will be the billing rate, which is the rate at which we bill our customers for the work done by the resource.

Figure A shows Table A for Resource One, and Figure B shows Table B. Notice that the rate in Table A is $50 per hour, while the rate in Table B is $100 per hour. You can use tables to store several sets of rates for your resources. For instance, if a resource has a number of skills, you might bill those skills at different rates. You would have a table for each set of rates and would then select the appropriate table on a project-by-project basis, depending on which skill was being used by the resource at that time. This does not help us with our goal of showing several different costs for the work that a resource does on a given project. However, by using Cost Rate Tables in our macro, we’ll be able to calculate a resource’s pay rates and billing rates for a project based on the values in those tables.

We’ll enter rate and billing information for each of our resources so that both tables are populated with the correct data.

Figure A

Figure B

The macro
Listing A contains the macro that will create the custom calculations we need in order to show our pay and billing costs for tasks and assignments.

First, we dimension several variables that the macro will use. We’ll use tskT and asnA for looping through the tasks and assignments of the project. The three remaining variables will hold temporary values for the calculation of our alternate costs.

  • Line 100 is a For…Next loop that will let us examine every task in the ActiveProject object.
  • Line 110 is an If…Then statement that checks to make sure the task object which tskT represents is not a blank line. If it is a blank line, the rest of the code is not executed, and execution begins again on line 260.
  • Line 120 loops us through all the assignment objects within the task that tskT currently represents.
  • Line 130 sets the value of the Assignment Cost1 custom field to equal the value of the Assignment Work field. This value is multiplied by the Standard Rate value from Cost Rate Table B for the resource associated with the Assignment represented by the asnA object. The use of the Left function is required here because the value returned by the CostRateTables(2).PayRates(1).StandardRate property is in a text format that looks like this: “$100.00/h.” The Left function allows us to remove the last two characters, /h, so that the remaining string can be used in the calculation.
  • Line 140 repeats the calculation of line 130 using the Assignment Actual Work field and inserts this value into Assignment Cost2.
  • Line 150 again repeats the calculation in line 130, using the Assignment Remaining Work value this time, and it inserts this value into Assignment Cost3.
  • Line 160 adds the value of the asnA.Cost1 field to the current value of the curCost variable.
  • Line 170 adds the value of the asnA.Cost2 field to the current value of the curActCost variable.
  • Line 180 adds the value of the asnA.Cost3 field to the current value of the curRemCost variable.
  • Line 190 loops to the next Assignment object in the task represented by tskT.
  • Line 200 sets the value of the Task Cost1 field for the task represented by the tskT object to be equal to the current value of the curCost variable, which at this point in the code is equal to the sum of all the Assignment Cost1 fields for all the assignments in the task. This ensures that the Task Cost1 field will represent the rolled-up sum of the Assignment Cost1 fields.
  • Line 210 does the same thing as line 200, except that it sets the Task Cost2 field to equal the curActCost variable.
  • Line 220 also does the same thing as line 200, except that it sets the Task Cost3 field to equal the curRemCost variable.
  • Lines 230 through 250 reset the value of the three cur variables to equal 0.
  • Line 260 is the end of the If…Then statement on line 110.
  • Line 270 is the end of the For…Next loop on line 100.

The last step in setting up the macro is to place a reference to it in the Calculate event for your project. This will ensure that each time Microsoft Project calculates the project, the macro will run and update the new cost information. The code required to do this is included in Listing A, so pasting the contents of Listing A into your project will also insert a reference to it into the Calculate event.

Setting up your project
To use the macro, you must insert the Cost1, Cost2, and Cost3 fields into your project so you can see the values the macro calculates. To do this, just select the field to the right of where you want the field inserted and then click the Tools | Insert Column command to bring up the Column Definition dialog box shown in Figure C.

Figure C

From the Field Name drop-down list, select Cost1. Then, enter a title such as Billing Cost in the Title field and click OK. Repeat the process for Cost2 and Cost3. You’ll wind up with a view similar to the one shown in Figure D.

Figure D

Project automatically calculates Cost, Actual Cost, and Remaining Cost using the rate information in the default Cost Rate Table (Table A, in our example). The three new fields are calculated using the rate information in Table B.

You can also view these costs at the assignment level by repeating the above field insertion process in one of the Usage Views. Figure E shows the Task Usage view with Cost1, Cost2, and Cost3 inserted.

Figure E

The rows showing Task One, Task Two, and Task Three each display the task level values of Cost1, Cost2, and Cost3. The rows under them show the assignment level values.