Excel - AVERAGE Function Problem - Expert Solution

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
Excelchat Expert 16/10/2018 - 01:39
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
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc