[Guest]

A puzzle from real life that I'm hoping the math minded will be able to solve for me...

We track and rank our ablity to move a product out as quickly as possible. Because of several factors the time frame involved can vary by several days and almost always there is at least some number of "aged" units.

What we have been doing is tracking by a weighted average to see which department has been able to move through their processes better. (A lower number is better then a higher one) So if on the first day I had 10 total units waiting to be distributed and 8 of them are 5 days old and 2 are 2 days old, my score for that day would be 4.4. We continue in manner all month long, mantaining the overall weighted average for the month.

The problem I have is, if a department is able to completely clear their backlog (something that we're striving for!) then they do not get credit for this. 0 inventory at 0 days old does nothing to affect the running total as we're currently calulating it.

How can I craft a formula or system that fairly rewards a department for getting to a zero backlog?

[curr3nt]

I get the 4.4 for the day.

How are you averaging for the month that doesn't take the zero into consideration?

Given the following daily values what would your average be for the week?

M - 4.4

T - 5.2

W - 2.8

T - 0

F - 1.5

I get 2.78. The 0 is credited as it helped bring the overall average lower.

[Guest]

I get the 4.4 for the day.

How are you averaging for the month that doesn't take the zero into consideration?

Given the following daily values what would your average be for the week?

M - 4.4

T - 5.2

W - 2.8

T - 0

F - 1.5

I get 2.78. The 0 is credited as it helped bring the overall average lower.

For example:

Units   Age   

7	10.43

14	6.21

17	3.82

15	5.07

13	6.23

10	8.50

9	8.22

10	7.30

7	4.00

3	5.00

14	5.29

13	4.23

13	4.69

7	5.14

This computes out to 5.81 (rounded). I get this by taking the product of each individual line and summing it (882.91) and dividing by the sum total of the units for the month (152).

If I add 0 units 0 age to the end of it, I still get 5.81.

[Guest]

Another item to consider would be to change the sample pool, similar to what curr3nt is suggesting. Using a weekly marker certainly accounts for the 0 by averaging it with the other numbers. If you have an average takt or cycle time for the product (say it takes three days to create), you could try using a sample pool of three. Thusly, monday's score would reflect a combined average score from friday, thursday, wednesday. This would help smooth the markers and provide (most likely) a better marker for how the department is doing by accounting for any special cause circumstance (say, two employees didn't show up thursday, so now friday's score is unnecessarily high).

On a side note, you should probably be using a weighted average formula for days in stock (assuming that days in stock are costing you extra in inventory, space, etc...) so perhaps being zero or one day in stock doesn't really hurt the company, being two-four days hurts a little, and five + days is just unnecessarily expensive. In this case, try assigning a scaled weighting such that 0-1 days = 1 score, 2-4 days = 3 score, 5+ days = 7 score (or however you decided to marker these for weights you feel appropriate). This kills two birds. First, assigning a score of 1 to a day with zero takes any problems your system has with zeroes out, and also "penalizes" the score more for locations that are taking way too long. Using this system, if 8 of them are 5 days old and 2 are 2 days old,you don't have a score of 4.4, you have a score now of 6.6. For a location with all cleared inventory, you'd have a score of 1. This "score" no longer reflects the total average days in inventory, but probable reflects better on the performance of the locations.

[Guest]

For example:

Units   Age   

7	10.43

14	6.21

17	3.82

15	5.07

13	6.23

10	8.50

9	8.22

10	7.30

7	4.00

3	5.00

14	5.29

13	4.23

13	4.69

7	5.14

This computes out to 5.81 (rounded). I get this by taking the product of each individual line and summing it (882.91) and dividing by the sum total of the units for the month (152).

If I add 0 units 0 age to the end of it, I still get 5.81.

I see your problem. So 5.81 equals the average days late of all the units that are left for the month. Is this figure really of any use to you? What you probably want to see is the average days late per department or per day. Let's assume that the values you show are the reported values at the end of each day. So 14 days had "leftover" units. Let's say there are 20 working days this month. So take the sum of the products 882.91 and divide by the number of working days 20 gives you an average score of 44.15. This means that the department has averaged 44.15 total days worth of product left in stock at the end of each day. Hope that makes sense. Operational definition are so so important. It is the number one downfall of metric reporting in business. All too often I see that people haven't defined what they really want to see, thus they are having trouble coming up with a report to quantify it. Hope it all goes well!

[Guest]

Unfortunately the inbound rate is not uniform either at a particular location (I can have 20 come in one day and 3 the next) nor across locations (A might get 20 today, 10 tomorrow but B will get 2 today and 24 tomorrow).

Since that's the case, I need to be able to deal with not only how long it is taking, but how many they are dealing with. I've been using weighted averages because that struck me as the best way to compare apples to apples across the board. Your point score system is intriguing, but I'm struggling to make it work for both the age and for the volume.

Example two: (note the "ages" are already weighted for the day.

A		B	

Units 	Age	Units 	Age

7	10.43	9	5.22

14	6.21	6	8.17

17	3.82	6	3.67

15	5.07	10	3.50

13	6.23	14	3.57

10	8.50	8	6.38

9	8.22	18	3.83

10	7.30	15	3.27

7	4.00	18	3.78

3	5.00	25	3.96


14	5.29	22	4.23

13	4.23	18	4.22

13	4.69	23	3.04

7	5.14	20	2.55

0	0.00	17	5.06

On the second to last day A was rated at 5.81 and B was rated at 3.91. On the last day, A was at 5.81 and B was at 4.00.

Edited February 28, 2011 by Rory

[curr3nt]

I concur with bekabeh. You need to be including work days into the equation to get a daily average for the month. However I would calculate it differently. I would take the daily averages of units and aging. ( Average units and aging mean would mean more to me then some total aging number. )

Either way you calculate it you need to reference how you calculated it. Nothing worse then someone coming up with a different number by calculating it differently and having to defend.

Units	 Age 	 Product

7	 10.43 	 73.01 

14	 6.21 	 86.94 

17	 3.82 	 64.94 

15	 5.07 	 76.05 

13	 6.23 	 80.99 

10	 8.50 	 85.00 

9	 8.22 	 73.98 

10	 7.30 	 73.00 

7	 4.00 	 28.00 

3	 5.00 	 15.00 

14	 5.29 	 74.06 

13	 4.23 	 54.99 

13	 4.69 	 60.97 

7	 5.14 	 35.98 

0	 -   	 -   

0	 -   	 -   

0	 -   	 -   

0	 -   	 -   

0	 -   	 -   

0	 -   	 -   


Averages

 7.6 	 4.21 	 31.97 

[Guest]

I see your problem. So 5.81 equals the average days late of all the units that are left for the month.

Not quite -- it equals the average days late over the course of the entire month.

I appreciate you taking the time to look at this problem with me.

[Guest]

make the minimium 1 vs 0 for both weight and age. so you're score will x+1. This gives everyday a base value.

[curr3nt]

Not quite -- it equals the average days late over the course of the entire month.

I appreciate you taking the time to look at this problem with me.

You can't account for zeros this way. To report average days late of product, product must exist.

If you want to report how a location is doing, you need an average of periods not product.

[Guest]

Not quite -- it equals the average days late over the course of the entire month.

I appreciate you taking the time to look at this problem with me.

Because you do not include the number of units that have a "0" day late, you are getting a false skew for days late. 5.81 is the average days late ONLY for the units that were in fact late. If you included the total number of units processed into your sum, then you would get the average days late for the month for ALL units. Does that make sense? Since you are only averaging the units that have days late, your 5.81 reflects 5.81 days late per late unit.

Another way to state this is if you have only 1 day with units late, and there was only 1 unit late by 8 days, your average days late would be 8. But if you shipped 99 other units on time, your true average days late would be 8/(99+1) = 0.08, not 8. See what I mean?

[Guest]

A               B

Units   Age     Units   Age

7       10.43   9       5.22 

14      6.21    6       8.17 

17      3.82    6       3.67 

15      5.07    10      3.50 

13      6.23    14      3.57 

10      8.50    8       6.38 

9       8.22    18      3.83 

10      7.30    15      3.27 

7       4.00    18      3.78 

3       5.00    25      3.96                         

14      5.29    22      4.23 

13      4.23    18      4.22 

13      4.69    23      3.04 

7       5.14    20      2.55 

0       0.00    17      5.06

using bekabehs method, we would have the following:

day	A		B		A wght  B wght  A total	B total	A score	B score

1	7	10.43	9	5.22	73.01	46.98	73.01	46.98	73.01	46.98

2	14	6.21	6	8.17	86.94	49.02	159.95	96	79.975	48

3	17	3.82	6	3.67	64.94	22.02	224.89	118.02	74.9633	39.34

4	15	5.07	10	3.5	76.05	35	300.94	153.02	75.235	38.255

5	13	6.23	14	3.57	80.99	49.98	381.93	203	76.386	40.6

6	10	8.5	8	6.38	85	51.04	466.93	254.04	77.821	42.34

7	9	8.22	18	3.83	73.98	68.94	540.91	322.98	77.272	46.14

8	10	7.3	15	3.27	73	49.05	613.91	372.03	76.738	46.503

9	7	4	18	3.78	28	68.04	641.91	440.07	71.323	48.896

10	3	5	25	3.96	15	99	656.91	539.07	65.691	53.907

11	14	5.29	22	4.23	74.06	93.06	730.97	632.13	66.451	57.466

12	13	4.23	18	4.22	54.99	75.96	785.96	708.09	65.496	59.007

13	13	4.69	23	3.04	60.97	69.92	846.93	778.01	65.148	59.846

14	7	5.14	20	2.55	35.98	51	882.91	829.01	63.065	59.215

15	0	0	17	5.06	0	86.02	882.91	915.03	58.860	61.002

here's an alternate suggestion. take the average number of units over the given time period, the average age over the given time period, and take the geometric mean of the two numbers.

(multiply and square root them)

for here this would give A a score of 7.539 and B a score of 8.099.

[Guest]

Another way to state this is if you have only 1 day with units late, and there was only 1 unit late by 8 days, your average days late would be 8. But if you shipped 99 other units on time, your true average days late would be 8/(99+1) = 0.08, not 8. See what I mean?

I agree 100%, unfortunately that's an argument that I've made and lost to TPTB. So Im stuck with tracking and weighting just the units that are late.

[Guest]

On a side note, you should probably be using a weighted average formula for days in stock (assuming that days in stock are costing you extra in inventory, space, etc...) so perhaps being zero or one day in stock doesn't really hurt the company, being two-four days hurts a little, and five + days is just unnecessarily expensive. In this case, try assigning a scaled weighting such that 0-1 days = 1 score, 2-4 days = 3 score, 5+ days = 7 score (or however you decided to marker these for weights you feel appropriate). This kills two birds. First, assigning a score of 1 to a day with zero takes any problems your system has with zeroes out, and also "penalizes" the score more for locations that are taking way too long. Using this system, if 8 of them are 5 days old and 2 are 2 days old,you don't have a score of 4.4, you have a score now of 6.6. For a location with all cleared inventory, you'd have a score of 1. This "score" no longer reflects the total average days in inventory, but probable reflects better on the performance of the locations.

To compensate for variation in the amount of product handled by location, this would wind up as being a score out of say 10, where you sum the scores of each given product and divide that sum by the number of products being dealt with, so it's a fair scale

[Guest]

I agree 100%, unfortunately that's an argument that I've made and lost to TPTB. So Im stuck with tracking and weighting just the units that are late.

Any idiot can see that it is impossible to account for units shipped on time by completely ignoring them. Unfortunately for you, TPTB aren't just any idiots, but are usually extremely stubborn and egotistical idiots who are incapable of believing they could be wrong. Luckily however, it is usually fairly easy to repackage whatever it is they hate so much, without them actually realizing that you've just suggested the exact same thing, only worded differently. It's even better if, instead of suggesting the actual idea, you steer the discussion in that direction, spoon-feed them all the pieces, and then wonder out loud "if only there were a way we could get x from y and z..." (where the relationship between x, y and z is so painfully obvious, one of them is bound to connect the dots). Once one of them has their great revelation, they'll figure out a catchy name for it (probably a three-letter acronym such as 'WIN' or 'FUN'), fabricate some statistics to show that it applies to every business situation since the dawn of history, get somebody to ghost-write a book about the decades of hard work they put into formulating it, and go on a lecture tour teaching it to MBA students the world over, becoming fabulously wealthy, but more importantly, leaving you alone to do your job.

In your case, I'm a little confused: what is it that you actually want to optimize? If the goal is to minimize inventory and the age of items in inventory, why not just average the number of items in stock at the end of every day? You can normalize it by the number of orders to get the average number of days to process an order. You can weight it to get the average age of items in inventory.

BTW, my consulting fee is $500/hr. You owe me $41.67.