none
Calculating the price of the task RRS feed

  • Question

  • I have created a custom resource field named "_Price_Rate" which is of type "Cost" (like standard field "Std. Rate").

    Then, in the Gantt Chart view, I have created a custom task field named "_PRICE" which is of type "Cost". 

    I need to calculate the field  "_PRICE" using this formula: _PRICE = [Work] * _Price_Rate

    My problem is: I cannot select _Price_Rate in the formula editor window !

    Question: How to solve this ?

    Wednesday, October 4, 2017 2:00 PM

All replies

  • If I understand you correctly, have you tried doing a crosstab query?
    Wednesday, October 4, 2017 2:23 PM
  • Cristian --

    Your problem is caused by the Microsoft Project data model.  There are basically two types of data in a Microsoft Project schedule:  Task data and Resource data.  When you display a Task view, you can apply Task tables, Task filters, and Task groups, and you an also insert Task fields.  There are a few resource-related fields that Microsoft Project allows to be displayed in Task views, including the Resource Names field.  Your problem is that you are trying to reference a Resource field in a Task formula, which the software will not do.

    You do already know that Microsoft Project already calculates the Cost of each task and resource assignment using the Std. Rate value you specify for the resource, multiplied by the number of hours of Work assigned to the task?  If so, then please tell us more about what you are trying to accomplish with the custom Price field.


    Dale A. Howard [MVP]

    Wednesday, October 4, 2017 2:35 PM
    Moderator
  • To add to Dale's excellent answer, the solution would go through VBA code. John and Rob, our VBA gurus should have this code since this post was quite similar.

    But like Dale said, I'd be curious to know why the default 5 rates don't make the job.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller

    Wednesday, October 4, 2017 2:41 PM
    Moderator
  • Cristian,

    As Dale stated, Project already calculates the type of data you appear to want. However, if you are trying to create a separate cost type value that represents "price" versus "cost" for a task/resource, then you don't need a separate custom field for that either.

    Each resource can have up to 5 separate cost rate tables A through E. Cost rate table A is the default and the rate is shown in the Standard Rate field seen on the Resource Sheet. You can create and apply additional rates via entry into one of the other resource cost rate tables and then selecting that rate via the Cost Rate Table field for assignment rows on either the Task Usage or Resource Usage views.

    For example, let's say you want to see the difference in total cost between what it costs you to perform the plan compared to what price you will charge the customer to perform the plan. You enter your labor cost rates as you normally would and Project calculates all costs as displayed on the Gantt Chart view. You can also enter the effective rate for each resource that includes fringe benefits to arrive at a "price" for each resource. Put those price rates into each resource's cost rate table B. Then on the Resource Usage view, select cost rate table B for each assignment row. The cost Project displays is now the value based on "price".

    There are of course those elements that enter into price versus cost for a project, but the above addresses the labor rate part of the overall cost.

    Hope this helps.

    John

    Wednesday, October 4, 2017 2:56 PM
  • Hi Dale and thank you for your answer!

    What I need to do is to keep in MSProject file both, the costs and revenues for a Time & Material project. For this purpose I need to work with a "cost rate" and a "sell rate" for every task, so that task cost and task revenue are based on the task work and the appropriate type of rate.

    As MSProject calculates the task cost with the embedded formula: Cost = Work * Std.Rate, I want to do the same with the task custom field "_PRICE" using a similar formula: _PRICE = Work * _Price_Rate,  _Price_Rate being a custom resource field;

    Based on the standard task field "Cost" and custom task field "_PRICE", I can calculate the custom task field Task_Profit = _PRICE - Cost

    Thanks again,

    Cristian


    Wednesday, October 4, 2017 3:36 PM
  • Cristian,

    I must be missing something, but here goes. Why not use a spare task number field to store the rate and  a spare cost field to multiply the task cost by the rate for that task?

    Otherwise, why not have two sets of std rates for each resource stored in spare resource number fields, and copy/paste them into the std rate field to get cost or price as needed?

    Thursday, October 5, 2017 1:47 AM