Forecasting using trend analysis

1、,I always avoid prophesying beforehand because it is much better to prophesy after the event has already taken place. -Winston Churchill,Quantitative forecasting methods in library management,2,Forecasting using trend analysis,Part 1. Theory Part 2. Using Excel: a demonstration. Assignment 1, 2,Quan

2、titative forecasting methods in library management,3,Learning objectives,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,To learn how:,Quantitative forecasting methods in library

3、 management,4,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 method

4、s in library management,5,Prerequisites: 1. Data pattern: Trend,Quantitative forecasting methods in library management,6,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 librar

5、y management,7,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 lin

6、ear relationship between the two variables,Quantitative forecasting methods in library management,8,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. T

7、rend 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 p

8、eriod into the future.,Quantitative forecasting methods in library management,9,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,10,Least Square Method,Least square method determin

9、es 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,11,The trend line is the “best-fit” line: an

10、example,Quantitative forecasting methods in library management,12,Statistical measures of goodness of fit,The Correlation Coefficient The Determination Coefficient,In trend analysis the following measures will be used:,Quantitative forecasting methods in library management,13,The Coefficient of Dete

11、rmination,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,14,Goo

12、dness of fitt: Determination Coefficient RSQ,Range: 0, 1. RSQ=1 means best fitting; RSQ=0 means worse fitting;,Quantitative forecasting methods in library management,15,Evaluation of the trend analysis forecasting method,Advantages: Simple to use (if using appropriate software) Disadvantages: 1) not

13、 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.,Part 2. Switch to Excel,Open a Workbook trend.xls, save it to your computer,Quantitative forecasting methods in library management,17,Working

14、 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,18,Using Excel to calculate linear trend,Sele

15、ct a line on the diagram Right click and select Add Trendline Select a type of the trend (Linear),Quantitative forecasting methods in library management,19,Part 3. Non-linear trends,Quantitative forecasting methods in library management,20,Non-linear trends,Logarythmic Polynomial Power Exponential,E

16、xcel provides easy calculation of the following trends,Quantitative forecasting methods in library management,21,Quantitative forecasting methods in library management,22,Quantitative forecasting methods in library management,23,Quantitative forecasting methods in library management,24,Quantitative forecasting methods in li