How to optimize stock distribution in a retail chain?


We need a script to help us fix the problem of retail balancing stock between locations.

Our retail business (I guess as many many others) have some locations used as distribution hubs (warehouse) and some locations used as retail points of sale (shops). A warehouse serves to N shops

Each shop location has its minimal stock defined by the reorder level values.

Daily stock is shipped to the shops from the warehouse by StockDispatch.php script, shipping the "needed goods" to each shop. Needed = RL - QOH * Dispatch percent.

We run this system daily as our stock is expensive (silver jewellery), and keeping it centralized allows us to run the business with lower stock levels. Internal shipping costs are way cheaper than stocking costs.

This procedure works OK if the warehouse has enough goods to be distributed at all the shops (stock at warehouse bigger than sum of needed stock at the shops).

Big problem occurs when stock at warehouse is not enough to cover the demand from the shops. We might be facing some scenarios:

1) No stock at warehouse and some shops have more stock than RL and some others less than RL.

After receiving a model from supplier at the warehouse, it is distributed next day to all the shops. After some days or weeks it is getting sold, re-distributed daily from warehouse until you run out in warehouse. Maybe in shop A have 0 stock and in shop B have 3 pcs (because of lower sales performance).

It would be really cool for webERP to recognize this scenario and prepare a transfer from shop B to shop A of "some" pieces.

2) No stock at warehouse and some stock left at some shops.

webERP should check past record of sales and transfer the goods to the best selling point for that model. Logic behind this is: The shop with remaining stock is the one with lower sales performance, stock will have a better chance to be sold in a good selling location, but it is not sold because is not properly placed.

Doing this stock balancing by hand with one thousand stockid and 10 shops is not practical. So a script is needed to do all this tasks automatically.

This is just v 1.00 of specs. Any other retailer or logistic expert with different ideas is highly welcome!


<Phil>
These are common problems to many businesses...

Interesting you suggest using reorder levels... these can quickly get out of date. Initially they may be based on estimates of the retail location sales, but these could exceed or be significantly less. The only real data that you have is historical ... which is useful in a mature market but useless in a new market. I really don't see much substitute for manual analysis for very regular review of reorder levels.

We could write a script to update reorder levels based on history of consumption at a location. But the consumption at a location is affected by stock outs - i.e. it is impossible to consume more stock than is on hand.

It would be easy to prepare stock transfers based on reorder levels that warehouse people could pick and pack for dispatch.

I would also be interested some APECs logistics guru analysis and recommendations.
</Phil>

<Ricard>
We use RL for everyday shipping. Shops should have a "minimum" stock, so RL fits our needs 100%. Sure for other businesses where shipping costs are higher than stocking costs, RL might not be so useful. Just a note: Stock Dispatch allows to add a % of overstock, so if there is extra QOH at warehouse can be shipped in excess of RL.

About RL getting out of date: You are absolutely true, but we found it is the best way to manintain stock levels. One of the primary tasks of head office is maintaining RL at its best level. Nowadays is done manually except some SQL queries run directly against the DB until we get an standard way...

Automatic update of RL will work on mature businesses where you have a constant demand. For seasonal / fashion /perishable goods it is quite different. We should be able to compute some kind of "speed of sales", taking into consideration if the goods were (or not) available for sale, more than consumption itself. More like "When we had it available, we sold X units/day" kind of measure. Obviosly for new items it must be adjusted manually.

Maybe I did not understand properly but when Phil says: "It would be easy to prepare stock transfers based on reorder levels that warehouse people could pick and pack for dispatch. " I think it is already done at StockDispatch.php. That's what we do on a daily basis for every point of sale, top-up stock up to RL. Am I missing something here?
</Ricard>
Valid XHTML :: Valid CSS: :: Powered by WikkaWikiGet webERP Accounting & Business Management at SourceForge.net. Fast, secure and Free Open Source software downloads