Cost rate table calculation - showing a markup on costs

[Monica West]

I want to be able to show not only the standard cost
(based on the cost rate table A costs) but also show a
column that has the costs based on cost rate table B
cost).

For example, we have a typical 20 - 30% markup on hourly
cost rates for a project. We enter $100 for a resource
in the Resource Sheet view for a resource, update the
Cost Rate Table A to show $100. Then, we add the hourly
PRICE rate of $125 in Cost Rate Table B (this is our
actual quoted price to the client).

I want to show the cost rate table calculation based on
both A and B in 1 view, i.e. COST vs. PRICE. Currently I
cannot find a way to do this. I've tried to customize a
field but cannot find the calculation to show the cost
rate table B value.

Any suggestions would be greatly appreciated!

 

[Steve House]

That is not what the rate tables are for and so it will be difficult to do
what you want to do with them. Some resources who do different jobs will
get different rates depending on which hat they're wearing at the moment. I
get one day rate for training tasks and a different rate for consulting
tasks, for example. This means that while there are 5 rate tables, only 1
of them can be applied to any individual task. Instead of using the rate
tables. how about creating a user defined cost field that applies a fixed
percentage margin to the cost field so you'll have a UDF called PRICE that
is equal to COST*1.25. If the margin varies by resource, you could use a
number field to capture the rate to apply.

 

[John Beamish]

I played around with this for a bit last night. I'm not a vba guru so you
might want to try posting this solution in the
microsoft.public.project.developer newsgroup for recommendations for
improvements.

Basically, I've created a macro that works on the View | Resource Usage
display. You can see how it works by doing the following setup:

View | Resource Usage
Insert Work, Cost1 and Cost2 columns

Double-click on a resource name and you'll get the Resource Information
popup dialog.
Click on the Costs tab

There's an embedded tab box with five tabs: "A (default)", "B", "C", etc.
The values my macro uses are the Standard Rate value on the "A" tab and
"B" tab.

Click on cancel to close the dialog.

When you run the macro it will go through each resource and pull out the
Standard Rate figures from the "A" and "B" tabs only and put them,
respectively, into Cost1 and Cost2.

You already know the amount of work (it's displayed in the Work column).
You'll have to be careful (I didn't have time to check a lot of scenarios)
to make certain that rates and work are both in the same measurement (by
hour, I think is the standard ... but I did very little checking/testing).

So, you could run the macro to populate Cost1 and Cost2 and then put a
formula in Cost3 and Cost4 -- something like [cost1] * Work and [cost2] *
work -- to see the extensions.

Here's the macro code (which probably can be improved) and watch out for
wrapped lines:


Sub TwoCosts()

Dim tt As Task
Dim rr As Resource
Dim aa As Assignment
Dim i As Integer
For Each rr In activeproject.Resources
If Not rr Is Nothing Then
Debug.Print rr.Name; " "; rr.Assignments.Count; " assignments"
For i = 1 To rr.Assignments.Count
Debug.Print rr.Name; " "; " "; i; " ";
rr.Assignments(i).TaskName; " ";
Debug.Print rr.Assignments(i).Work
rr.Assignments(i).Cost1 =
Mid(rr.CostRateTables.Item(1).PayRates(1).StandardRate, 2,
InStr(rr.CostRateTables.Item(1).PayRates(1).StandardRate, "/") - 2)
rr.Assignments(i).Cost2 =
Mid(rr.CostRateTables.Item(2).PayRates(1).StandardRate, 2,
InStr(rr.CostRateTables.Item(2).PayRates(1).StandardRate, "/") - 2)
Next
End If
Next

End Sub


JLB, PMP

I want to be able to show not only the standard cost
(based on the cost rate table A costs) but also show a
column that has the costs based on cost rate table B
cost).

For example, we have a typical 20 - 30% markup on hourly
cost rates for a project. We enter $100 for a resource
in the Resource Sheet view for a resource, update the
Cost Rate Table A to show $100. Then, we add the hourly
PRICE rate of $125 in Cost Rate Table B (this is our
actual quoted price to the client).

I want to show the cost rate table calculation based on
both A and B in 1 view, i.e. COST vs. PRICE. Currently I
cannot find a way to do this. I've tried to customize a
field but cannot find the calculation to show the cost
rate table B value.

Any suggestions would be greatly appreciated!

--