Using Excel to understand if I have enough resource

[DAFTCHILD]

Hi


I am trying to build a database that will tell me if I have enough resource to cover a number of different tasks. The numbers stack up as this. I have a team of 20 people who are trained in anything from 2 to 50 activities. Each activity takes a different amount of time, and has different incoming volume. I have a database which forecasts the incoming volume for each of the activities based on the last 6 months work we have received. I have another database which outlines which activities my team of 20 are trained in. What I need to understand is whether I have enough staff trained in each activty to cover all the work going forward.


I can easily add up the number of people I have trained in eaach activity, and I can see if that would be enough to cover the volume we have coming in, but when you start spreading that resource across a number of activities that is when it gets complicated.


My issue with the current database is this: In the database which shows what people are trained in, doesnt give a true picture of how much they can actually do. To explain, if John Smith is trained in all 50 tasks, the database shows that I have 1 person trained in all 50 activities, when in reality he will only be able to work a certain number of those each day due to the volume of each activity and how long they take.


Does anyone have any suggestions for how I can best use the data that I have to understand how much resource I have for each task, or is there a better way of recording the information in those databases to give me a true reflection?


Sorry if that doesnt make sense, I have been playing around with these databases all day and have read so many articles about skills matrix and planning but nothing seems to cover this scenario off

 

[jason]

im not sure how much i can help but it kind of sounds like you'll need to assign a volumne percetange for each task: Task 1= 10%, Task 2: 2%, Task 3: 43%.... and use these to evaluate (somehow) how each person can be utilized in their trained tasks and not 'over load them'. so that if bob is assigned to tasks 1,4,6 you would be able to see how much of the daily volumne those tasks would use up... im not sure how you wold/could go about that

 

[SirJB7]

Hi, DAFTCHILD!


From the green sticky topics at this forums main page:


"How to get the Best Results at Chandoo.org


When drafting a question, try and lay out the question in a clear and concise way.

Try and tell the readers what is the situation, what have you tried and what do you want to achieve.

Don't leave out information more info is better

For the best/fastest results, Upload a Sample File."


So consider uploading a sample file of your workbook, tell us what you've done and where're you stuck, include manual examples of desired output if applicable, ... it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!

 

[DAFTCHILD]

Hi


I have uploaded a version of what I am thinking of making here


https://www.dropbox.com/s/955iyb9yq7nd289/RESOURCE%20SPREADSHEET.xls


What I would like to do is the insert a calculation next to their names, in B16:B22. For example, for Lucy it shows that she is trained in tasks 1,4 & 10. I would like B22 to sum L3,L6 & L12. I can do this manually, but this way if people are trained in more tasks, or the volume of the task changes, I dont need to re-do all the calculations I can just update the relevant field. Hopefully that makes sense

 

[Hui]

DaftChild


What you have shown using John as an example is that he is trained in Tasks 1, 2, 3, 5, 7 & 9, I assume that is what the 1 means


What you also need to know is how long each task takes 1 person


Task 1 shows it requires 9 minutes of time

John, Sam Ian & Lucy can all do it


But how long does it take them?


If the answer is 1 minute each, your in trouble as 4 x 1 = 4 < 9 unless they can spend multiple blocks of times on each task

If the answer is 3 minutes each, you will have spare capacity as 4 x 3 = 12 > 9

 

[NARAYANK991]

Hi ,


Try this in B16 and copy down.


=SUMPRODUCT((INDEX($D$3:$J$12,0,MATCH(A16,$D$2:$J$2,0))=1)*($L$3:$L$12))


Narayan

 

[DAFTCHILD]

Hiya Hui


Sorry I didnt make my data clear. The 1 is just an indicator that they are trained in it, but it could be an X or a Y, its not hugely relevant but what ever will fit a formula I can put in there.


The time on the left is the incoming volume for that task each day. I can add a column in there to show how long each individual task takes if you feel it will make the calculations easier (in real life it will range from 40 seconds to 19 minutes)


To add more fun to the pot, the consultants dont have to do an equal amount of each task they are trained in. They could spend all day working task 1 if required.


Thanks so much for all your help, Narayank991 Ill give that a try later on today