Capacity & Inflow Threshold

[prasaddn]

Hi,

I have an interesting problem, and need help from any of you. Let me try to put my case as simple as possible, though it sounds very crazy for me myself.

Now, let me explain what is Inventory here. I get cases which are called “NewTasks”. And once you work on it, it becomes “Follow Up” cases. The catch here to be noted is one “NewTasks”, when worked will become multiple “Follow Up” cases.

Based on historic data, I know that one “NewTasks”, will become 1.27 “Follow Up” cases.

Also, “Newtasks” need to be attended within 24 hours.

The “Follow Ups” do not have any 24 hours timeline, but we need to close them equally. When you work on the “follow up”, it may get closed at that stage or can get pushed to next day “Follow Ups”.

Based on historic data I know what % of “Follow up” gets closed at which stage.

I have 25 folks in the team, who have to work on both the type of cases (“NewTasks" as well as “Follow Ups”).

The average handling time for “NewTasks” is 15 mins, while for “Follow Ups”, it is 7.5 mins.

I thought I could make use of What if Analysis, but, I do not know how to use the same.

Now, to make my problem more visible, I have attached the sample file, which I tried to work manually with few formulas, but it is not dynamic nor helping me to work with different scenarios.

Let me explain the file:

1-oct-2015 dated row gives what is my inventory on hand for “NewTasks”, split by age-wise, Col C to Col G, while Col O to Col S gives the “Follow Up” cases on hand at various stage.

For my calculation purpose, I have split the 25 FTEs into two groups of 8.5 and 16.5 FTEs, but you may want to take a different approach.

Based on capacity Column I and U, I put the formula to see how much I can handle every day, which can be seen in the rows marked as output.

Col J indicates my % of TAT met for “NewTasks”, while Col V indicates my coverage of “Followup” cases.

Rows dated, 2-oct-2015 onwards is mostly formula driven, except for column G. Similarly, I have formula driven inventory for “Follow Up” cases. I have put 200 for now as my threshold intake of “NewTasks” per day.

As stated earlier, whatever I work on “Newtasks”, previous day, (1-oct-2015), becomes 1.27 times of FU1s the next day, (2-oct-2015).

And, the “Follow Up” cases remains in same stage, until it is worked. If I work, according the $O$3 to $S$3 %s, the volume gets closed, rest will get moved to next stage.

Now these are some of the questions I am trying to address:

1. In order to maintain the TAT 100% (Col J), what should be my maximum intake of “Newtasks”(Col G).
   
   
2. What is my best combination I should deploy to get maximum coverage in Followup as well as maintain 100% TAT for new tasks.
   

You can change the team size Col K, to any number but remember total team size is 25. And accordingly the FTEs should change in Col W.

How do I get answers for my above two questions, with various combinations:

1. if I change team size in “NewTasks” and/or “FollowUP”
   
   
2. if I change my average handling time

Kindly Help me

Regards,
Prasad DN

 

[Nebu]

Hi:

I have not gone through your problem in detail, but at a glance I felt this is an optimization problem and something that you can tackle through a solver. Here is a link to get you started
http://www.solver.com/examples-optimization-problems

The key here is to clearly defining your objective function and constraints and to convert it into a mathematical equation. I believe this will be fairly easy for you since you know your business variables well.

Note: There are numerous examples on the web if you do a little bit of research. You may also want to search for how to interpret the results produced by solver as well.

Thanks

 

[prasaddn]

Hi Nebu..

I had earlier heard about this Solver and had never tried nor felt its requirement.

Now, with this problem on hand, I had no other option but to learn and do.

Finally the result, yes I could make use of Solver to solve my problem.

Thank you.
Regards,
Prasad Dn