Question

The number of heart surgeries performed at Heartville General Hospital has increased steadily over the past several years. The hospital's administration is seeking the best method to forecast the demand for such surgeries in year 6. The data for the past five years are shown below. 4 Year Demand 1 44 2 47 3 53 5 58 55 The hospital's administration is considering the following forecasting methods. Begin error measurement in year 3, so all methods are compared for the same years. i. Exponential smoothing, with a = 0.6. Let the initial forecast for year 1 be 44, the same as the actual demand. ii. Exponential smoothing, with a = 0.9. Let the initial forecast for year 1 be 44, the same as the actual demand. iii. Trend projection with regression. iv. Two-year moving average. v. Two-year weighted moving average, using weights 0.6 and 0.4, with the more recent data given more weight. If MAD is the performance criterion chosen by the administration, which forecasting method should it choose?

Answer 1

Question: The number of heart surgeries performed at Heartville General Hospital has increased steadily over the past several years. The hospitals administration is seeking the best method to forecast the demand for such surgeries is year 6. The data for the past five years are shown.

Answer:

1. Exponential Smoothing Forecast with Alpha = 0.60

Given that:

Alpha = 0.6

Initial Forecast = 44

Exponential Smoothing is given by the formula:

Ft+1 = α x At + (1 - α) x Ft

Therefore:

F2 = 0.6 x 44 + (1 - 0.6) x 44 = 44

F3 = 0.6 x 47 + (1 - 0.6) x 44 = 45.8

F4 = 0.6 x 53 + (1 - 0.6) x 45.8 = 50.12

F5 = 0.6 x 55 + (1 - 0.6) x 50.12 = 53.048

Let us now calculate |Error|:

|Error| = |Actual - Forecast|

Therefore:

Year Demand Error Exp.Sm 44 1 44 2 44 3 47 53 55 45.8 7.2 4 4.88 50.12 53.048 5 58 4.952

Now:

Mean Absolute Deviation (MAD) is given by the formula:

MAD = ∑ |Error| / n

Therefore:

MAD = (7.2 + 4.88 + 4.952) / 3

MAD = 5.68

2. Exponential Smoothing Forecast with Alpha = 0.90

Year Exp.sm 44 Error Demand 44 47 1 2 44 3 53 6.3 46.7 52.37 4 55 2.63 Сл 58 54.737 3.263

Therefore:

MAD = (6.3 + 2.63 + 3.263) / 3

MAD = 4.06

3. Trend projection with regression forecast

Easiest method to find the linear equation is by using a line chart in excel. Select "Period" and "Sales" column and insert a line chart, right click on the line chart and click "Add trendline", in the corresponding popup check "Display Equation on chart".

Hence the equation is: Y = 40.6 + 3.6(x)

Therefore:

Y3 = 40.6 + 3.6(3) = 51.4

Y4 = 40.6 + 3.6(4) = 55

Y5 = 40.6 + 3.6(5) = 58.6

Let us calculate |Error|

Year Linear [Error Demand 44 1 44.2 47 47.8 N 3 53 51.4 1.6 4 55 55 0 5 58 58.6 0.6

Therefore:

MAD = (1.6 + 0 + 0.6) / 3

MAD = 0.73

4. Two-year moving average

Moving average is given by the formula:

Moving Average Forecast = (At-1 + At-2 + At-3......An) / n

Therefore:

F3 = (47 + 44) / 2 = 46

F4 = (53 + 47) / 2 = 49.6

F5 = (55 + 53) / 2 = 53.2

Let us calculate |Error|

2MA Error Year 1 Demand 44 47 53 2 3 46 7 4 55 49.6 5.4 5 58 53.2 4.8

Therefore:

MAD = (7 + 5.4 + 4.8) / 3

MAD = 5.73

5. Two- Year weighted moving average

Given that:

Weights are: 0.6, 0.4 = 1, hence weights are balanced.

Weighted moving average is given by:

Weighted moving average = (W1 x At-1 + W2 x At-2 + W3 x At-3......Wn x An) / n

Since n = 1

Therefore:

F3 = 0.6 x 47 + 0.4 x 44 = 45.8

F4 = 0.6 x 53 + 0.4 x 47 = 50.6

F5 = 0.6 x 55 + 0.4 x 53 = 54.2

Let us calculate |Error|

Error Year 1 Demand 2WMA 44 47 53 45.8 2 3 7.2 4 55 50.6 4.4 5 58 54.2 3.8

Therefore:

MAD = (7.2 + 4.4 + 3.8) / 3

MAD = 5.13

Conclusion:

If MAD is the performance criterion chosen by the administration, which method should it choose: Linear trend with regression forecast. Since, with respect to MAD the value is lowest and closest to actual value hence linear trend with regression is the best forecast method.

Linear trend with regression forecast - MAD = 0.73