Formula/Logic Writing

[ncrb_10]

Hello,

I am building an inventory production schedule and need help writing a formula for the following logic.

For example, in Row A, Column A:L (January-December), I have projected sales of 25 units per month (starting in June). Additionally, my minimum monthly production order is for 30 units (Row B).

Therefore, my ending inventory each month is 5 units. At the end of 5 months, I should have accumulated 25 units in inventory and not have to produce any units on the 6th month.

How would I write a formula that displays the following without having to do any manual entry. My main issue is overcoming circular references. Thanks in advance for the help!

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[TD]May
[/TD]
[TD]Jun
[/TD]
[TD]Jul
[/TD]
[TD]Aug
[/TD]
[TD]Sep
[/TD]
[TD]Oct
[/TD]
[TD]Nov
[/TD]
[TD]Dec
[/TD]
[/TR]
[TR]
[TD]Sales
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[TD]25
[/TD]
[/TR]
[TR]
[TD]Production
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]30
[/TD]
[TD]0
[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Ending Inv.
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[TD]10
[/TD]
[TD]15
[/TD]
[TD]20
[/TD]
[TD]25
[/TD]
[TD]0
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]

 

[ncrb_10]

Ok, then in...

Cell L12:

=IF(L11>K13,34,0)

and in...

Cell L13:

=K13-L11+L12

Does that work?

Yes that works. We are 90% of the way there. The final step is to essentially move the data in row 12 back one column. In other words, I need cell K12 (not L12) to show if L11>K13 then K12=34. Does that make sense?

 

[CyrusTheVirus]

Yes that works. We are 90% of the way there. The final step is to essentially move the data in row 12 back one column. In other words, I need cell K12 (not L12) to show if L11>K13 then K12=34. Does that make sense?

Unfortunately there lies your issue, I don't believe you can do that. That is what is causing the circular referencing. What are you trying to achieve by doing it that way, logically it doesn't seem correct because you are basing current values on future values, but then the future values will always be referencing current values, this is what causes the logical bind.

Let me know your thoughts and what you are trying to achieve. Perhaps I can help achieve it in a different way. Just let me know.

 

[ncrb_10]

Unfortunately there lies your issue, I don't believe you can do that. That is what is causing the circular referencing. What are you trying to achieve by doing it that way, logically it doesn't seem correct because you are basing current values on future values, but then the future values will always be referencing current values, this is what causes the logical bind.

Let me know your thoughts and what you are trying to achieve. Perhaps I can help achieve it in a different way. Just let me know.

That is what I figured. It is an obstacle I have been trying to overcome for a while now. It is at least settling to hear a second opinion that it may not be possible.

The reason I am trying to do this is for two reasons. 1) I want a realistic cash flow picture, and 2) I want to know when I must produce inventory the month before it is needed. In this case, it takes approx. 21 days to produce inventory.

One idea I have which is a simple workaround is just to add another row at the bottom with an offset formula. In other words, K14=offset(K12,0,1). This will move the production values back one column but keep the other data in tact (for ending inventory purposes). Any other ideas you may have is appreciated, and thank you for helping me out on this one. What do you think about the offset addition?

 

[CyrusTheVirus]

Sounds like the offset addition would be a good idea, or else you will continually run into circular referencing due to the false logic being used... wondering if there is something missing here...

... perhaps what you could do is 'assume' the worst case scenario for the following month, and then base current production on the worst case scenario therefore you will always be covered. Though not sure. Best of luck.