Question description:
This user has given permission to use the problem statement for this
blog.
This is the problem your team consists of 10 advisors . one advisor is able to complete on average 22 items per day(split:10 written, 12 emails) Time to complete averages 20 mins written, 12 mins email.
You currently receive 144 written & 83 Email per day.
You have a backlog of 3200 letters, 1000 emails.
How long will it take you to clear the backlog and return to the daily SLA's
Is there a way of showing me how to work this out in excel???
Solved by C. E. in 55 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
16/10/2018 - 01:37
Hello! How are you?
Excelchat Expert
16/10/2018 - 01:38
Hello! Are we still connected?
Excelchat Expert
16/10/2018 - 01:39
10
Excelchat Expert
16/10/2018 - 01:39
0
Excelchat Expert
16/10/2018 - 01:40
Do you have additional information on the SLA metrics please?
User
16/10/2018 - 01:41
written 5 working days
User
16/10/2018 - 01:41
emails 24hours
Excelchat Expert
16/10/2018 - 01:42
Thanks. I'll start working on it
Excelchat Expert
16/10/2018 - 01:45
By any chance, do you have a specific methodology to compute SLA for both written and emails?
User
16/10/2018 - 01:46
this is a presentation problem I have been given for an interview
User
16/10/2018 - 01:47
they want to know when sla will be returned and when the backlog will be completed by
Excelchat Expert
16/10/2018 - 01:53
Hi! Could you please extend the session?
Excelchat Expert
16/10/2018 - 01:54
Thank you!
User
16/10/2018 - 01:54
this is free??
Excelchat Expert
16/10/2018 - 01:54
Yup. The extension is free of charge.
User
16/10/2018 - 01:54
Will you be able to give the formuls for these calculations
Excelchat Expert
16/10/2018 - 01:55
Sure. The formula are presented in the worksheet
Excelchat Expert
16/10/2018 - 01:56
Any thoughts on how will this information impact our calculation?
Excelchat Expert
16/10/2018 - 01:57
"Time to complete averages 20 mins written, 12 mins email."
User
16/10/2018 - 01:59
These are the questions in full: 1.How would you plan and structure your team to manage daily incoming numbers and recover the backlog?
User
16/10/2018 - 02:00
2.In what period would you expect to a )recover SLA & B) eliminate all outstanding items?
User
16/10/2018 - 02:02
3. What support would you require from line management to implement and achieve this?
Excelchat Expert
16/10/2018 - 02:04
Honestly, I can only support you with Excel related problems. But let me do my best to come up with the answer on the number of days based on my understanding of the data you presented.
User
16/10/2018 - 02:04
thank you
Excelchat Expert
16/10/2018 - 02:05
As it stands now, the existing average completion per day for written is way below your average flow of written work per day
Excelchat Expert
16/10/2018 - 02:06
i.e., 100 < 144
Excelchat Expert
16/10/2018 - 02:06
Given this, it is not possible to achieve the desired SLA of outstanding 720 written work a day.
User
16/10/2018 - 02:07
yes they are the correct figures guess thats why there is a back log
User
16/10/2018 - 02:07
More staff needed
Excelchat Expert
16/10/2018 - 02:07
720 is computed as 5 days x 144 workflow
Excelchat Expert
16/10/2018 - 02:11
Can you see cell J8?
Excelchat Expert
16/10/2018 - 02:11
Total minutes per day spent on work in 244
User
16/10/2018 - 02:11
yres
Excelchat Expert
16/10/2018 - 02:12
Assuming an 8-hour shift (or 480 minutes a day), the utilisation is low
Excelchat Expert
16/10/2018 - 02:12
Perhaps we can increase the average number of completed written work per day.
User
16/10/2018 - 02:15
What about increasing another member of staff
Excelchat Expert
16/10/2018 - 02:16
You mean additional Advisors?
User
16/10/2018 - 02:16
As team leader I can be included as a suggestion
Excelchat Expert
16/10/2018 - 02:17
That's an option too.
User
16/10/2018 - 02:18
Why is additional advisors 9
Excelchat Expert
16/10/2018 - 02:19
That is just an arbitrary number
Excelchat Expert
16/10/2018 - 02:20
You can adjust it up or down
Excelchat Expert
16/10/2018 - 02:21
As long as the total adjusted completion per day is greater than average inflow per day, then you may catch up to your backlogs and achieve your sla
Excelchat Expert
16/10/2018 - 02:25
At the minimum, as per my calculation - that will require 5 additional people, working at the same average completion rate as per your existing staff
Excelchat Expert
16/10/2018 - 02:26
Hello? Are you still with me?
User
16/10/2018 - 02:26
Hi yes
Excelchat Expert
16/10/2018 - 02:27
As you can see, the answer using 5 additional people would be 405 days
Excelchat Expert
16/10/2018 - 02:27
....for written and 11 days for email
Excelchat Expert
16/10/2018 - 02:29
If you double the headcount, then you can reduce that to 44 days
User
16/10/2018 - 02:29
I think thats whats needed to I get the sheet to study the formulas never used this before
Excelchat Expert
16/10/2018 - 02:30
Sure. You may download a copy of this worksheet by going to File>Download As>Microsoft Excel
User
16/10/2018 - 02:31
Thank you so much for your help
Excelchat Expert
16/10/2018 - 02:31
You're most welcome. I hope the solution I shared makes sense to you.
Excelchat Expert
16/10/2018 - 02:32
If you don't have any more clarifications, you may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)
Excelchat Expert
16/10/2018 - 02:32
Appreciate if you could leave feedback and rating after the session. :) Thanks and have a great day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
