I am trying to create a Recommended Reorder Report. We previously used re-order points to determine when we needed to order goods, but now management wants this all based on estimated weeks of stock remaining. Although I am chugging right along, I have hit a snag. When we have new items with no sales history, I can't use the formula that is built on historical sales numbers.
We have come up with an alternative, trying to build a formula based on the date a report gets refreshed.
If the report is refreshed from March 3-8, then we would divide 26 by 1, 1 being the first week;
if the report is refreshed from March 10-15, then we would divide 26 by 2, 2 being the second week and so on...
We are using 26 weeks from March through August to build a formula here.
Now to the meat of the question:
Is there a way to build into the formula the refresh date?
Any help would be very appreciated. If you need any further information, I will be happy to give it.