Chapter 2 and 3
1 / 50

Chapter 2 and 3 - PowerPoint PPT Presentation

  • Uploaded on

Chapter 2 and 3. Forecasting Advanced Forecasting. Operations Analysis Using MS Excel. Forecasting. Forecasting is the process of extrapolating the past into the future

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' Chapter 2 and 3' - yaakov

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 2 and 3

Chapter 2 and 3


Advanced Forecasting

Operations Analysis Using MS Excel


Forecasting is the process of extrapolating the past into the future

Forecasting is something that organization have to do if they are to plan for future. Many forecasts attempt to use past data in order to identify short, medium or long term trends, and to use these patterns to project the current position into the future.

Backcasting: method of evaluating forecasting techniques by applying them to historical data and comparing the forecast to the actual data.


Why Forecasting?

Some Characteristics of Forecasts

  • Forecasts are seldom (hardly) perfect

  • Product family and aggregated forecasts are more accurate than individual product forecasts

    Assumptions of Forecasting Models

  • Information (data) about the past should be available

  • The pattern of the past will continue into the future

Steps to forecasting
Steps to Forecasting

  • Starts with gathering and recording information about the situation.

  • Enter the data into a worksheet or any other business analysis tool

  • Creation of graphs

  • Examine the data and the graphs visually to get some understanding of the situation (judgmental phase)

  • Developing hypotheses and models

  • Try for alternative forecasting approaches and do ‘what-if’ analysis to check if the resulting forecast fits the data

Evaluation of Forecasting Model

To judge how well a forecasting model fit the past observation, both precision and bias must be considered.

Measuring the precision of a forecasting model:

There are three possible measures used to evaluate precision of forecasting systems, each of them is based on the error or deviation between the forecasted and actual values: MAD, MSE, MAPE

Evaluation of Forecasting Model

Mean Absolute Deviation - MAD

No direct Excel function to calculate MAD

Excel: =ABS(AVERAGE (error range))

Evaluation of forecasting model
Evaluation of Forecasting Model

Mean Square Error - MSE

Excel: =SQRT(SUM(error range)/COUNT(error range))

----------------------- Student activity --------------------------

Evaluation of Forecasting Model

Mean Absolute Percentage Error - MAPE

----------------------- Student activity --------------------------

Which of the measure of forecast accuracy should be used?

  • The most popular measures are MAD and MSE.

  • The problem with the MAD is that it varies according to how big the number are.

  • MSE is preferred because it is supported by theory, and because of its computational efficiency.

  • MAPE is not often used.

  • In general, the lower the error measure (BIAS, MAD, MSE) or the higher the R2, the better the forecasting model

Good Fit – Bad Forecast

As it was discussed previously that neither MAD nor MSE gives an accurate indication of the validity of a forecast model. Thus, judgment must be used.

Raw data sample should always be subjected to managerial judgment and analysis before formal quantitative techniques can be applied.

a- Outlier

Outlier may result from simple data entry errors. or sometime the data may be correct but can be considered as atypical observed values.

Outlier may occur for example in time periods when the product was just introduced or about to be phased out.

So experienced analyst are well aware that raw data sample may not be clear.

Demand data with an outlier


b- Causal data adjustment

Cause-and-effect relationshipsshould be examined before applying any quantitative analysis on the historical data sample.

Examples of causes that may affect the patterns in data sample :

1- The data sample before a particular year may not be applicable because:

- Economic conditions have changed

- The product line was changed

2- Data for a particular year may not be applicable because:

- There was an extraordinary marketing effort

- A natural disaster prevented demand from occurring

c- Illusory (misleading) patterns

The meaning of a “good fit” is subjective to the manager’s interpretation of the forecasting model.

So before a forecast is accepted for action, quantitative techniques must be augmented by such judgmental approaches as decision conferencing and expert consultations.

To prepare a valid forecast, the following factors that influence the forecasting model should be examined:

  • Company actions

  • Competitors actions

  • Industry demand

  • Market share

  • Company sales

  • Company costs

  • Environmental factors

Forecasting Approaches influence the forecasting model should be examined:

1-Qualitative Forecasting

Forecasting based on experience, judgment, and knowledge. Used when situation is vague and little data exists. Example: new products and new technology

2- Quantitative Forecasting

Forecasting based on data and models. Used when situation is ‘stable’ and historical data exist. Example: existing product, current technology

Quantitative models influence the forecasting model should be examined:

Market survey

