## Instructions

Instructions As you have learned forecasting can be of two different types: a) Forecast using historical data (Time Series); or b) Forecasting using indicator variables (Regression) In this assignment you will be doing one of each type of forecasting method Good luck…Bernie

## Time Series – DATA

Forecasting Stock Index Funds | NOTE: for all the following – Copy and Paste as Numbers – do not use formulas as they will change | ||||||||

Most recent period (lag = 1 month) è | Period #1 | 60% | Alpha = | 40% | Step #1: Find the 3 month moving average Forecast , MAD and MSE. | ||||

Middle period (lag = 2 months) è | Period #2 | 30% | 3-Month Moving Average Forecast for Month 21 = | ||||||

Most distant period (lag = 3 months) è | Period #3 | 10% | 3-Month Moving Average MAD = | ||||||

3-Month Moving Average | Weighted Moving Average | Exponentially Smoothed | 3-Month Moving Average MSE = | ||||||

Fund | Forecast | Error | Squared Error | Forecast | Error | Squared Error | Forecast | Error | Squared Error |

Month | Price | Step #2: Find the 3 period Weighted moving average Forecast , MAD and MSE. (Most recent period = 60%, lag #2 = 30% and lag #3 = 10%) | |||||||

1 | 63.250 | 3-Month Weighted Moving Average Forecast for Month 21 = | |||||||

2 | 60.125 | Weighted Moving Average MAD = | |||||||

3 | 61.750 | Weighted Moving Average MSE = | |||||||

4 | 64.250 | ||||||||

5 | 59.375 | Step #3: Find the Exponential Forecast , MAD and MSE. (Alpha = 40%) bwarren: When finding MAD and MSE do not include Months #1, #2 or #3. Eliminating these makes comparison more accurate |
|||||||

6 | 57.875 | 3-Month Exponential Forecast for Month 21 = | |||||||

7 | 62.250 | Smoothed Forecast MAD = | |||||||

8 | 65.125 | Smoothed Forecast MSE = | |||||||

9 | 68.250 | ||||||||

10 | 65.500 | Step #4: Use Solver to find the optimal weights that minimize the MSE for the 3 period Weighted Moving Average Forecast – Constrain Total Weight to = 100% | |||||||

11 | 68.125 | Using Solver to minimize MSE | |||||||

12 | 63.250 | Optimized 3-Month Weighted Moving Average Forecast for Month 21 = | |||||||

13 | 64.375 | Optimal Weighted Moving Average MSE = | |||||||

14 | 68.625 | Period #1 = | |||||||

15 | 70.125 | Period #2 = | |||||||

16 | 72.750 | Period #3 = | |||||||

17 | 74.125 | Total of all Weights = | |||||||

18 | 71.750 | Percentage Improvement in MSE with optimization = | |||||||

19 | 75.500 | ||||||||

20 | 76.750 | Step #5: Use Solver to find the optimal weights that minimize the MSE for the 3 period Weighted Moving Average Forecast – DO NOT Constrain Total Weight to 100% | |||||||

21 | Using Solver to minimize MSE | ||||||||

ñ | ñ | ñ | ñ | ñ | ñ | ñ | ñ | ñ | Optimized 3-Month Weighted Moving Average Forecast for Month 21 = |

Forecast | MAD | MSE | Forecast | MAD | MSE | Forecast | MAD | MSE | Optimal Weighted Moving Average MSE = |

Period #1 = | |||||||||

Period #2 = | |||||||||

Period #3 = | |||||||||

Total of all Weights = | |||||||||

Percentage Improvement in MSE with optimization = | |||||||||

Step #6: Use Solver to find the optimal Alpha that minimize the MSE for the Exponential Forecast | |||||||||

Using Solver to minimize MSE | |||||||||

3-Month Exponential Forecast for Month 21 = | |||||||||

Optimal Alpha = | |||||||||

Smoothed Forecast MAD = | |||||||||

Smoothed Forecast MSE = | |||||||||

Percentage Improvement in MSE with optimization = | |||||||||

ç |

Mary Hernandez has invested in a stock mutual fund and is considering liquating and investing in a bond fund. She would like to forecast the price of a stock fund for the next month before making a decision. She has collected the following data on the average price of the stock fund for the last 20 months. Goal: Find the best time series forecast for the month 21.

Which forecast would you recommend to Mary. Justify your recommendation Answer:

Plot original data and your recommended forecasting technique to the left of this text box. Make sure your chart is “Client Ready” with proper titles and labels. Also make sure your months line our for both data series.

## Regression Forecast – DATA

Kamloops Police Department | ||

Total Kilometers | Car Age (yrs) | Average Annual Maintenance $ |

219,955 | 6 | $1,120 |

270,592 | 7 | $1,610 |

356,659 | 8 | $1,545 |

95,949 | 4 | $900 |

55,526 | 3 | $650 |

215,846 | 7 | $1,560 |

41,472 | 2 | $550 |

48,384 | 3 | $730 |

Total Variation explained by Regression = | ç make sure you use a formula | |

Variable that can be eliminated = | ||

Total Kilometers | Car Age (yrs) | |

250,000 | 5 | |

Forecasted Annual Maintenance $ | ç make sure you use a formula | |

Total Kilometers | Car Age (yrs) | |

250,000 | 5 | |

Revised Forecasted Annual Maintenance $ | ç make sure you use a formula |

The manager of the Kamloops Police Department want to develop a forecast or prediction model for annual maintenance cost on police cars based on the average annual kilometers and the age of the police car. The data above has been collected for 8 cars. Goal: Find the best forecast for a vehicle that is 5 years old and had 250,000 Kilometres on the odometer.

Part C Develop a regression that uses Total Kilometers Driven AND Car Age to predict Annual maintenance $ spent. What percentage of the total variation is explained by these two variables? Enter your answer in Cell below. Round your answer to 2 decimals

Part D Of the two variables (Total Kilometers Driven & Car Age) which one of these can be eliminated? – Enter 0 in cell below for no variable can be eliminated – Enter 1 in cell below for Total Kilometers Driven variable can be eliminated – Enter in 2 in cell below for Car Age variable can be eliminated

Part E Using the multiple Regression in part C what would be the forecast for the Annual maintenance for a vehicle that was 5 years old and had 250,000 kilometers? Enter your answer in the cell below rounded to the nearest integer.

Part A Plot two charts to the right of this textbox. – Chart #1 is a scatter chart of Average Annual Maintenance (Y Axis) versus Total Kilometers (X axis). Make sure your chart is properly labeled. – Chart #2 is a scatter chart of Average Annual Maintenance (Y Axis) versus Age of Car (X axis). Make sure your chart is properly labeled.

Part B Using the two charts – is Total Kilometers driven or Car Age better related to he Annual Maintenance costs? Briefly explain your answer: ANSWER:

Part F Using this data is this the BEST Forecast you can come up with? If so Explain why and if NOT what would you do to improve the forecast and what would your revised Annual maintenance forecast be? rounded to the nearest integer. ANSWER:

Part E Briefly Justify your answer in part D: ANSWER: