Question

Rockstar Carpet Outlet wants to develop a method to forecast its carpet sales. The manager believes that the store’s sales are related to the number of new housing starts in the area and has gathered data of construction permits from the county records and from store sales.

Monthly carpet sales (1000s yd)	Monthly construction permits
11	17
8	30
5	12
12	14
6	18
5	10
8	38
4	20
14	16
9	31
9	15
16	21

1. Develop a linear regression model relating sales in a period to construction permits in the current or previous periods. What is the strength of the causal relationship between monthly sales and new construction permits for the same month?
2. Develop other linear regression models for different lagged months? What do you conclude?
3. What are the sales forecasts the next two months? How would you forecast sales for the next six months?

USE EXCEL AND SHOW EXCEL FORMULAS PLEASE

Answer 1

a. The strength of the casual relationship is given by R-value. R-value is 0.037, which is closer to 0. This means that the strength of the relationship is very low.

b. The linear regression equation is

Monthly carpet sales = 8.584618591 + 0.016465194 * Monthly construction permits

c. To forecast for next two months, the independent variable should be the month number.

The equation is

Month sales = 6.67 + 0.346*Month

Forecast for the next two months is

>> For Month 13, Sales = 6.67 + 0.346*13 = 11.168

>> For Month 14, Sales = 6.67+0.346*14 = 11.514

>> The forecast for next six months can be calculated similarly by increasing the month numbers.