Hi, can anyone recommend a forecasting tool that we can use to help us maintain inventory levels based on sales trends? We would like to be able to export our I/C sales data into something that will take into account sales history of an item to predict when it will run out, when it would need to be ordered based on lead time, etc. We have over 950 items, and we order from small fair trade partners in India & Nepal, so lead time is substantial, and we receive a limited number of container shipments per year. Right now, by the time we realize that sales of an item are up 15%, we may already have missed the chance to increase our order, and we will run into an out-of-stock situation, resulting in lost sales. Does anyone know of a forecasting tool to help us analyze our item sales data to better predict stock levels?

Hi Kristin:
Do you own the Adagio Gridview module? I don't know of dedicated forecasting tools (though I'm sure they exist) however I don't think you need one. And getting the information into such a tool would take a bit of work.

With Gridview you could build a report that would should show each item as a row. You could then define columns to show sales. The columns could be monthly sales figures or quarterly. Other columns would be "On Hand", "On Sales Order", "On P/O" and "Days Lead Time" (the latter could be assigned to each item using an optional field). You could then add columns that show calculations such as "Days of stock remaining", "% Change" and so on. Or you could export to Excel and apply more complex calculations there.

The starting point is that you need to have a calculation (or set of calculations) that you use to determine order quantities and timing. Once you have those in place, then you can systemize them into GridView and Excel.

Thanks Bruce, we don't have Gridview, but Carol is looking into whether we should purchase it for this, or if we can get what we need from Crystal Reports, which we do have.

Isn't this a classic EOQ problem right out of Finance 101?

The classic EOQ (Economic Order Quantity) model assumes constant demand, but you can change your demand figures each time you run the model. You provide the model with your inventory holding costs (usually just your cost of capital unless you rent varying amounts of warehouse space as your stock levels vary, your fixed costs (typically your shipping and handling costs, your cost of preparing the order including arranging for and sending wire transfers, etc.) any quantity discounts that you can take at different order quantities.

I vaguely remember from MBA school, variations on the classical OEQ model that included both variable demand and an assumed stock-out cost. All this should be readily available with a Google search.

Combine this with what Bruce has already suggested and you should have some interesting approaches to your order quantity issue.