Hi,
I've got what I think is an interesting puzzle. Straightforward until you start digging into it.
One of our components is a piece cut from one of two stocked lengths, which is proving uneconomical.
So we're talking about one of two options - A) increase the number of stocked sizes to reduce the wastage on each piece (expensive option because it increases stock holdings). Or B) only stock one long size and keep the offcuts to go on subsequent widgets.
I've got one year's worth of historical data to play with. The mean length is 4.6m, with standard deviation of 1.3m, so you can see we get everything from 0.7m to 8.1m in a reasonably normal distribution. The wasteage for A) is easy to figure out. I'm trying to discover the optimal value for B), in terms of minimum wastage.
Just pairing the biggest and smallest values won't necessarily give an accurate result because I might cut one piece 8 months before its friend shows up. The 28-day moving average is pretty stable, 4.6+/-0.5m so I THINK it's ok to just match the biggest and smallest values for the sake of the exercise - but then I get pairs that add up to 9.9m in some cases which is more than my mean pair of 9.2m. Using 9.9m as the stocked length would then give 0.7m wastage on average, using 9.2m would leave all those long ones without enough friends. The thing is that sometimes we sell multiples of the same widget so it throws everything out of whack. I'm getting confused.
Has anyone ever looked at this sort of thing before? I'm trying to work out the optimum stock length in this scenario, as well as a way to calculate what the wastage would be. I can PM the raw data if you're interested in having a go.
This is an example of the data:
I've got what I think is an interesting puzzle. Straightforward until you start digging into it.
One of our components is a piece cut from one of two stocked lengths, which is proving uneconomical.
So we're talking about one of two options - A) increase the number of stocked sizes to reduce the wastage on each piece (expensive option because it increases stock holdings). Or B) only stock one long size and keep the offcuts to go on subsequent widgets.
I've got one year's worth of historical data to play with. The mean length is 4.6m, with standard deviation of 1.3m, so you can see we get everything from 0.7m to 8.1m in a reasonably normal distribution. The wasteage for A) is easy to figure out. I'm trying to discover the optimal value for B), in terms of minimum wastage.
Just pairing the biggest and smallest values won't necessarily give an accurate result because I might cut one piece 8 months before its friend shows up. The 28-day moving average is pretty stable, 4.6+/-0.5m so I THINK it's ok to just match the biggest and smallest values for the sake of the exercise - but then I get pairs that add up to 9.9m in some cases which is more than my mean pair of 9.2m. Using 9.9m as the stocked length would then give 0.7m wastage on average, using 9.2m would leave all those long ones without enough friends. The thing is that sometimes we sell multiples of the same widget so it throws everything out of whack. I'm getting confused.
Has anyone ever looked at this sort of thing before? I'm trying to work out the optimum stock length in this scenario, as well as a way to calculate what the wastage would be. I can PM the raw data if you're interested in having a go.
This is an example of the data:
Code:
DATE LENGTH
YYYYMMDD (mm)
20110615 4670
20110616 4670
20110616 4670
20110617 4670
20110617 5980
20110617 5980
20110617 5980
20110620 5980
20110620 4630
20110620 4630
20110621 4630
20110621 3770
20110622 3770
20110622 3770
20110622 3770
20110623 3770
20110623 3770
20110623 5160
20110623 5160
20110624 5160
20110624 5160
20110624 5160
20110624 5160
20110624 7180
20110624 7180
20110624 7180
20110624 5880
20110627 5880
20110628 5880
20110628 5130
20110628 5130
20110628 5130
20110629 5130
20110629 5130
20110629 5130
20110701 4330
20110701 4330
20110704 4330
20110704 4630
20110704 4630
20110704 4630
20110705 5280
20110705 5280
20110705 5280
20110705 5280
20110705 5280
20110705 5280
20110705 4130
20110706 4130
20110706 4130
20110706 6180
20110706 6180
20110706 6180
20110706 4130
20110707 4130
20110707 4130
20110708 5480
20110708 5480
20110711 5480
20110711 5180
20110711 5180
20110711 5180
20110711 6180
20110712 6180
20110712 6180
20110714 4130
