How can I project the values for November and December if I have values for
January - October? What is the correct formula?
How can I project the values for November and December if I have values for
January - October? What is the correct formula?
The short answer is try using the =FORECAST() function, where the known_y's
are your values, the known_x's can just be 1, 2, .. 10 (or =MONTH() of the
date) and then the points for which you want to predict a value are 11 and 12.
However, this makes a huge assumption that the relationship is linear. So
the first step has to be simply to plot them and see if this assumption is
valid. If it is, FORECAST is perfectly OK. If they are something that is
growing at a constant rate then =LOGEST() might be the function to use.
Otherwise you're moving into much more complex area.
HTH, David Jessop
"Gabriele" wrote:
> How can I project the values for November and December if I have values for
> January - October? What is the correct formula?
Answer given by someone else using "FORECAST()" is fine, but I also use a dynamic "AVERAGE", which might also serve:
Say you have values like this:
ColA ColB ColC
JAN FEB MAR
$15 $32 $16
Assume cell with JAN is A1
In cell D2 enter: =AVERAGE($A2:C2) ... note the "$" anchor. Now copy it across the row into the other columns. The number will be the same, of course, but as you overlay the formulae in future months with "Actuals" during the year, the average changes automatically. This is a simpler method when you're dealing with "flat-line" expenses in a budget ... not in the least bit sophisticated, but invariably more explicable to the boss!
Another way is to use the prior year to pro rata YTD in current year ... you can use the same "anchor" method above to measure YTD in prior year, thus determining a ratio to apply to the current year. A little more complex, but allows for any seasonalization issues.
Hope that helps.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks