Trying to find a way around a circular reference in a stock forecast

[gravanoc]

Order Forecast.xlsx

I'm using excel to track our historic sales and forecasted sales. I have limits for min and max stocks of Product A. What I would like to do is have the excel sheet identify when stocks are forecast to go below min, generate the need for a re-order at that point in the future and then amend the stock forecast to reflect the arrival of the new stock. Im getting caught in an iteration loop. Any help is appreciated.

The circular reference occurs here. I'm adding the Sales Record stock, the Sales forecast, and the Re-order value in CK3 together, but the value in CK3 has to check whether the value in BY3 (the Stock Level) is less than the Min Stock level in BA3, and if it is, it sets itself to the value in J3 (the minimum re-order amount). I just can't think of a way to do this without somehow getting a circular ref!

 

[gravanoc]

Bump

 

[bbotzong]

One way to fix that is to turn on Iterative Calculations for the sheet. Click File-->Options-->Formulas and look in the top right for a checkbox and some limits. This will get rid of the circular reference as it will cycle through your calculations until it reaches one of those two limits.

 

[gravanoc]

Thanks