Expert opinion

Decision conferencing

Data cleaning

Data adjustment

Environmental factors

Forecasting Approaches


Time Series


Moving average


Curve fitting

Exponential smoothing


Trend projection

Seasonal indexes

Quantitative forecasting
Quantitative Forecasting influence the forecasting model should be examined:

Time Series Models:

Casual Models:





Year 2000


Time Series








Year 2000


Time series forecasting
Time Series Forecasting influence the forecasting model should be examined:

Is based on the hypothesis that the future can be predicted by analyzing historical data samples.

  • Assumes that factors influencing past and present will continue influence in future.

  • Obtained by observing response variable at regular time periods.

Time series model influence the forecasting model should be examined:

The Time series model can be also classified as

Forecasting directly from the data value

  • Moving average

  • Weighted moving average

  • Exponential smoothing

    Forecasting by identifying patterns in the past

  • Trend projection

  • Seasonal influences

  • Cyclical influences

Forecasting directly from the data value influence the forecasting model should be examined:

1- Moving Average Method

  • The forecast is the mean of the last n observation. The choice of n is up to the manager making the forecast

  • If n is too large then the forecast is slow to respond to change

  • If n is too small then the forecast will be over-influenced by chance variations

  • This approach can be used where a large number of forecasting needed to be made quickly, for example in a stock control system where next week’s demand for every item needs to be forecast

Demand influence the forecasting model should be examined:


Longer-period moving averages (larger n) react to actual changes more slowly

----------------------- Student activity --------------------------

2- Weighted Moving Average changes more slowly

When using a moving average method described before, each of the observations used to compute the forecasted value is weighted equally.

In certain cases, it might be beneficial to put more weight on the observations that are closer to the time period being forecast. When this is done, this is known as a weighted moving average technique. The weights in a weighted MA must sum to 1.

Weighted MA(3) = Ft+1 = wt1(Dt) + wt2(Dt-1) + wt3(Dt-2)

----------------------- Student activity --------------------------

2- Weighted Moving Average changes more slowly

n = 3

F4 = ((w1* d1)+(w2 * d2)+ (w3 * d3))/(w1 + w2 + w3)

Where w1, w2, w3 are weights and d1, d2 & d3 are demands.

Many books on forecasting state that the sum of weights (w1+w2+w3) must be equal to 1.

----------------------- Student activity --------------------------

3- Exponential Smoothing changes more slowly

  • The exponential smoothing techniques gives weight to all past observations, in such a way that the most recent observation has the most influence on the forecast, and the older observation always has the less influence on the forecast.

  • It is only necessary to store two values the last actual observation and the last forecast.

  • Smoothing constant () is the proportion of the difference between the actual value and the forecast.

  • The value of the smoothing constant () is needed to be included in the model in order to make the next period’s forecast.

Exponential Smoothing can be calculated

using the following formula:

F2 = *D1 +(1- )*F1

3- Exponential Smoothing changes more slowly

  • Smoothing constant () must set between 0 and 1. Normally the value of the smoothing constant is chosen to lie in the range 0.1 to 0.3.

  • Typically, a value closer to 0 is used for forecasting demand that is changing slowly, however, value closer to 1 is used for forecasting demand that is changing more rapidly.

  • There is no way to calculate F1 because each forecast is based on the previous forecasts.

3- Exponential Smoothing changes more slowly

  • How to select smoothing constant 

  • Sensitivity analysis is an analysis used to test how sensitive the the forecast is to the change in alpha or smoothing constant.

  • A general rule for selecting alpha is to perform scenario analysis and pick the value that produces a reasonable value for the MAD and a forecast that is reasonably close to the actual demand.

4- Trend – Adjusted Exponential Smoothing changes more slowly

With trend-adjusted exponential smoothing, the trend is calculated and included in the forecast. This allows the forecast to be smoothed without losing the trend.

Trend-adjusted exponential smoothing requires two parameters: the alpha value used by exponential smoothing and the beta value used to control how the trend component enters the model. Both values must be between 0 and 1.

Fit1= F1 + T1

The formula to calculate the forecast component is :

F2 = Fit1+ *(D1-Fit1)

The formula to calculate the trend component is

