Mastering Custom Field Formulas in MS Project | Articles


By Ismet Kocaman

 

 

 

Calculating Material Resource Costs Separately

 

Version 1, Published on November 29th, 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 how to calculate the material resource costs of a project separate from the other resource costs within the same project plan file by using a set of custom field formulas. 

 

As it is seen in the table below, the arithmetic operation (or an explicit conversion to decimal number) performed on a reference to the custom text field Text1 before passing its value to a custom number field Number15, enables us to get more than two decimal places of the original value in the Text15 field. On the other hand, the Number15 field displays the value as rounded off to two decimal places.

 

Task table

Text1

Number10

Text10

Number15

Text15

6.24500

6.25

6.25

6.25

6.245

Text1 field : <enter decimal numbers>

Formulas:

Number10 field  : [Text1]

Text10 field         : [Number10] & “”

 Number15 field  : [Text1] * 1 or enter CDbl([Text1])

Text15 field          : [Number15] & “”

 

Having discovered this behavior, we will now use the custom text fields in order to calculate the material resource costs incurred by using standard rate and the consumption amounts entered with three decimal places as shown in the example below. The resulting value will also be rounded off to three decimal places.

 

 

Testing the Method

 

Task Sheet view of a simple schedule below shows several tasks with work and material resources assigned:

 

 

In this schedule, the standard rates for the work resources have not been entered (see the Resource Sheet view below), therefore the costs calculated by MS Project will include only the material resource costs. As a result, we can compare the total actual cost of the material resources calculated by the formula, with the Cost and Actual Cost field values calculated by MS Project. Note that MS Project automatically rounds off the standard rates and the fixed consumption rates while entering; for example, the fixed consumption rate 17.456 is rounded off to 17.46 and then displayed and stored as 17.46 units.

 

 

Suppose that all the tasks are now complete and the amounts consumed are equal to the fixed consumption rates estimated while assigning the material resources M1 and M2. Let us now enter the actual consumptions for M1 and M2 to the custom text fields Text2 and Text3, respectively, as shown below.

 

 

A custom number field (e.g., Number1) is used to calculate the actual costs by the formula below. Note that the formula contains the standard rates with three decimal places:

 

iif( [Resource Names] Like "*M1*", [Text2] * 4.337,0) + iif( [Resource Names] Like "*M2*", [Text3] * 5.231,0 )

 

Note   The formula above needs to be modified if the Resource Names field is likely to contain more than one resource matching the name pattern.

 

The custom number field Number1 is used as an intermediate field to obtain the sum of the actual cost values for the material resources, calculated at the task level since the custom text fields do not have a rollup option to sum all the sub-values at the summary level. Therefore, we will select the Rollup: Sum option in the section “Calculation for task and group summary rows” in the Custom Fields dialog box while entering the formula for the custom number field. The custom number field is hidden in the Task Sheet shown above; it will display the calculated values with two decimal places, but we will get the values with three decimal places through a custom text field formula referencing the custom number field as shown below:

 

            The Text20 field’s formula:

 

iif( [Number1] <> 0,"$" & Format( [Number1],"#,0.000"),"")

 

It is important to select the Use formula button in the section “Calculation for task and group summary rows” in the Custom Fields dialog box while entering the Text20 field’s formula, otherwise the field will not display the total value at the summary row.

 

As it is seen in the Task Sheet view, MS Project calculates the total actual cost for the material resources as 1105.54 while the Text20 field’s formula gives 1105.317. The Text20 field shows 1105.317359 when the formula [Number1] & “” is used in the field.

 

You can use the technique explained above to perform calculations by using the values with three or more decimal places, if it is a requirement in your project. But note that a rounding error in cents may not necessarily represent a significant loss in accuracy of the result which in fact depends on how accurate the consumption data have been collected. Therefore, using the custom text fields enables us to perform calculations by using numbers with additional precision but it does not guarantee more accurate results.

 

 

Calculating Material Resource Costs Separately

 

The schedule above is used to compare the results. We will now calculate the incurred cost for each material resource separately, by using the custom text and number fields. Suppose that the accrual method is “End” for the material resource costs in the schedule above. Then, the resource costs can be tracked as follows:

 

  • Assign material resources with 0 amounts (or units) and enter 0 for their standard rates, so that the associated material resource costs will not be added to the work resource costs calculated by MS Project (that is, the Cost field).

 

 

  • Next, enter the estimated material resource consumption amounts to the Text2 and Text3 fields as shown in the Task Sheet view below (we will use Text2, Text3 and Text20 for both estimated and actual data based on the current phase of the project, therefore, the column headers are updated):

 

 

The Text20 field now shows the total material resource estimated cost at the summary level (that is, $1,105.317); we can use this value while discussing the project budget in the planning phase.

 

Note   Regarding the number of decimal places in the values entered (that is, 17.456, and so on), it is not likely to have such level of exactness (or precision) in estimations even when they are calculated. This is a demonstration, but in real life, the values such as 20, 100, 40 and 80 (whole numbers) might be sufficient for estimations. Then, when the tasks are complete, we can update the consumptions with the precise actual data, if we collect the actual consumption data by measurement. We can interpret the accuracy of the estimations by comparing the estimated values to the actual values measured when the tasks are complete; and in some cases, it also shows how successful we are in managing the overall consumption.

 

  • Use the following Cost1 field’s formula for adding the material resource costs calculated by the formula (that is, Number1 field’s formula) to the actual costs for the work resources, calculated by MS Project for the tasks completed:

 

iif( [% Complete] = 100, [Actual Cost] + [Number1],[Actual Cost] )

 

Select the Use formula button in the section “Calculation for task and group summary rows” in the Custom Fields dialog box while entering the Cost1 field’s formula.

 

  • Text30 field’s formula (select Use formula):

 

iif( [Cost1] <> 0,"$" &  Format( [Cost1], "#,0.000" ),"")

 

  • Suppose that all the tasks are now complete and the consumption amounts for M1 and M2 are the same as the values entered initially, so there is no need to update the values in the Text2 and Text3 fields. Then the schedule will look like as below. Note that the Actual Cost field now shows the same values as the Cost field.

 

Total resource actual costs, separately:

      Work resources : $880.00  <- Project calculates (Actual Cost)

      Material resources : $1,105.32  <- Calculated by the formula (Text20, Number1)

Total Actual Cost (sum of the two resource costs above):

      All resources : $1,985.32  <- Calculated by the formula (Text30, Cost1)

 

 

The custom flag fields containing formula [Resource Names] Like "*resource_name*" and having graphical indicator defined for Yes lines can be used to mark the task lines where we need to input estimated and actual data for the material resources, as shown below.

 

 

In the first part of the example, the Text2 and Text3 fields were initially blank, but later, they are used to enter the actual amounts consumed for the material resources. In the second part which demonstrates tracking material resource costs with the accrual method set to “end”, the same custom text fields were initially used for entering fixed rate consumption amounts estimated for the material resources and then for entering the actual amounts consumed when the tasks are complete. In this example, the material costs are not included in the project costs, therefore, these costs need to be managed manually.

 

 


Revision History:

Version 1 - November 29th, 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.