Cost calculation

[Adrian]

Hi,

I want to be able to calculate the cost of the work on a task from a user
defined date to the end of the task, irrespective of progress on teh task.

Assume the following :-

A 4 week task, with a single resource assigned full time.

The task starts on Monday the 14th August and has a 4 week duration.

At any time from now to the end of the task I need to be able to calculate
the work that is scheduled to be done on the task from a date I choose to
the end of the task, and thereby calculate the cost. I need this to work
even if progress has been entered, or if a work contour has been assigned.

Ive been thinking about this and noticed that if I bring up the Resource
Graph this can show the cumulative work value for a resource. So i'm
thinking that if this can be done for each single task in turn, then it
should be possible to calculate from a given date the following value :=
Work - Cumulative Work from start of task to chosen date.

If thats possible then I can calculate the cost based on this.

The problem is that I am not a programmer and I wondered if someone could
help point me in the direction of some code that would do the above.

The code needs to ask the user for a date, run through each task in the
project (ignoring summary tasks) and place the resulting value in a custom
field.

I'd appreciate any guidance on this.

Thanks

Adrian

 

[John]

Hi,

I want to be able to calculate the cost of the work on a task from a user
defined date to the end of the task, irrespective of progress on teh task.

Assume the following :-

A 4 week task, with a single resource assigned full time.

The task starts on Monday the 14th August and has a 4 week duration.

At any time from now to the end of the task I need to be able to calculate
the work that is scheduled to be done on the task from a date I choose to
the end of the task, and thereby calculate the cost. I need this to work
even if progress has been entered, or if a work contour has been assigned.

Ive been thinking about this and noticed that if I bring up the Resource
Graph this can show the cumulative work value for a resource. So i'm
thinking that if this can be done for each single task in turn, then it
should be possible to calculate from a given date the following value :=
Work - Cumulative Work from start of task to chosen date.

If thats possible then I can calculate the cost based on this.

The problem is that I am not a programmer and I wondered if someone could
help point me in the direction of some code that would do the above.

The code needs to ask the user for a date, run through each task in the
project (ignoring summary tasks) and place the resulting value in a custom
field.

I'd appreciate any guidance on this.

Thanks

Adrian

Adrian,
First I have a question. How can the value be irrespective of progress?
Let's take a simple example of a two day task with one resource assigned
at 100%. The scheduled work is 8 hours each day and if the resource is
paid at the rate of $10/hr, the cost each day is $80. If I understand
what you want, you would like to see an $80 value if the date input by
the user is the start of the second day. But what if the resource only
worked 6 hours the first day? The remaining cost is $100, not $80. It
seems like the classical ETC (Estimate to complete) would be a better
metric.

Nonetheless, I feel like I'm getting a little rusty with my VBA so I
developed the following macro which I think will do what you asked. You
just need to make sure you have set a baseline for your project.

