Forecast Formula
|
|
|
|
Month
|
Sales
|
|
|
|
|
|
|
1
|
£1,000
|
|
|
|
|
|
|
2
|
£2,000
|
|
|
|
|
|
|
3
|
£2,500
|
|
|
|
|
|
|
4
|
£3,500
|
|
|
|
|
|
|
5
|
£3,800
|
|
|
|
|
|
|
6
|
£4,000
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Type the month number to predict :
|
12
|
|
|
|
|
|
|
The Forecast sales figure is :
|
£7,997
|
=FORECAST(E11,F4:F9,E4:E9)
|
||
|
|
|
|
|
|
|
|
|
What Does It Do ?
|
|
|
|
|
|
|
|
This
function uses two sets of values to predict a single value.
|
|
|
||||
|
The predicted
value is based on the relationship between the two original sets of values.
|
||||||
|
If the
values are sales figures for months 1 to 6, (Jan to Jun), you can use the
function
|
||||||
|
to
predict what the sales figure will be in any other month.
|
|
|
||||
|
The way
in which the prediction is calculated is based upon the assumption of a
Linear Trend.
|
||||||
|
|
|
|
|
|
|
|
|
Syntax
|
|
|
|
|
|
|
|
=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.
|
||||||
|
|
|
|
|
|
|
|
|
Formatting
|
|
|
|
|
|
|
|
No
special formatting is needed.
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
Example
|
|
|
|
|
|
|
|
The following
table was used by a company considering expansion of their sales team.
|
||||||
|
The Size
and Performance of the previous teams over a period of three years were
entered.
|
||||||
|
The size
of the New Sales team is entered.
|
|
|
|
|||
|
The =FORECAST()
function is used to calculate the predicted performance for the new sales
|
||||||
|
team
based upon a linear trend.
|
|
|
|
|
||
|
|
|
|
|
|
|
|
|
|
Year
|
Size Of
Sales Team |
Known
Performance |
|
|
|
|
|
1996
|
10
|
£5,000
|
|
|
|
|
|
1997
|
20
|
£8,000
|
|
|
|
|
|
1998
|
30
|
£8,500
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Size Of The New Sales Team :
|
40
|
|
|
|
|
|
|
Estimated Forecast Of Performance
:
|
£10,667
|
=FORECAST(E43,E39:E41,D39:D41)
|
||