Project Management with Excel - Workload, Schedule and Resource Sharing

This article has been originally published on www.LinkedPHPers.org

The title should actually be turned around - since workload and schedule are quite easy once we do not share resources, but they are tied to our project only. The difficulties starts when you need a resource, that is not exclusively yours - not only that you need to fight for it, but after you succeed that, you need to take under consideration that estimated workload and schedule are different things... And that is actually what I would like to elaborate in this article.

The series as you might have noted is called Project Management with Excel - and indeed, an appropriate Excel amount will appear - but let's start from

a little bit of theory...

In the previous article you could have read about my version of PERT equation that I hope can help you in estimating workload for your tasks better, more accurate, but most of all - more predictable. These estimations you could actually easily use then to make your own project plan - maybe again using my template for project plan described in the first article. 

And if your team are only members that has been assigned to your project and they do nothing more for other parts of the organisation - you can easily use the estimated workload values in your schedule. The only place you could be mistaken there is the fact these people will have some vacation leaves, sick-leaves or so - but in scope of first problem it should be fairly easy solved by leave calendar (which I actually plan to describe in some next articles), while the second is already risk responding.

The real "fun" starts when your team member is not assigned only to your project - you need to share him/her with someone else. Then eventhough you have calculated basing on his data that the task will take 3 workdays, the real amount of days he will be doing that is dependent on how much of his time he will dedicate to your project. That is why the first step in here is to agree with him, his line manager or the person you share him with what is the amount of time he is assigned to your project. I normally try to get the percentage of assignment of resource to my project, which allows me to know that in the specific period of time this person will be working for me, no one else. The need for that is quite simple - if a person is assigned to me for 50% and estimated the work for 3 days - he/she will effectively to that in no less than 6 - since he will work only halftime for me.

Why am I saying "no less than" you might ask ? Well - I call it switching costs... It costs time to change the focus between areas you work in... From my own experience - no less than 3%, per project - and in reality depending on specific, personal abilities for working in parallel on several things. If someone works for 2 projects - total time reduction for switching should be at least 6%. If the number of projects increases to 3 - then the person already spends at least 9% to switch between them. 4 projects in my opinion is already getting too much - and actually switching time per project should be extended to 4% or even 5% - which means we land in 20% of resource time only for switching. To accept such level I would see that resource really very unique :)

So getting back to an example earlier where we have a resource working for us for 50% of time, while he works for 2 projects, he is actually working for us only for 47% of his/her time - which means our work estimated for 3 days will be done in 6,4 days... This in such small values might not count - but once you will recalculate that to the whole project, the difference might get significant (and actually the one you will miss the GoLive date :) ).

So since we are in Project Management with Excel series

let's move that into Excel

To do that effectively I actually have merged the estimating and project plan templates into one - especially that in most of my projects I use them this way (together with some additional ones - but these would be planned for next articles :)). The new template you can download

HERE

Additionally I have also added another 

Project Team sheet

where you can specify the team members that will be performing tasks. Let me explain what it does there in a bit more details:

• Team member column - here you can put team members that later you will be able to select once designing your project tasks. Once you check the column G in Estimations sheet you will see that values from this column are taken as correct cell values - so they can be actually selected from drop down.
• Agreed assignment column - here is the assignment you have agreed this resource will work for your project. This assignment does not (yet) take under consideration focus switching costs - this is calculated further.
• No total projects column - here we should set the number of projects such resource is assigned to. If you look at values you can see we have either people assigned to 1 project (which means they work only for us), as well as people that shares their time between 2 or even 3 projects.
• Quite interesting you might find example of Steve (row 5) which is assigned to our project for 50%, while he has only 1 project. This would tell us this is a person that basically works in company only halftime - so eventhough he works for us only 50% of normal time, he do not need focus switch cost, as he still works only for us.
• Standard switching cost column - here we can actually change switching cost basing on actual person. You can see that in most cases you have a standard value, while Dave has been here presented as person that does not feel comfortable in changing the assignments - so his switching cost has been increased to 5%.
• Switching cost column - here we calculate real switching cost that hits our project. I assumed that no one actually cares about the switching costs (meaning this cost is not secured within "administrative" costs) and we need to cover it - so it reduces availability of this resource for our project. At the same time if the resource works for more than one project - the switching cost is proportional to the assignment - which means that in case if John (row 3) works for 75% for our project and being assigned to another project for another 25%, it is us who is hit by 4,5% of his switching time, while another project is hit only by 1,5% (so overall switching time closes in 6%)
• Final assignment column - here is the real assignment a resource works for us, which is basically equal to agreed assignment is the resource works only for us or agreed assignment deducted with switching cost if resource works for more than 1 project

The most interesting, calculated value that we get from this sheet is the Final assignment mentioned above. This one is taken to the

Estimations sheet

where it is being used in column O to calculate real calendar days for performing the task.

In this column the owner of task (specified in G column) is searched for in Project Team sheet and the final assignment is used to calculate how long it will take to complete the task, which workload has been calculated in column N (for more about the calculation methods here please refer to Project Management with Excel - Workload estimating article).

As you can see the values differs - sometimes quite a lot. The final schedule timeframe is longer by more than 80% - and that only since we have taken under consideration fact our resources are not working for us exclusively.

One more factor that has been here considered is the fact that if the task has any decimals - it already means it will not be finished within specific day. And easy rounding (using mathematical rule) will not very much work here - since no employee will be staying max. 4 hours a day to complete the tasks. I believe in the project plan we actually should not consider person to be staying even a minute after the contractual time - since I have simplified the approach by using always-round-up rule - even if there is 0,03 to be rounded (cell N16 - task assigned to Alice, which is exclusively assigned to our project for 100%).

Once we have real workday values we can take them further to our

Project Plan sheet

Here the changes comparing to the original version from first article are smaller. Mainly the values of duration for atomic tasks (the ones that were originally typed manually in) has been changed so they refer Estimations sheet. Since the Finish dates are mainly created by calculating the start date and number of workdays specified in column J - once this number is taken from Estimations, we can see how the whole project looks like now.

I have also made 2 changes there which are actually not related to the topic, but allows this example to be easier to be reviewed at any point in time. These changes covers:

• cell I6 (also marked in Red) which sets the start date for today minus 3 weeks - which allows us to see the tasks that are completed, inactive or delayed
• cell M3 so it will get the previous Monday comparing to I6 value - so the Gantt chart will be also more user friendly no matter when you will download the spreadsheet

Can the resource sharing be handled easier ?

Well - funny question for the ending, don't you think so ? :)

And the answer is even funnier - since IT CAN !

To avoid that switching costs, as well as extending time if the resource is shared, while still sharing the resource you can simply arrange with the resource that he/she will be working for you only in specific periods of time. It would mean that in your project plan you will need to remember during which time the resource is available for you, while treating everything else as his/her unavailability.

In our example of John working for us for his 75% of time it would mean during 4 weeks (20 workdays) for first (or last) 3 weeks he will work for us, while another 1 week left he will be unavailable.

This would mean he does not need to switch, hence we have no switching costs, no thinking on whether the time we have in our estimations should be recalculated to calendar time - only we will have to manage the project plan accordingly to leaves.

The bad thing behind this is - that I have actually never had such a comfort :) Maybe I am poor negotiator - but never been able to get a resource that way :) So had to figure out how still to work on much tougher constraints - and the idea I worked out you can read in this article and see in attached file.

Hope you will like it - and would very much hope for some discussion, maybe you sharing how you deal with problems described ?