Sub CostFromDate()
Dim Date1 As Date, SDate As Date
Dim tvalues As TimeScaleValues
Date1 = InputBox("Enter date from which task cost will be calculated" &
Chr(13) & _
"The result will appear in spare Task Cost1", "Cost Calculator -
1.0")
For Each t In ActiveProject.Tasks
If Not t Is Nothing Then
If t.Summary = False And Date1 < t.Finish Then
t.Cost1 = 0
SDate = Date1
If Date1 < t.Start Then SDate = t.Start
Set tvalues = t.TimeScaleData(SDate, t.Finish,
pjTaskTimescaledBaselineCost, _
pjTimescaleDays)
For i = 1 To tvalues.Count
t.Cost1 = t.Cost1 + tvalues(i).Value
Next i
End If
End If
Next t

End Sub

Note: the space/underscore is for line breaks in the code, so if you cut
and paste this into your VB editor (Tools/Macro/Visual Basic Editor),
make sure the lines break properly.

John
Project MVP

 

[Adrian]

Hi John,

That's great - Thanks. Just a couple of things ....

When the plan moves away from the baseline, the macro generates a type
mismatch error ?

And can you explain why the baseline is necessary please ? Would it not be
possible to use pjTaskTimescaledCost instead of pjTaskTimescaledBaselineCost
?

Many Thanks again.

Regards

Adrian.

 

[John]

Hi John,

That's great - Thanks. Just a couple of things ....

When the plan moves away from the baseline, the macro generates a type
mismatch error ?

And can you explain why the baseline is necessary please ? Would it not be
possible to use pjTaskTimescaledCost instead of pjTaskTimescaledBaselineCost
?

Many Thanks again.

Regards

Adrian.

Adrian,
Yeah you're right. I probably should have used the Baseline Start and
Baseline Finish fields. That would prevent the error.

I used the baseline cost because you said you wanted the data
irrespective of progress. In the opening paragraph of my original
response I explained how progress can affect the timescaled cost values.
So what do you want? If you want to go with more realistic values, then
certainly, use straight cost data (i.e. pjTaskTimescaledCost). In that
case leave the t.Start and t.Finish as is.

John

 

[Adrian]

Hi John,

I tried using the Baseline dates, but this didn't seem to prevent the error.
I'm still unclear why using the baseline dates ro the normal start/finish
dates should make any difference though. And as per your suggestion I also
tried the pjTimescaledCost values but I still get an error.
In fact in both cases I get a data mismatch error.

I m not really a programmer, so please bear with me. I may have just missed
something or misunderstood something. Let me provide you with some more
information. Our business works on the basis of accounting periods, which
means that on a given date we have to provide the remaining forecast of cost
on that date. Take a 10 day task for example. If a resource is assigned full
time and charged at $100/d the total would be $1,000. Lets assume that the
task wass scheduled to start on the 17th July and for the first 5 days the
resource only worked 4 hours per day, not 8. This means that the actual cost
of work per day for the first 5 days was $50 per day, not $100/day.

Lets say our A/P finished on the Thursday 20th July, we'd expect the
remaining cost to be calculated as $1,000 - 4 x $50 = $800
If the A/P finished on Wed 26th July the remaining cost would be $1,000 - 5
x $50 - 3 x $100 = $1,000 - $550 = $450

And that's what we need the macro to calculate...

I appreciate your help....

Thanks

Adrian

 

[Rod Gill]

If you update your schedule (accurately) to the end of the reporting period
then remaining cost will give you what you want? No macro needed.

 

[Adrian]

Hi Rod,

Yes I understand that, but progress may be updated to either before or
after the reporting period, and what I need is to account for the cost from
a specific date to the end of the task, irrespective of whether it ahead of
schedule or not and irrespective of the current date.
Assume for example that all tasks are up to date as at todays date, BUT our
AP only went to last Wednesday. Then I'd need to report on outstanding cost
from last Wednesday to the end of the task for the forecast of cost for the
next AP. Microsoft Project doesn't support AP's and this has always been a
problem. It doesn't support it in 2003 and as far as I can tell to date
won't support it in 2007 either. SO we have to find a way of reporting this
information. Hope that explains it. If there's another way to do this that
you know of please let me know.

Thanks

Adrian.

 

[Rod Gill]

OK, then you will need to create and run a VBA macro that accepts the next
AP start date (unless there is a foolproof way of calculating it each time
that works for every year) and reads and sums cost of task from AP Start to
end of Task. This is straightforward using the timephasedata method on a day
by day basis.

 

[John]

Hi John,

I tried using the Baseline dates, but this didn't seem to prevent the error.
I'm still unclear why using the baseline dates ro the normal start/finish
dates should make any difference though. And as per your suggestion I also
tried the pjTimescaledCost values but I still get an error.
In fact in both cases I get a data mismatch error.

I m not really a programmer, so please bear with me. I may have just missed
something or misunderstood something. Let me provide you with some more
information. Our business works on the basis of accounting periods, which
means that on a given date we have to provide the remaining forecast of cost
on that date. Take a 10 day task for example. If a resource is assigned full
time and charged at $100/d the total would be $1,000. Lets assume that the
task wass scheduled to start on the 17th July and for the first 5 days the
resource only worked 4 hours per day, not 8. This means that the actual cost
of work per day for the first 5 days was $50 per day, not $100/day.

Lets say our A/P finished on the Thursday 20th July, we'd expect the
remaining cost to be calculated as $1,000 - 4 x $50 = $800
If the A/P finished on Wed 26th July the remaining cost would be $1,000 - 5
x $50 - 3 x $100 = $1,000 - $550 = $450

And that's what we need the macro to calculate...

I appreciate your help....

Thanks

Adrian

Adrian,
OK, well that's kind of what I thought you should be looking at, but
that didn't come through in your initial post - hence the code I wrote
was based on the baseline data, although I did screw up by not
coordinating the dates with the timescaled data. In other words, if the
dates used in the TimeScaleData Method are baseline then the timescale
data must be baseline data (i.e. pjTaskTimescaledBaselineCost). If the
dates are scheduled dates then the timescale data must be scheduled data
(i.e. pjTaskTimescaledCost).

If you made those modifications (either way) and the code still
generates an error then I obviously need to do some more work on the
code. It would be a whole lot easier to insure the code works with your
file if I could work directly with your file. Could you e-mail it to me?
If it has sensitive data, I can send you a macro that will desensitize
it, or you can download the equivalent macro from fellow MVP, Jack
Dahlgren's website at: http://masamiki.com/project/macros.htm
Look for Jack's macro called "Scrub".

If you can send your file, zip it and e-mail to my address below.

John
Project MVP
jensenj6atatcomcastdotdotnet
(remove obvious redundancies)