Streamline Your Personal Contacts with airSlate SignNow's Personal Contact Management for Staffing

How to create outlook signature

okay so let's get going on our spreadsheet model I'm going to work at a modest Pace cuz I assume that you can pause and rewind if necessary now hopefully you've downloaded this file and have it open so you can follow along cuz 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 defined name feature in Excel which is very very handy in part three I'm going to uh walk you through entering the data into the cells and formulas and so forth and part four we will quickly use the solver addin 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 a sort of a salmon color and that is the unit cost or cost per shift you will see all five uh customer service representative shifts that the cost center offers 6:00 a.m. to 2: 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 2hour period as you can see they're on 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 2-hour period that corresponds to the two-hour periods on the left okay and the the purple column in the middle once we're all done that will tell us exactly how many csrs are working during any given 2hour period which is fantastic it's it's almost magic now the large box in the uh Center those cells will hold binary information either ones or zeros and all that does is it tells us is the 2hour shift on the left covered by the daily shift that runs vertically so if the the the overall shift covers that two hour period we'll put a one if it does not we'll put a zero but we'll type all that in here in a in a 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 labor 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 uh labor cost per day in total so the average cost per customer service rep and the number number of reps On Any Given shift of course will add up to be our total labor cost for the day okay so the next thing we're going to do is we are going to Define names for certain cell regions now Excel has a 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 C8 through E8 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 region 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 so I'm 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 uh drop- down box uh in 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 going to go ahead and hit okay now that row of five cells C5 to G5 we can now refer to that row using cost per shift in any formula or anything like that and you'll 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 BL column on the very right I will highlight that column right click select defined name I'm going to call this minimum needed minimum needed and I'll leave scope to workbook and click okay 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 just 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 toine name we're g to call that Shift Works time period Shift Works time period go ahead and click okay now on the bottom right which is the one in the end we're going to be most concerned about our total cost we're going to rightclick 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 and luckily we only only have to enter two because we can actually use the use the autofill feature so NH h8 which is the first cell in the total working column we're going to enter a formula and it 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 it will 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 uh 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 parenthesis 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 those going to do is it will take the shift covers 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 2hour period from 6:00 a.m. to 8:00 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 zero now the cool thing is we can just since we 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 G10 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 and that's going to be our total cost of labor per day and that would be equals again sum product now what do you think this what's going to go here what row in 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 SE select the tag there and of course we need to multiply that by the number of csrs that are working if you're remember that is our blue at the very bottom here so we're going to multiply cost per shift by the number of CSR is working so number working select that close parenthesis 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 going to go ahead and enter our shift data in the top row so the first shift was $170 per CSR the next one was 160 per CSR 175 180 and 195 okay now let's 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 we'll go over to the minimum csrs 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 to our period so we had 48 79 65 87 64 73 82 43 52 and 15 so what we're telling Excel is that for example from 8:00 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 shift covers time period and remember this is either one or zero one being yes zero being no and the question is does this does the shift column we're in cover the 2hour period to the left so the 6:00 a.m. to 2: p.m. well that that covers 6:00 a.m. to 8:00 a.m. same thing for the next one the next one and the next one and then that shift is over for the day so the rest are all zeros okay so we're the same thing for the 8 to four 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 the evening and overnight so you're smart you can see how this works I'm just 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 uh solver Magic on track here to solve our problem now I do want to make sure you have the solver add then installed or uh 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 addins and at the bottom of the addins screen you'll see where it says manage Excel add-ins so you go ahead and click go and then an addin box will pop up now I have many other statistical addins in here but you want to make sure at the very bottom you see it will say set solver addin make sure that's checked of course I have mine checked if yours is not go ahead and check it and click okay all right now our solver addin 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 so 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's a set objective remember our objective here is the lowest possible labor cost so our objective is sort of our Target in previous versions of excel it was called Target cell but that's the one we want to uh 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 Min for minimum okay now below that we have by changing variable cells now what we have to do here is tell Excel what do 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 in the bottom of our spreadsheet to make our cost the lowest so if you remember we named that light blue row number working and that's that now we do have to add a couple of constraints or in I guess 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 because remember we cannot go below the number of csrs 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'll go and click okay now there are two more things we have to 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 and basically all that means is that in this sorder of problem we cannot have negative people on shift which should make sense we can either have zero 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 and basically that just 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 solver found a solution as it tells us and we can keep solver solution or we can restore what we had before but we're going to go ahead and keep the solver solution go and click okay now let's look at what this this did here in one click it's it still amazes me every time I do it in one click of a button Excel was able to tell us exactly how many csrs to have on shift on each shift that one met our minimum requirements of csrs needed and two at the lowest possible cost so if we look down at the blue row at the bottom Excel figured that out Forest so it's telling us that we need 48 csrs on shift from 6 to 2 31 on shift from 8 to 4: 39 on shift from noon to 8 43 on shift from 4 to midnight and 15 on shift from from 10:00 p.m. to 6:00 a.m. now if we 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:00 a.m. to 8:00 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 on duty at that time it's the 6 2 p.m. shift Excel told us we need 48 so 48 * 1 is 48 okay now in terms of the 8 to 10 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 at8 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 toour 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 ing 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 that'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 you will we will have to cover you know CSR lunches during that time period as well so that should make intuitive sense anyway okay 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 $3,610 and that $3,610 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 past data current wages and use Excel to tell us exactly how many we should have for shift great hope you enjoyed it