Foescasting Checkpoint

CheckPoint: Forecasting

Exercise 9.1

The following data represent total personnel expenses for the Palmdale Human Service Agency for past four fiscal years:

20X1 $5,250,000
20X2 $5,500,000
20X3 $6,000,000
20X4 $6,750,000

For moving averages and weighted moving averages, use only the data for the past three fiscal years. For weighted moving averages, assign a value of 1 to the data for 20X2, a value of 2 to the data for 20X3, and a value of 3 to the data for 20X4. Forecast personnel expenses for fiscal year 20X5 using moving averages, weighted moving averages, exponential smoothing, and time series regression.

Moving Averages

Fiscal Year Expenses
20X2 $5,500,000
20X3 $6,000,000
20X4 $6,750,000
20X2-4 $18,250,000
20X5 $18,250,000/3 = $6,083,333 Weighted Averages

Fiscal Year Expenses Weight Weighted Score
20X2 $5,500,000 1 $5,500,000
20X3 $6,000,000 2 $12,000,000
20X4 $6,750,000 3 $20,250,000 6 $37,750,000
20X5 $37,750,000/6 = $6,291,667

Exponential Smoothing

Given:
Last Forecast (LF) = $6,300,000 Last Data (LD) = $6,750,000 α = 0.95
I used the 0.95 alpha because I strongly believe that the new forecast will be based on the last data.

NF = LF + α (LD – LF)
NF = $6,300,000 + 0.95 ($6,750,000 – $6,300,000)
NF = $6,300,000 + 0.95 (450,000)
NF = $6,300,000 + 427,500
NF = $6,727,500

20X5 = $6,727,500

Time Series Regression

Computer Output

Constant = 4,625,000
Variable = 500,000
R-Square = 0.95

Y = 4,625,000 + 500,000X
20X5 = 4,625,000 + 500,000 (5)
20X5 = 4,625,000 + 2,500,000
20X5