The excel help in this subject is not very good for novices.
And non-novices alike, especially given the lack of context for the
example.
Question:
Jan 5000
Feb 7500
Mar 10000
etc.
How do I use "forecast" or what is the syntax for projecting
future sales for the following 3 months?
I'll give you a quick answer -- probably what you need. Then I'll
discuss some finer points.
For your example, I am going to assume that you want to treat each
month as equal. Assume that the month names are in A1:A3 and the
quantities are in B1:B3. To forecast Apr (4th month), enter the
following into B4 and copy down:
=forecast(row()-row($A$1)+1, $B$1:$B$3, row($A$1:$A$3))
If you think of how you might graph the data, the month names would be
plotted along the X axis ("known_X"), and the corresponding values in
column B would be plotted along the Y axis ("known_Y"). The first
argument of FORECAST() ("X") is the X-axis data point for you want to
compute the Y-axis value.
The problem is: FORECAST() does not treat text labels as the ordinals
1, 2 3, etc (or 0, 1, 2 etc). That is the purpose of row($A$1:$A$3)
above. Alteratively, you could create a parallel column (optionally
hidden) that contains the ordinal corresponding to the month. This
would be useful if you had gaps in your chronology; for example, if
you had Jan, Mar and Jun [sic; purposely not May] instead of Jan, Feb
and Apr.
You might consider replacing "Jan", "Feb" et al with real dates (e.g.
1/1/2007, 2/1/2007, etc) and formatting with the Custom format "mmm".
However, in that case, months are not treated as equal; FORECAST()
will use the actual number of days between dates. To see the
difference, make the change in column A and change the formula in B4
to:
=forecast(A4, $B$1:$B$3, $A$1:$A$3)
For most people, that is not the answer they intended.
Please note that FORECAST() assumes that the growth is linear
(straight-line). That is obviously the right answer in your example.
But in general, it would be advisable to graph the known data first to
be sure that you agree that the growth is linear. If it is not, do
not use FORECAST().
HTH.
