Help with stock management

by quitelekker. Posted on Sep 10, 2020    3    15

Hi everyone. I have a bit of a convoluted question, hoping someone can help me out or point me in the right direction.

We sell coffee beans, but we only keep them for 2 weeks from their roast date. Thereafter, they go on sale at a discounted price.

We have a supplier who only roasts once a week, on a Wednesday. We order from them on a Monday, and receive the stock the following Monday. The problem we have is estimating how much to order each week so that we not only have enough to fulfil current orders, but also have enough to fulfil some of the ones that come in before the next purchase order (buffer stock). Currently, we are more or less just estimating how much buffer to order to hold us over for the week, but this means that when we get it wrong, a customer can end up waiting more than a week for dispacth, depending on how soon after Monday morning they order (this is when we do the weekly order). If we over-order, we end up having to sell more than we'd like at a discount. So essentially we are trying to balance it between not making customers wait too long and not have to discount too much of it.

I'm sorry if this is not clear, please let me know if I can clarify anything. Also, if this is not the sub for this kind of question, can anyone please point me toward a sub where it would be more appropriate.

Thanks for reading


mgumms1 1

This feels like an SAT question.

nimble_fox 1

Calculate your DSOH and use empirical data to see where you struggled to keep orders and where you had expirations/spoilage.

Best of luck, if you need more help feel free to message me.

Quadling 1

Buy a small roaster. When out offer fresh roasted at a discount. Green beans stay good for a much longer time than roasted coffee

  quitelekker 1

Please see my answer to LavernderAutist. We buy from roasteries, so our customers trust the names they see on the packaging. Roasting ourselves is not lucrative for the amount of coffee we sell vs the space/equipment required to roast

Quadling 1

Ok. Then find a roaster that can do rush orders. Always order short and then call in a day at a time.

Alternative. Always order short. Economy of scarcity. The most exclusive coffee in the city. Better order up front! Or be here early!!!!

  quitelekker 1

Unfortunately, it is not that simple but thanks for your suggestions

bonafidelatina 1

Just sell ✨ aged ✨ coffee

  quitelekker 2

Haha that would make my life a lot easier. But we are in the 'specialty' market and our customer's would see right through that

MangoCreamPie 2

Hi! I’m an amateur dabbling in SC so apologies in advance if my answer is bad.

First identify how much of a priority being in stock is for you. Is it more painful to keep a customer waiting because of a backorder, or is it more painful to discount it later on?

The newsvendor model is a way to capture that trade off. They simplify it by saying: compare the cost per unit of being short (cs) with the cost per unit of being overstocked (ce). Aka, how much you lose per bag because a customer switched suppliers out of frustration or decided not to buy, and how much you lose per bag because you had to overstock. The more expensive it is to be short relative to being overstocked, the more important it is to be in stock all the time. Using this you can decide on your service level — the % of the time you want to be in stock.

Next step is to find the patterns in your data. There are a lot of fancy ways to approximate this. My rule of thumb is if you have a lot of direct customers it is likely that your demand follows a normal distribution (bell curve). If you have a lot of resellers your demand will not follow a normal distribution until you zoom out from days to weeks or months.

l would suggest to track down your daily received orders so you can have a gauge of your daily demand. Then, take a good long range of demand data (at least 3 months worth) and test it for normality with an online test (e.g. If it is not yet normal, try computing your weekly demand instead over at least 3 months and check if that is normal. If it isn’t normal at that point, most likely you have customers ordering in bulk in erratic periods, and you might need to forecast for them manually, or switch over to some kind of moving average model (search “exponential smoothing”)

From there, you can use the =NORM.DIST function in Excel to figure out how likely it is you will go out of stock, the amount you need to order based on your service level, and how likely it is for your order to expire.

  quitelekker 1

Thank you so much for this. I watched a video on the newsvendor model but I couldn't get through the formula jargon. Thanks for putting it in a way I can understand. I will try this out and let you know how it goes

LavenderAutist 7

Ask r/supplychain

Here is the appropriate formula that they would teach you in school.

They might have a more simplified solution for you.

Or you can Google the newsvendor model and see if you can find easier explanations than Wikipedia.

Also, operations management may not be your only solution. You can create a ??? Brand that you sell at a slightly reduced price to get rid of old stock. Or you can create a subscription service. Or something else as a sales program to manage the variability where the customer takes on the risk of the order being too much (Like ordering several weeks ahead for a discount). Or you can also create a retail location to push through the excess. Lots of other ways to tackle it.

  quitelekker 1

Thanks so much. I'll have a look at the formiula.

On your other points, the coffee in question is just one of many roasters we order from. Ideally I'd like to find a solution for them alone instead of having to overhaul the whole system. We already sell the 'old' stock at a reduced price, so we are still making money on it, but we'd like to reduce the amount of stock that gets old.

Thanks again for your suggestions, I really appreciate it

LavenderAutist 2

Well, the newsvendor problem is what they would teach you in b-school for that issue. The people in supply chain are the experts. Good luck.

  quitelekker 2

Thank you very much

LavenderAutist 1

Just updated the answer.