Forecast Formula help

[Jason Hall]

Ok, I have read and reread the forecast function and I still have questions....

Scenario:
I need to forecast a dollar value for the 2 quarters based on the last 6
quarters dollar value.

Example:
FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4
$2.76 $2.40 $2.07 $2.36 $2.00 $2.17

Issue:
Can't figure out how to set the forumla up to forecast FY08Q3 and FY08Q4

 

[Red]

Hey Jason.

Here's what I did... see if it works for you.

Insert a row somewhere near your quarter headings (I did above my version of
your data). Then instead of using the year and Q#, just input 1 above
FY07Q1, 2 above FY07Q2, etc. These will represent your known X values.

In the cell under FY08Q3 use the formula wizard to select forecast. In the
X value, place a 3 (since you are trying to find a forecasted value for
FY08Q3.) For the known y's, select your current data points (2.76, 2.40,
2.07 etc). And for the known x's you'll need to select the numbers in the
new row you added. Once you hit OK you'll have a value for FY08Q3. You'll
need to repeat the process in FY08Q4, changing the X from 3 to 4 and then
selecting your data all over again.

Hope this helps.

 

[Jason Hall]

I think it worked but, wouldn't FY08Q3 actually be 7 and not 3?

 

[Red]

Yes is would. it would be 3 if you were only forecasting FY08 without the
FY07 historicals.



Just a fellow Excel user here to help when I can.....

 

[joeu2004]

I need to forecast a dollar value for the 2 quarters based on the last 6
quarters dollar value.
Example:
FY07Q1 FY07Q2 FY07Q3 FY07Q4 FY08Q1 FY08Q2 FY08Q3 FY08Q4
$2.76 $2.40 $2.07 $2.36 $2.00 $2.17

Before you use any Excel function, it is important first to graph the data
that you have. For example, FORECAST assumes a linear trend. If you graph
your data, I think you will see that is not a likely assumption.

You can graph the data quickly by simply selecting your data and using the
Chart Wizard on the standard toolbar to plot an XY chart.

In fact, if you select a linear trendline and extend it forward 2 units, you
will see the result that FORECAST gives you. You be the judge as to whether
or not the fits your data. (Note that the RSQ value is around 55%. A good
fit would be 85% or higher.)

What I see might be a decaying sinusoidal curve. I don't know of an Excel
function that plots a trendline for such a curve. Alternatively, the data
reflects a "sideways" trend (a flat horizontal line). Caveat: Don't be
fooled by selecting a 5- or 6-degree ploymomial trendline. Of course, that
fits you data exactly due to a mathematical principle. If you extend the
curve forward 2 units, you will see that it's misleading.

Moreover, my comments assume a sequential time series of the data. You have
not said anything one way or another to justify that assumption. Perhaps the
trend to look for year-over-year per quarter. But IMHO, you do not provide
sufficient information to project such a trend.

 

[Scott]

I too am at a loss regarding this forecasting thing. Where does the 3 or 7
come from?

According to what I read in the Excel Help:

X is the data point for which you want to predict a value.

Known_y's is the dependent array or range of data.

Known_x's is the independent array or range of data.

I understand the Known-y's and Known-x's, but is the plain old x and where
does it come from? How would you know which to use. I'd appreciate some
assistance on this.

Thanks