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]

 

[CyrusTheVirus]

ok... re-edited this post, let me review your last couple posts.

 

[CyrusTheVirus]

[TABLE="width: 863"]
<tbody>[TR]
[TD="width: 15"][/TD]
[TD="width: 193"][/TD]
[TD="width: 107"][/TD]
[TD="width: 65"][/TD]
[TD="width: 100"][/TD]
[TD="width: 23"][/TD]
[TD="class: xl64, width: 52"]Jan-19[/TD]
[TD="class: xl64, width: 53"]Feb-19[/TD]
[TD="class: xl64, width: 57"]Mar-19[/TD]
[TD="class: xl64, width: 53"]Apr-19[/TD]
[TD="class: xl64, width: 59"]May-19[/TD]
[TD="class: xl64, width: 52"]Jun-19[/TD]
[TD="class: xl64, width: 48"]Jul-19[/TD]
[TD="class: xl64, width: 56"]Aug-19[/TD]
[TD="class: xl64, width: 53"]Sep-19[/TD]
[TD="class: xl64, width: 53"]Oct-19[/TD]
[TD="class: xl64, width: 56"]Nov-19[/TD]
[TD="class: xl64, width: 55"]Dec-19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66, colspan: 4"]Total Barrels Sold[/TD]
[TD][/TD]
[TD="class: xl63, align: right"] -[/TD]
[TD="class: xl63, align: right"] -[/TD]
[TD="class: xl63, align: right"] -[/TD]
[TD="class: xl63, align: right"] -[/TD]
[TD="class: xl63, align: right"] -[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[TD="class: xl63, align: right"] 21[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66, colspan: 4"]Production Batch[/TD]
[TD][/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] 34[/TD]
[TD="class: xl65, align: right"] 34[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] 34[/TD]
[TD="class: xl65, align: right"] 34[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] 34[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66, colspan: 4"]Ending Inventory[/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] -[/TD]
[TD="class: xl65, align: right"] 13[/TD]
[TD="class: xl65, align: right"] 47[/TD]
[TD="class: xl65, align: right"] 13[/TD]
[TD="class: xl65, align: right"] 13[/TD]
[TD="class: xl65, align: right"] 47[/TD]
[TD="class: xl65, align: right"] 13[/TD]
[TD="class: xl65, align: right"] 13[/TD]
[/TR]
</tbody>[/TABLE]
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri, sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { }.xl64 { color: white; font-weight: 700; text-align: center; background: rgb(31, 78, 120) none repeat scroll 0% 0%; }.xl65 { }.xl66 { text-align: right; }</style>

Your ending inventory for July doesn't seem correct. Shouldn't the ending inventory be 26, which is 34 +13-21?

 

[ncrb_10]

Yes you are correct, that was an error on my end. I just adjusted the formula to be =L13-M11+M12

 

[CyrusTheVirus]

ok, then try out the below. Let me know if you are all set.

Row 12 Jun:

=IF(F11>E13,34,0)

Row 13 Jun:

=F12-F11+E13

 

[ncrb_10]

The first code works as needed, however I am still getting a circular reference when I try adding the second code.

 

[CyrusTheVirus]

Perhaps we need to start over. First question: You need a total of 3 formulas correct, one which you already have which is pulling from the profit and loss, one for the production batch and one for the ending inventory, correct?

 

[ncrb_10]

Yes correct

 

[CyrusTheVirus]

ok. Two questions:

1) What cell is Production Batch for June in?

2) What cell is Ending Inventory for June in?

 

[ncrb_10]

ok. Two questions:

1) What cell is Production Batch for June in?

2) What cell is Ending Inventory for June in?

Production Batch for June is cell L12 and Ending Inventory for June is L13

 

[CyrusTheVirus]

Production Batch for June is cell L12 and Ending Inventory for June is L13

Ok, then in...

Cell L12:

=IF(L11>K13,34,0)

and in...

Cell L13:

=K13-L11+L12

Does that work?