Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Displaying Per-Use Costs Separately

 

Version 1, Published on October 19th, 2015

Please first see Disclaimer, Copyright Notes and Trademark Notes sections on the bottom of this page. This document is related to all

standalone desktop versions of MS Project Professional/Standard supporting the custom field formulas feature.

 

In this article, we will work on a simple schedule with four resources defined; namely, the work resources WR1 and WR2, the material resource MR1 and the cost resource CR1. Resources can be assigned to tasks by using either the Task Entry view or the Assign Resources dialog box. Suppose that the resources have already been assigned to various tasks in the schedule, as shown below:

 

 

Let us review the assignments:

 

  • MR1 has been assigned to Task 1 with a variable consumption rate at 2 units per hour (for example, by entering 2/hr to the Assignment Units field in the Task Entry view; MS Project automatically adds the label MR1). MS Project calculates the resource work value for this assignment to be 48 units (that is, 48 MR1).

 

  • MR1 has also been assigned to Task 2 with a fixed consumption rate at 2 units by entering 2 to the Assignment Units field (MS Project automatically adds the label MR1). MS Project calculates the resource work value for this assignment as 2 units (that is, 2 MR1).

  •  

    As a result, the total material resource units assigned become 50 units (the resource Work field shows 50 MR1). Per-use cost for the material resource is $20. Therefore, the total cost can be calculated as follows:

     

Total cost of the material resource MR1 = 50 MR1 * $200/MR1 + 2 * $20  = $10,040.00

 

  • The cost resource CR1 has been assigned to Task 3 only, so the total cost is $300 which is the amount of currency entered while assigning the resource. Note that per-use cost cannot be entered for a cost resource.

  

  • The work resource WR1 has been assigned to the Task 1 at the rate 100%; and the work resource WR2 has been assigned to three tasks, each at a rate of 100%, so the total cost of each work resource can be calculated as follows:

 

Total work resource cost for WR1 = 3d * $200/d + 100% * $50 = $650

Total work resource cost for WR2 = 1d * $200/d + 100% * $50  + 1d * $200/d + 100% * $50  + 1d * $200/d + 100% * $50  = 3d * $200/d + 300% * $50  = $750

 

The Resource Usage view with a customized table shows the details of the resources and their task assignments as follows:

 

 

Note that the rate at which a work resource is assigned to a task determines the amount of per-use cost to be added to the total resource cost. For example, assigning WR2 to the same three tasks at a rate of 50%, 60% and 70% would result in total resource cost of $690 as calculated below:

 

Total work resource cost for WR2 = 1 * $200 + 50% * 50  + 1 * $200 + 60% * 50  +  1 * $200 + 70% * 50  = 3 * $200 + 180% * 50  = $690

 

As a result, $600 is the total cost of the resource’s work on the tasks while $90 is the total amount contributed to the total resource cost by per-use costs from all assignments. Unlike the work resources, the per-use costs of the material resources are calculated based on the number of task assignments of the resources.

 

Suppose that we are in the planning phase of a project and we want to see the per-use costs in a separate column. We will now develop formulas for this purpose. The per-use costs are entered to the Cost Per Use field (the column title shows Cost/Use in the Resource Sheet view) for work and material resources. In MS Project, only one of the two accrual methods can be applied to the per-use cost (also referred to as fixed resource cost) of a resource on all of its task assignments; that is, either Start or End. If the enumerated field Accrue At is set to Prorated for a resource, then the resource’s per‑use costs are accrued at the beginning of its task assignments.

  

MS Project calculates per-use costs as follows:

 

(1)        Per-use cost of a work resource on a task assignment = assignment units x per-use cost entered for the work resource

           

(2)        Total per-use cost of a work resource = the sum of per-use costs on all of its task assignments

 

(3)        Total per-use cost of a material resource = number of assignments x per-use cost entered for the material resource

 

As it is seen in (1), per-use cost of a work resource on a task assignment is recalculated as the assignment units change. Also note that, in (3), the total per-use cost of a material resource is calculated based on the number of assignments, regardless of whether it is consumed at a fixed or variable rate.

 

MS Project calculates the total costs for the resources as follows:

 

            Cost of a work resource = Total regular work assigned to the resource * Standard Rate + Total overtime work assigned to the resource * Overtime Rate +

Total per-use cost (2)

 

            Cost of a material resource = Total number of units assigned to the resource on all assignments * Standard Rate + Total per-use cost (3)

 

