Question 1 contains the actual values for 12 periods (listed in order, 1-12). In

Question

Question 1 contains the actual values for 12 periods (listed in order, 1-12). In Excel, create forecasts for periods 6-13 using each of the following methods: 5 period simple moving average; 4 period weighted moving average (0.63, 0.26, 0.08, 0.03); exponential smoothing (alpha = 0.23 and the forecast for period 5 = 53); linear regression with the equation based on all 12 periods; and quadratic regression with the equation based on all 12 periods. Round all numerical answers to two decimal places.


1. The actual values for 12 periods (shown in order) are:

(1) 45 (2) 52 (3) 48 (4) 59 (5) 55 (6) 55 (7) 64 (8) 58 (9) 73 (10) 66 (11) 66 (12) 74

Using a 5 period simple moving average, the forecast for period 13 will be:

2. Using the 4 period weighted moving average, the forecast for period 13 will be:


3. With exponential smoothing, the forecast for period 13 will be:

4.With linear regression, the forecast for period 13 will be:

5. With quadratic regression, the forecast for period 13 will be:

6. Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the methods?

Explanation / Answer

I will carry out the calculations in MS_EXCEL and at the end of the solution provide the link for the EXCEL file in which I have originally carried out the work.

1. Using a 5 period simple moving average, the forecast for period 13 will be:

Solution :-

The forecasted value in this case is 67.40

2. Using the 4 period weighted moving average, the forecast for period 13 will be:

Solution :-

The forecast using this method for 13th period is 70.65

3. With exponential smoothing, the forecast for period 13 will be:

Solution :-

In this case the forecasted value for 13th period would be 65.26

4.With linear regression, the forecast for period 13 will be:

Solution :- Using the trendline function which comes alogwith scatterplot we find the linear regression equation to be,

y = 2.3042x+44.606

Based on this the forecasted value for linear regression will be 74.56

5. With quadratic regression, the forecast for period 13 will be:

Solution :-

The forecasted value as per this method would be 73.98

6. Considering only the forecasts for period 6-12, what is the lowest MAD value for any of the methods?

Solution :-

The MAD values for the processes are given below.

5 period Moving Average :- 6.63

4 period weighted Moving Average :- 7.86

Exponential Smoothing :- 7.33

Linear Regression :- 3.82

Quadratic regression :- 3.86

Thus from above it is evident that Linear Regression has the lowest MAD value.

Link for excel file :- https://www.dropbox.com/s/9wbhv6igiujrwdp/Question%201.xlsx?dl=0

Period Value 5 period MA 1 45 2 52 3 48 4 59 5 55 6 55 51.80 7 64 53.80 8 58 56.20 9 73 58.20 10 66 61.00 11 66 63.20 12 74 65.40 13 67.40

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.