Custom Search
Home Excel VBA Macro's Data Analysis Data Mining Forecasting Reporting About Me Contact Me

Tutorial:

SQL
VBA

Home >> Forecasting

Forecasting:

Before you do forecasting you need to keep one thing in mind that you predicting some value. That means your finding can not be exactly as actual values or some times may be. But you intention should always to get most closest value to actual value.

Trend Analysis and Seaonality
Steps in Trend Analysis and Seasonality:

1. Calculate Seasonal Index
2. Find trends in given data
3. Determine equation that best repressent the existing data(by comparing the actual values and value given by equation).
4. Use choosen equation for forecast. 5. Incorporate seasonal index into trends.

Suppose your organization sell some product in the market and your manager is interested to know about sales figures of next three months. That means how much revenue organization can generate from that product. You have previous data that shows how much revenue that product had generated in the past. You can create a chart in excel to view the sale trends and to make prediction about next quater(3 months). Trend lines helps to represent the sale trends in a graphical form using linear or non-linear regression with time. Trend can weekly, monthly, quaterly depends upon organizational needs. When we measure the trends for a particular period we can't ignore the the seasonality effect. Seasonality effect is the effect cause by season on the sale of a particular product. By analyze seasonality effect we can determine the difference between grand mean(for example 1 year) and periodic mean(lets have 2nd quater of choosen year).

So the first step is measure seasonal index for given data. Suppose we have following data.
Month Sale
1 11,948
2 12,888
3 13,867
4 15,464
5 16,463
6 17,986
7 18,936
8 20,324
9 21,454
10 22,833
11 21,433
12 23,644

The following formula is used to measure the seasonal index:

S(i)=D(i)/D

Where:
S(i) is the seasonal index for ith period.
D(i) is the average or mean of ith period(lets say 2nd quater mean)
D is the overall mean(lets say mean value for 1 year)

The following figure shows the calculated seasonal index for each quater.

The following figure shows how to display data graphically with Polynominal trend lines. With the help of R-square value we can predicted the sale figures for next quater. So far we have not consider the seasonality effect. Thus predicted value is not acctually correctly predicted. The reason we choose here polynominal trend line is the value shown corresponding to polynominal trendline in the following figure. If R-square value is close to 1, predication has more chance to be accurate.


So with the help of Equation y=-33.57x2+1523.1x+10021 we predicated following values(without considering the effect of seasonality)
MonthSale
13-Jan24,147.97
14-Feb24,764.68
15-Mar25,314.25

You can use VBA language to create the chart and to draw trend lines.

The following VBA Code will create Polynominal trendline into chart. 




Private Sub CreateChart_Click()

  Dim chartobj As Chart 'create chart varibale
  Set chartobj = Charts.Add 'set chart variable to new chart
  Set chartobj = chartobj.Location(where:=xlLocationAutomatic, Name:="Forecasting") 'set location of the chart


    With chartobj
      .ChartType = xlColumnClustered
      .SetSourceData Source:=Sheets("Forecasting").Range("a1:b24"), PlotBy:=xlColumns
 
      .HasTitle = True
      .Legend.Position = xlLegendPositionBottom
      .ChartTitle.Text = "Sale Trends"
      .Parent.Top = Range("d11").Top 'used to specify that chart must be start from 9 row not before that
      .Parent.Left = Range("d11").Left 'used to specify thatc hart must be start from d column not before that
      .Parent.Name = "Sale Trends"
   End With

End Sub




Private Sub FormatChart_Click()
  
  ActiveSheet.ChartObjects("Sale Trends").RoundedCorners = True   'to round the corrner of the chart
  ActiveSheet.ChartObjects("Sale Trends").Shadow = True
  ActiveSheet.ChartObjects("Sale Trends").Activate              'to activate the chart
  ActiveSheet.ChartObjects("Sale Trends").Chart.Type = xlLine     'to chage the chart type to line type

    With Sheets("Forecasting").ChartObjects("Sale Trends").Chart
      .ChartArea.Font.Name = "Tahoma"
      .ChartArea.Font.FontStyle = "Regular"
      .ChartArea.Font.Size = 8
      .PlotArea.Interior.ColorIndex = xlNone
      .HasLegend = True
      .Legend.Position = xlLegendPositionBottom

    End With

End Sub



Private Sub ViewTrends_Click()

   ActiveSheet.ChartObjects("Sale Trends").Activate
   ActiveChart.PlotArea.Select
   ActiveChart.SeriesCollection(2).Trendlines.Add(Type:=xlLinear, Forward:=0, _
   Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select

End Sub

Now we can consider the seasonal effect to make better prediction. That mean we can use the seasonal index(calculated above) to predict the values for the next quater. In the following figure you can see the total income for 1st quater for the current year and total(predicted with the help of equation comes from trend analysis)for the next year.

To make this figure accurate we can simply multiply the predicted total with seasonal index of the current year(1st quater). Now you can see the difference between two values 74226.9 - 52896.38 = 21330.51. So now prediction looks bit more realistic.

Forecasting

Go to Top