• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel problem related to complex scheduling (Potential contract work)

Mukund

New Member
Hello everyone,

I'd like to use excel for solving this complex scheduling problem that I'm running into and I'm hoping that there's a superstar here who can help out.

The solution is very valuable for me and I'm willing to pay very well for it, so if you're keen to take this up please do get in touch.

I've attached an excel called task_simulation_01_02.xlsx file that contains more information about the problem

Context

I have 2 data tables​
    1. Table 1 Contains resources (manpower) with
      1. Their Efficiencies
      2. Their Capabilites to do a particular task
      3. Their Availabilities (Start Dates & End Dates)
      4. Their Leaves
    2. Table 2 Contains Tasks with
      1. Dependencies
      2. Type of resource needed for the task (same as resource Capability)
      3. Effort for that task
      4. Task Status
and 1 result Table (with Yellow headers)


Problem

Get the shortest overall duration to complete all the tasks, keeping in mind​
    • Task Dependencies
    • Resource Availability
    • Resource Efficiency
    • Match Resource and Task capabilities
    • Filter out the tasks that are already done
Present this information on a per task basis in the 3rd table with the Yellow headers​


Do you think this is something that's do-able in excel?

I believe most project management tools don't have task schedulers that can handle so many conditions. If that information is wrong, I'd also appreciate any other tool that can handle this problem.

Thanks in advance! and greatly appreciate your time!

Mukund
 

Attachments

  • Task_Simulation_01_02.xlsx
    13.3 KB · Views: 18
Hi Mukund ,

Just two points :

1. How are you doing this task at present ? What ever you are achieving through manual methods , do you have any performance measures by which you can decide whether a solution is optimal ?

2. What is the time frame for completion ?

Narayan
 
Thanks for the quick response Narayan!

With respect to your questions...

1a. Current Methods - To be honest we don't have a way of dealing with this complexity at a manual level, we go nuts trying to get to an optimal solution. What we do now is that just solve for 1 criteria at a time (Manually) then repeat it for each criteria, then look at the largest date and go with that as a milestone date.

1b. Optimal Solution - If you consider all the tasks as a project. The optimal solution is to get the project end date to be of the shortest duration possible (given all the criteria)

2. Time frame for completion - It's always asap isn't it ;) Ideal time frame = 3rd Mar 2014. Workable time frame = 07th Mar 2014. How does that sound?

Looking at your questions, it does look like you feel that this is a solvable problem. Which gives me a lot of hope :) Though, I'd love some confirmation :) Off the top of your head - do you think it's do-able?

Thanks again for looking into it! Much appreciated

Mukund
 
Hi Mukund ,

Thanks for the details.

I certainly think your manual method can be improved upon ! Whether the impoved version will be optimal or not is to be seen. However , I think your file is somewhat sketchy , and a better grip on the whole task can come with more realistic data ; just as a first comment , Task 7 has task 7 as a dependent task ; I think this is a typo.

It would be nice if we could draft a complete functional spec. so that there are no loose ends , and the problem is comprehensively understood. Do you already have such a document drafted ?

Narayan
 
Hi Mukund ,

Another point is that if we need to use Excel to solve the problem , then we should be somewhat elastic on the data structures / layout , and see how the layout can be rearranged so that it becomes to deal with using Excel.

Narayan
 
@Mukund
Hi,
Just got interested in problem. I have some questions if you can clarify.
1. What is Task Dependicies? Means what does the number means like 0.
2. What is Task Effort? Again same what does number means?

Regards,
 
@ Narayan -

Typo - Yes, the task 7 depending on itself is a typo, apologies for that. I guess I need to clean this one up.

Functional Spec
- Unfortunately, I'm not sure how to write up a functional spec. Would you be able to point me to a link that will help me write it out?

Data Structure/Layout Elasticity
- I don't think that should pose any issues. The current structure is present jus to help explain the problem

Do let me know if you'd like more information​

@ Somendra

Good to see that it spoke to you!

Task Dependencies - I thought it would be better to have a number that indicated that there were do dependencies and I chose 0 as the number that signified no dependencies. Apologies for not making that clear. To be honest, I wasn't even expecting any replies to the question.

Task Effort - This means is the potential manday duration of the task. Resources (employees) with different efficiencies will take different durations. For example Emp 05 would take 40 days for Task 1 (i.e. 40 = 10d/25%)

Do let me know if you'd like more information​

Once again, thanks so much guys! Looking forward to hearing from you soon!
 