We can implement the two different methods shown above in a custom field formula, in order to calculate per-use costs for each resource type. The enumerated resource field Type returns 0 for the resource type “Work” and 2 for the resource type “Material”; therefore, the Type field can be used to determine the resource type and then to use the related method accordingly. Before creating the formula, we need to determine how to process the values returned from the fields, therefore, let us first create simple formulas to see what values the field references return to the formula on the background, as shown in the table below:

 

Resource table

 

 

 

Number1

Work

Number2

Standard Rate

Name

1440

24 hrs

25

$200.00/day

WR1

1440

24 hrs

25

$200.00/day

WR2

3000

50 MR1

200

$200

MR1

          

 

 

 

 

Formulas:

Number1 field : [Work]

Number2 field : [Standard Rate]

 

Note that the Work field returns a value corresponding to units displayed x 60 to the Number1 field for the material resource. The values returned to the Number1 field by the reference to the work field for the work resources are the work amounts in minutes, regardless of the units used in the Work field.

 

And also note that the Standard Rate field returns the hourly rate to the Number2 field for the work resource; that is, ($200/day) / ([Minutes Per Day] / 60). We can create the formula below by taking these notes into account, as follows:

 

            The formula using the resource custom cost field Cost9:

 

iif( Type = pjResourceTypeWork, [Cost] - [Regular Work] * [Standard Rate] / 60 – [Overtime Work] * [Overtime Rate] / 60,

iif( Type = pjResourceTypeCost, 0, [Cost] - ( [Work] / 60 ) * [Standard Rate] ) )

 

Let us now create a dummy group RESOURCE_TOTALS based on the Text9 field (which is blank) in order to get the total values and also use the following formula on the Text11 field to set easy to understand row titles: iif( [Group By Summary], "ALL RESOURCE TOTALS:", [Name] ).

 

 

The “Calculation for task and group summary rows” option should be set to “Use formula” and “Rollup: Sum”, for the Text11 and the Cost9 fields, respectively. We can also modify the column headers (i.e., right-click the header and select the Field Setting command, then enter information to the Title box in the dialog box opened); for example, entering space for Text11 and so on, as shown below:

 

 

Note   In order to further customize the display of the grouped resource data, the Name column’s (it is the first column in the table) width is set to 0 in the table above. It is also possible to hide the Name column or move it to the end of the table.

 

 

In this example, the fields Cost9, Text9 (blank field) and Text11 have been used. We can save the table as the resource table TABLE_PER_USE_COST and create the VIEW_PER_USE_COST view with this table and the resource group RESOURCE_TOTALS. We can switch to the VIEW_PER_USE_COST view anytime when we need to see the total per-use cost values.

 

 

Creating a Graphical Report to Display Per-Use Costs for Resources

 

 

Follow the steps below, in order to create a graphical report to display the total per-use costs separately:

 

  • Create a blank report PER USE COSTS and insert a resource table with the following fields:

Text11 field. Note that there is no need to display the Name field on the table.

Text30 field renamed to <Resource Type> and contains the following formula:

 

Switch( [Type] = pjResourceTypeWork, "Work", [Type] = pjResourceTypeMaterial, "Material", [Type] = pjResourceTypeCost, "Cost" )

 

Cost field renamed to <Total Cost (incl. per-use)>.

Cost9 field renamed to <Per-Use Cost>.

 

  • Apply RESOURCE_TOTALS group also this report table (groups based on the blank resource field Text9 in order to show totals on the group header).

  • Modify formatting of the table to hide Text11 in the header.

  • Now copy the field Text11, Text30, Cost9 and the resource group RESOURCE_TOTALS and the PER USE COSTS report to the Global.mpt.  No need to copy the blank resource field Text9 to the global template.

 

 

Now all active project plan files can display this report (REPORT | (View Reports)Custom | PER USE COSTS)

 

 

 

Updating Per-Use Costs

 

When we directly edit the Assignment Units field’s value in an assignment, then MS Project updates the resource’s per-use cost associated. In the versions prior to MS Project 2010, MS Project recalculates the assignment units value in an assignment when we modify either one of the other two scheduling parameters, that is, duration or work, and thus updates the per-use cost associated accordingly. On the other hand, in the versions MS Project 2010 and later, MS Project displays the new units calculated in the Peak field, instead of updating the Assignment Units field accordingly. This means that the per-use cost will not be updated when MS Project recalculates the peak units value of the resource. So when does this happen ?

 

