Switch to desktop Register Login

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 225

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Warning: Creating default object from empty value in /home4/cpc38/public_html/components/com_k2/models/item.php on line 494

Forecasting with the time series model

Rate this item
(0 votes)

Although this model looks slightly complicated, it can be easily modeled into Excel so you’ll find spreadsheets all ready for your own use in the template area.
The time series model relies on historical data and its principle is to plot the demand data on a graph over time that why it is called time series.

The example computations can be found in our download area to get full details.

So first we take the historical salesl data (more than a year!) and we plot in a chart according to the months. The method is first to find out the main components of the sales pattern in order to build the future forecast. In other word, we try first to “understand” the pattern before forecasting its basic components.

In the time series model, the demand pattern can be split into the three main following components:

  1. Seasonal variation (S): A demand variation that occurs due to a result of seasonal impacts. For example, beer sales are higher in the summer, or swimsuit sales are higher in spring and summer than rest of the year.
  2. Trend (T): the demand steadily increases or decreases over time, and its graph is a line.
  3. Random variations (V):  random variations are unpredictable but using past data and statistics, it can be estimated for the forecasting.

We could also add in the cyclical business pattern (technological pattern…) and irregular occurrences (financial crisis, 9/11…) as components but those are less easy to model and are rather built in an empiric way, reflecting the company knowledge.

How to proceed

There are two methods to build the forecast according to the above components:

  • The multiplication model:  Demand = T x S x V is the most common one
  • The additive model : Demand = T + S + V

We should first deseasonal the sales (in any) then use it to find out the trend, then finally compute the average random variation if you really want to be precise.
Then to forecast the next year, the focus is on the trend then the rest (seasonality and random variations) could be assumed as constant.

Decomposition example

So let’s try to find T, S and R in order to model our forecasts for next year.

For that, let’s use the quarterly sales (in unit) distribution below:

Clearly the quarterly sales are seasonal, we can see that Q4 is the most important one and second is Q2, the rest is low sales volume.

Removing the seasonal effect (S)

First we need to remove the seasonal effect before calculating the trend, so we need to compute the seasonal index (S) over each quarter.
We need to compute the seasonal index by dividing the actual sales for each quarter by the moving average. Then we calculate the average seasonal index for Q1, Q2, Q3 and Q4.
Here is the result:

Using a centered moving average of 6 months, here what I got:

This confirms the high demand pattern in Q4 and the very low demand pattern in Q1.

Estimating the trend (T)

We can now perform a regression analysis to found out the trend of the sales per quarter. This is could be done immediately trough Excel:

The trend coefficient here is 104.2 per quarter, which will be useful to forecast next year: [T = 104.2 x Quarters]


Estimating random variations (V)

Now we need to compute the random variations in order to integrate in the future forecasts.

We obtain them per quarter by dividing the actual sales with the trend multiplied by the average seasonal index.

The result should follow a normal distribution so we can get the standard deviation directly with Excel:

Se we should take into account the average random variation [1.031] and confidence interval if we really want to impress people.

Next year forecast

Finally let’s forecast next year sales in volume.
Let’s be optimistic and assume that the past trend will simply continue as is! (Easier to write than to do).

We use our decomposition to get the next year forecast as follow:

Here is the result:

Please check out here our templates developed under Excel used to have all models ready to your own use.

Last modified on Friday, 11 May 2012 08:13
More in this category: « Forecast time horizons
Login to post comments

© 2009-2012 Logisitik - All Rights Reserved

Top Desktop version