OPTIMIZING INVENTORY/TRANSPORTATION
bandrade1981
EBBDImportsData.xlsxIMPORTED BEERS DATA
| Markup % | 140% | Forecast Demand | 4.0% | Annual Inventory Holding Cost Rate | |||||||||
| Actual Last Qtr | Next 4 Quarters | ||||||||||||
| Product Code | Cost | Price | Ending Inventory | 1 | 2 | 3 | 4 | ||||||
| 7979 | $ 10.49 | $ 14.69 | 30 | 100 | 120 | 125 | 130 | ||||||
| 6786 | $ 11.59 | $ 16.23 | 25 | 90 | 95 | 102 | 108 | ||||||
| 2389 | $ 12.45 | $ 17.43 | 20 | 85 | 90 | 90 | 85 | ||||||
| 5453 | $ 11.43 | $ 16.00 | 12 | 66 | 80 | 90 | 90 | ||||||
| 9327 | $ 13.49 | $ 18.89 | 15 | 77 | 78 | 80 | 80 | ||||||
| 9134 | $ 16.22 | $ 22.71 | 8 | 105 | 95 | 100 | 105 | ||||||
| 4276 | $ 9.88 | $ 13.83 | 10 | 60 | 70 | 75 | 75 | ||||||
| 5532 | $ 10.43 | $ 14.60 | 28 | 110 | 120 | 125 | 125 | ||||||
| 7612 | $ 12.28 | $ 17.19 | 22 | 130 | 130 | 130 | 140 | ||||||
| 5583 | $ 15.73 | $ 22.02 | 5 | 50 | 60 | 70 | 80 | ||||||
| 175 | |||||||||||||
| Inflation | 100% | 102% | 104% | 97% | 102% | ||||||||
The actual data from the end of the last quarter is shown with the costs and prices - Imported Beers in Kegs.
We plan our pricing by using a markup factor of 1.4 or 140%
This is our forecast of quarterly sales of the Imported Beer line for Kegs. And this is the forecast of inflation factors for the next four quarters. The inflation for the last quarter is shown as 100% as a basis for the next four quarters. Note that we expect the usual seasonal cost reduction three quaters hence.
The total number of kegs of Imported Beer in ending inventory is 175. Our capacity for Kegs is 190 for Imported Beers. The annual inventory holding cost rate is 4%.
