Results 1 to 4 of 4

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

  1. #1
    Neophyte AAbrams's Avatar
    Join Date
    Oct 2013
    Posts
    4
    Articles
    0
    Excel Version
    2010, 2013

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    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,

  3. #3
    Neophyte AAbrams's Avatar
    Join Date
    Oct 2013
    Posts
    4
    Articles
    0
    Excel Version
    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.

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •