+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (http://www.weberp.org/forum/forumdisplay.php?fid=1)
+--- Forum: Development Discussion & Specification (http://www.weberp.org/forum/forumdisplay.php?fid=10)
+--- Thread: Optimization of stock for retail businesses (/showthread.php?tid=2)
Optimization of stock for retail businesses - PakRicard - 01-14-2012
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>
Tim said:
t seems to me that a pre-requisite for this is that the locations be
organised in a hierarchy, so that the retail outlets are child
locations of the warehouses. Each location can calculate a speed of
throughput based on a user defined time period, and its parent is
responsible for ensuring that the quantity on hand at the child is
sufficient to meet this speed.
Then each location would be responsible for the quantity on hand not
just of itself but of its child locations, and can easily calculate
any movements that need to be done from a child location back to its
parent, and then back down to another child location.
Does this make sense? Its late and I may not be describing it very well.
Ricard said:
If I understood properly:
Yes, it makes sense but we don't live in a perfect world, so:
1) supplier is not able to finish and deliver PO's on time
2) sales are higher than expected for any impredictable reason (as Shakira wearing a yellow skirt on a new clip on MTV, next week, sales of yellow skirts spike ) and run out in some locations but not in other locations.
3) lack of funds to keep a big enough stock
4) error forecasting purchases
The scenario I understood from your email it's 99% covered by StockDispatch (you can select the parent location as "from" and the child as "to"), MRP module and the inventory planning reports. Inventory planning does have a kind of "speed of sales" calculated as the average needed in X months.
When you have enough stock at warehouse, reorder level works perfect.
When you don't have enough at warehouse is when you need to take from one child to feed another one. One child might have 5 pcs and his sibling none. So sibling needs some stock from his brother. Father location is not able to feed the child for any reason, so one child needs to rely on his brothers to get it.
The only problem then is how to define:
1) With N locations with enough stock and 1 without, where should I take the stock from?
2) And how much?
Question 1) could be solved as "the location with more stock" or "the location with some stock but slower sales during the last X days"
Question 2) is always tricky... Half/half? Take it all out from the slow place and move it to the fast selling place?
Maybe you just sparked the light with the idea of father/sons locations. Father has nothing to do in this scenario (is unable to feed the child locations should be the only check) and then just cross checking sibling locations (in pairs)
I'll prepare some psuedo code and publish it, maybe will help.
Tim said:
I think I was right and didn't express things very well. What I had in
mind was something like this:
X
A----------|---------B
| |
-------|------ --------|--------
| | | |
1 2 3 4
Where A and B are your warehouses and 1,2,3,4 are retail outlets, and
A feeds 1 and 2 and B feeds 3 and 4.
Now if the stock of part ZZ is as follows:
location A = 10
location 1 = 2
location 2 = 15
then A sees that it has a total stock of 27 to allocate amongst itself
and its child locations. Now how it allocates is going to depend on
the cost of moving stock and the frequency with which you can move it
around. If it can be done easily then I would suggest half and half,
otherwise it could be done on a ratio of the speed of throughput.
Remember this speed of throughput could be recalculated daily or even
hourly if you wanted.
You could also have a level above called X which is responsible for
feeding A and B, and the same principle works here.