# capacity planning in excel



## etaf (Oct 2, 2003)

I'm trying to make a capacity model
I have X number of cased type red/amber/green and they take a different time each colour to complete.
If i want a model to say how many people I need if the red or amber of green or combination of increase or decrease
Is there a simple excel way to do this


----------



## Dreambringer (Jan 19, 2005)

I am no excel pro, and someone else might chime in and correct me, but the answer is no, not an easy way but is possible.

Before you get to that, you need to gather old data, like average cases recieved by color, if you could break that down by hour, average time it takes to complete a case, how many cases you get a day, how many average cases you are closing in a day (if you could break this down by hour, that would be ideal as well).

So there really is alot of info you need. 

Check your PMs


----------



## Zack Barresse (Jul 25, 2004)

Perhaps a zipped/uploaded sample of your workbook, stripped of any sensitive data?


----------



## etaf (Oct 2, 2003)

lets say I know the following
one person runs with an average 6 cases a day..
each person can handle
2 red cases
or
4 amber cases
or
6 green cases 
and typically the volume mix maybe 
10% Red
30% amber
60% green

I have loads of data I think, {because I'm not happy on quality} however, if I just need the methodology (I can then re-apply the new data)
to then say if red cases increase next year by 20% and green cases reduce by 8% the capacity manpower needed will be.....

Then we also want to start splitting the coloured cases by type
so if our products for example are
chairs
Tables
Sideboards
and we know the mix of red/amber/green for theose products what happens if we increase the production of chairs ....


----------



## Dreambringer (Jan 19, 2005)

I sent you an email with a sample file, let me know if you have any questions or if that helps you in any way


----------



## etaf (Oct 2, 2003)

thanks - i can see what you are doing on your profile and infact i worked on a simular matrix some years ago.
I guess the bit I'm stuck on is that we have different types of cases with a different mix of time capacity.....
and what to forecast capacity as type and volume changes   

in you example you know 10calls /person /hour
I know a person can handle - 2 red calls or 4 amber calls or 6 green calls per person per day but also only a max of 6 calls per person.... 
so say a person per day can handle 1 red 2 amber and 3green per day on average

what would happen if green decreased by 10% but red increased by 8%...


----------



## Dreambringer (Jan 19, 2005)

I would probley go with something similar to this.

The first Set is the actual calls/emps broken down by Red/Amber/Green

The second set is projected, right now at 100%, but you can change it to reflect the volume, so if you think red would drop down to 92% of the current volume and the Green would go up 20% it shows the changes that need to be made.

Short and sweet, I hope it helps.


----------



## ddockstader (Oct 21, 2004)

I hate to sound too academic, but this sounds exactly like a perfect linear programming exercise. There may not be too many people familiar with this technique, but here is a tutorial that can get you started:

http://www2.isye.gatech.edu/~spyros/LP/LP.html

Essentially, the simplex method has you set up equations to specify the constraints you have identified and then solve the equations algebraically for the optimum results. Obviously, you will have to change the equations for differing conditions and re-solve the solution, but it's not rocket science once you get the first problem solved.


----------



## etaf (Oct 2, 2003)

thanks 
i have had another idea
The max work units possible is 6 units
a red is worth 3 units
a yellow is worth 2 units
a green is worth 1 unit

so as the max is 6 units
the 1 red and 1 amber and 1 green = 6units

how would that work


----------



## Dreambringer (Jan 19, 2005)

I need to find out more about the process to give you an example.

So lets say "Joe" reaches his 6 units for the day, and its early, can he go home? Is 6 when HIS day is done?

Are they "calls" so he might get two reds in one day, or might get one red, and 4 greens?

Which is why I was looking for an average.

Once you have that average, you can base all your forcasting on it.

I have attached another sample, I hope I heading in the right direction.


----------



## etaf (Oct 2, 2003)

Its not based on a day - so its not like engineer calls or call centre calls which stop and start with a short MTTR - to use a call centre type of call - i guess these are more like escalation type calls - if i have a major escalation RED then it takes lots of resource - if green maybe only an hour a day for sixty days..

a red case my last 28days so max 2 red cases for 28days
a green case 60days and amber 40days - i tried factor that to a year and then perday... and got lost, but i guess I need to add the dimension for time....

six is not really an end of day just the max he can handle at any one time...
I played with a units workload as you have produced {thanks very much}
and that seems to give us a manpower for volume mixture changing.
which i would like to graph to show that you can look up a row and get a number   I guess that just leaves world hunger for tomorrow  

I'm afraid i have to leave now for the night - {UK time} so will pick up late tomorrow night if you have any further suggestions and I'll have a read of th elinear programming  

Thanks for the support so far


----------



## Dreambringer (Jan 19, 2005)

etaf said:


> which i would like to graph to show that you can look up a row and get a number   I guess that just leaves world hunger for tomorrow


When you get a chance explain what you are looking for on the graph, I dont quite understand.


----------



## etaf (Oct 2, 2003)

yea i know its a bit vaugue sorry - 
I sort of had a concept this afternoon that i could have a sort of look up graph - that you then start on the Y axis for the increase and go along to a line drop down to the X axis and bingo theres the headcount,......
or look along the X axis at headcount and say you want to have 10 people go up the graph reach the line and then look on Y for Volume of units - which {MAGIC NOW HAPPENS } on another graph converts to a series of business mixes of red/green/amber
Or may be a set of lines which when bysected gives you some combinations of red/green/amber mixes 

as i said just a concept for a simple model using a methodology - so as the data inproves - quick pivot table extrapulate update graph ....
- which I'm rapidly losing the plot on........

but thanks for all your help so far....

its ging to get complicated when I had product mixes in ---- but later i think


----------



## etaf (Oct 2, 2003)

so i have played some more

Using the UNITS Idea further to also add the number of days elapsed..
The flaw here is the distribution of the calls - this assumes sequential capacity
For example assuming a person can work 5 days a week 52 weeks a year thats 260 days {NOT CORRECT but we can change to 80% availability and 70% utilization type stuff in spreadsheet)
Now they can handle six units of work each and every days - that gives them a unit per day workload of 260 * 6
So thats the max capacity 1560 units of work in any year..
OK so red cases are worth 3 units of work and last 28days - so thats 84 units of work.
And so on for Amber & Green
So how many redcases can I person do in a year 
1560 / 84 = 18.571 red cases
Or
how many amber cases can I person do in a year
Amber = 2 units of work and lasts 38 days - so thats 76 units of work
1560/76 = 20.526 amber cases
OR

Green = 1 units of work and lasts 62 days - so thats 62 units of work
1560/62 = 25.161 green cases

So in the spreadsheet
You can enter the number of cases - then in the next block that will then calculate the weighting for units - the next block calculates the time and then everything is normalized - add the unit-days up to a total and divide by the unit days possible per person gives the manpower.

Now on the lower blocks, as created "dreambringer" - same calculation but you can change the % of red amber green cases it then recalculates the manpower 
So you can say 110% in RED 80% in green and it works out new manpower

I think 
Now the really BAD NEWS - 
So a person can do 2 reds - so thats 49/2 = 24.5 people if they where all running at the same time but only needed for 28days - (they run for an average of 28days) - so 24.5 * 28days =686 days we have 260 days per person per year so 686 / 260 = 2.6 - but we could get away with 2.6 people if we run the cases all sequentially over a year
Thats the flaw I think the overlap and distribution..


----------



## Dreambringer (Jan 19, 2005)

I am not quite sure I understand where you are giong.

By looking at your temp.xls, I do not see what you are doing.

What I see is the first chart, *(average number of cases) that came in on Monday.
Lets us 10.

The second chart just puts a unit value to it. (3 units per red, 2 per Amber, and 1 per Green)
So we are going to use 30.

Then on your 3 chart (Unit-Day) you have multiplied the days by the total units. Why?

You say that 3(Units per red)*28(Days it takes)=84(Total Units of Work)

I dont see that. You are not comparing apples to apples. By what your said is that every day for 28 days its worth 3 units, but it should only be worth 3.

So if you have 1 case that comes in on the first day, its worth 3, the next day it will not be worth 6 units, its still worth only 3 units

So I am not sure why you are counting the units that way.

Are you trying to drill down on the stats, but then in order to get an accurate forcast you are going to, again have to look at your historical data.

*_So lets say "Tom" gets 2 cases on Monday Jan 1st. Does that mean for the next 28 days he will not be able to anything untill he gets the case done, or is there a case load maximimum/minimum?_


----------



## etaf (Oct 2, 2003)

> *So lets say "Tom" gets 2 cases on Monday Jan 1st. Does that mean for the next 28 days he will not be able to anything untill he gets the case done, or is there a case load maximimum/minimum?


correct - the maximum tom can run with is six units of work
A red case is worth 3 units which is assuming 4hours a day for the next 28days
A amber case is worth 2 units so 2.6hours perday for the next 38days every day 
A green is worth 1 iunit so 1.3 hours perday every day for next 62days every day.

so if he has 2 red cases that the max work for the next 28days and he cant do anything else....


----------



## Dreambringer (Jan 19, 2005)

Ok here is what I came up with.

I basicly broke it down by hour. This will give you a more accurate forcast.

What I figured was...

Using the actual charts. Lets say on Monday you get an average of 12 Red Calls.
Using your forumuals, that equals 36 units.

36(units)*28(days)=1008 Units per day.
So of that 1008(Units)/8(Hours in the day)=126 Units per hour.

In order to meet the staffing needs...
126(Units per Hour)/6(Units per day)=31.5 Average Employes for Mondays to support the Red Cases.

Are we going in the right direction?


----------



## etaf (Oct 2, 2003)

almost..
But its not really a daily figure as we cant do the 28days work in one day.....
and a red calls takes 3 units of work amnd as a person can only work 8 hours - than thats 4 hours every day on the call for 28 days


12 red calls will be 12 x 4hours x 28 days = 1344 hours of work
so in a year we have 8hours for 365 days (as the elasped time 28days is not work days ... yea it gets worse)
so 8 x 365 = 2920 hours

so to do 12 calls 1344/2920 = 0.46 of a person

and therefor one person can handle 26 red calls in a year

But I think my theory is so flawed its unreal .....

but a red call after 28days turns into the amber call for XX days and then turns into a green call for XX days 
so an event which started as red will last reddays + amberdays + greendays 
and take 4 hours resource each day for the redsays 2 hours resource for the amberdays and 1 hour resource for greendays..

Thanks for the help and keeping with it - sorry its so sketchy - but the data i have is crap I think....... 
I'm getting brain dead on this problem....


----------



## Dreambringer (Jan 19, 2005)

Does a red call take no less then 28 days?

Or can it take less then 28 days?


----------



## etaf (Oct 2, 2003)

no thats the average - so no less than 28days even putting 200 people on it so by having 2 people you cannot half the time..- its not the time thats an issue - its creating and developing and implementing the solution


----------



## Dreambringer (Jan 19, 2005)

So I just want to make sure, A red call comes in, it takes no less then 28 days to finish, but after the 28th day, it now becomes an amber call? how long after day 28 does ti turn amber/green?


----------



## etaf (Oct 2, 2003)

immediately after 28days turns amber
stays amber for 38days then turns green
stays green for 62days

so the call lasts a total of 28 {red} +38 {amber} +62 {green} =128days


----------



## Dreambringer (Jan 19, 2005)

Does it follow the same person? So lets say "Tom" gest the case on Jan 1st, and On May 8th(128 Days, approx) it still isin his que, but now as a green case?


----------



## etaf (Oct 2, 2003)

yes - but instead of taking 3/6th of his time a day it now only takes 1/6 
as he can do 6 units of work
red =3
amber =2
green =1


----------



## Dreambringer (Jan 19, 2005)

Oh happy belated birthday btw , give me a while and let me think of how else we can approch this


----------



## Dreambringer (Jan 19, 2005)

Once last question, if "Tom" gets 2 cases on the 1st, and they are both red, does that mean that for the next 28 days he is only going to work on the 2 cases until they become Amber cases?


----------



## etaf (Oct 2, 2003)

correct


----------



## Dreambringer (Jan 19, 2005)

Sorry another question, and I guess I am just trying to Drill down to figure this one out, lets say "Tom" is out for a week, what happens to that case?


----------



## Dreambringer (Jan 19, 2005)

OK, I was looking at the numbers, and the way I see it, is that once someone gets 2 reds, it would be near impossible to get another one, with the unit system we are using.

Ok using these valuse

Case 1=Red (3 Units)
Case 2=Red (3 Units)

As Case 1 and 2 pass their 28th day they turn to Ambers

Case 1=Amber (2 Units)
Case 2=Amber (2 Units)

Which frees up 2 Units. Either 2 greens or 1 more amber. For this example we will use 2 greens.

Case 1=Amber
Case 2=Amber
Case 3=Green
Case 4=Green

At Day 62 (38 days at as an amber) both Amber cases would now become Green, which would free up 2 more units, either an amber or a green.

Using Amber now for this one, we are at:

Case 1=Green (1)
Case 2=Green (1)
Case 3=Green (1)
Case 4=Green (1)
Case 5=Amber (2)

So by using this example the most it frees up is 2 units at a time, Not sure ifyou see where I am going? It is almost impossible using the units for someone to get another Red, without them finishing up a case earlier then the dates (28,38,62) you suggested.

In that case, you are giong to have find another way to track the cases.

I really am trying, but I think thats what I was stuck on, until I drew it out on paper.

I tried to explain it on the spreadsheet I attached


----------