@Mukund

Correct me if I am wrong.

So you want table with yellow header to be filled in & if it is so than for example
for task ID 1 EMP ID should be EMP06 start date should be 5-Jan_2014 and end date will be 19-01-2014. Is it?

Regards,
 
The solution is very valuable for me and I'm willing to pay very well for it, so if you're keen to take this up please do get in touch.
Hi, Mukund!
It's a very interesting project from the technical challenge aspect, and maybe not only from that point of view... but are you sure that is not missing any information about the project?
Regards!
 
Hi Mukund ,

I will just put down my thoughts , and you and anyone else who cares , can comment.

First let us see what data is being entered :

1. Employee ID

2. Employee Name

3. Employee Availability

4. Employee Capability

5. Employee Efficiency

A few points about this :

a. At present you are detailing the employee availability through a column which mentions the start date , another which mentions the end date , and a third which lists the dates on which the employees will not be available. To make things easy , at least from the programming point of view , can we have a project calendar , which will start from the project start date , list all the dates thereafter till the project end date , as column headers , have the employees as the row labels , and indicate whether an employee is available or not on each day ; to make things easier , we can have just an “X” signifying that an employee is not available on that particular date , and an absence of this “X” will signify that the employee is available on that date.

b. At present each employee has only one capability ; is this true , or can one employee have more than one capability ? If so , how are you going to represent this ?

c. The above point extends to this ; if an employee has multiple capabilities , their efficiencies in each of them may be different.

When we consider task related data , it is :

1. Task ID

2. Task Name

3. Task Effort , in mandays

4. Task Capability

5. Task Dependencies

6. Task Status

The same points mentioned above against employee related data apply here ; will all the tasks be strictly single capability tasks ? If not , how are you going to represent , not just the capabilities , but also the effort , since a multi-capability task may have different efforts in the different capabilities ?

The task dependencies have been mentioned as mere numbers , but will that suffice , or do you want a more rigorous treatment as in any project management software , where either start dependency or completion dependency is mentioned ? For example , if may happen that for Task 9 to start , Task 7 may have to start , but Task 8 will have to be completed ; how will this be represented ?

I assume that the task effort is in terms of employees who are 100 % efficient ; to take an example , suppose a task effort is 17 days ; let us assume there are two employees whose efficiencies are 25 % and 70 % ; the above task , if given to the employee whose efficiency is 25 % , will actually take 68 days ( 17 * 100 / 25 ) , whereas if it is given to the employee whose efficiency is 70 % , will take only 25 days ( 17 * 100 / 70 , rounded up to the next whole number of mandays ).

If a Task Status is Done , it can be ignored except for Task Dependencies , where other tasks are dependent on the completion of this task.

Lastly , the project flow from beginning to end will be assumed to start with task ID 1 , and go downwards till the last task ; the listing of the tasks should be in this order. How do we consider parallel tasks ? It may happen that if we take tasks 1 , 7 and 17 first , we may be able to complete the project in the shortest possible time , but how do we assign the available employees to these 3 tasks first ? Our natural order of tasks will be 1 , 2 , 3 , 4 , 5 ,…

More comments can be made while we continue our dialogue.

Narayan
 
Hi Narayan,

First of all thanks for your swift response!

Also - Wow! Thanks for the thorough analysis! This is quite impressive (at least for me :)) - As I said earlier, I wasn't expecting much interest, so I thought it would be best to limit the amount of information and make it easily readable.

My thoughts inline...

Hi Mukund ,

I will just put down my thoughts , and you and anyone else who cares , can comment.

First let us see what data is being entered :

1. Employee ID

2. Employee Name

3. Employee Availability

4. Employee Capability

5. Employee Efficiency

A few points about this :

a. At present you are detailing the employee availability through a column which mentions the start date , another which mentions the end date , and a third which lists the dates on which the employees will not be available. To make things easy , at least from the programming point of view , can we have a project calendar , which will start from the project start date , list all the dates thereafter till the project end date , as column headers , have the employees as the row labels , and indicate whether an employee is available or not on each day ; to make things easier , we can have just an “X” signifying that an employee is not available on that particular date , and an absence of this “X” will signify that the employee is available on that date.

Narayan

This sounds like a wise move! Agreed! Makes total sense!

b. At present each employee has only one capability ; is this true , or can one employee have more than one capability ? If so , how are you going to represent this ?

c. The above point extends to this ; if an employee has multiple capabilities , their efficiencies in each of them may be different.
Narayan

