none
PWA 2013 ECF's: How to "emulate" an new "rate" type custom field for a resource? RRS feed

  • Question

  • Hi there,

    Some context first:

    The default cost fields in PWA are calculated from the resource rate when using work resources.  Simplistically, a task cost would then be the sum of the respective assignments cost where the assignment cost is work(hrs)*standard_rate/hr.  As we present our schedules to clients, this resource standard rate is a sales rate.  We would however like to track internal costs (i.e. CTC) in a similar fashion but the more I delve into it the more elusive it proves. 

    Essentially, I'd like to create a new Resource_CTC field per Enterprise Resource (that will contain the Cost To Company per resource per hour) that we can use to calculate an Assignment_CTC which can in turn rollup to a Task_CTC and ultimately a Project_CTC. I'm unable to reference the Resource_CTC from the Task_CTC formula (Assignment_CTC = Assignment_work*Resource_CTC) and (Task_CTC = SUM(Assignment_CTC))

    Any assistance or guidance in this regard would REALLY be appreciated - even if it's a totally different off-the-wall method that can give the same result.  In short, HELP!  ;)

    Many thanks in advance,

    Gary

    Wednesday, November 12, 2014 1:39 PM

All replies

  • Hi Gary,

    Maybe before digging into the technical solutions, I'd like to take a step backward and be sure to fully understand the business need.

    Basically you have consultant resources which have a sale rate for your customers and an internal rate. While presenting the project to your customer you need to show the sale rate (how much it will cost to your customer) but you would also like to see how much it costs to you, meaning with the internal rate. Am I right?

    In this case, why not using the cost rate table, having the default rate (A) for the sale rate and the internal rate as the rate B. Doing this, you'll not have to maintain 2 costs, just to switch the rate table.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, November 12, 2014 1:49 PM
    Moderator
  • Hi Guillaume,

    Thanks for the prompt reply.  We do use the cost rate table to accommodate the multiple potential sales rates per consultant already.  Notwithstanding that though, the cost rate table means that my project costing (i.e. dashboards, reports, etc.) would then either reflect internal costing or sales costing - never both "side by side".  Having access to both simultaneously means that Gross Profit is a mere calculated field away.  Separating internal costs from sales rates also dramatically simplifies the management of fixed price projects where we assign the Zero Sales Rate cost table to each of the resources and track sales cost based on costs associated to milestones - this means that additional cost is not accrued as work is performed.  Being able to compare an internal cost as work is performed against a fixed sales cost on internal dashboards is my holy grail right now!

    Hope that sheds a bit more light on my dilemma as apposed to casting a smoke screen. ;)Regards

    Gary

    Wednesday, November 12, 2014 2:04 PM
  • I see your point Gary. And I see also the issue: after creating a resource custom field for the internal rate, unfortunately you cannot refer to it through a task cost custom field doing [internal cost]=[internal rate]*[work]. Since the internal rate is a resource field, it is not available at task level.

    What you want to do might be achievable with some VBA. I'll let VB guys of the forum jump in to confirm if it is possible. In the meanwhile, I'll try to think to another way to adress your need.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, November 12, 2014 2:22 PM
    Moderator
  • Thanks Guillaume,  I was hoping to avoid customisation (i.e. writing code) and achieve it solely through configuration but it doesn't appear that this is not feasible.  If customisation is the only way forward then so be it.

    I anxiously await their response and your "out of the box" thinking in this regard.

    Gary

    Wednesday, November 12, 2014 2:49 PM
  • Unfortunately, unless other folks jump in and propose an "out-of-the-box" solution, I don't see it.. The most obvious would be to use the cost rate table and maybe to play with baseline. Meaning you can set the internal rate, save the project with the baseline 10. Then switch back to the sale rate and compare your project cost with your project baseline 10 cost. This is kind of manual and tedious but it'll work.

    You could also manage it at task level with task custom fields but it wont work if you have more than 1 resource per assignments and will require to be managed for every single task, thus it is not an acceptable solution.


    Hope this helps,


    Guillaume Rouyre, MBA, MVP, P-Seller |

    Wednesday, November 12, 2014 3:03 PM
    Moderator
  • This has got to be one of the top 10 feature requests we have had for a while from Project Server Users. I do not think there is an OOB solution here.

    One way I could think of is to branch out into BI/Reporting, and do this on a report. You could store the rates in an Excel file, and then combine OData Feed from Project Server, and the Excel file into a single report using PowerPivot.

    A similar process was 'demoed' at the Project Conference 2012 by Andrew Lavinsky and Mike McLean. 


    Cheers,

    Prasanna Adavi, Project MVP

    Blog:   Podcast:    Twitter:    LinkedIn:   

    Wednesday, November 12, 2014 5:56 PM
    Moderator
  • Gary --

    Pardon me for bumping into this thread, but I have previously done what you are attempting to do.  All it will require of you is some simple copying and pasting to get this to work, and will not require VBA code.  Here is what you will need to do first.  For each resource in your Enterprise Resource Pool, specify the Standard Rate for Cost Rate Table A using the rate you charge to the client.  I believe you have already done this.  Correct?  Assuming so, then specify the internal rate for each resource using Cost Rate Table B, C, D, or E.  For simplicity purposes, let's assume you will use Rate Table B for the internal rate.

    From there, you will need to create three custom enterprise Cost fields.  Name the first one Client Cost and select the Roll Down Unless Manually Entered option when you create the field.  Name the second one Internal Cost and again select the Roll Down... value for the field.  Name the third field Gross Profit, select the Roll Down... value for the field, and select the Use Formula option as well.  In the Gross Profit field, enter the following formula:

    [Client Cost] - [Internal Cost]

    After creating these new custom fields, open an enterprise project and navigate to the Task Usage view.  In the Task Usage view, temporarily insert the following columns:

    • Cost Rate Table
    • Cost
    • Client Cost
    • Internal Cost
    • Gross Profit

    By the way, you could create a custom enterprise table named something like Gross Profit Tracking that contains the ID, Indicators, and Task Name columns, along with the preceding columns, as this would speed up the process.  Once you have the preceding columns available for you to use in the Task Usage view, do the following:

    1. Make sure that Cost Rate Table A is applied to EVERY resource assignment.  At this point, the Cost column displays the cost that needs to be copied into the Client Cost column.
    2. Right-click on the Cost column header and then select the Copy item on the shortcut menu.
    3. Select the first cell in the Client Cost column and then press Control + V to paste the values from the Cost column into the Client Cost column.
    4. In the Cost Rate Table column, select the B cost rate for the first resource assignment, and leave this cell selected.
    5. On your computer keyboard, press Control + Shift + Down-Arrow to select every cell in the entire Cost Rate Table column.
    6. On your computer keyboard, press Control + D.  This action will copy the B value from the first resource assignment to EVERY other resource assignment in the project.  At this point, the Cost column now shows the internal cost for your company.
    7. Right-click on the Cost column header and then select the Copy item on the shortcut menu.
    8. Select the first cell in the Internal Cost column and then press Control + V to paste the values from the Cost column into the Client Cost column.
    9. In the Cost Rate Table column, re-select the A cost rate and then use the Fill Down procedure documented previously.

    At this point, you will now see the Gross Profit field populated with the profit for every task, every resource assignment, and even for the entire project if you are displaying the Project Summary Cost (Row 0) in your project.  Once you have this set up, you would need to repeat steps #1-9 on a regular basis to make sure that the Gross Profit column is showing the latest data.  If you know VBA, or you have someone on your staff who knows VBA, you could probably automate steps #1-9 above with a pretty simple macro.  But I can confirm the above process will work, and the Gross Profit column will show you what you seek.

    Hope this helps.


    Dale A. Howard [MVP]

    Wednesday, November 12, 2014 8:19 PM
    Moderator
  • Thanks for all the feedback - it does seem that it is pretty much as I expected and can understand that this features right high in the pack of new feature requests w.r.t to Project Server.

    @Dale:  Thanks for the detailed and concise description of your solution - I understand exactly what you're proposing and what I will end up with.  I unfortunately don't think it will be very practical in our environment as for the most part, we utilise all cost rate tables on various sales rates already (dependant on nature service levels signed for in advance, etc.).  Consultant actuals are updated daily via timesheets with status updates reviewed and processed the next morning - having to manually update the schedules as described above across 70+ projects daily will receive a lot of resistance from the PM's.  In an environment with a lower work load - it would definitely be a feasible solution.

    For me now though, I think building a Power Pivot (or similiar) dashboard combining seperate data sources to automatically display the "realtime" GP for those with relevant access is probably the most feasible solution.

    I thank you all for your effort and especially you Dale.

    Kind regards

    Gary

    Friday, November 14, 2014 12:24 PM
  • Gary --

    If any of our responses answers your original question, would you please mark the response as the answer?  Thanks!  


    Dale A. Howard [MVP]

    Friday, November 14, 2014 1:30 PM
    Moderator