Forecast Formula

[jchungerford]

Hi All,

I am needing to create an annual forecast formula and I am not sure how to do it. :|

What I am looking to accomplish is this:

1) Sum the values in a range of a row (values) between multiple columns (months). In this case range I4:T4.
2) Take the sum and create an overall projected annual value for the year in cell V4.

An easy example would be if only two months have been summed then you simply multiply by 6 however where I am being tripped up is what happens when:

1) an uneven number of values have been summed, how to calculate for that (example - 5 months out of 12) ?
2) if previous months are omitted, how to include that variable in the equation (example - only 10 production months total in forecast instead of 12)??

Any help is appreciated.

[FDibbins]

How about this?
=AVERAGE(I4:T4)*12

[6StringJazzer]

Is your underlying assumption that your annual forecast will be the same as if the average for the existing months occurred for all 12 months? That is, you expect flat performance through the year? If so the formula you want is:

=12*AVERAGE(I4:T4)

I'm not sure I understand #2. Do you mean if the year will not have 12 months? If so, then adjust the 12 in the formula to the number of months expected.

[MrShorty]

An easy example would be if only two months have been summed then you simply multiply by 6

It sounds like you are intending to use simple proportions to solve this. Do you recall your beginning algebra from back in the day http://www.purplemath.com/modules/ratio2.htm

current sum/number of months=forecast/12 months

solve the proportion for forecast.

On edit, noting that current sum/number of months=AVERAGE(), this should be the same as the other suggestions using AVERAGE().

[mehmetcik]

Please Login or Register  to view this content.

[jchungerford]

@6StringJazzer Yes, but I need the formula to automatically adjust itself for expected months. So, if the first value occurred in march if then Jan and Feb will just be ignored and the coefficient would be 10 instead of 12.

My brain is pretty fried from some help needed with some very complicated formulas recently.

[jchungerford]

In this example, there will only be 10 months. So forecast would be 25.07137975 because the sum of 2 months would be 5.01427595 and since there are only 10 months the coefficient would be a multiplying factor of 5.

[6StringJazzer]

Use this formula as an array formula:

=(13-MATCH(FALSE,I4:T4="",0))*AVERAGE(I4:T4)

Instead of pressing ENTER to enter the formula, press CTRL+SHIFT+ENTER. You will know the array is active when you see curly braces { } appear around your formula in the formula box.

[jchungerford]

AWESOME! *high five* 6StringJazzer! That's it!

Thank you so much!!!

[6StringJazzer]

Glad to help!