How to use Forecast?

[c24c4]

I dont understand how to use the forecast function. I have a colum of numbers that represent the number of events by month(8 items), given the data at hand i would like to predict the number of events for the next 6 months.

I cant seem to get the forecast or trend function to work because i dont understand the arguments that need to be entred and how to get a range of numbers out. thanks
This message was edited by c24c4 on 2002-08-31 08:17
This message was edited by c24c4 on 2002-08-31 08:18
This message was edited by c24c4 on 2002-08-31 13:12

 

[Ekim]

FORECAST

From Excel's Help file:

FORECAST(x,known_y's,known_x's)

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.

In plain English from Peter Noneley's Excel Dictionary (http://homepage.ntlworld.com/noneley)

=FORECAST(ItemToForeCast,RangeY,RangeX)

ItemToForecast is the point in the future, (or past), for which you need the forecast.

RangeY is the list of values which contain the HISTORICAL DATA to be used as the basis of the forecast, such as Sales figures.

RangeX is the intervals used when recording the HISTORICAL DATA, such as Month number.


1. In A1:A14 put 1 to 14.

2. In B1:B8 put your data for the 8 months.

3. In B9 put =FORECAST(A9,$B$1:B8,$A$1:A8)

4. Copy the above formula to B10:B14


TREND

Again, In plain English from Peter Noneley's Excel Dictionary (http://homepage.ntlworld.com/noneley).

=TREND(KnownYs,KnownXs,RequiredXs)
The KnownYs is the range of values, such as Sales Figures.
The KnownXs is the intervals used when collecting the data, such as Months.
The RequiredXs is the range for which you want to make the prediction, such as Months.

The TREND function is an array function and must be entered using Ctrl+Shift+Enter.

In your example:

1. In B9 put =TREND(B1:B8,A1:A8,A9:A14) - enter normally.
2. Click B9 and highlight cells B10:B14
4. In the Formula Bar, click the formula.
5. Now hit the following keys together: Ctrl-Shift-Enter

(Excel will add parenthesis around the formulas)

If there is a straight-line correlation in your data, the FORECAST function and the TREND function will return the same result.

GENERAL NOTES

A majority of optimization problems involve linear relationships between variables. Linear problems are represented by a straight-line on a graph. These include problems using simple arithmetic operations such as:

• Addition and subtraction
• Built-in function such as SUM(), FORECAST() AND TREND()

If sales are doubling or trebling, you are seeing an exponential growth. Use the GROWTH function instead of the TREND function.

Regards


Mike
This message was edited by Ekim on 2002-09-01 10:39

 

[c24c4]

Mike thaks for the help and the link to the online dictionary.

Curt