This is a very valid point and something that we run into quite frequently :(, but considering the added complexity with having efficiencies per employee per capability, this can be pared down to an average efficiency. However, if doing this is not adding more complexity, then it's a good feature to have

When we consider task related data , it is :

1. Task ID

2. Task Name

3. Task Effort , in mandays

4. Task Capability

5. Task Dependencies

6. Task Status

The same points mentioned above against employee related data apply here ; will all the tasks be strictly single capability tasks ? If not , how are you going to represent , not just the capabilities , but also the effort , since a multi-capability task may have different efforts in the different capabilities ?
Narayan

Another good point - But usually, in our situation, tasks are broken such that there is only a single capability that needs to be considered

The task dependencies have been mentioned as mere numbers , but will that suffice , or do you want a more rigorous treatment as in any project management software , where either start dependency or completion dependency is mentioned ? For example , if may happen that for Task 9 to start , Task 7 may have to start , but Task 8 will have to be completed ; how will this be represented ?
Narayan

In my case, the dependencies are either present or always dependant on a particular dependant task finishing. I hope that limites the complexity in some ways

I assume that the task effort is in terms of employees who are 100 % efficient ; to take an example , suppose a task effort is 17 days ; let us assume there are two employees whose efficiencies are 25 % and 70 % ; the above task , if given to the employee whose efficiency is 25 % , will actually take 68 days ( 17 * 100 / 25 ) , whereas if it is given to the employee whose efficiency is 70 % , will take only 25 days ( 17 * 100 / 70 , rounded up to the next whole number of mandays ).
Narayan

This assumption is spot on!

If a Task Status is Done , it can be ignored except for Task Dependencies , where other tasks are dependent on the completion of this task.
Narayan

That sounds accurate too!

Lastly , the project flow from beginning to end will be assumed to start with task ID 1 , and go downwards till the last task ; the listing of the tasks should be in this order. How do we consider parallel tasks ? It may happen that if we take tasks 1 , 7 and 17 first , we may be able to complete the project in the shortest possible time , but how do we assign the available employees to these 3 tasks first ? Our natural order of tasks will be 1 , 2 , 3 , 4 , 5 ,…
Narayan

I don't have an answer to this one, but yes, the goal is to complete the project in the shortest possible time, so naturally, any task order that's present is not important, the only important thing is to make the best match between tasks & resources to arrive at the shortest possible time!

I hope this has helped! Though I'm aware that I may not have provided all the clarity you need. However, if there are any questions please do let me know!
 
Hi, Mukund!
It's a very interesting project from the technical challenge aspect, and maybe not only from that point of view... but are you sure that is not missing any information about the project?
Regards!

Hi SirJB7,

Good to see that you find this interesting! I'm sure that there are all a lot of holes in the information. If you feel that you'd like more information do let me know!

Thanks for your help in advance!
Mukund
 
Hi Mukund ,

I have taken one step forward !

A sheet labelled Calendar has been added to your file ; if you run the macro named Populate_Calendar , it will fill up the calendar area with Xs on those days when the respective employee will not be available ; availability is to be entered in two formats :

date 1 , date 2 , date 3 , ... where the individual dates are separated by commas

date 1 - date 2 , where all dates between date 1 and date 2 are dates of absence

The project start date has been made a named range , and this date is to be manually entered ; this date may be prior to the earliest employee availability date or it may be after.

Narayan
 

Attachments

  • Task_Simulation_01_02.xlsm
    40 KB · Views: 16
Hi Mukund ,

One very crucial point : can employees be assigned to tasks the way machines are ? Can a task with a task effort of 10 mandays be split up among 5 employees , all of whom have the capability , and have the task completed in 2 mandays ( assuming 100 % efficiency for each employee ) ?

Or is it that one task can be assigned to only one employee ?

Narayan
 
Wow - Narayan, you're a ninja! ;)

With respect to the question, I'd suggest that We ignore the option of applying multiple resources to a task, primarily because our tasks are not easily divisible and we can always decide to split the tasks logically and run the whole solution all over again. My guess is that any solution would lead to further decision making by the producer and subsequent re-running of the simulation.
 
Hi Narayan,

Very cool! Thanks, looks like the stage is set. I'm very curious and excited about how you're going to move forward ;)
 
Hi Mukund ,

I think the next step will take a long time !

In the meantime , can you say whether the entire calendar is to be worked out considering a 5-day working week ?

Narayan
 
Hi Narayan,

