Manual Forecasting Tool That Adjusts based on inventory

Trying to figure out when we will need to ship units to a customer. Their sales forecast is D3:BK3. However, we won’t be shipping them anything until their inventory hits 8 weeks of supply. I’ve entered this into BM1, and want to be able to change this to 10 or 12 or whatever and have this still calculate. BM3:DT3, I want to put what I should be using my ship forecast.

I’ve tried sequence but it wasn’t bringing back the correct cells.

Essentially, I want a formula that looks at the weeks of supply in C and doesn’t populate BM:DT until that number is below 8.

So based on the 3 examples shared:

Row 3 would be 0 in BM3:BS3 as there are 14 weeks of supply and my target in BM1 is 8. So then at BT3, it would finally populate with K3 which is the first week in which we would be shipping them inventory.

Row 4 would populate immediately with D4:BK4 in BM:DT as there is no inventory. If there is a calc that can be done that would take the sum of the first 8 weeks and put that into BM4, great. But I think I will just be doing that manually. They would order 8 weeks of inventory up front now as they are at 0.

Row 5 would be 0 in the first week BM5 but then immediately start populating BN5 onward with E5:BK5.

Does this make sense? Trying to paste in via ExcelToReddit.

|+|A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|AA|AB|AC|AD|AE|AF|AG|AH|AI|AJ|AK|AL|AM|AN|AO|AP|AQ|AR|AS|AT|AU|AV|AW|AX|AY|AZ|BA|BB|BC|BD|BE|BF|BG|BH|BI|BJ|BK|BL|BM|BN|BO|BP|BQ|BR|BS|BT|BU|BV|BW|BX|BY|BZ|CA|CB|CC|CD|CE|CF|CG|CH|CI|CJ|CK|CL|CM|CN|CO|CP|CQ|CR|CS|CT|CU|CV|CW|CX|CY|CZ|DA|DB|DC|DD|DE|DF|DG|DH|DI|DJ|DK|DL|DM|DN|DO|DP|DQ|DR|DS| |:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-|:-| |1| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |8|<-- WOS Target| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |2|ITEM|Inventory|WOS|WK 1|WK 2|WK 3|WK 4|WK 5|WK 6|WK 7|WK 8|WK 9|WK 10|WK 11|WK 12|WK 13|WK 14|WK 15|WK 16|WK 17|WK 18|WK 19|WK 20|WK 21|WK 22|WK 23|WK 24|WK 25|WK 26|WK 27|WK 28|WK 29|WK 30|WK 31|WK 32|WK 33|WK 34|WK 35|WK 36|WK 37|WK 38|WK 39|WK 40|WK 41|WK 42|WK 43|WK 44|WK 45|WK 46|WK 47|WK 48|WK 49|WK 50|WK 51|WK 52|WK 53|WK 54|WK 55|WK 56|WK 57|WK 58|WK 59|WK 60|X|WK 1|WK 2|WK 3|WK 4|WK 5|WK 6|WK 7|WK 8|WK 9|WK 10|WK 11|WK 12|WK 13|WK 14|WK 15|WK 16|WK 17|WK 18|WK 19|WK 20|WK 21|WK 22|WK 23|WK 24|WK 25|WK 26|WK 27|WK 28|WK 29|WK 30|WK 31|WK 32|WK 33|WK 34|WK 35|WK 36|WK 37|WK 38|WK 39|WK 40|WK 41|WK 42|WK 43|WK 44|WK 45|WK 46|WK 47|WK 48|WK 49|WK 50|WK 51|WK 52|WK 53|WK 54|WK 55|WK 56|WK 57|WK 58|WK 59|WK 60| |3|ABC123|2300|14.0|165.00|162.00|141.00|168.00|171.00|172.00|170.00|161.00|186.00|169.00|175.00|171.00|185.00|178.00|181.00|176.00|185.00|184.00|190.00|175.00|189.00|184.00|171.00|348.00|166.00|146.00|149.00|152.00|144.00|157.00|150.00|152.00|164.00|169.00|180.00|218.00|195.00|231.00|288.00|371.00|697.00|999.00|1,609.00|1,496.00|1,376.00|1,046.00|332.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00|193.00| |0.00|0.00|0.00|0.00|0.00|0.00|0.00|161.00|186.00|169.00|175.00|171.00| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |4|DEF123|0|0.0|33.00|44.00|49.00|61.00|58.00|65.00|65.00|60.00|78.00|72.00|72.00|70.00|99.00|91.00|95.00|74.00|801.00|86.00|83.00|76.00|98.00|97.00|92.00|178.00|92.00|85.00|82.00|81.00|77.00|76.00|72.00|77.00|91.00|89.00|130.00|162.00|149.00|190.00|225.00|302.00|565.00|911.00|1,748.00|1,816.00|1,819.00|1,645.00|415.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00|179.00| |33|44|49|61|58|65|65|60|78|72|72|70|99| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |5|GHI123|888|8.6|98.00|102.00|97.00|103.00|106.00|107.00|109.00|104.00|119.00|111.00|109.00|114.00|123.00|113.00|130.00|124.00|134.00|122.00|133.00|138.00|148.00|152.00|134.00|295.00|139.00|132.00|137.00|128.00|135.00|120.00|131.00|117.00|114.00|111.00|111.00|127.00|123.00|117.00|108.00|126.00|157.00|202.00|479.00|457.00|529.00|679.00|408.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00|260.00| |0.00|102.00|97.00|103.00|106.00|107.00|109.00|104.00|119.00|111.00|109.00|114.00| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |

^{Table formatting by ExcelToReddit}