Call Center Staffing and Cost Reduction using Excel

okay so let's get going on our spreadsheet model I'm going to work at a modest pace because I assume that you can pause and rewind if necessary hopefully you have downloaded this file and have it open so you can follow along because after all that is the idea of doing the example now I'm going to cover this in four parts the first part I'm going to go over the spreadsheet model explain each section of the spreadsheet and talk about what it does and how it corresponds to the information in our original problem part two we are going to enter formulas and name cell regions using the define name feature in excel which is very very handy in part three I'm going to walk you through entering the data into the cells and formulas and so forth and part four we will quickly use the solver add-in to have Excel generate a wonderful quick elegant answer to a very complex problem okay so let's get going now the first part of the spreadsheet to take note of is at the top center you'll see a row of cells in this sort of a salmon color and that is the unit cost or cost per shift you will see all five customer service representative shifts that the cost Center offers 6:00 a.m. to 2:00 p.m. 8:00 a.m. to 4:00 p.m. and so forth now below each one of those will be the average daily cost labor cost of one customer service representative or CSR so each shift will have its average daily cost for one CSR over on the very right in the blue color if you remember in our problem management decided that there is just a certain number of CSRs that have to be on any given shift or any given two-hour period as you can see there on the left and that's so that the call center can maintain a minimum quality of service because we could have three CSRs trying to answer 20 calls a minute but that's not going to offer very good service so that column will represent the minimum number of CSRs that management has decided must be on shift during any given two-hour period that corresponds to the two-hour periods on the left okay in the purple column in the middle once we're all done that will tell us exactly how many CSRs are working during any given two-hour period which is fantastic it's almost magic now the large box in the center those cells will hold binary information either ones or zeros and all that does is it tells us is the two-hour shift on the Left covered by the daily shift that runs vertically so if the the overall shift covers that two-hour period we'll put a 1 if it does not we'll put a zero but we'll type all that in here in a couple minutes now on the light blue at the very bottom this is actually what Excel is going to generate for us it is going to tell us exactly how many CSRs to put on each shift in order to minimize our labour cost so that's what we're actually kind of solving for and then the pink box in the lower right will tell us our actual labor cost per day in total so the average cost per customer service rep and the number of reps on any given shift of course will add up to be our total labor costs for the day okay so the next thing we're going to do is we are going to define names for certain Silla regions now Excel has a wonderful feature in it that will allow you to select a cell or range of cells and then give that range of cells or individual cell a unique intuitive name that's a lot easier to remember than see 8th through 8th or something like that so we're going to give certain regions of our spreadsheet names so we can then use those names in formulas and in the solver and things like that so let's go ahead and get started on naming our regions so the first reason we're going to name is the unit cost or cost per shift line which is the very top salmon color row so we're going to highlight c5 through g5 then I'm going to right click to the context menu and right near the bottom we have defined name some select that and this will bring up our new name box now I have a convention for for naming cell regions so I'm going to stick with that so I'm going to call this cost per shift now the scope drop down box and this you can decide do you want this name to permeate your entire workbook or do you want it to only work for this sheet so that way you could reuse that name on different sheets since I'm only using one sheet in this demonstration I'm just going to select workbook and leave it at that so I'm gonna go ahead and hit OK now that row of 5 cells c5 to g5 we can now refer to that row using cost per shift in any formula or anything like that when you will we'll see how that plays out here in a minute okay so we have a few more to do so I'm going to go over to the blue column on the very right I will highlight that column right-click select define name I'm going to call this minimum needed minimum needed and I'll leave scope to workbook and click OK okay the next range we're going to do is the number working which is the very bottom the light blue color there right click define name and we'll let's call this number working three more to do now the next thing we're going to do is the very large region here in the middle that extends from c8 all the way down to g17 right-click define name we're going to call that shift works time period shift works time period code and click OK on the bottom right which is the one in the end we're going to be most concerned about other total costs I'm going to right click that define name call it total cost and then one more is the purple column select that right click define name we're going to call this total working okay so just a couple minutes there we have defined different areas of our spreadsheet using the defined name feature in excel and that way we can use the name instead of the cell region in formulas and any other manipulation we might want to do okay we do need to enter a few formulas in the spreadsheet luckily we only have to enter two because we can actually use the autofill feature so in h8 which is the first cell in the total working column we're going to enter a formula and will look like this so it will be equals sum product and Excel will go ahead and guess what you want to do and that's what we want to sum product now what this does is it will take an array of cells multiply it by a corresponding array of cells and then sum all of that up so I'm not going to go into a whole lot about what that does but if you google some product function tell you exactly what it does so basically we need two arrays so the first array we're going to do is the shift covers time period row to the left of that so that's going to be our first array and we'll put a comma now our second array will be number working and you can see Excel guesses you want that and we'll close the parentheses now what is this going to do let's make sure we understand what this what's going to happen the sum-product will take one array multiply it by another array or matrix if you prefer and then sum that up so what this is going to do is it will take the shift coverage time period row right here at the top it will multiply that by the number working row down below and then it will tell us how many csrs are working during that two-hour period from 6 a.m. to 8 a.m. pretty cool huh so we're going to go ahead and hit enter for that there's no data in the spreadsheet now so it'll say 0 now the cool thing is we can just simply want all this information in total working we can just select that cell and drag down and it will fill it in so if we look at the next one it says c9 to g9 number working next one is c10 to g-10 number working and so forth so just using the fill down feature in Excel it changes the the reference cells there at the beginning keeping the number working array the same so it moves down a row each time in the shift covers time period but maintains number working row there at the bottom so that's that we have one more that's going to be our total cost of labor per day and that would be equals again some product now what do you think those what's going to go here what row and our spreadsheet tells us how much each shift costs per CSR well that's our cost per shift up here at the top so our first array will be cost per shift and we'll select select the tag there and of course we need to multiply that by the number of CSRs that are working if you remember that is our blue at the very bottom here so we're going to multiply a cost per shift by the number of csr is working so number working select that close parentheses and enter and as far as formulas go and naming regions that's it we did it in two or three minutes okay so part three we're going to go ahead and enter in some numbers which will make everything start to look a little bit better so we're go ahead and enter our data in the top row so the first shift was 170 dollars per CSR the next one was 160 per CSR 175 180 and 195 okay and if you remember that the shifts get tend to get more expensive as the day goes on because there are shift differentials for evening and overnight workers which is actually fairly common okay well go over to the minimum CSR is needed and again this was provided to us by management who went back and looked and said we at least need this many in this given two-hour period so we had 48 79-65 87 64 73 82 4352 and 15 so what we're telling excel is that for example from 8 p.m. to 2 p.m. we cannot have less than 43 csrs on duty that's just the absolute minimum that we're willing to accept so that is that row there all right so we're going to go ahead and go to our middle section our ship covers time period and remember this is either 1 2 0 1 being yes 0 being no and the question is does this does the shift column where am cover the two-hour period to the left so the 6:00 a.m. to 2 p.m. well that that covers 6 a.m. to 8 a.m. same thing for the next one the next one and the next one and then that shift is over for the day to the rest for all zeroes ok so we're the same thing for the 8 2 4 6 to 8 is not covered there but we're covered from 8 to 10 10 to 12 12 to 2 and 2 to 4 and then not for the rest of the evening and overnight so you're smart you can see how this work so let's go ahead and type them in okay one more okay so now we have our cost per shift we have our minimum number needed and we have our shift covers time period yes or no and from here we're ready to get into the solver okay so let's go ahead and get the solver Magic on track here to solve our problem now I do want to make sure you have the solver add-in installed or loaded into Excel so I'll walk you through that real fast in 2010 at least it's under the file menu and go down to options in the Excel options you will see Eddins and the bottom of the add-ins screen you'll see where it says manage Excel add-ins so you go ahead and click go and then an add-in box will pop up now I have many other statistical attends in here but you want to make sure at the very bottom you see it will say set solver add-in make sure that's checked of course I have mine checked if yours does not go ahead and check it click OK all right now our solver add-in is loaded into Excel and we are ready to solve now to access that oops it's under data and then over on the right hand side under analysis you'll see the solver let's go ahead and click that now this can be a little intimidating at first but once you do it a couple times it's fairly self-explanatory now where it's a set objective remember our objective here is the lowest possible labor cost so our objective is sort of our target and previous versions of Excel it was called target cell but that's the one we want to hone in on now the great thing is because we've named it we can just use the name which is total cost now if you remember that was the pink at the very bottom right of our spreadsheet so that's what Excel is going to really focus in on now we are wanting to minimize that so we select the mi m4 minimum okay now below that we have by changing variable cells now what we have to do here is tell Excel what we want Excel to figure out in order to create the lowest possible cost so of course we want Excel to tell us what's the optimum number of employees per shift that covers our minimum requirements but does so at the lowest possible cost so we are going to have Excel manipulate and change the number working which is the light blue at the bottom of our spreadsheet so Excel is going to be is going to figure out the best combination or the best set of numbers and the bottom of our spreadsheet to make our cost of the lowest so remember we named that light blue row number working and that's that now we do have to add a couple of constraints or nuggets in this case one constraint and that is our total working the purple column has to be greater than or equal to the minimum number remember we cannot go below the number of CSR is in the blue so we're going to add a constraint okay and remember we name these two and this is total working must be greater than or equal to minimum needed so the total working has to be greater than or equal to the minimum needed we're going to click OK now there are two more things we have to do here real quick and then we're pretty much done you want to make sure the make unconstrained variables non-negative is checked basically all that means is that in this sort of problem we cannot have negative people on shift which should make sense we can either have 0 or more but we cannot have negative people and then in select solving method you want to click on that arrow and select simplex LP basically that is means simplex linear programming okay and that is pretty much it so if we've done everything right we'll go ahead and click solve okay so solve our final solution as it tells us that we can keep solver solution or we're going to restore what we had before but we're going to go ahead and keep the solver solution go ahead and click OK now let's look at what is this did here in one click it's still amazes me every time I do it in one click of a button Excel is able to tell us exactly how many CSR's to have on shift on each shift that one met our minimum requirements of CSR is needed and two at the lowest possible cost so if we look down at the blue row at the bottom Excel figured that out for us so it's telling us that we need 48 csrs on shift from 6 to 231 on shift from 8 to 4 39 on shift from noon to 843 on shift from 4:00 to midnight and 15 on shift from 10 p.m. to 6 a.m. now if you look at the total working rows the purple column that tells us how many CSRs will be working during any given two-hour period so you will see from 6 a.m. to 8 a.m. we have 48 working which should make sense because if we go down to the bottom row there's only one shift on duty at that time it's the 6-2 p.m. shift Excel told us we need 48 so 48 times 1 is 48 ok now in terms of the 8:00 to 10:00 we're going to have a total of 79 CSRs so of course we will have people from the 6 to 8 shift and then we will have some new people coming in at 8:00 that will add to that 48 total and so on and so forth so you can see the distribution of CSRs on shift during any given 2 hour period by the total working column and it would tend to make sense that from 12 to 2 we're going to have the most CSRs on shift that's the busiest time of day according to our past data and a lot of people call in on their lunch breaks if they have an issue they need to work out or something like that so it's a very busy period for the call center also that's when people in the call center will probably be taking their lunches so we'll have to cover you know see us our lunches during that time period as well so that should make intuitive sense anyway ok I'm going to wrap this up the video part at least here and point out that in the lower right hand corner of our spreadsheet our total labor cost per day will be 35 thousand six hundred and ten dollars and that 30 thousand six hundred ten dollars is the absolute lowest possible cost that we can staff the cost center given the minimum CSR constraints we had and the cost per shift and that is how we solve and staff a call center using path data current wages and use Excel to tell it exactly how many we should have per shift great I hope you enjoyed it

Channel: Brandon Foltz

Views: 117,540

Rating: 4.9013157 out of 5

Keywords: call center staffing and cost reduction using excel, excel call center, call center excel, call center staffing, call center scheduling, workforce management excel, call center forecasting, erlang excel, workforce management call center, erlang c formula, erlang c tutorial, erlang c, excel solver scheduling, statistics 101, customer service, excel, staffing, service level, svl, microsoft excel (software), call center, agent performance, anova, call centre, software tutorial

Id: 5tDamQdgARg

Length: 25min 39sec (1539 seconds)

Published: Thu May 24 2012