Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

Calculating Fixed Costs for Resources:

Cost/Assignment and Cost/Calendar Day

 

Version 1, Published on December 13th, 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 discuss two different methods to calculate the fixed costs for the work and material resources in a project plan, by using the custom field formulas.

 

Calculating fixed costs based on the number of task assignments

 

The fixed costs for the work and material resources can be calculated according to the number of task assignments, regardless of the assignment units. The steps below explains how to do it, by using the schedule above:

 

Entering fixed costs for all the resources to a resource custom cost field:

 

  • Firstly, we need to enter the fixed costs for the work and material resources to a custom field, such as the Cost10 field:

  • Open the Resource Sheet view and insert the Cost10 field next to the Cost/Use field.

  • Enter the fixed costs for all the work and material resources to the Cost10 field.

 

If there are values already entered to the Cost/Use field, then use the field reference [Cost Per Use] in Cost10 field’s formula in order to get the values and then delete the formula; or the values from the Cost/Use field can be copy-pasted to the Cost10 field. Then delete all the entries in the field Cost/Use for the resources since we do not want those values to be added to the total resource costs calculated by MS Project.

 

 

Creating the formulas and adjusting other settings for the custom fields:

 

  • Open the Resource Usage view and insert the following custom fields to the default table displayed (the Usage table); then enter the formulas along with the other settings explained:

  • Text7 field’s formula (fixed cost accrual):

iif( Not ([Group By Summary]), Choose( [Accrue At], "Start", "End", "Start"),"")

In MS Project, the prorated per-use costs are accrued at the start of the task assignment, therefore the last item in the list of the Choose function is also “Start” instead of “Prorated”. Thus, the Text7 field references the Accrue At field, and displays "Start" if the accrual method selected is "Prorated", which is not available for per-use costs.

 

The other settings for Text7 in the Custom Field dialog box are as follows:

Calculation for task and group summary rows - Use formula

Calculation for assignment rows - Roll down unless manually entered

As a result, Text7 will show our fixed cost accrual methods in both resource rows (by the formula) and task assignment rows (by rolling down). It will show blank in summary rows. Accrual methods displayed in the Text7 field are for information only, and they have no effect on the calculation of the resource costs incurred.

  • Text10 field’s formula (labels for the group headers):

                                        Switch(

                                        Instr( [Name],"Text10"), "Total for " & Mid( [Name], 9, 100 ),

                                        Instr( [Name],"Text30"), "GRAND TOTAL", True, [Name] )

The other settings for Text10: select the following Custom Field dialog box settings for the field:

Calculation for task and group summary rows - Use formula

Calculation for assignment rows - Roll down unless manually entered

We will later group the resource data in the Usage table based on Text10 and Text30. Then the resource custom text field Text10 will display custom row headers in order to make the table more readable (see the Use formula setting). Otherwise, it will show just the resource’s name in the resource row and rolled-down name in the resource’s task assignment row (see the related assignment row setting above). 

  • Cost10 field has no formula. Select the following Custom Field dialog box settings for the Cost10 field:

Calculation for task and group summary rows - Rollup: Sum

Calculation for assignment rows - Roll down unless manually entered

So here, we are using both the roll-up and the roll-down features of the resource custom field Cost10. It is important to understand how those two settings affect the Cost10 field’s data:

 

First, the fixed cost entered for a resource will be rolled-down to each task assignment row. Then the group summary rows of the Cost10 field will show the rolled-up sum of all these fixed cost values in the task assignment rows as soon as we apply grouping to the Usage table. As a result, the totals in the group summary rows yield fixed costs times the number of task assignments of the resources.

Customizing the Usage table further:

 

  • Insert the Type field to the table; this field automatically populates (rolls down the information in the resource line) the task assignment lines with the resource type as it is seen in the picture.

  • Insert the Cost field to the table. Do not hide the Cost resources by filtering otherwise the Cost field will display incorrect grand total value because of the missing costs of the Cost resources.

  • Insert the Unique ID field to the locked first column position.

  • Move the Resource Name (the Name field) to the last position in the table.

  • Rename the Usage table as FIXED COSTS TABLE and clear the checkbox Show 'Add New Column' interface in the Table Definition dialog box.

  • Use the Text Styles dialog box (FORMAT | Text Styles) to further modify the formatting of the information displayed in the table. This is how the Resource Usage view with FIXED COSTS TABLE looks like now:

 

 

Note that the Cost field does not include the values in the Cost10 field. That is, the per‑use costs entered has no contribution to the project costs on that schedule.

 

Grouping the table data:

 

  • We will now create a resource group named FIXED COSTS GROUP in order to see the totals as follows:

The first line (Group By line) will contain a blank field Text30 with no value, since we need to see the grand totals. The second line (Then By line) will contain the Text10 field. Order for both lines can be Ascending. Also it is important to select the checkbox Group assignments, not resources. See the dialog box below:

 

Now apply the new group to the table. The table will look like as follows:

 

 

  • As a final step, save the modified Resource Usage view as RESOURCE FIXED COSTS with the table set to FIXED COSTS TABLE and group set to FIXED COSTS GROUP. The resource custom fields used in the project plan are as follows: Text7 (contains formula), Text10 (contains formula), Text30, and Cost10.

 

Total resource cost excluding the per-use costs is $11,500.00 in this project plan. The resource fixed costs must be handled manually now since they are excluded from the cost data calculated by MS Project, and therefore, we need to determine how to track and add resource fixed costs incurred to the resource actual costs as the work progresses on the project.

 

Creating a group, a table and a view, all are one-time tasks. When needed, it is just sufficient to switch to the view in order to see the total resource fixed costs entered that are calculated, based on the number of task assignments. Hiding some columns (the Name column can also be hidden) and collapsing the outline (just select all the lines below the Text30 group summary header and then select Hide Subtasks command from the Outline drop-down menu in the Data group of the VIEW tab; or select Level 2 command in the Outline drop-down menu) will give the following compact view which summarizes all the costs in the plan:

 

 

In this table, the grand total for fixed costs (that is, $240) will only change when the number of task assignments changes, otherwise the value will stay constant throughout the project phases.

 

Note   Reselect the same group if you see #ERROR in the group-summary rows when you open the project plan file.

 

 

Calculating fixed costs for the work resources based on the total task assignment duration

 

In our scenario, some work resources in the project work in a remote site, and therefore, there are some fixed daily costs incurring, which amount to 2 percent of a resource’s daily standard rate. The daily fixed costs will be calculated based on the calendar days between the first and last day of the resources on the project site, regardless of whether the resource has been assigned to work or not for any calendar day. Also it is not dependent on the number of assignments during a workday. The fixed cost calculated for a work resource will incur at the end of the resource’s work on the project.

 

Using the schedule in the previous example, the formula in the resource custom cost field Cost7 calculates the fixed costs for the work resources as it is seen below:

 

 

            The Cost7 field’s formula:

 

iif( Type = pjResourceTypeWork, 0.02 * ( [Standard Rate] * [Minutes Per Day] / 60 ) * ( Int( Finish – Start ) + 1), 0)

 

In the formula, the expression Int( Finish – Start ) + 1 yields the number of days between resource’s Start and Finish dates. The field Standard Rate returns hourly rate to the formula, therefore it is multiplied by hours per day (that is, [Minutes Per Day] / 60) in order to obtain the daily rate (note that the entry is in $/day).

 

Next, grouping the data on the Resource Sheet view based on the resource type and selecting the rollup option “Sum” for the Cost7 field in the Custom Fields dialog box give us the total work resource fixed cost for the project (that is, $24):

 

 

Here, the fixed costs for work resources must be managed manually since they are excluded from the cost data calculated by MS Project, and therefore, we need to determine how to track and add these costs incurred to the actual costs reported as the resources complete their work on the project site.

 

 


Revision History:

Version 1 - December 13th, 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.