Question

6.Use Exponential smoothing forecasts with alpha of 0.1, 0.2, ..., 0.9 to predict March 2019 demand. Identify the value of alpha that results in the lowest MAD.

7.Find the monthly seasonal indices for the demand values using Simple Average (SA) method. Find the de-seasonalized demand values by dividing monthly demand by corresponding seasonal indices.

8.Use regression to perform trend analysis on the de-seasonalized demand values. Is trend analysis suitable for this data? Find MAD and explain the Excel Regression output (trend equation, r, r-squared, goodness of model).

9.Find the seasonally adjusted trend forecasts for March through May 2019.

10.Perform simple linear regression analysis with ADV as the independent variable. Write the complete equation, find MAD and explain the Excel Regression output. Make sure to use the de-seasonalized demand data for this model and all future models.

11.Repeat part (10) with DIFF as the independent variable.

12.Construct multiple linear regression model with Period, AIP, DIFF, and ADV as independent variables. Formulate the equation, find MAD, and explain the output. Rank variables based on their degree of contribution to the model. Observe significant F, R-squared, and p-values and explain.

13.Perform multiple linear regression analysis with Period, DIFF, and ADV as independent variables. Formulate the equation and find MAD. Which variable is the most significant predictor of demand? Rank the independent variables based on their degree of contribution to the model. Observe significant F, R-squared, and p-values and explain.

14.Use the model obtained in parts 13 and make forecasts for the following months. Make sure to seasonalize final forecasts.

Period   Year                Price               AIP                ADV

March     2019                $6.10               $6.50               $10.3

April       2019                $6.30               $6.60               $10.7

May        2019                $6.50               $7.10               $11.1

                          
                          
   Month/Yr.   PERIOD   PRICE   AIP   DIFF   ADV   DEMAND
   June 2016   1   6.1   5.8   -0.3   5.3   14.4
       2   5.75   6   0.25   6.75   15.3
       3   5.7   6.3   0.6   7.25   16.5
       4   5.7   5.7   0   7.3   16.1
       5   5.6   5.85   0.25   7.2   16
       6   5.6   5.8   0.2   6.5   15.5
       7   5.6   5.75   0.15   6.75   15.2
   Jan. 2017   8   6.3   5.85   -0.45   6.89   13.9
       9   6.4   5.65   -0.75   5.8   13.3
       10   6.2   6   -0.2   5.5   13.12
       11   5.9   6.1   0.2   6.5   13.8
       12   5.9   6   0.1   6.25   14.8
       13   5.7   6.1   0.4   7   15.3
       14   5.75   6.2   0.45   6.9   16.3
       15   5.75   6.1   0.35   6.8   17.5
       16   5.8   6.1   0.3   6.8   17.4
       17   5.7   6.2   0.5   7.1   17.1
       18   5.8   6.3   0.5   7   16.8
       19   5.7   6.1   0.4   6.8   16.5
   Jan. 2018   20   5.8   5.75   -0.05   6.5   16
       21   5.8   5.75   -0.05   8.1   15.2
       22   5.75   5.65   -0.1   7.7   15.3
       23   5.7   5.9   0.2   7.3   15.9
       24   5.55   5.65   0.1   7.5   16.2
       25   5.6   6.1   0.5   8.1   17.5
       26   5.65   6.25   0.6   8.3   18.4
       27   5.7   5.65   -0.05   8.7   19.4
       28   5.75   5.75   0   9.2   19.1
       29   5.8   5.85   0.05   8.4   18.7
       30   5.3   6.25   0.95   8.8   18.2
       31   5.4   6.3   0.9   9.5   18.4
   Jan. 2019   32   5.7   6.4   0.7   9.3   17.5
   Feb. 2019   33   5.9   6.5   0.6   9.1   17.1
   Mar-19   34                  
   Apr-19   35                  
   May-19   36                  
                          

Answer 1

The multiple linear regression model with Period, AIP, DIFF, and ADV as independent variables

Result

So, the regression equation is:

DEMAND = 22.886 + 0.037 * PERIOD - 2.066 * AIP + 2.312 * DIFF + 0.631 * ADV

Month/Yr.	PERIOD	AIP	DIFF	ADV	PRICE	DEMAND	FORECAST	|ERROR|
Jun. 2016	1	5.8	-0.3	5.3	6.1	14.4	13.6	0.81
	2	6	0.25	6.75	5.75	15.3	15.4	0.1
	3	6.3	0.6	7.25	5.7	16.5	15.9	0.56
	4	5.7	0	7.3	5.7	16.1	15.9	0.24
	5	5.85	0.25	7.2	5.6	16	16.1	0.1
	6	5.8	0.2	6.5	5.6	15.5	15.7	0.19
	7	5.75	0.15	6.75	5.6	15.2	15.9	0.67
Jan. 2017	8	5.85	-0.45	6.89	6.3	13.9	14.4	0.5
	9	5.65	-0.75	5.8	6.4	13.3	13.5	0.17
	10	6	-0.2	5.5	6.2	13.12	13.9	0.75
	11	6.1	0.2	6.5	5.9	13.8	15.3	1.45
	12	6	0.1	6.25	5.9	14.8	15.1	0.31
	13	6.1	0.4	7	5.7	15.3	16.1	0.8
	14	6.2	0.45	6.9	5.75	16.3	16	0.31
	15	6.1	0.35	6.8	5.75	17.5	15.9	1.56
	16	6.1	0.3	6.8	5.8	17.4	15.9	1.54
	17	6.2	0.5	7.1	5.7	17.1	16.3	0.76
	18	6.3	0.5	7	5.8	16.8	16.1	0.69
	19	6.1	0.4	6.8	5.7	16.5	16.2	0.3
Jan. 2018	20	5.75	-0.05	6.5	5.8	16	15.7	0.27
	21	5.75	-0.05	8.1	5.8	15.2	16.8	1.58
	22	5.65	-0.1	7.7	5.75	15.3	16.7	1.35
	23	5.9	0.2	7.3	5.7	15.9	16.6	0.71
	24	5.65	0.1	7.5	5.55	16.2	17.1	0.86
	25	6.1	0.5	8.1	5.6	17.5	17.5	0.03
	26	6.25	0.6	8.3	5.65	18.4	17.6	0.84
	27	5.65	-0.05	8.7	5.7	19.4	17.6	1.81
	28	5.75	0	9.2	5.75	19.1	17.8	1.25
	29	5.85	0.05	8.4	5.8	18.7	17.3	1.41
	30	6.25	0.95	8.8	5.3	18.2	18.8	0.63
	31	6.3	0.9	9.5	5.4	18.4	19.1	0.69
Jan. 2019	32	6.4	0.7	9.3	5.7	17.5	18.3	0.83
Feb. 2019	33	6.5	0.6	9.1	5.9	17.1	17.8	0.71
							Average	0.75
								MAD

Variable ranking based on contribution in the model:

1. DIFF, 2. AIP, 3. ADV, 4. PERIOD

The multiple-R-squared value os 0.83 meaning the variation of the dependent variables can be explained by 83% of the raw data.

The p-value of PERIOD and AIP are more than 0.05. So, at 95% confidence level, the null hypothesis that the slope is zero cannot be rejected. In other words, the slope associated with these two variables is statistically insignificant.

The significance-F is less than 0.05. So, at 95% confidence level, the above multiple regression model is appropriate.