The last 20 years of annual sales for a small business is shown in Worksheet P1. The data is shown in $1000s so Year 1 is $283,000.

The last 20 years of annual sales for a small business is shown in Worksheet P1. The data is shown in $1000s so Year 1 is $283,000. I will ask for all answers in terms of $1000s so just enter the data in your worksheets as it is shown in the file. (Note: I have made some modifications to this problem since creating the supporting video - so there is some extra information in the video that you are not responsible for at this time. Specifically, I show you how to use Solver to optimize the answers - you are not responsible for using Solver at this point. Also although it shows a different problem number - it is the solution for this problem).

a) Plot the data - does the data appear stationary? b) Compute the forecasts using the two-year moving average. What is the MSE? c) Compute the forecast for Year 21 using the two-year moving average. d) Compute the forecasts using the four-year moving average. What is the MAD? e) Compute the forecast for Year 21 using the four-year moving average. f) Do the two and four-year moving averages tend to underestimate or overestimate the actual data? Why? g) Compute the forecasts using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. What is the MAPE? h) Compute the forecast for Year 21 using the three-year weighted moving average using weights of 0.6, 0.3, and 0.1. i) Compute the forecasts using exponential smoothing with a smoothing constant of 0.2 and a forecast for Year 1 of 283. What is the MSE? j) Compute the forecast for Year 21 using exponential smoothing with a smoothing constant of 0.2 and a forecast for Year 1 of 283. k) Compute the forecasts using linear regression using Excel's slope and intercept function. What is the MAD? l) Compute the forecast for Year 21 using linear regression. m) Comparing one of the error measurements (MAD, MSE, or MAPE) - which is the best forecasting method? Which is the worst forecasting method?

M2_A2. Western Home Inspections is a home inspection service that provides prospective homebuyers with a thorough assessment of the major systems in a house prior to the execution of the purchase contract. Prospective homebuyers often ask the company for an estimate of the average monthly heating cost of the home during the winter. To answer this question, the company wants to build a regression model to help predict the average monthly heating cost (Y) as a function of the average outside temperature in the winter (X1), the amount of attic insulation in the house (X2), the age of the furnace in the house (X3), and the size of the house measured in square feet (X4). Data on 20 homes has been recorded and is shown in Worksheet A2. The company wants to build a regression model to estimate the average monthly heating cost based on outside temperature, attic insulation, age of the furnace, and size of the house. (Note: I have made some modifications to this problem since creating the supporting video - although it shows a different problem number - it is the solution for this problem).

a) Prepare a scatter plot showing the relationship between the heating cost and each of the independent variables. b) If the home inspector wanted to build a regression model using only one independent variable to predict heating cost, which variable should be used? c) Why? d) How do you use the value of Significance F in the model with only one independent variable? e) If the home inspector wanted to build a regression model using two independent variable to predict heating cost, which variable should be added to the model? f) Why? g) If the home inspector wanted to build a regression model using three independent variable to predict heating cost, which variable should be added to the two variable model? h) Why? i) If the home inspector wanted to build a regression model using four independent variable to predict heating cost, which variable should be added to the three variable model? j) Why? k) How do you use the value of Significance F in the model with more than one independent variable? l) Does there appear to be any multicollinearity among the independent variables? m) How can you tell if you have multicollinearity? n) Which sets of variables indicate multicollinearity o) Based on your best model, what is the expected average monthly heating cost for a home which has an average outside temperature of 45, 8 inches of attic insulation, a 7 year old furnace, and is 2000 square foot?