I need to smooth out my production curve over time while still respectiing inventory

AAbrams

New member
Joined
Oct 25, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2010, 2013
I'm trying to smooth out production to level out extreme values and I'm having a hard time coming up with the right logic. With Min, Max, ROQty, BegInv, and Sales all constant, I can develop the required production to a) meet demand + minimum and 2) not go skying off into the stratosphere (logic in cell C6 of the attached example.)

What I can't do, apparently, is average these production requirements over a rolling series of periods (say, this period and the next two) in such a way that I don't violate min/max constraints, don't lose control of inventory (either going negative or growing beyond control), and don't have huge swings in production from period to period.

Any thoughts on how to level production over time without increasing overall inventory levels? As it is, my model is demanding high levels for a given month, followed by multiple months of zero or very low production, and that's no way to live.

Thanks,
Alan
 

Attachments

  • Inventory example2.xlsx
    16.5 KB · Views: 29

bgoree09

New member
Joined
Aug 20, 2013
Messages
179
Reaction score
0
Points
0
Good afternoon,

Are the shipping figures constant? In the sample they are dead cells. Production is following these numbers (which have severe swings). I apologize if I'm misunderstanding.

Thanks,
 

AAbrams

New member
Joined
Oct 25, 2013
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2010, 2013
The numbers in the example are randomized - I was creating a simpler version of the problem without the 36 period planning window or the references in the "real" sheet. Shipping is not constant from period to period, although max, min, and roqty (basically the increment in which I can produce) are. Shipping is not, however, dependent on production.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Hi There
This is an interesting problem, but Im not sure if formulae alone can solve it. Before I get pounced on I should explain :)
The logical approach is to develop or (improve existing) forecasting of your sales. Armed with this, you can deal with peaks and troughs better
by planning ahead. The experts will tell you that they can program an accurate forecasting system, but that depends on how well/accurately
you can identify all the variables (e.g the weather). Those that you can't (called residuals) determine by proportion the expected accuracy of
your forecast.
Although difficult, it is the only way to get production smoothing without stock problems.
When you can't get enough accuracy, the answer is usually overstocking, especially if your planners get roasted for not being able to meet orders!

We had some success asking customers to commit to a forecast and/or maintain their own buffer stocks. If your products are perishable, this
would be less feasible however. We did find that when we discussed the pros and cons quite a few customers were willing to work with us.

Good Luck

Hercules
 
Top