Forecasting Methods
Read Problem 6 in Chapter 6 of your textbook. Calculate and answer parts a through d. Include all calculations and spreadsheets in your post. Explain why the moving average method was used instead of another forecasting method. What might be another forecasting method that could prove to be just as useful? Your initial post should be 200-250 words.
Guided Response: Respond to at least two of your classmates’ posts to identify some of their recommended forecasting methods. Give additional advice and alternative solutions that might be used as well.
——————————
1. Rebekah.
a. Calculate a 5-year moving average to forecast the number of mergers for 2012.
To find the solution for a. one needs to find the moving average which is found by using the average mergers from 2007-2011 (Vonderembse & White, 2013). It can be calculated in Excel by taking the number of mergers note in the attached document as B9:B13/5 for a result of 174.2.
b. Use the moving average technique to determine the forecast for 2005 to 2011. Calculate measurement error using MSE and MAD.
The response to this aspect is more complicated as one needs to find the forecast, which can be to use the 5 years before and average to find the forecast (Vonderembse & White, 2013). Then one would subtract the actual mergers from the forecast to get the error rate (Vonderembse & White, 2013). The absolute deviation is taking the error rate and removing the positive or negative notation, which is not applicable in the sample problem as all forecasts are positive. The squared error is found by taking the absolute error to the second power, or X^2 (Vonderembse & White, 2013). The Mean Absolute Deviation (MAD) and Mean Squared Error (MSE) are now easy to find as one calculates for the average of the found SE and AD. The MSE is shown as 5,378 and MAD as 61.45.
c. Calculate a 5-year weighted moving average to forecast the number of mergers for 2012. Use weights of 0.10, 0.15, 0.20, 0.25, and 0.30, with the most recent year, weighted being the largest.
The moving weighted average takes the number of mergers then multiplied by the given weights to find the weighted average (Vonderembse & White, 2013).
2011 0.30*240=72
2010 0.25*252=56.25
2009 0.20*186=37.2
2008 0.15*97=14.55
2007 0.10*123=12.3
d. Use regression analysis to forecast the number of mergers in 2012
To find the regression, one can use Excel to create a scatter plot and then find the linear correlation (Vonderembse & White, 2013). When this is graphed in Excel, one can find the formula of the line, which is noted as y = 17.734x – 35460. Given what is shown in the graph, it would appear from the regression that the trend shows a strong positive correlation (Vonderembse & White, 2013). Based on the graph, one can estimate the mergers in 2012 would be 248.
Moving Average and Other Methods to Forecast
When considering why the moving average model is used, one can see that it shows what is expected to see based on past performance. Simple moving average smooths out the outliers, as it can be used to find the overall trend to predict what may occur in the future (Vonderembse & White, 2013). One may trust the average moving method as it is statistically based on what can be seen as fact. The variation would be when the actual is found, and the error rate is found to see how far off one was from the forecast. One method that may be better for forecasting can be the exponential smoothing as it is a variation of the weighted moving but uses more recent data to continue to evolve based on the results. (Vonderembse & White, 2013). As all the methods are trying to predict something that is dependent on many other factors, they all have value in business assessment. It may be best to try multiple methods to find which seem to produce the most accurate result given the business that the methods are used.
Reference
Vonderembse, M. A., & White, G. P. (2013). Operations management. Retrieved from https://content.ashford.edu/
Year
Mergers
B. Forecast
Error Rate
Absolute Deviation
MSE
Weighted Moving Average
Formula
2000
46
B9:B13/5
2001
46
2002
62
2003
45
2004
64
2005
61
52.6
8.4
8.4
70.56
2006
83
55.6
27.4
27.4
750.76
2007
123
63
60
60
3600
12.3
0.10*B9
2008
97
75.2
21.8
21.8
475.24
14.55
0.15*B10
2009
186
85.6
100.4
100.4
10080.16
37.2
0.20*B11
2010
225
110
115
115
13225
56.25
0.25*B12
2011
240
142.8
97.2
97.2
9447.84
72
0.30*B13
A.
Moving Average 2012
B9:B13/5
174.2
174.2
B9:B13/5
B.
Noted in C7:C13
Error=B7-C7
AD=ABS(error)
MSE=#^2
MAD=Average (E7:E13)
61.45714286
MSE=Average (F7:F13)
5378.508571
C.
Weighted Moving Average For 2012
192.3
SUM B9:B113
————————-
2. Ramanjeet.
Note: Please refer attached excel for the data tables and detailed calculations.
A. Calculate a 5-year moving average to forecast the number of mergers for 2012.
To calculate the 5-year moving average, sum the data for the most recent 5 years and divide by 5
Estimated Mergers for 2012 = (123+97+186+225+240)/5
= 174.2
~ 174 Mergers in 2012
B. Use the moving average technique to determine the forecast for 2005 to 2011. Calculate measurement error using MSE and MAD.
Error in the forecast is the difference between the actual value and the forecasted value.
Mean Squared Error (MSE) is the average of all the squared errors
MSE for 2005 to 2011
= (70.56+750.76+3600+475.24+10080.16+13225+9447.84)/7
= 5378.51
Mean Absolute Deviation (MAD) is the average of the error values after positive and negative signs are removed from the error values.
MAD for 2005 to 2011
= (8.4+27.4+60+21.8+100.4+115+97.2)/7
= 61.46
C. Calculate a 5-year weighted moving average to forecast the number of mergers for 2012. Use weights of 0.10, 0.15, 0.20, 0.25, and 0.30, with the most recent year weighted being the largest.
Estimated mergers for 2012 using a 5-year weighted moving average is the sum of weighted values over the last 5 years, with the most recent period weighted more heavily, being the closest representative of the future (Vonderembse & White, 2013).
Estimated Mergers for 2012, using weighted moving average
= 0.3*240 + 0.25*225 + 0.2*186 + 0.15*97 + 0.1*123
= 192.3
D. Use regression analysis to forecast the number of mergers in 2012.
Regression analysis is a method to predict the value of one variable (the dependent variable) based upon the value of another variable (the independent variable)
The regression equation is described as below:
Y = a + b(X)
Y is the dependent variable
X is the independent variable
n is the number of data points in the sample
r is the coefficient of correlation that measures the strength of the
relationship between the variables (Vonderembse & White, 2013).
From the calculations, as described in the Attached excel, the linear regression equation is
Y = -8.75 + 17.73 (X)
Since for the simplicity of calculations, the years were coded as continuous series of numbers, 2012 will be represented by number 13.
Thus, Approx. Mergers in 2012(Y) = -8.75 + 17.73*13 = 222 (approx.)
Justification for Moving Average Method
One of the simplest methods of forecasting is to refer to the most recent value and make an approximate forecast based on it. However, this methodology could introduce significant bias in the forecast if any one of the past values was based on some temporary data trend or sharp fluctuation (Vonderembse & White, 2013). To minimize the impact of temporary data fluctuations, the moving average concept is used that forecasts the next period based on the average of the recent periods. The technique is called a moving average because with each forecasting iteration, the most period is added, and the oldest period value is subtracted based on the number of periods used in the calculation.
An alternative technique could be Exponential smoothing, which is a variation of moving average with continuously revising an estimate to include the most recent value (Vonderembse & White, 2013). In other words, unlike standard moving average where the oldest period data is dropped and newest added for the calculation of forecast, in the exponential moving average, data elements are never dropped from the calculations. In fact, even the oldest data contributes to future values, with its impact decreasing exponentially over time. Thus, instead of abruptly dropping any spiked data point, the exponential moving average gradually soothes it out.
References
Vonderembse, M. A., & White, G. P. (2013). Operations management. Retrieved from https://content.ashford.edu/
Mergers data in savings and loan industry over 12 years
Years
Mergers
2000
46
2001
46
2002
62
2003
45
2004
64
2005
61
2006
83
2007
123
2008
97
2009
186
2010
225
2011
240
Question1: Calculate a 5-year moving average to forecast the number of mergers for 2012
To calculate the 5-year moving average, sum the data for most recent 5 years and divide by 5
Estimated Mergers for 2012 = SUM (C10: C14)/5 =
174.2
Question2: Use the moving average technique to determine the forecast for 2005 to 2011. Calculate measurement error using MSE and MAD
Years
Mergers
Forecasted Mergers
Error
Squared Error
2000
46
2001
46
2002
62
2003
45
2004
64
2005
61
52.6
8.4
70.56
2006
83
55.6
27.4
750.76
2007
123
63
60
3600
2008
97
75.2
21.8
475.24
2009
186
85.6
100.4
10080.16
2010
225
110
115
13225
2011
240
142.8
97.2
9447.84
Mean Squared Error (MSE) is the average of all the squared errors
MSE (2005-2011) =
SUM (F28:F34)/7 =
5378.51
Mean Absolute Deviation (MAD) is average of the error values after positive and negative signs are removed from the error values
MAD (2005-2011) =
{SUM(ABS(E28:E34))}/7 =
61.46
Question3: Calculate a 5-year weighted moving average to forecast the number of mergers for 2012. Use weights of 0.10, 0.15, 0.20, 0.25, and 0.30, with the most recent year weighted being the largest.
Years
Mergers
Weight
Weighted Forecast
2007
123
0.1
12.3
2008
97
0.15
14.55
2009
186
0.2
37.2
2010
225
0.25
56.25
2011
240
0.3
72
Estimated mergers for 2012 using 5-year weighted moving average is the sum of weiighted values over the last 5 years, with most recent period weighted more heavily, being the closest representative of future
Estimated Mergers for 2012, using weighted moving average =
SUM (E44:E48) =
192.3
Question4: Use regression analysis to forecast the number of mergers in 2012
Years
Coded Val. (X)
Mergers (Y)
XY
X^2
Y^2
2000
1
46
46
1
2116
2001
2
46
92
4
2116
2002
3
62
186
9
3844
2003
4
45
180
16
2025
2004
5
64
320
25
4096
2005
6
61
366
36
3721
2006
7
83
581
49
6889
2007
8
123
984
64
15129
2008
9
97
873
81
9409
2009
10
186
1860
100
34596
2010
11
225
2475
121
50625
2011
12
240
2880
144
57600
Sum
78
1278
10843
650
192166
Regression analysis is a method to predict the value of one variable (the dependent variable) based upon the value of other variable (the independent variable)
Y = a + b(X)
Y is the dependent variable
X is the independent variable
n is the number of data points in the sample
r is the coefficient of correlation that measures the strength of the relationship between the variables
b =
17.73
a =
-8.75
r =
0.9
Thus, regression equation is
Y = -8.75 + 17.73 (X)
For 2012 estimate, X = 13
Number of Mergers in 2012 = -8.75 + 17.73*13 =
221.74
222 Mergers (approx.)
—————————
Required Resources
Text
Vonderembse, M. A., & White, G. P. (2013). Operations management [Electronic version]. Retrieved from https://content.ashford.edu/
• Chapter 5: Supply Chain Management: A Strategic Perspective
• Chapter 6: Models and Forecasting
Recommended Resources
Article
Terreri, A. (2007). Who’s minding the yard? Food Logistics, (97), 24-30. Retrieved from the ProQuest database.
Multimedia
July, E. (Producer) & Rodrigo, J. M. (Director). (2003). Business is blooming: The international floral industry (Links to an external site.) [Video file]. Retrieved from the Films On Demand database.
• Watch the following segment:
o Ecuadorian Roses for Valentine’s Day
https://fod.infobase.com/p_ViewVideo.aspx?xtid=34961
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."