I've been looking around a bit and came across this configurable scheduler called Vishnu. Overview as below

Vishnu is a reconfigurable, web-based, optimizing scheduling system. It performs scheduling in the sense that it assigns tasks to resources at particular times. It is reconfigurable in that it is capable of being configured purely by data specifications and not by recoding to handle a wide range of different scheduling problems with different scheduling semantics. It is optimizing insofar as it allows the specification of a criterion for evaluating how good a legal schedule is and will try to find a schedule that is as good as possible (in many cases actually finding an optimal or near optimal schedule)

What are you're thoughts on using Vishnu as a scheduler solving engine? Is this problem so complex that it would need something like this?

Thanks and regards,
Mukund
 
Hi Mukund ,

From what I have read after your post ( I did not know about this prior to reading your post ) , my comments are :

1. The software is not a commercial product , in the sense that it is not a stand-alone installable package ; it is actually software which has been developed in the course of research ; to create a stand-alone scheduler out of the developed software , you will need to install several other software such as Apache , PHP , MySQL ,... This itself is not as simple as just downloading a program from CNET and running it ; it requires a fair amount of IT knowledge and experience.

2. The next part , after installation of all the above packages , is their customization ; this requires even more IT knowledge and experience.

3. After these two steps have been completed correctly , then comes the actual part of specifying your problem requirements / constraints / rules ; this is an equally complex matter , since the software has been developed with its own set of rules and specification language ; someone in your organization will have to go through the complete documentation of Vishnu , understand it , and then set about converting your problem specification into a specification which Vishnu can understand and act upon.

Based on the amount of expertise that you and others in your organization have , this can take as little as one week or even a month or two !

You need to decide whether your problem is a critical one where even a day can cost thousands of dollars ; if so , it is worth going in for software which will perform optimization to this level ; if your requirement can do without this kind of fine-tuning , then I think what you can get using Excel will do.

The advantage of Excel is that you and everyone else in your organization is already familiar with it ; in your absence , someone else can do the job ; in the case of Vishnu , I doubt that many people can become familiar with it ; eventually , the expertise needed to specify problems in its language will be restricted to one or two people , and everyone else will become dependent not only on the software , but also on these two individuals !

Narayan
 
Hi Mukund ,

I have uploaded a file , where I have manually scheduled the tasks in the Calendar tab ; can you go through it , and see if it is optimal ?

It would be nice if you could upload a project which has more complexity , by way of task dependencies , since I think this is the one aspect of project management which makes it most difficult to optimize.

Narayan
 

Attachments

  • Task_Simulation_01_02.xlsm
    54.1 KB · Views: 10
Really cool Narayan! Apologies, I haven't been able to revert earlier...

On Vishnu

I agree with your analysis, and it would be better to do this in excel. Thank you very much for the analysis.​

On Workdays

yes I think going with the 5 day week would be the best option​

On the manual schedule

I think there are 2 tasks that seem to be missing... I don't see task 1 or 4 in the plan. Also, task 14 seems to be a belong to a particular skill, but has been mapped to another skill. Or at least that's what i'm seeing at my end. Otherwise, it does look like an optimal solution.

I've colored the employees by skill as well as provided the same color coding to the tasks, which means that it's easier to reconginze what's perhaps not working well. Can you take a look please?​


Also, I'm curious, where are you based out of?

As before - you're help is greatly appreciated!

Thanks!
Mukund
 

Attachments

  • Task_Simulation_01_02 (3).xlsm
    54.1 KB · Views: 17
Hi Mukund ,

Off-hand my comment is that Tasks 1 and 4 are missing because they are already Done !

I'll check up about Task 14 as well as your uploaded file. See if you can upload a more complex file to work on.

At present I'm out of India.

Narayan
 
Hi Narayan,

Ah - I missed out on Task 1 & 4's completion status, apologies for that! I'll see if I can put together a more complex file, but by the looks of it, the manual schedule (apart from Task 14) seems to be working fine.

Catch you soon.
 
Hi Mukund ,

I checked Task 14 , and the only mistake I could find was the number of mandays assigned to it ; the calculations had 4 against it , but since I had done the calendar manually , I had put in the value from the wrong cell , as 3.

This will not affect the project timeline , since Task 14 is being done at the beginning of the project itself ; it is not dependent on any other task(s).

The main point , which I forgot to mention is that I have , in manually entering the schedule days , not considered the employee leave days ; if these are taken into account , the project end date may shift by a day or two.

Narayan
 
Back
Top