I need to extrapolate my inventory at the item level into 4-week buckets between 0 and 52 weeks. I have about 4500 unique lines as follows:
Item Unit Weekly
Code Description Cases Cost Mvmnt WOH Bucket
595384 PREMIUM FRYING OIL 500 $17.92 50 10 8-12 wks
546760 DARK BROWN SUGAR 650 $11.81 10 65 >1 year
598456 STEAM CORN 330 $15.42 15 22 20-24 wks
532943 CHC SEMI SWEET 240 $34.13 80 3 <4 wks
The first item moves 50 cases a week, so the 500 cases in inventory represents 10 weeks on hand (WOH) and it falls into the 8-12 wks bucket. I want to show where this inventory falls, but it is inaccurate to say 500 cases of PREMIUM FRYING OIl fall into the 8-12 wks bucket. In reality 200 cases fall into <4 wks, 200 cases fall into 4-8 wks, and the remaining 100 cases fall into the 8-12 wks bucket. So I want to convert the first line into the following:
Item Unit Weekly
Code Description Cases Cost Mvmnt WOH Bucket
595384 PREMIUM FRYING OIL 200 $17.92 50 10 <4 wks
595384 PREMIUM FRYING OIL 200 $17.92 50 10 4-8 wks
595384 PREMIUM FRYING OIL 100 $17.92 50 10 8-12 wks
...and repeat this for each item. Ultimately I want to turn this into a pivot table to sum all cases by bucket.
What I actually did was create 14 columns at the end of this data, one column for each 4-week bucket and wrote up a formulas to create running subtraction. This worked, but is time consuming and difficult to repeat, hence I am looking for a different solution. Please let me know if I need to clarify anything better.