"I always avoid prophesying beforehand because it is much better to prophesy after the event has already taken place. " --Winston Churchill
Forecasting using trend analysis
• Part 1. Theory • Part 2. Using Excel: a demonstration. • Assignment 1, 2
Quantitative forecasting methods in library management
2
Learning objectives
To learn how:
• To compute a trend for a given time-series data using Excel • To choose a best fitting trend line for a given time-series • To calculate a forecast using regression equation
Quantitative forecasting methods in library management 3
Main idea of the trend analysis forecasting method
• Main idea of the method: a forecast is calculated by inserting a time value into the regression equation. The regression equation is determined from the time-serieas data using the “least squares method”
Quantitative forecasting methods in library management
4
Prerequisites: 1. Data pattern: Trend
Trend (close to the linear growth)
Quantitative forecasting methods in library management
5
Prerequisites: 2. Correlation
There should be a sufficient correlation between the time parameter and the values of the time-series data
Quantitative forecasting methods in library management
6
The Correlation Coefficient
• The correlation coefficient, R, measure the strength and direction of linear relationships between two variables. It has a value between –1 and +1 • A correlation near zero indicates little linear relationship, and a correlation near one indicates a strong linear relationship between the two variables
Quantitative forecasting methods in library management 7
Main idea of the trend analysis method
• Trend analysis uses a technique called least squares to fit a trend line to a set of time series data and then project the line into the future for a forecast. • Trend analysis is a special case of regression analysis where the dependent variable is the variable to be forecasted and the independent variable is time. • While moving average model limits the forecast to one period in the future, trend analysis is a technique for making forecasts further than one period into the future.
Quantitative forecasting methods in library management
8
The general equation for a trend line
F=a+bt
Where: • F – forecast, • t – time value, • a – y intercept, • b – slope of the line.
Quantitative forecasting methods in library management
9
Least Square Method
• Least square method determines the values for a and b so that the resulting line is the best-fit line through a set of the historical data. • After a and b have been determined, the equation can be used to forecast future values.
Quantitative forecasting methods in library management
10
The trend line is the “best-fit” line: an example
Municipal public libraries in Lithuania in 1991-2002
Number of libraries
1700 1650 1600 1550 1500 1450 1400 1350
1990
1991
Quantitative forecasting methods in library management
1992
1993
1994
1995
1996
Year
1997
1998
1999
2000
2001
2002
2003
11
Statistical measures of goodness of fit
In trend analysis the following measures will be used:
• The Correlation Coefficient • The Determination Coefficient
Quantitative forecasting methods in library management
12
The Coefficient of Determination
• The coefficient of determination, R2, measures the percentage of variaion in the dependent variable that is explained by the regression or trend line. It has a value between zero and one, with a high value indicating a good fit.
Quantitative forecasting methods in library management
13
Goodness of fitt: Determination Coefficient RSQ
• Range: [0, 1]. • RSQ=1 means best fitting; • RSQ=0 means worse fitting;
Quantitative forecasting methods in library management
14
Evaluation of the trend analysis forecasting method
• Advantages: Simple to use (if using appropriate software) • Disadvantages: 1) not always applicable for the long-term time series (because there exist several ternds in such cases); 2) not applicable for seasonal and cyclic datta patterns.
Quantitative forecasting methods in library management 15
Part 2. Switch to Excel
Open a Workbook trend.xls, save it to your computer
Working with Excel
• Demonstration of the forecasting procedure using trend analysis method • Assignment 1. Repeating of the forecasting procedure with the same data • Assignment 2. Forecasting of the expenditure
Quantitative forecasting methods in library management
17
Using Excel to calculate linear trend
• Select a line on the diagram • Right click and select Add Trendline • Select a type of the trend (Linear)
Quantitative forecasting methods in library management
18
Part 3. Non-linear trends
Quantitative forecasting methods in library management
19
Non-linear trends
Excel provides easy calculation of the following trends
• • • •
Logarythmic Polynomial Power Exponential
Quantitative forecasting methods in library management
20
Logarithmic trend
12 10 8 6 4 2 0 0 y = 4,6613Ln(x) + 1,0724 R2 = 0,9963
2
4
6
8
Quantitative forecasting methods in library management
21
Trend (power)
10 8 6 4 2 0 0
y = 0,4826x1,5097 R2 = 0,9919
2
4
6
8
Quantitative forecasting methods in library management
22
Trend (exponential)
80 60 40 20 0 0 2 4 6 8 y = 0,0509e1,0055x R2 = 0,9808
Quantitative forecasting methods in library management
23
Trend (polynomial) 8 6 4 2 0 0 2 4 6 8 y = -0,1142x3 + 1,6316x2 - 5,9775x + 7,7564 R2 = 0,9975
Quantitative forecasting methods in library management
24
Choosing the trend that fitts best
• 1) Roughly: Visually, comparing the data pattern to the one of the 5 trends (linear, logarythmic, polynomial, power, exponential) • 2) In a detailed way: By means of the determination coefficient
Quantitative forecasting methods in library management
25
End