Is it possible to customize the Task cost calculation?

[christophe_schram]

I have a question on how to customize the calculation of Task costs.

A bit of context: in my research organization, most of our research projects are funded by institutions (European Commission, regional agencies, ...), which funding rates differ between each other. Example: the EC reimburses all direct costs (i.e. personnel + equipment + travel) and adds 25% of indirect costs rate; a regional agency reimburses only 10% of indirect costs and they only apply to the personnel costs.

I have created a Project custom field for the funding scheme (EC, ...) and some custom fields for our resources (Manpower, Equipment, Travel, ...). My objective is to create a custom Cost1 field which would test the type of funding scheme and the cost category, and apply the corresponding formula.

The problem is that I don't see how to access the resource individual costs under the Task Usage view, it seems the formulas only apply to the total cost of the Task.

Does anyone have a clue of how I could achieve what I need? I hope the explanations above were clear enough.

Thanks in advance!

Christophe.

[#2]

christophe_schram --

It is not possible to use a formula to calculate cost rates for resources in the Std. Rate, Overtime Rate, and Cost/Use fields. These values must be manually entered. Hope this helps.

[#3 · Accepted solution]

Christophe,
First, I suggest you read this Wiki article on data types in Project:
https://web.archive.org/web/20200720083203/https://social.technet.microsoft.com/wiki/contents/articl...

Then you might also want to read the article referenced in the above concerning extra fields:
https://web.archive.org/web/20200722225529/https://social.technet.microsoft.com/wiki/contents/articl...

Those articles will explain why your formulas don't seem to do what you want. However, based on your description, I don't see a need for custom cost fields at all. Instead of creating separate custom fields for funding types and resource types, why not combine the two. For example, you could have an EC work resource, an EC material resource, an EC cost resource, a Regional work resource, Regional material resource, a Regional cost resource, and so forth for each funding entity. Set up each resource type with the appropriate rate based on how they fund. You can even set up a time based cost escalation and/or up to 5 separate cost rates for each resource via resource cost rate tables A through E. This is accessible through the Cost tab on the Resource Information window. Varying cost rate tables for each resource are applied via the Cost Rate Table field at assignment level on either the Task Usage view or the Resource Usage view.

Hope this helps.
John

[#4]

Thanks @Dale Howard, I was more referring to the way the costs of the resources assigned to a Task would be aggregated to build up the Task cost. I realize my message was probably confusing. Nevertheless, it's good to know that these cannot be calculated using formulas, thanks!  

[#5]

Thanks @John-project, this helped a lot. I read the articles you suggested, and understand better the differences between Task, Resource and Assignment data now.

I agree with your suggestion, the Cost Rate Tables seem indeed the natural solution.

I'm now trying to wite a macro creating the Cost Rate Table B for the EC hourly rates using the raw hourly costs of Table A:

Sub AssignHorizonEuropeCostRatesToCostRateTableB()
    Dim projApp As Object
    Dim resource As Object
    Dim costTableA As Object
    Dim costTableB As Object
    Dim rateIndex As Integer
    
    ' Create an instance of MS Project
    Set projApp = CreateObject("MSProject.Application")
    
    If projApp Is Nothing Then
        MsgBox "MS Project is not installed."
        Exit Sub
    End If
    
    projApp.Visible = True
    
    ' Set the active project
    Set ActiveProject = projApp.ActiveProject
    
    ' Loop through all active resources
    For Each resource In ActiveProject.Resources
        ' Specify the resource cost rate tables
        Set costTableA = resource.CostRateTables("A")
        Set costTableB = resource.CostRateTables("B")
            
        ' Table B rates = (Table A rates) * 1.25
        For rateIndex = 1 To costTableA.PayRates.Count
            costTableB.PayRates(rateIndex).StandardRate = costTableA.PayRates(rateIndex).StandardRate * 1.25
            costTableB.PayRates(rateIndex).OvertimeRate = costTableA.PayRates(rateIndex).OvertimeRate * 1.25
        Next rateIndex
    Next resource
End Sub

It's nearly working at the exception of a type mismatch error in the last couple of instructions (I'm completely new to VBA, this was largely inspired by Copilot), which I should be able to fix.

But the next step then will still be the creation of a custom field Cost1, which will use the Table B rates instead of the Table A ones. Would that be possible? I haven't found how.

[#6]

@christophe_schram

I'm glad the reference links gave you some insight. What I don't understand though is why you still think you need a custom cost field. Maybe a marked up screen shot would help.

I also don't understand why you need a macro to create the cost rate tables. Unless there are several tables with multiple time based changes, setting them up should only take a few minutes manually. If you still want to pursue a VBA approach and the code you posted is coming from Copilot, I find another airline.

John

[#7]

Hello Chirtophe.
As requested above, it would be important to understand what you are looking for, an image of a table, spreadsheet or something similar, with a brief example, rather than an abstract description.
Ignacio

[#8]

Hi @John-project,

The thing is that our contract management organization requires to have a simultaneous view on

• the actual costs incurred by our organization, where an indirect cost rate is applied to the direct personnel costs, and
• the costs that will be eligible for reimbursement by the funding agency, which uses a different indirect cost rate, and applies it this time to all the costs (personnel+ equipment + travel, etc.).

This means that we really need to have two different cost columns side-by-side. I hope this makes more sense now.

The reason for the macro is that we have about 100 employees which can be potentially assigned to our projects, and I didn't find a way to enter the cost tables (beyond the default table A) in the MS Project Server Sharepoint interface. But I found ou that if I open the Enterprise Resource database in MS Project, run a macro to create the Cost Rate Table B, and save it to the Server, the data are actually saved.

About Copilot... ;) I follow you on this one, but it turns ou that it does a better job than ChatGPT (3.5 at least), and believe me: when it's about VBA programming, you really don't want me in the cockpit either! (Until 3 days ago I didn't know about it.)

[#9]

Hi @John-project , @Ignacio_Martín ,

Following your suggestions, here is what it should look like in the Task Usage view:

[#10]

Christophe,
What Text field identifies the type of resource and how is it different from the resource Type field?

And what Text field identifies the project type?

Perhaps you could show those Text fields as columns on your previous screen shot.
John

[#11]

Hi @John-project ,

I have a Custom Field "VKI Cost Cat.", consistent in our ERP system:

and one Custom Field "Funding Scheme", associated to the Project:

So, fundamentally, what I need is a Custom Field "Cost1" that would calculate our internal costs with a formula doing (not using the proper syntax, I know), for a given Task:

IF (Assigned resource VKI Cost Cat.) == "T1 Manpower": # the category to which we add indirect costs
   Assigned resource Cost1 = (Assigned resource Cost using Cost Rate Table B)
ELSE: # all other cost categories are not affected by our internal indirect cost rate
   Assigned resource Cost1 = (Assigned resource Cost found in default Table A)

The second custom field Cost2 calculates the costs that will be eligible for reimbursement by the funding agency:

IF (Project Funding Scheme) == "EC Horizon Europe":
   IF (Assigned resource VKI Cost Cat.) == "Subcontracting": # the only category not affected by EC indirect costs
      Assigned resource Cost2 = (Assigned resource cost found in default Cost Rate Table A)
   ELSE: # all other cost categories are subject to indirect costs
      Assigned resource Cost2 = (Assigned resource cost found in Cost Rate Table C)
ELSE IF (Project Scheme) == "Regional":
   (etc.)

Is that more clear?

[#12]

@christophe_schram 

Hello Christophe.

I am attaching a sample calculation with formulas in case it suggests any useful ideas.

The scenario takes place in the Resource Sheet view.

A field is included VKI(Text1)=resource class (vki1, vki2, vki3) and another Proj_type(Text2)=Project type (ECHE, REGIONAL)

The Work, Cost, Remaining Cost and Rate of each resource in the project are informatively shown.

The Cost1 and Cost2 formula is based on the Switch function, similar to Iif, and multiplies the Remaining Cost by the value that follows the condition that is met for Resource Class and Project Type, and that in this example, for Cost1 is as follows:

[Remaining Cost]*switch(Text1="vki1" And Text2="ECHE";1;Text1="vki2" And [Text2]="ECHE";2;Text1="vki3" And [Text2]="ECHE";3;Text1="vki1" And [Text2]="REGIONAL";1;Text1="vki2" And [Text2]="REGIONAL";3;Text1="vki3" And [Text2]="REGIONAL";3)      ...

In the case of Cost2, the only thing that varies are the coefficients:

[[Remaining Cost]*switch(Text1="vki1" And Text2="ECHE";4;Text1="vki2" And [Text2]="ECHE";0;Text1="vki3" And [Text2]="ECHE";0;Text1="vki1" And [Text2]="REGIONAL";4;Text1="vki2" And [Text2]="REGIONAL";0;Text1="vki3" And [Text2]="REGIONAL";0)     ...

To obtain the totals you must have the Rollup Sum option selected in the Cost1 and Cost2 formula form.

Additionally, you must group by the Proj_type(Texto2) field (Right click on the column title).

I hope it helps you or gives you some new clue.

Ignacio

[#13]

Christophe,
Yes, the only issue I have is the custom field "funding scheme" at Project level. You are working in an enterprise environment whereas I only have the desktop version of Project so writing macro code to create the two custom Cost fields may be a bit of a challenge.

I see Ignacio presented an approach that captures the essence of what you are after but unfortunately it will only apply at resource level so it will not provide the cost data you want in the Task Usage view. You would be able to see the custom cost values at resource level on the Resource Usage view. If that meets your needs than Ignacio has a good solution, not requiring any programming.

If you think Ignacio's approach might work, then you're all set. If not, we can continue on with some VBA code.

John