Forecasting: Time series and trend analysis
Sheet1
| next year price is $10 and forecast with 99% confidence | |||||||||||
| Sales | Price | ||||||||||
| 80 | 15 | SUMMARY OUTPUT | |||||||||
| 120 | 13 | ||||||||||
| 110 | 12 | Regression Statistics | |||||||||
| 140 | 11 | Multiple R | 0.8109307589 | ||||||||
| 100 | 12 | R Square | 0.6576086957 | ||||||||
| Adjusted R Square | 0.5434782609 | Sales = 260.65- 11.9 (PRICE) | Talpha for 99% | 5.8409093097 | |||||||
| Standard Error | 15.1083046556 | Forecast | Ycap = | 141.0869565217 | forecast for year 6 | ||||||
| Observations | 5 | Est Error | 88.2462373169 | ||||||||
| fails F test | |||||||||||
| ANOVA | critical F | 10.127964486 | |||||||||
| df | SS | MS | F | Significance F | |||||||
| Regression | 1 | 1315.2173913044 | 1315.2173913044 | 5.7619047619 | 0.0958402802 | ||||||
| Residual | 3 | 684.7826086957 | 228.2608695652 | ||||||||
| Total | 4 | 2000 | |||||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | ||||
| Intercept | 260.652173913 | 63.1240115309 | 4.129208008 | 0.0257639049 | 59.7633966417 | 461.5409511844 | 59.7633966417 | 461.5409511844 | |||
| Price | -11.9565217391 | 4.981060538 | -2.4003967926 | 0.0958402802 | -27.8084794447 | 3.8954359664 | -27.8084794447 | 3.8954359664 | |||
| critical T | 3.1824463053 | Price failed T test | |||||||||
| RESIDUAL OUTPUT | |||||||||||
| Y | Ycap | Y-Ycap | Abs(err) | Err^2 | APE | ||||||
| Observation | Predicted Sales | Residuals | |||||||||
| 80 | 1 | 81.3043478261 | -1.3043478261 | 1.3043478261 | 1.7013232514 | 1.6304347826 | |||||
| 120 | 2 | 105.2173913043 | 14.7826086957 | 14.7826086957 | 218.5255198488 | 12.3188405797 | |||||
| 110 | 3 | 117.1739130435 | -7.1739130435 | 7.1739130435 | 51.4650283554 | 6.5217391304 | |||||
| 140 | 4 | 129.1304347826 | 10.8695652174 | 10.8695652174 | 118.1474480151 | 7.7639751553 | |||||
| 100 | 5 | 117.1739130435 | -17.1739130435 | 17.1739130435 | 294.943289225 | 17.1739130435 | |||||
| 10.2608695652 | 136.9565217391 | 9.0817805383 | |||||||||
| MAD | MSE | MAPE |