Incorrect calculations in custom fields

[Sherri]

I've created a custom cost field that represents the total value of a
deliverable and a second custom cost field that calculates the revenue earned
to date based on the % of work completed. It uses a formula that calculates
[total value]*[%WorkComplete] and is supposed to display the product in the
revenue earned to date field. However, none of the products are accurate
when I do the calculations manually and in most cases are off quite
significantly (thousands of dollars). Is there a reason why this field is not
calculating properly?

example:
Work % Complete = 65%
Total Value = $18,200.00
Revenue earned to date is calculated to be: $4,732.00 (Should be $11830.00)

Thanks for your help.

Sherri

Revenue earned to date should be $1000, but will show up as

 

[Jim Aksel]

There has to be more to the story. If you key $18,200 into a custom cost
field, then [Percent Work Complete]*[Cost1] is going to give you what you
need.

Is the $18,200 entered or calculated? if so, what is that formula??
Are you using %Work Complete against an extremely long duration task?
Verify that you are using consistent units of [%Complete] or [%Work
Complete]. Project calculates them separately. You could be 65% Work
complete but only 26% duration complete which would account for the lower
value.

We would do this with Earned Value, what you have identfied is what is known
as "Earned Value" or "Budgeted Cost of Work Performed" and it is the [BCWP]
field in Project. There is a wealth of information available on this in
"help" In effect, if you have a properly costed baseline and you have
entered some type of resources that value up to $18,200 then you will get
BCWP when you enter and actual start/finish date and a %Complete (or %Work
Complete).
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

 

[Sherri]

This has helped, thank you, and at the very least has shown me where MS
project is getting the results I'm seeing. It is calculating revenue from the
% complete which is different from the % work complete. I have two questions:

Why is it using the % complete to calculate revenue when the formula
specifically asks for it to use % work complete?

Formula: [Total Value]*[%Work Complete]

And, How do I verify I'm using consistent units between % complete and %
work complete? They are connected and if I change one, the other calculates
it's own value.

I also checked out the BCWP, but nothing is calculating (all $0). I suspect
that this is because the resources contributing to the % complete do not have
rates assigned to them. This is because the work is being done at a lump-sum
price and the client is billed based on the % of the work complete, not by
how much it actually cost to perform the work. The lump sum price is a
manually entered value (entered into the Total Value field at the summary
task level)

Thanks for your help. Sherri

There has to be more to the story. If you key $18,200 into a custom cost
field, then [Percent Work Complete]*[Cost1] is going to give you what you
need.

Is the $18,200 entered or calculated? if so, what is that formula??
Are you using %Work Complete against an extremely long duration task?
Verify that you are using consistent units of [%Complete] or [%Work
Complete]. Project calculates them separately. You could be 65% Work
complete but only 26% duration complete which would account for the lower
value.

We would do this with Earned Value, what you have identfied is what is known
as "Earned Value" or "Budgeted Cost of Work Performed" and it is the [BCWP]
field in Project. There is a wealth of information available on this in
"help" In effect, if you have a properly costed baseline and you have
entered some type of resources that value up to $18,200 then you will get
BCWP when you enter and actual start/finish date and a %Complete (or %Work
Complete).
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

I've created a custom cost field that represents the total value of a
deliverable and a second custom cost field that calculates the revenue earned
to date based on the % of work completed. It uses a formula that calculates
[total value]*[%WorkComplete] and is supposed to display the product in the
revenue earned to date field. However, none of the products are accurate
when I do the calculations manually and in most cases are off quite
significantly (thousands of dollars). Is there a reason why this field is not
calculating properly?

example:
Work % Complete = 65%
Total Value = $18,200.00
Revenue earned to date is calculated to be: $4,732.00 (Should be $11830.00)

Thanks for your help.

Sherri

Revenue earned to date should be $1000, but will show up as

 

[Jim Aksel]

By consistency, I meant only for you to visually inspect your formulas. sorry
for the confusion.

I am unable to produce your error. In a test file I have
[Cost1]=$2000 (your Total Value item)
[Cost2]=[Cost1]*[%Work Complete]/100
[Cost3]=[Cost1]*[%Complete]/100

I loaded work non-linearly with duration so they would be forced to be
different. When changing %Complete or %WorkComplete it drives the other as I
would expect along with the correct results in my formulas. Note: If I have
the same amount of work assigned each day, then %Work Complete and %Complete
move identically. If the work is loaded non-linearly (2 hours one day, 8
another, 6 after that...) then %Complete and %Work Complete drive each other
and they move in the same direction but at different rates.
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

This has helped, thank you, and at the very least has shown me where MS
project is getting the results I'm seeing. It is calculating revenue from the
% complete which is different from the % work complete. I have two questions:

Why is it using the % complete to calculate revenue when the formula
specifically asks for it to use % work complete?

Formula: [Total Value]*[%Work Complete]

And, How do I verify I'm using consistent units between % complete and %
work complete? They are connected and if I change one, the other calculates
it's own value.

I also checked out the BCWP, but nothing is calculating (all $0). I suspect
that this is because the resources contributing to the % complete do not have
rates assigned to them. This is because the work is being done at a lump-sum
price and the client is billed based on the % of the work complete, not by
how much it actually cost to perform the work. The lump sum price is a
manually entered value (entered into the Total Value field at the summary
task level)

Thanks for your help. Sherri

There has to be more to the story. If you key $18,200 into a custom cost
field, then [Percent Work Complete]*[Cost1] is going to give you what you
need.

Is the $18,200 entered or calculated? if so, what is that formula??
Are you using %Work Complete against an extremely long duration task?
Verify that you are using consistent units of [%Complete] or [%Work
Complete]. Project calculates them separately. You could be 65% Work
complete but only 26% duration complete which would account for the lower
value.

We would do this with Earned Value, what you have identfied is what is known
as "Earned Value" or "Budgeted Cost of Work Performed" and it is the [BCWP]
field in Project. There is a wealth of information available on this in
"help" In effect, if you have a properly costed baseline and you have
entered some type of resources that value up to $18,200 then you will get
BCWP when you enter and actual start/finish date and a %Complete (or %Work
Complete).
--
If this post was helpful, please consider rating it.

Jim
It''s software; it''s not allowed to win.

Visit http://project.mvps.org/ for FAQs and more information
about Microsoft Project

I've created a custom cost field that represents the total value of a
deliverable and a second custom cost field that calculates the revenue earned
to date based on the % of work completed. It uses a formula that calculates
[total value]*[%WorkComplete] and is supposed to display the product in the
revenue earned to date field. However, none of the products are accurate
when I do the calculations manually and in most cases are off quite
significantly (thousands of dollars). Is there a reason why this field is not
calculating properly?

example:
Work % Complete = 65%
Total Value = $18,200.00
Revenue earned to date is calculated to be: $4,732.00 (Should be $11830.00)

Thanks for your help.

Sherri

Revenue earned to date should be $1000, but will show up as