Utilization, Efficiency, Turnaround & SLA KPI's - calculate hours in a month by 7.5hrs

Hi

Im trying to create a set of new Utilization, Efficiency, Turnaround Time & SLA KPI's for our helpdesk using excel "2010" for weekly and monthly, but I cant seem to get the formula correct in that excel to automatically calculate the numbers, days, time and percentages etc.

For this example "Efficiency KPI"... I have member of staff who works 7.5 hours a day - 5 days a week - 20 days/4weeks a month unless its a 5 week month minus Holidays, B Holidays & any Sickness so I may need some cells for these headings.

On this example my Efficiency calculation is:-
Weekly Calc - Total number of hours spent on tickets / Total Available Hours Per Week = % (for that member of staff)
Monthly Calc - Total number of hours spent on tickets / Total number of workable hours in that month (each month will be different) = %

I need to automatically calculation this for each week but some months have 5 weeks, then to calculate all the weeks giving the percentage for the month.


I have attached what Ive do so far as im sure my explanation may not be that clear, any help would be really appreciated.


thanks in advanced

Hi 6StringJazzer

Thanks for the reply, being totally blunt I have no idea what I need to do or should be doing with formulas... I just know what results I'm trying to achieve although I'm always willing to learn and give anything a go, hopefully my answers will help..



Apparently you are using fiscal months that are exactly 4 weeks or 5 weeks:- I need to use the weeks and months for reporting figures of Utilization, Labour Efficiencies and SLA's etc.

This will have no relationship to the number of days in a month you have calculated starting in Efficiency!C5. What is the rule for determining what week falls in what month:- re-looking at the worksheet i don't think i have one, is this my starting point?

What day of the week does your reporting week start on? (I have seen this before where, for example, the Monday of a week determines what month the week falls in for accounting purposes.):- We work Monday to Friday so it should start on a Monday.

You need a list of holidays someplace:- i currently have another spreadsheet with holidays called IT-Holidays but i am happy to use another TAB within the same worksheet.

What is a B Holiday:- Bank Holiday.

Your data layout is not friendly for calculations. You should lay out all the weeks in the same 5 columns, or all in the same 5 rows, but not in blocks like that:- I did this so i could print out the figures on one/two pages "landscape" but i am happy to move to rows of 5 weeks if it will make it work. Shall i move all the weeks on all the tabs to rows of 5, add sickness, holiday and bank holidays then upload again?


Many Thanks
T

Hi 6StringJazzer

Thank you so much, after spending nearly an hour trying to work out what's doing what, how and why... I think I understand what you've done and why you've done it. well in my way

Excel needs a starting point to calculate from hence [="1/1/2015"+8-WEEKDAY("1/1/2015",11)] in cell in C11 and [=MONTH(C11)] in D11. im not sure what the code actually means but I'm guessing its saying this is the first day of the first week in that month therefore its the first week of the month and then works out the rest from this point knowing which week is in which month.

Do I just need to do the same format for the rest of the TABS and then just use my formulas to do my Utilization, SLA calculation?

One thing im not sure of, is how it including sickness, bank holidays or normal holiday?



Once again thanks for your help.

T

In going through this explanation I realized there is a bug. If the January 1 falls on a Monday, my original formula will give January 8 as the answer. The corrected formula is

="1/1/2015"+7-WEEKDAY("1/1/2015", 12)

This gives you the date of the first Monday on or after 1/1/2015. In this case, the purpose is to determine the first Monday of the year, which starts the first week of the year.

The WEEKDAY function returns a number giving the day of the week of the date given in the first argument. The second argument is an optional code that determines how Excel numbers the days. The code 12 means that Tuesday is numbered 1, continuing until Sunday is 6 and Monday is 7.

You can use the same model to make changes in your other sheets.

