# In this assignment you will be doing one of each type of forecasting method

## 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.

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: