This paper seeks to examine historical sales revenue data to establish a forecast for 2005 and 2006. The historical data exhibit quarterly seasonality, which will be verified by plotting a scatter diagram of sales revenue against time. This scatter diagram, otherwise called a time series plot, further demonstrates the trend. The trend will be estimated through regression analysis. Errors that may arise from forecasting will be identified in addition to ways of mitigating those errors.
tailored to your instructions
for only $13.00 $11.05/page
The rationale for presenting this paper is to utilize available historical data to forecast sales for 2005 and 2006. Additionally, the seasonality of data and trend will be examined. This will be confirmed by drawing a time series plot.
The main objective of the case is to forecast sales revenue for 2005 and 2006. In the same line, the seasonality component of data and the trend will be examined. The possibility of errors resulting from the choice of forecasting technique utilized will be determined.
The initial step entails the establishment of a trend by least-squares techniques. This can be done manually or using regression statistical tool in excel. The next step is to use the trend line obtained in the first step to calculating the estimated sales revenue. Percentage variations between actual and estimates sales revenue are then calculated. The percentage variations are averaged with a view of finding average seasonal variations. Finally, the sales revenue forecast is calculated with the help of the trend line and the percentage average seasonal variations. All these calculations are done in Microsoft excel.
Time series plot
The time series plot above shows the seasonality of revenue and its trend. Seasonality is evident where revenue is low in a certain period and high in another period. These periodic fluctuations are regular. Their cyclical nature is confirmed by the repeated swings over the five years. The time series plot also shows an upward trend i.e. data is increasing over time.
The summary of regression output gives a trend line: Y=92.63X+375.18. (Wang and Chaman 25). This trend line means that sales revenue will change 92.63 times for each increment in period X. The regression output also shows an R square of 0.8726. This implies that 87.26% of the variations in sales revenue are explained by period, x.
as little as 3 hours
Table of seasonal variations seeks to highlight variations that are anticipated in the trend. Looking at the table below, the sales revenue of the first quarter will vary by 111% from the trend line. A similar observation is drawn from Q2, Q3, and Q4. It is expected that the total seasonal variation will be in the region of 400%. A variation from this value is however expected simply because of estimation done in the whole exercise.
To obtain seasonally adjusted forecast, trend estimate is multiplied with seasonal variation. This is tabulated on a table.
Seasonally adjusted forecast
|Year||Q||Period (x)||Sales (y)||Trend estimate||Seasonally adjusted forecast|
|2000||1||1||$684.20||$ 467.80||$ 521.40|
|2000||2||2||$584.10||$ 560.43||$ 458.93|
|2000||3||3||$765.40||$ 653.05||$ 673.90|
|2000||4||4||$892.30||$ 745.68||$ 823.09|
|2001||1||5||$885.40||$ 838.30||$ 934.36|
|2001||2||6||$677.00||$ 930.93||$ 762.34|
|2001||3||7||$1,006.60||$ 1,023.56||$ 1,056.24|
|2001||4||8||$1,122.10||$ 1,116.18||$ 1,232.05|
|2002||1||9||$1,163.40||$ 1,208.81||$ 1,347.32|
|2002||2||10||$993.20||$ 1,301.43||$ 1,065.74|
|2002||3||11||$1,312.50||$ 1,394.06||$ 1,438.57|
|2002||4||12||$1,545.30||$ 1,486.68||$ 1,641.02|
|2003||1||13||$1,596.20||$ 1,579.31||$ 1,760.27|
|2003||2||14||$1,260.40||$ 1,671.93||$ 1,369.14|
|2003||3||15||$1,735.20||$ 1,764.56||$ 1,820.90|
|2003||4||16||$2,029.70||$ 1,857.19||$ 2,049.99|
|2004||1||17||$2,107.80||$ 1,949.81||$ 2,173.23|
|2004||2||18||$1,650.30||$ 2,042.44||$ 1,672.55|
|2004||3||19||$2,304.40||$ 2,135.06||$ 2,203.23|
|2004||4||20||$2,639.40||$ 2,227.69||$ 2,458.95|
|2005||1||21||$ 2,320.31||$ 2,586.19|
|2005||2||22||$ 2,412.94||$ 1,975.95|
|2005||3||23||$ 2,505.57||$ 2,585.56|
|2005||4||24||$ 2,598.19||$ 2,867.92|
|2006||1||25||$ 2,690.82||$ 2,999.14|
|2006||2||26||$ 2,783.44||$ 2,279.36|
|2006||3||27||$ 2,876.07||$ 2,967.89|
|2006||4||28||$ 2,968.69||$ 3,276.88|
Forecasts for all quarters during the years 2005 and 2006 have been highlighted. The respective formulas applied in forecasting are available in an excel spreadsheet. Seasonality and trend in projected sales revenue are evident in the table. The highest projected revenue is in the fourth quarter. This observation is also confirmed by the time series plot of the forecast shown below.
It is expected that forecasted sales revenue will differ slightly from actual sales revenue. These differences are explained by random differences. Choosing a regression approach as opposed to a moving average approach also generates errors in forecasting. It is also important to note that people cannot be certain about the future thus predicted values might be different from the actual values. However, the errors can be minimized by choosing the best alternative i.e. with the least errors.
Sales revenue for all quarters in 2005 and 2006 were forecasted using historical sales revenue data for five years starting with 2000. To aid in understanding trend and seasonality, visual diagrams were drawn. The regression approach was mainly deployed in carrying out the forecasts.
Wang, George, and J. Chaman. Regression Analysis: Modeling and Forecasting, New York: Graceway Publishing Company, 2003. Print.