Determining the worst turn-around times for repairs

[Dreamlax]

Hi,

I work for Toshiba fixing laptops, and each month we run this report which creates a list of every job we've done, how long it took, etc.

I've already created an Excel VBA app to munch the data into something more useful than a list of jobs, with graphs and other more relevant representations of that list.

Something I want to have calculated automatically is the "slowest model to turn around". By slowest, I mean slowest in terms of how many jobs there are. For example, if we only fixed 1 particular model but it took 20 days to fix, that isn't as bad as say 5 of another model taking 19 days (on average).

To put it another way, say we only fixed four different models:

1. 10x Satellite A80, taking 15 days on average to repair
2. 5x Satellite A100, taking 5 days on average to repair
3. 3x Tecra M5, all turned around on the same day
4. 1x Tecra M7 taking 20 days to repair

There's no doubt that the Tecra M5 is the fastest model to turn around, and "technically speaking" the Tecra M7 was the slowest, except we would consider the Satellite A80 model to be the slowest because we haven't repaired enough M7s to make a fair judgement.

What I would like to know is, should I base the "worst" model on some sort of minimum repairs (perhaps, only consider the model as a possible worst if it makes up at least 5% of our total repairs), or whether there is a better way to rank "severity".

All thoughts welcome!

TIA.

[VBAhack]

See the PM I sent to you.

[eranga262154]

VBAhack, it is better to post your thought here. What I believe is that unless you don't have a real personal thoughts for someone else don't use PM. Then you get the maximum for your replay.

[eranga262154]

I think it is better to rank it depending the "severity". Since we used Satellite and Tecra difficult to determine that exactly how may days spend to repair it.

[jemidiah]

I know this is a few weeks old, but it's late and I need to do some math to relax after today.


So, you basically want a ranking function that takes in arguments such as the number of laptops that were repaired and the time it took to repair each one, and outputs a rank (or a number that could be used to rank the laptops).

My function would simply be Rank(# Repaired, Avg Time Repaired) = # Repaired * Avg Time Repaired, which would output the number of days that technicians worked on the laptop in that month. This would give you exactly which laptop model is costing you the most time each month, and I think it would account very well for mass recalls or really difficult-to-fix cases; that is, your ranking system would still work as I think it should (with mass recalls or really difficult laptops being at the top of the list)

Using your example, the laptops would be ranked like this:
A80 = worst with 10 * 15 days = 150 days of repair time
A100 = 2nd worst with 5 * 5 days = 25 days of repair time
M7 = 3rd worst with 1 * 20 days = 20 days of repair time
M5 = 4th worst (best) with 3 * 1 days = 3 days of repair time


Now, you could get fancier than this. You might want to take into account for some statistic that certain model's repair time varies wildly while others are generally the same (this could indicated that some models have lots of possible problems while others have only a few problems [perhaps ones that could be fixed en-masse more cost effectively on new laptops]). This statistic would probably make use of the standard deviation of each laptop (I won't get into how you calculate it or what it means; the internet has plenty on that).

In this case, you could rank the laptops according to their standard deviations. The function would be Rank({Repair Times}) = StdDev({Repair Times}) [{Repair Times} = the set of repair times].

Like I said there are lots, and lots, and lots of ways to rank your data, but I think my first suggestion is the most simple, straightforward, and useful.

[eranga262154]

I think he gives that the average time taken for all the items. Son, for the first case 15days averaging time for 10 items, isn't it?