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 subvalues 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.
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.