T2 = T1 +  *  *(D1-Fit1)

  • Time series data are usually considered to consist of six component :

  • Average demand:is simply the long-term mean demand

  • Trend component : long term overall up or down movement. Changes due to population, technology, age, culture, etc. Typically several years duration.

  • Autocorrelation: is simply a statement that demand next period is related to demand this period

  • Seasonal component: periodic pattern of up and down fluctuations repeating every year. It is that portion of demand that follows a short-term pattern. Occurs within a single year

  • Cyclical component: is much like the seasonal component, only its period is much longer. Affected by business cycle, political, and economic factors.

  • Random component: random movements that follow no pattern. Due to unforeseen events. Short duration and non-repeating

Components of a time series model
Components of A Time Series Model component :









Trend with

seasonal pattern




Forecasting by identifying patterns in the past component :

Cyclical and Seasonal Issues

Seasonal Decomposition of Time Series Data

There are two types of seasonal variation:

Additive seasonal variation :

Occurs when the seasonal effects are the same regardless of the trend.

Multiplication seasonal variation :

Occurs when the seasonal effects vary with the trend effects. It’s the most common type of seasonal variation

Cyclical and Seasonal Issues component :

Computing Multiplicative Seasonal Indices

  • Computing seasonal indices requires data that match the seasonal period. If the seasonal period is monthly, then monthly data are required. A quarterly seasonal period requires quarterly data.

  • Calculate the centered moving averages (CMAs) whose length matches the seasonal cycle. The seasonal cycle is the time required for one cycle to be completed. Quarterly seasonality requires a 4-period moving average, monthly seasonality requires a 12-period moving average and so on.

  • Determine the Seasonal-Irregular Factors or components. This can be done by dividing the raw data by the corresponding depersonalized value.

  • Determine the average seasonal factors. In this step the random and cyclical components will be eliminated by averaging them.

  • Estimate next year’s total demand

  • Divide this estimate of total demand by the number of seasons, then multiply it by the seasonal index for that season

Cyclical and Seasonal Issues component :

Computing Multiplicative Seasonal Indices

Step 1

Step 4

Step 2


Step 3

= B3/C3

Cyclical and Seasonal Issues component :

Using Seasonal Indices to Forecast

To forecast using seasonal indices

1- Compute the forecast using annual values. Any forecasting techniques can be used.

2- Use the seasonal indices to share out the annual forecast by periods

Cause and effect relationships
Cause-and-Effect Relationships component :

  • Causal forecasting seeks to identify specific cause-effect relationships that will influence the pattern of future data. Causes appear as independent variables, and effects as dependent, response variables in forecasting models.

    • Independent variable Dependent, response variable

    • Price demand

    • Decrease in population decrease in demand

    • Number of teenager demand for jeans

  • Causal relationships exist even when there is no specific time series aspect involved.

  • The most common technique used in causal modeling is least squares regression.

Linear trend analysis
Linear component :Trend analysis

It is noticed from this figure that there is a growth trend influencing the demand, which should be extrapolated into the future.

Linear component :Trend analysis

The linear trend model or sloping line rather than horizontal line. The forecasting equation for the linear trend model is

Y = +X or Y = a + bX

Where X is the time index (independent variable). The parameters alpha and beta ( a and b) (the “intercept” and “slope” of the trend line) are usually estimated via a simple regression in which Y is the dependent variable and the time index t is the independent variable.

Linear component :Trend analysis

Using a data table (what if analysis ) to determine the best-fitting straight line with the lowest MSE

Linear component :Trend analysis

Simple Linear Regression Analysis

Regression analysis is a statistical method of taking one or more variable called independent or predictor variable- and developing a mathematical equation that show how they relate to the value of a single variable- called the dependent variable.

Regression analysis appliesleast-squares analysisto find the best-fitting line, where best is defined asminimizing the mean square error (MSE)between the historical sample and the calculated forecast.

Regression analysis is one of the tools provided by Excel.

Linear component :Trend analysis

Multiple Linear Regression Analysis

Simple linear regression analysis use one variable (quarter number) as the independent variable in order to predict the future value. In many situations, it is advantageous to use more than one independent variable in a forecast.

Multiple Linear Regression Analysis component :

Two factors that control the frequency of breakdown. So they are the independent variables.

Y = a + bX1 + cX2


Slope 1


Linear component :Trend analysis

Quadratic Regression Analysis

Quadratic regression analysis fits a second-order curve of the form

Y = a + bX + cX2

Quadratic regression is prepared by adding thesquared value of the time periods. The coefficients in the quadratic formula are calculated again using regression, wheretime periods and the squared time periodsare theindependent variablesand thedemandremains thedependent variable.