There are three cases when MS Project recalculates the Peak field’s data (i.e., the peak units value) in assignment(s):

 

  • Modifying task’s duration in a fixed-work type task

  • Modifying task or any assignment’s work in a fixed-duration type task

  • Although it does not serve a clear purpose, also adding or removing resources from an effort-driven task when the task type is set to fixed-duration forces MS Project to recalculate the peak units in all assignments.

 

Now the question is "how can we update the per-use costs as well, when the peak units are recalculated ?" Let us experiment with the cases mentioned above by using the example schedule:

 

  • Set Task 1’s type to fixed-work and increase its duration to 4 days:

 

MS Project will now recalculate the peak units as 75% but the assignment units will remain unchanged, and therefore the total resource cost for WR1 will also remain unchanged.

 

As an alternative, try this: undo the operation, keep the task type setting as fixed-work and enter 75% (see the note below) to the Assignment Units field. The duration will increase to 4 days and the costs for WR1 will be $637.50 as shown below:

 

Cost of WR1 = 24 hrs * ($200 per day / 8 hrs per day) + 0.75 * $50 = $637.50

 

 

Note that in both methods, the cost of the resource’s work does not change, but the per‑use cost changes according to the assignment units value in the latter method; it is reduced from $50.00 to $37.50.

 

Note   Use caution while entering the assignment units value by typing in since MS Project always rounds off the value displayed. For example, the Peak field will show 86% when we set task’s duration to 3.5 days (the assignment units will remain unchanged). We can now use the Immediate Window to see the value stored in the background (but first, open Task Usage view and click the Peak field):

 

Immediate Window

? ActiveCell.Assignment.Peak <enter>

  0.857142857142857

 

As it is seen, the calculated value has been rounded off to 86 while displaying. Now the task duration will be 3.49 days (which looks like a measured data rather than an estimated value) as soon as we enter 86 to the Assignment Units field. Instead, we should enter 85.7 to the the Assignment Units field. Then the task duration will be 3.5 days as we want since MS Project uses the value stored in calculations. Now both fields will show 86% but store 0.857 in the background.

 

Immediate Window

? ActiveCell.Assignment.Units <enter>

  0.857

? ActiveCell.Assignment.Peak <enter>

  0.857

 

Also remember that we cannot enter formulas to the assignment custom fields.

 

  • Next, let us set Task 1’s type to fixed-duration and decrease the task work from 24 hrs to 12 hrs.

 

MS Project will recalculate the peak units as 50%, and the assignment units will remain unchanged, but this time the total resource cost for WR1 will also change while the per-use cost remains unchanged.

 

Cost of WR1 = 12 hrs * ($200 per day / 8 hrs per day) + 1 * $50 = $350

 

Instead let us try this, alternatively: undo the operation, keep the task type setting at fixed-duration and enter 50% to the Assignment Units field. The work on this assignment will decrease to 12 hrs and the costs for WR1 will be $325.00 as shown below:

 

Cost of WR1 = 12 hrs * ($200 per day / 8 hrs per day) + 0.5 * $50 = $325

 

Note that the per-use cost changes according to the assignment units value from $50.00 to $25.00.

 

As a result, in versions MS Project 2010 and later, we need to edit assignments accordingly if we want to reflect the changes in peak units to the per-use costs as well.

 

 


Revision History:

Version 1 - October 19th, 2015 - Initial document


 

 

 

Disclaimer

The information contained in this document is intended only for the general interest of its readers and should not be used as a basis for making any business or other important decisions. Though all the efforts have been made to create accurate content, mistakes can occur. The author of this document cannot, therefore, guarantee the accuracy of content. The author of this document disclaims all warranties and must advise you to use this document at your own risk. The author of this document is not liable for loss of any nature resulting from the use of or reliance upon the information found therein.

 

Copyright Notes

All Content Copyright © Ismet Kocaman | ikocaman.pm[at]gmail.com | No part of this document may be reproduced, stored in a retrieval system or transmitted in any form or by any means, without the prior written permission of the Author (Ismet Kocaman). Do not quote/refer to the parts of the document and do not use it as training material without permission. 

 

Trademark Notes

Microsoft® is a registered trademark or trademark of Microsoft Corporation in the United States and/or other countries. The author of this document has no affiliation with Microsoft Corporation. All other trademarks mentioned herein are the property of their respective owners. Screen captures were used with authorization from Microsoft Corporation. This document is not a product of Microsoft Corporation.