Question

PLEASE SOLVE USING EXCEL. PLEASE INCLUDE STEP BY STEP ON HOW TO SOLVE THIS QUESTION ON EXCEL.. THANK YOU!!!!

1. DEVELOP SPREADSHEET MODELS FOR FORECASTING ATTENDANCE USING SINGLE MOVING AVERAGE AND SINGLE EXPONENTIAL SMOOTHING. (completed)

2. Using MAD, MSE, AND MAPE as guidance, find the best number of moving average periods and best smoothing consant for exponential smoothing.

PLEASE USE EXCEL TO SOLVE THIS QUESTION

N5 0San Francisco Giants (1958-1967 seasons) 4 Year Attendance1 Giants plus Oakland A's after A's move to Oakland (1968-1978 seasons) 5 1958 6 1959 7 1960 8 1961 91962 10 1963 11 1964 12 196.5 13 1966 14 1967 15 1968 16 1969 17 1970 18 1971 19 1972 20 1973 21 1974 22 1975 23 1976 24 1977 25 1978 1273 1422 1795 1391 1593 1571 1504 1546 1657 1242 1674 1652 1519 2021 1569 1835 1366 1601 1408 1196 2267 1273 0 1496.67 1377.3 1536 1669.69 1593 1474.61 0 1518.33 1557.48 15561566.94 0 1540.33 1522.88 15691539.07 0 1481.67 1621.62 1524.33 1355.89 11522.67 1578.57 11615 1629.97 11730.67 1552.29 1703 1880.39 11808.33 1662.42 11590 1783.22 1600.67 1491.17 11458.33 1568.05 1 1401.67 1456.02 1274 1 1623.67

Answer 1

(2)

Formulas used:

$1.\ MAD=\frac{\sum |X-F|}{N}$

$2.\ MSE=\frac{\sum (X-F)^{2}}{N}$

$3.\ MAPE=\frac{\sum |\frac{X-F}{X}|}{N}\times 100$

Compute MAD using excel as follows:

Step 1: Enter the data in excel.

Step 2: Compute the difference between 'Attendance' and 'Forecast'.

Step 3: Find the absolute of the differences using '=ABS' function.

Step 4: Compute the sum of the absolute values of differences and divide it by number of terms to obtain the solution as follows:

Total 6229.22 MAD 296.63

Compute MSE using excel as follows:

Step 1: Enter the data in excel.

Step 2: Compute the difference between 'Attendance' and 'Forecast'.

Step 3: Find the square of the differences.

Step 4: Compute the sum of the square of differences and divide it by number of terms to obtain the solution as follows:

Total 6229.22 3802332.7 MAD 296.63 MSE 181063.5

Compute MAPE using excel as follows:

Step 1: Enter the data in excel.

Step 2: Compute the difference between 'Attendance' and 'Forecast'.

Step 3: Divide the differences by respective 'Attendance' values.

Step 4: Find the absolute values of the ratio using '=ABS' function.

Step 5: Compute the sum of the ratio of absolute values of differences and respective 'Attendance' values and divide it by number of terms. Now, multiply the result with 100 to obtain the solution as follows:

Total 6229.22 3802332.7 4.02949 296.63 MAD MSE MAPE 19.18805 181063.5

Conclusion:

MAD = 296.63

MSE = 181063.5

MAPE = 19.18805