Question

Use data below to create a file with monthly data from January 2015 to December 2017 for Hansen Company. The first variable is Time. The second variable, Sales 1, has data on sales of a product 1. The third variable, Sales 2, has data on sales of a product 2.

Part 1: For this problem, use the Sales 1 variable to answer the following questions.

What are the forecasts for the January of the year 2018 and evaluate the MSE using the Moving Average with MA(3), and MA(12).

Which appears to provide the best fit for this data (explain why)?

Comment on the appropriateness of this forecasting method (explain).

Can you apply this forecasting method to the third variable, Sales 2 (Explain)?

Part 2: For this problem, use the Sales 1 variable to answer following questions

What are the forecasts for the January of the year 2018 using the exponential smoothing method with ?

Use Solver to estimate the optimal value of that minimizes MSE and evaluate the MSE based on the optimal value . What is the forecast for the January of the year 2018 based on the optimal value of ?

Comment on the appropriateness of this forecasting method (explain).

Can you apply this forecasting method to the third variable, Sales 2 (Explain)?

Part 3: For this problem, use the Sales 1 variable to answer following questions

What is the forecast for October of the year 2018 using Holt’s method with ?

Use Solver to estimate the optimal value of smoothing constants ( ) that minimize MSE and evaluate the MSE based on the optimal value of.

Comment on the appropriateness of this forecasting method.

Prepare a line graph to compare the predictions from this method with the optimal value of smoothing constants against the original data (sales 1).

Can you apply this forecasting method to the third variable, Sales 2 (Explain)?

Part 4: For this problem, use the Sales 2 variable to answer following questions.

What is the forecast for October of the year 2018 using Winter’s method with ?

Use Solver to estimate the optimal value of smoothing constants ( ) that minimize MSE and evaluate the MSE based on the optimal value of.

Comment on the appropriateness of this forecasting method.

Prepare a line graph to compare the predictions from this method with the optimal value of smoothing constants against the original data.

Monthly sales data
Month	Sales1	Sales2
Jan-15	1701	1189
Feb-15	1732	1209
Mar-15	1758	1754
Apr-15	1774	1843
May-15	1808	1769
Jun-15	1827	2207
Jul-15	1844	2471
Aug-15	1871	2288
Sep-15	1898	1867
Oct-15	1908	1980
Nov-15	1934	1418
Dec-15	1968	1333
Jan-16	1986	1333
Feb-16	2021	1370
Mar-16	2056	2142
Apr-16	2095	2138
May-16	2122	2078
Jun-16	2143	2960
Jul-16	2168	2616
Aug-16	2207	2861
Sep-16	2226	2237
Oct-16	2255	2225
Nov-16	2283	1590
Dec-16	2309	1659
Jan-17	2338	1613
Feb-17	2382	1605
Mar-17	2400	2349
Apr-17	2452	2468
May-17	2486	2532
Jun-17	2522	3127
Jul-17	2547	3288
Aug-17	2570	3285
Sep-17	2611	2485
Oct-17	2628	2723
Nov-17	2662	1835
Dec-17	2696	1894
Jan-18
Feb-18
Mar-18
Apr-18
May-18
Jun-18
Jul-18
Aug-18
Sep-18
Oct-18
Nov-18
Dec-18

Answer 1

Part 1

1.2 = MSE of Sales1 (MA(3)) is less compared with MA(12) .

As such MA(3) is appropriate in for sales1

1.3 Sales1 is non-stationary series with deterministic trend and is already a linear function of time.

1.4 It can be applied to Sales2 data .Sales2 data appears to be seasonal and is volatile. To determine the underluing trend Moving average cab be applied.

Part 2.1 Using Exponential Smoothing Factor and alpha = 0.5

Part 2.2 Using Solver to minimize MSE

Forecat = 2705 with alpha = 1.5033

Part 3:

Holt Method

Initially any random value for alpha and beta is used.

Than after doing all the calculations and calculating root mean square roo t error (RMSE) Solver is used.

Minimize cell with value of RMSE by changing Cells with Alpha and Beta values.

Constraints 0<= alpha <= 1

0<=Beta<= 1