ECO 6214 : Module 5, Assignment 5: Forecasting | Due: Sun Apr 20, 2025 11:59pm ...

Introduction

This assignment assesses your understanding of and ability to apply multiple regression models to a series of business data using the Excel Analysis ToolPak.

Instructions

Submit your answers to all of the questions in a Word document. Download the data sets and use Excel to solve the problems.

Question 1. Download the data and develop a multiple-regression model for auto sales as a function of population and household income for 10 metropolitan areas.

1. Estimate values for b₀, b₁, and b₂ for this model:
   AS = b₀ + b₁ (INC) + b₂ (POP)
2. Are the signs you find for the coefficients consistent with your expectations? Explain.
3. What percentage of variation in AS is explained by this model?
4. What point estimate of AS would you make for a city where INC=23,175 and POP=128.7?

Question 2. The quarterly sales of the TRK-50 mountain bike for the previous four years by a bicycle shop in Switzerland are presented in the table:

1. Plot the sales against time.
2. Use the data to estimate the monthly trend in sales using a linear trend model of the form:
   Q_t = a + bt. Does your statistical analysis indicate a trend? If so, is it an upward or downward trend and how great is it? Is it a statistically significant trend at 5 percent level of significance?
3. Now adjust your statistical model to account for seasonal variation in sales. Estimate this model of sales:
   Q_t = a + bt + cD₂ + dD₃ + eD₄
   where D₂, D₃, D₄ are the appropriately defined dummy variables for quarters 2, 3, and 4.
   Do the data indicate a statistically signifi¬cant seasonal pattern at 5 percent level of significance? If so, what is the seasonal pattern of sales?
4. Comparing your estimates of the trend in sales in parts b and c, which estimate is likely to be more accurate? Why?
5. Using the estimated forecast equation from part c, forecast sales for Quarters 1 and 4 of 2014 and Quarters 2 and 3 of 2015.

Submission

• Submit your answers in a Word document.
• Submit the Excel files to show your calculations.
• Include the relevant graphs of actual and forecast values of the series. Please do not forget to title your graph.

Submit your calculations and answers in a 1–3-page document and spreadsheet to the Dropbox by Day 7 of the week.