Hey all,
Thanks for taking a look at this, hope I'm able to explain what I'm trying to accomplish well enough to come to a solution.
Right now we have a column F that shows the current inventory available - it goes down 27 rows for each item so F2:F27. We also have historic sales numbers from 2014-December 2016. This equates to G:AP and also has the same range G2:G27 through AP2:AP27. Right now we have forecasted numbers through 2017, which is AQ:BB and want to find out at what time, using the 2017 forecasted sales, our product will go out of stock based on the current inventory available (F2:F27) in 2017 and how many days left that equals.
Let me know if you need the actual data to do this. Right now I'm using this:
=(IF(AP2>F2, 30, IF(SUM(AP2:AQ2)>F2, 60, IF(SUM(AP2:AR2)>F2, 90, IF(SUM(AP2:AS2)>F2, 120, IF(SUM(AP2:AT2)>F2, 150, IF(SUM(AP2:AU2)>F2, 180, IF(SUM(AP2:AV2)>F2, 210, IF(SUM(AP2:AW2)>F2, 240, IF(SUM(AP2:AX2)>F2, 270, IF(SUM(AP2:AY2)>F2, 300, IF(SUM(AP2:AZ2)>F2, 330, IF(SUM(AP2:BA2)>F2, 360, IF(SUM(AP2:BB2)>F2, "390+")))))))))))))-15)
Which basically just says if the sum of the first month (Jan 2017) is greater than the current inventory available, say 30 and if the sum of the first two months (Jan-Feb 2017) is greater than the current available, say 60... etc. This is ok for it but it rounds something like 31 days left into 60 which could be dangerous so I'm looking for a better solution!
Thanks.
Bookmarks