Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Calculating assigned hours automaticaly

Status
Not open for further replies.

aracioppi

Technical User
Apr 20, 2011
12
NZ
HI all:
When using Microsoft Project 2003, I have a task A to which I assign a resource Peter (let's say I assign 40 hours for the following 6 weeks (everything OK upto there)
But we use the same project to "possible future" projects. These type of projects have a probability to happen, and I need to change the hours of the resource assigned based on that probability. e.g. if we get the project B, it will require resource John for 8 weeks @ 40 hs week (probability=100%), but right now the probability=50%, so I need to put 20hs per week instead of 40. Is there a way to avoid doing this by hand everytime the probability changes? Thanks in advance!
 
I can see what you're trying to do ... using the probability of getting the project and multiplying that against the estimated work for a resource assignment as a way of projecting the potential overallocation of a resource.

I wouldn't do this for a variety of reasons. Chief among these is that at the early stages (when a project is at 10% or 25% or even 50%) you really don't know (simply because you haven't done the full analysis) exactly what tasks need to be in the schedule, what the real estimated work will be, and which resource you actually will assign to that deliverable. OK, there are some tasks where you know that only one resource can do the work but, generally speaking, you do not know who will be assigned.

Second, it is rarely a good idea to embed multiple projects within a single file. It can be done ... but it's rarely a good idea.

You are also assuming that the existing project will complete on time. Even in the tightest shop, some projects slide for reasons beyond your control. If a follow on project goes to 100% with a specified mandatory start date then you're going to end up with overallocations anyway.

Having said all of that:

for future projects

1. Enter all tasks
1.1 set them up as Fixed Duration (for this to work, you *must* do this), enter the Duration, enter the predecessors/successors and enter the correct estimated work.
1.2 Assign the resources to the tasks

2. Enter the calculation data
2.1 Display the Duration1, Number1 and Duration2 columns
2.2 There are no Work1, Work2, Work3, etc. columns so Duration1 will have to do. Copy the Work column and paste it into Duration1. (We're now using Duration1 as a "save area" for the originally estimated amount of Work.)
2.3 In the Number1 column enter the probability of getting the project. If the probability is 25% enter .25; for 50% enter .5 and so on. Do that in one cell and then drag that value down to the subsequent tasks.
2.4 In the Duration2 column enter the formula: [Duration1]*[Number1]. Project will automatically calculate Duration2.
2.5 Copy Duration2 and paste it into Work.

3. When the probabilities change
3.1 Enter the revised probability into Number1 as you did in step 2.3 and then drag this value down for all the affected tasks.
3.2 Project will automatically recalculate Duration2.
3.3 Copy the values in Duration2 and paste them into Work.

 
Hi PDQBach: thank you very much for your answer.
We use project as our planning work/resources tool. We have 6 divisions (so there are 6 projects) a resources pool project (the company employees) and everything is linked in a "Master" project (that has 6 main tasks (file inserted)) and when you open these main tasks you can see all the projects for this division.
It's working ok up to there, so I think it's not such a bad idea.
Every division has a task that is called "Submitted Tenders" that are jobs that are not happening yet, but we need to plan ahead, so if a submitted project requires 400 hours of an electrician and we know the job needs to be done in around ten weeks, we assign 40hs a week for ten weeks to a resource called _Electrician-Placeholder, so if we were sure that the job will happen we know we need to hire one electrician for 10 weeks.
Now, we have many submitted tenders and many hours that might happen, so if in tender x there are only two bidders (ourselves and another company) and let's say both companies offer similar services, this job has 50% of chances to happen.
I'm already using durations and numbers to do the calculation in the columns (e.g. budget hs (duration 1) * probability (number) = calculated hours (duration 2) and this works ok (except when I don any kind of grouping, but this is part of a separate thread.
Now, what I'm trying to find out is if there is a way to change the data in the timeline automatically (e.g. for the job mentioned above, I'd insert 40 hs/week for ten weeks with probability=100%, but if I change the probability to 50% I need the hs assigned per week in the timeline get reduced to 20 hs/week
Does it make sense?
Thanks again for all your support
 
OK, I can see how that makes sense for your operations.

If you set the tasks as fixed duration then the approach I showed above should be working for you. Let me know what doesn't seem to be working for you.

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top