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]

welcome to the forums. you could try the below formulas, please test them because not entirely sure what you're trying to achieve, given that your example looks unusually straightforward, so not sure the formulas will account for any variations that may arise with your data. reach out with questions.

F5 (copied over):

=25

F3 (copied over):

=IF(E4-F2<0,30,0)

F4 (copied over):

=E4+F3-F2

 

[ncrb_10]

Thank you for your reply! Can you please clarify what F5, F4, and F3 are referring to? I would assume column and row, but I do not have 5 rows. Maybe I am misunderstanding.

 

[CyrusTheVirus]

well, actually F5 meant F2, but you don't even have to type that formula, but the other ones (F3 and F4) you do... does that help clarify?

 

[ncrb_10]

That helped tremendously! I guess sometimes you stare at a screen too long and start confusing yourself. However, I just recognized that solved half of my problem.

I actually need to produce inventory produce inventory for the following month. In other words, "30" should start at "E3" in order to satisfy sales in F25. Based on the formula you provided, "E3" would be 0. How can the formula be modified to accommodate?

Thanks again for your help.

 

[ncrb_10]

The issue I seem to be having is that if I start the formula in F3 one column back, I run into a circular reference.

 

[CyrusTheVirus]

who's formula? yours or mine? can you please copy/paste a sample of your new data set so i can see and please list all formulas being used.

 

[ncrb_10]

Row 11 is pulling sales data from a profit and loss statement. Currently, Row 12 Column L is running the formula you suggested =if(k13-l11<0,$C$9,0)

In this table "$C$9" is a cell that contains the minimum units I have to produce per month.

Row 13 Column L is =K12-L11+L12 or previous months production, minus current months sales, plus current months production.

My goal is the following logic: if L11>0, then K12=34. However, if K13>L11, then K12=0. My issue is that since K13 relies on a formula referencing K12, I end up with a circular reference. The formula you provided me with works perfect, but it tells me that I need to produce during the current month. I need it to tell me to produce one month prior. Does that make more sense?

 

[ncrb_10]

[TABLE="width: 863"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:469;width:11pt" width="15"> <col style="mso-width-source:userset;mso-width-alt:6186;width:145pt" width="193"> <col style="mso-width-source:userset;mso-width-alt:3413;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:2090;width:49pt" width="65"> <col style="mso-width-source:userset;mso-width-alt:3200;width:75pt" width="100"> <col style="mso-width-source:userset;mso-width-alt:725;width:17pt" width="23"> <col style="mso-width-source:userset;mso-width-alt:1664;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:1706;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:1834;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1706;width:40pt" width="53"> <col style="mso-width-source:userset;mso-width-alt:1877;width:44pt" width="59"> <col style="mso-width-source:userset;mso-width-alt:1664;width:39pt" width="52"> <col style="mso-width-source:userset;mso-width-alt:1536;width:36pt" width="48"> <col style="mso-width-source:userset;mso-width-alt:1792;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1706; width:40pt" width="53" span="2"> <col style="mso-width-source:userset;mso-width-alt:1792;width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:1749;width:41pt" width="55"> </colgroup><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>

 

[ncrb_10]

FYI - in the Excel sheet "Jan-19" is Row 10 Column G