I use a version of this formula to evaluate inventory against a forecast of
future sales, so empty months (or weeks, in my case) are counted.

As Shane pointed out, when using sales history to predict future sales, missing
data makes this much more difficult. You are probably going to have to use
work-arounds suited to your business and the purpose of the data you are generating.

For example, if you have partial information on a new item, you may need to find
a comparable item that you have complete history for and use that for your
projections.

As for the adjustments you made, I would have to see the final formula and the
data that led you to make the adjustment to see what was happening. However, if
you are getting the correct answer, than the adjustment works and I wouldn't
worry about finding a better fix.


JLewis wrote:
Glenn,

After checking the data calculations, I noticed that partial months (less
than one month) calculations do not work quite right. I sorta fixed this by
including an "IF" function in the 2nd half of the formula that says if the
calculation is less than zero to add a 1. This works. For instance, I had 1
item that the calculation was -.02. I had 81 in stock and 83 sales in the
first month. It should have been .98, so adding 1 was the solution. If there
was no inventory the calculation was -1. Adding one solved this problem as
well.

Again, this is a great formula, it just needed a little tweaking for less
than a month supply. Did you have a better fix for this? or did I have
something wrong in your formula that caused the negatives?

I also had another problem that wasn't really associated with the formula.
We have some new items that do not have sales in the first few or more months
of the year (because the item was introduced later in the year). The
calculation counts these zero months as full months of supply. Is there any
way to account for leading zero months and skip them? As a fix to get the
report finished, I created a new formula in another cell to count zero months
and then deducted them from the calculation. This worked somewhat well, but
it also counts months where there was no sales in the middle of the year.
(sales in Jan-May, no sales in Jun, sales in Jul-Dec for instance). These
should be accounted for as having no sales. Mostly due to the seasonality of
our business.

Any thoughts you have would be appreciated. Again, I really love the
formula, it saved me hours of work!

Jennifer

"Glenn" wrote:

JLewis wrote:
Awesome! It works. I see now what I was and wasn't doing. Thank you, Thank
you! I really appreciate your help on this. This will help me in other
reports as well.


Glad I could help.