This spreadsheet allows you to perform forecasting, replenishment, and investment analysis on up to100 items at a time by loading your data into a single worksheet. Areas of analysis include: • Differentiating types of usage.
• For items with recurring usage, identify the best forecast formula from among nine methods preloaded into the spreadsheet; calculate the forecast and suggested replenishment parameters. • For items with sporadic usage, calculate normal quantity sold/used in one transaction as well as minimum and maximum quantities. • Compare your current inventory value, turnover and return on investment to potential “ideal” values. You can then see how varying safety stock and other parameters will affect your service level and inventory investment. • Employ user maintained controls to smooth out unusually high or low usage. • Graphically review the history and suggested replenishment parameters of a specific item comparing the results of all 11 forecasting methods. • Compare the current and potential values of these key metrics: • Inventory turnover
• Turn/earn index
• Gross margin return on investment
• Adjusted gross margin (i.e., gross margin considering your average inventory investment) • Percentage of excess inventory
• Planned excess (what vendors force you to buy in excess of what you need). Spreadsheet 2: Price Break (Item)
If a vendor offers you a lower price per piece for a larger...