Gee, math sure is hard. Help me use it to predict the future.

April 27, 2009 4:30 PM

I work for a company that sells professional sports souvenirs (e.g. Lakers, Dodgers, Raiders products). I'm trying to come up with a relatively simple formula to help me compute minimum reorder levels for each product/team.

That is, instead of having to wade through an inventory report of thousands of items every week, I'd like to see a report of just the items that, according to my magic formula, I should consider reordering.

Here's the info I have readily available to figure into the formula:
1. qty sold in the past x and or y months. Since we would sell less of a baseball item when it's not baseball season, and more when it *is* baseball season, factoring this in will help decrease the number of items I need to look at during any given week

2. qty sold last year. This gives me a rough sense of how popular a particular item/team is over an entire season.

3. lead time. Some items take 4-5 weeks to produce and bring in. Other items might take longer.

There may be other pieces of info available, but these are the main ones I look at when I manually decide what and when to order.

One big piece of info that might greatly help, which I don't really have at the moment, is forecasting. We really don't have any sort of projections from our customers and sales reps on what might be popular in the coming months.

I'm open to any and all suggestions on how to wrap my head around this.

posted by edjusted to computers & internet (8 comments total)

Interesting. You've got n time dependent vectors, say (x_1(t), y_1(t), z_1(t)), ..., (x_n(t), y_n(t), z_n(t)). If (x_i(t), y_i(t), z_i(t)) is in a certain range, display the number i. The range could be something like {x_i(t) > 2 OR y_i(t) > 3 OR z_i(t) > 4}. You could replace OR by AND to get a smaller list.
posted by metastability at 7:42 PM on April 27

also, from your description is looks like y_i(t) and z_i(t) are not going to depend very heavily on t.
posted by metastability at 7:45 PM on April 27

edjusted, first I would suggest adding the following tags: forecasting, supplychain.

What you are encountering in the first half is a very commonly studied problem in statistics and machine learning. Ideally, you would like to build a function d = f(x,y,z) for each of your goods, where d is the expected demand for the product, and x, y, and z are your independent variables and could be things like the previous sales this month last year, the amount of money spent on advertising, and how well you're currently performing in sales this year vs last year. The simplest form of model you can build uses a Least Squares Fit.

Supply chain is probably more of a Controls problem, but I would suggest that you simply forecast your demand for as far out as you need to comfortably stay ahead of your ability to resupply. That means if one product can be restocked in 3 days, you don't worry about forecasting since you can easily resupply. If a product requires a month from order-to-restock, then you will need a bit more than a month's forecasting, so you can better predict sales.

Forecasting is pretty tricky, especially in your case where there are a lot of contributing factors. I would pay the most attention to what your sales were in this month last year and how well you're performing this year as opposed to last year.
posted by onalark at 7:26 AM on April 28

metastability: you overestimate my math/stat skills! :P Could you give me a rough idea in plain English of what you mean?

onalark: yes, forecasting would definitely be a big plus. Unfortunately, we currently don't have this info, so I'm limited to making educated guesses based on past history.


Here's roughly what I've come up with so far, but it's not well thought out. Something like take the average sales from the last x months, where x corresponds to lead time, and compare to current on-hand. If on-hand is less than average sales, then show up on report.

So far, I haven't figured out how to factor in last year's sales...or if that's even necessary. What I would do in the above example is to then manually look up what last year's sales is for the items that show up on the report and then use my own judgement on what to order.
posted by edjusted at 1:16 PM on April 28

It seems to me the difficult component of this is the fact there are spikes in the year (ie in the first month of the baseball season the sale of caps is 5x what it was the previous month).

Another factor that's tricky is sizes. If your volumes are low enough that you can't just assume that the size mix is constant that's hard and I'm not sure what to do about that.

Lastly you don't mention whether there are benefits to ordering a little often (because freight is cheap and terms are bad) or whether for a given item it's better to order three months worth (because freight is expensive and terms are good). I'm going to assume that every item is able to be ordered economically every month - ie the first case.

Having said that this is what I'd do:

For each item in your inventory:

1. Generate the ratio of last months sales for this item to that the same month last year.
2. Get the total sold for this item in the forthcoming month last year (ie now-11 months ago).
3. Scale 2 by 1.
4. If 3 is less then stock level then order !

I have got an idea of how to deal with "freight is expensive and terms are good" but I'll only bother you with that if you think the above is worth considering.
posted by southof40 at 6:32 PM on April 28

At any time t, for every item i on the list, you've got three numbers x_i(t), y_i(t), z_i(t). Each week, or however often you do this, run a loop. "For i = 1 to n, If xyz satisfy the following inequalities x > a, or y >b, or z> c, then display i." Here a, b, and c are apriori functions of all values x_i(t'), y_i(t'), z_i(t') for all t' < t and all i. But for simplicity, just make a, b, and c constants at first. All this stuff about statistics, forecasting, etc, is about how to come up with more complicated expressions for a, b, and c. But since you've been doing this manually, you could probably just guess a, b, and c as constants.
posted by metastability at 7:20 AM on April 29

(as for how to guess for a,b and c, see other peoples' responses :P )
posted by metastability at 7:23 AM on April 29

@southof40: Ratios! Ok, great idea. I can weigh certain months more heavily than others to accounts for "in-season" vs "out of season" sales.

@metastability: Ok, that makes more sense now. Thanks for the clarification. That certainly helps!

Thanks everyone!
posted by edjusted at 11:08 AM on April 29