Answered Essay: Show your work! For this assignment IHIGHLY RECOMMEND working in groups up to four people and using Excel to do the forecasting spreadsheet program)
Show your work! For this assignment IHIGHLY RECOMMEND working in groups up to four people and using Excel to do the forecasting spreadsheet program), print out the sheet of results, as well as a sheet containing the formulas that you used (click Formulas->Formula Auditing> Show Formulas). Excel formatting can sometimes be difficult, and because of that there will be 5 points for “professionalism” on this assignment. problem below. If you choose to use an Excel spreadsheet (or another I. For this forecasting assignment you are going to compare linear regression against exponential smoothing for the data below. The data is monthly demand for the past 12 months. Demand 1000 1050 975 1022 1055 1098 1100 1200 1095 1150 1220 1175 Month 4 7 12 (5 points) What is the linear regression equation using the data from the last I2 months? (use 3 decimals for slope and intercept) a)
Expert Answer
Answer to Question a :
We insert the values of Month and demand in two different columns in excel.
Let, the Linear regression equation :
Y = a + b.X
Y = Value of demand
X = Month
.a and b are constants
Then,
We apply the formula ,
Linest ( ) in excel and obtain following values :
.a = 19.167
.b = 970.409
Accordingly, we obtain linear regression equation :
Y = 19.167 + 970.409.X
Answer to question b and d :
Refer below table .
Using above formula , forecasted values of month 1 to 12 are presented below .
For each data , Absolute Deviation is calculated as Absolute Value of difference between Forecasted value and Actual Value.
Mean Absolute deviation ( MAD ) is calculated as
= Sum of all deviations / 12 ( i.e. number of observations)
Accordingly , MAD = 29.833
| Month | Actual Value | Forecast value | Absolute deviation |
| 1 | 1000 | 990 | 10 |
| 2 | 1050 | 1009 | 41 |
| 3 | 975 | 1028 | 53 |
| 4 | 1022 | 1047 | 25 |
| 5 | 1055 | 1066 | 11 |
| 6 | 1098 | 1085 | 13 |
| 7 | 1100 | 1105 | 5 |
| 8 | 1200 | 1124 | 76 |
| 9 | 1095 | 1043 | 48 |
| 10 | 1150 | 1162 | 12 |
| 11 | 1220 | 1181 | 39 |
| 12 | 1175 | 1200 | 25 |
| MAD = | 29.833 | ||
Answer to question c and d:
Formula for exponential smoothing as follows :
Ft = alpha x At + ( 1 – alpha ) x Ft-1
Ft = forecast for period t
Ft-1 = Forecast for period t-1
At = Actual value for period t-1
Alpha = smoothing factor = 0.25
Therefore,
Ft = 0.25.At-1 + 0.75.Ft-1
Using above formula , we obtained forecasted value against each month which are presented in below table :
| Month | Actual Value | Forecast value | Absolute deviation |
| 1 | 1000 | 1000 | 0 |
| 2 | 1050 | 1000 | 50 |
| 3 | 975 | 1013 | 38 |
| 4 | 1022 | 1003 | 19 |
| 5 | 1055 | 1008 | 47 |
| 6 | 1098 | 1020 | 78 |
| 7 | 1100 | 1039 | 61 |
| 8 | 1200 | 1054 | 146 |
| 9 | 1095 | 1091 | 4 |
| 10 | 1150 | 1092 | 58 |
| 11 | 1220 | 1106 | 114 |
| 12 | 1175 | 1135 | 40 |
| MAD = | 54.583 | ||
Next step is to calculate Absolute Deviation against each data and
Absolute deviation = Difference ( Actual Value – Forecast Value )
Mean Absolute Deviation ( MAD ) is calculated as = Sum of absolute deviations / 12 ( number of data)
Accordingly Mean Absolute Deviation ( MAD) = 54.583
Answer to question e:
The forecast which gives minimum error ( i.e. difference between actual and forecasted values) is considered to be the best and most accurate forecast since such forecasts best represents the actual values. The measure of error in this case is MAD. Therefore , the forecast which gives minimum MAD should be accepted as most appropriate forecast.
In the given case MAD of 29.833 basis linear regression equation is much less than MAD of 54.583 derived basis exponential smoothing function.
Therefore , we recommend to move forward with Linear Regression equation model
| RECOMMENDATION : LINEAR REGRESSION EQUATION MODEL |
