Formula to Equalize Output Based on Time of Service

jconkl02

Board Regular
Joined
May 25, 2016
Messages
55
I'm trying to figure a way to measure the output of our engineers. The theory being that a person who has been here for a few weeks should not be expected to complete as many tickets as a person who has been here for a year. The way I came up with was to use a percentage to measure how long they have been here. I'd like to use that percentage as a way to add weight to the tickets. Does this make sense at all? Here is my table. I'm looking for a formula or method to use the percentage and the number of tickets to equalize output so that all engineers can be measured on a level playing field.

Daily Ticket History Autosaved.xlsm
DEFGH
27NameTotal Days%Number of TicketsWeight of Ticket
28Mickey Mouse39386.18%47
29Minnie Mouse378.11%0
30Donald Duck456100.00%51
31Daisy Duck71.54%0
32Huey, Duck15634.21%17
33Dewey Duck456100.00%104
34Louie Duck42593.20%73
35Scrooge McDuck33673.68%48
36Goofy19542.76%78
37Pluto15634.21%36
38Oswald the Lucky Rabbit24754.17%10
39Ortensia24754.17%68
40Clarice19843.42%112
41Pete15634.21%9
42Clarabelle Cow456100.00%56
NAMES
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
This type of approach is a linear one. It assumes that time on the job should translate into an ability to handle a linearly increasing number of tickets. Many "learning curves" do not follow that model. Often a new person requires some time to learn the system, and their production is relatively low. Then once the system is understood, the individual can begin to draw on their experience to handle more tickets. And finally, after being exposed to a great number of issues, the individual should have developed sufficient expertise to handle nearly all issues efficiently. This suggests that a non-linear model might be worth considering, or perhaps a tiered model where new arrivals are expected to perform within some limits, mid-level engineers with some higher limits, and senior engineers at the highest levels.

I don't understand the idea behind "weight of ticket". My first thought is that the weight of a ticket should relate to the difficulty that a reasonably competent person would have handling the ticket. The difficulty might be due to the scope or complexity of the issue, so some system for assigning a "weight" or difficulty score may be appropriate. Or are all tickets of equal complexity and scope? For example, in your table, is Clarice a gifted employee who outperforms everyone even though she has been there for less than half the time of the most senior employees, or does she take all of the easy tickets? Conversely, is Donald Duck so good that he handles all of the most difficult tickets that require substantial effort--consequently he handles only half as many tickets as some of his colleagues--or is he a bad employee who wastes a lot of time?

These questions illustrate a potential issue with normalizing everyone based on time on the job of the longest tenured employee. If Donald Duck, Dewey Duck, and Clarabelle Cow all left to take jobs elsewhere, the tenure percentage for everyone remaining changes dramatically, yet they haven't magically gained any greater expertise or capacity to handle more tickets. To me, it seems that two things need to be considered: 1) establishment of performance tiers based on experience, and 2) establishment of a ticket scoring system--a weighting system--that reflects the scope and complexity involved (unless all tickets are considered equal). Then the "performance expected" of an employee at some level would be based on a composite score involving the number of tickets and the ticket "weight". One idea for that would be to establish the composite score as the sum of the products of the number of tickets and the ticket weights, so if Mickey Mouse's 47 tickets consist of 35 tickets that are routine and easy (weight factor of 1), 10 tickets that are more involved (weight factor of 2), and 2 tickets that require substantial effort due to their complexity (weight factor of 3), the composite score might be 35*1 + 10*2 + 2*3 = 61. And based on analysis of a large number of employees, their skill levels, and their past performance, you might determine that someone like Mickey with 393 days on the job should be performing within Tier III composite score limits of 60-80. Here I'm assuming that all entry-level employees would fall within Tier I, and mid-level employees should operate at Tier II and transition into Tier III after some time, before becoming fully qualified (expert) at Tier IV...and each tier would be defined by composite score ranges.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top