As for sick hours, where are you getting the data for "number of hours spent on tickets"? It seems that this has to be actual hours counted somehow, and it should automatically reflect any absences due to illness. Your Total Hours Available each week is just typed in, so that would have to be adjusted manually for sick time. Because holidays are the same for everybody (what's the difference between a bank holiday and a normal holiday? In the U.S., we just have the one flavor ), you could have a central list of holidays that can be used to calculate the available hours each week.

Is there one file per person?

Hi,

I will apply the new code, thanks. I only mention holidays and bank holidays because in the UK our guys always have bank holidays off, but normal holiday some guys keep them until the end of the year and get paid if they done use them so in theory a member of staff could only have bank holidays off. Just wanted to mention it in case it made a difference on the calculation code, it maybe that the excel calculation does not differ and just minuses x number of hours regardless from the working week i.e 1 day off = 30hrs that week.

We only have a small team of 2 full time and 1 part time. so yes I guess this means there is one kpi/file per person unless you can recommend another way of doing it.


Thanks
T

Hi 6StringJazzer

I've been trying to understand the calculations a bit more and I have a question about the number of available hours in a month. Within the Efficiency Data TAB, cell C5 the total number of available hours = 150 (20 days at 7,5)... but when I check the calendar for Jan 2015, there are 22 days at 7.5hrs per day totalling 165 available hrs.

Is there a way to get excel to calculate the actual number of working hours available for that week/month regardless of the day it starts on? i.e. excel calculates and knows that Jan 2015 starts on Thursday 1st and finishes on Sat 31st and works out that there are only 22 working days (Mon-Fri) at 7.5hrs per day totalling 165 available hrs.


Thanks
T

You need to decide if you want to do this by the week or by the month. Based on your description, I set it up to define a "month" as either 4 or 5 full weeks. I thought that was what you needed based on some kind of fiscal month. If you want to go by the calendar month, it can work that way too, but then there is no point in having a whole list of 52 weeks. You calculate working days in a month by using function NETWORKDAYS (look it up in Help), which will also take into account holidays if you provide a list.

Hi 6StringJazzer

I need both, weeks and month but I don't know how calculate it in excel. As an example for Labour Efficiency and to summarise what I need is... I would like to see how Efficient a member of staff has been within there working week. this is the same for Utilization and so on.

Based on 7.5hrs a day * 5 days a week - for the number of days in that month (i.e. the first week may only be a 2 day week), then to add up all the days or weeks to give a total percentage for that month.


Example:
So in Jan 2015 (total of 31 actual days but only 22 working days based on Mon-Fri - 4 weeks and 2days)... Fred Blogs works 11 days in Jan and is 50% Efficient, the second 11 days he takes as holiday so week1 = 50% Efficiency... week2 = 50% Efficiency... week3 = 0% Efficiency... week4 = 0% Efficiency... ----- so Fred Blogs Total Efficiency for Month1 = 50%


Hope this makes sense, it may be that ive not asked what I need in the right way and you feels I need to change my structure to achieve what im looking for? i.e. hours rather than weeks?


Thanks
T

Hi 6StringJazzer

Can this be done?


Thanks
T

Originally Posted by tdustan

I need both, weeks and month...

I am starting to think that you started solving this problem from the middle instead of from the beginning. I understand what results you need, but I don't understand how you will be collecting and recording the raw data of how many hours are spent on tickets, and the number of tickets resolved (I don't know what an "achievable" ticket is). The only way that makes sense to do this if you need to report by week and by month is to record this data by day. That's the very first thing to figure out--how are you going to collect this data, how are you going to record it.

Hi

We use sysaid IT helpdesk, this records how long we spend on the ticket also it gives me the total number of tickets assigned to a particular administrator, and how many thickets the administrator closes in the ay, week or month.

I aim to run manual and scheduled reports on a weekly basis telling me how many tickets that person has closed, total time spent on the closed tickets then to manually input the data into excel (weekly) then excel add's up all the weeks and gives me the figures for the month.

Hope this help?

Thanks
T

I've got to believe that SysAid must do some of this for you if it's worth anything, but I am completely unfamiliar with the tool. In any case, solving this problem is going to require an overhaul of your data layout and I'm afraid I can't take the time for that kind of project.

Also, there is something broken about your image attachment. I don't see anything.

Hi,

I guess there's a lot of work to be done and its a bit complicated... In my opinion unfortunately sysaid is not great for KPI's, SLA's or Reporting unless im willing to learn it like an employee or pay them to set it up for me, by default its not configured the way I need it to be used with my KPI's which is why I really needed the excel file.

Oh well thanks for taking the time to have a look.

T