e) Use SP500 returns from 1/4/16 to 10/3/16 and your estimated results to foreca
ID: 3363296 • Letter: E
Question
e) Use SP500 returns from 1/4/16 to 10/3/16 and your estimated results to forecast monthly returns for JPM from 1/4/16 to 10/3/16. Finally, calculate MAPE of your forecast. Please show excel forumulas and upload excel work book if possible, using external link.
Results from previous problem
2139.18
SUMMARY OUTPUT Regression Statistics Multiple R 0.666203967 R Square 0.443827726 Adjusted R Square 0.420653881 Standard Error 2.325525728 Observations 26 ANOVA df SS MS F Significance F Regression 1 103.5758804 103.5759 19.1521 0.000203 Residual 24 129.7936779 5.40807 Total 25 233.3695583 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 0.275875942 0.463647873 0.595012 0.557405 -0.68105 1.232798 -0.68105 1.232798 X Variable 1 0.033045114 0.007550906 4.376311 0.000203 0.017461 0.048629 0.017461 0.048629Explanation / Answer
The excel Forecast function is used. The cell formula that I've used is
=FORECAST(C2,B$2:B$11,C$2:C$11)
The results are given below
Residual sum of squares is also known as the Sum of errors. Here, its value is 129.7937
Mean Absolute Percentage Error (MAPE) = SUM OF ALL ERRORS / SUM OF FORECAST OR ACTUAL VALUES
= 129.7937 / 623.68 = 0.208109
FORECAST JPM 02-01-2016 55.97525 03-01-2016 61.32738 04-01-2016 61.56075 06-01-2016 62.96941 07-01-2016 66.10656 08-01-2016 65.99532 09-01-2016 65.88283 05-02-2016 62.88924 10-03-2016 64.6618 01-04-2016 56.31146Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.