C.I.S forecasting model

klemetovich_week_8_11-14.xls

11-3B

Jeannette's GPA
Forecasting		3 period weighted moving average


Input Data			Forecast Error Analysis
Period	Actual value	Weights	Forecast	Error	Absolute error	Squared error	Absolute % error
Month 1	2.4	1
Month 2	2.9	2
Month 3	3.1	3
Month 4	3.2		2.917	0.283	0.283	0.080	8.85%
Month 5	3		3.117	-0.117	0.117	0.014	3.89%
Month 6	2.9		3.083	-0.183	0.183	0.034	6.32%
Month 7	2.8		2.983	-0.183	0.183	0.034	6.55%
Month 8	3.6		2.867	0.733	0.733	0.538	20.37%
Month 9	3.2		3.217	-0.017	0.017	0.000	0.52%
			Average		0.253	0.117	7.75%
Next period	3.267				MAD	MSE	MAPE

Weights for the 3 previous months

MAD, MSE, and MAPE are measures of forecast error.

Enter the data in the cells shaded YELLOW.

Time

Value

3 period weighted moving average

Jeannette's GPA
Forecasting		3 period weighted moving average


Input Data			Forecast Error Analysis
Period	Actual value	Weights	Forecast	Error	Absolute error	Squared error	Absolute % error
Month 1	2.4	0.2218
Month 2	2.9	0.5927
Month 3	3.1	0.1855
Month 4	3.2		2.826	0.374	0.374	0.140	11.68%
Month 5	3		3.074	-0.074	0.074	0.006	2.47%
Month 6	2.9		3.141	-0.241	0.241	0.058	8.30%
Month 7	2.8		3.026	-0.226	0.226	0.051	8.06%
Month 8	3.6		2.904	0.696	0.696	0.485	19.34%
Month 9	3.2		2.971	0.229	0.229	0.053	7.17%
			Average		0.307	0.132	9.51%
Next period	3.348				MAD	MSE	MAPE

Sum of weights =		1.000

This column contains the optimal weights.

Target is to minimize MAPE

=SUM(C7:C9)

Enter the data in the cells shaded YELLOW.

Jeannette Phan is a college student who has just completed her junior year. The following table summarizes her grade point

average (GPA) for each of the past nine semesters.

___________________________________________________________________

Year Semester GPA

___________________________________________________________________

Fresman Fall 2.4

Winter 2.9

Spring 3.1

Sophome Fall 3.2

Winter 3.0

Spring 2.9

Junior Fall 2.8

Winter 2.6

Spring 3.2

____________________________________________________________________

A). Forecast Jeannette’s GPA for the fall semester of her senior year by using a three-period moving average.

B). Forecast Jeannette’s GPA for the fall semester of her senior year by using exponential smoothing with

a = 0.3

C). Which of the two methods provides a more accurate forecast? Justify your answer.

D). If you decide to use a three-period weighted moving average, find the optimal weights that would minimize MAPE. Is

this method an improvement over the previous two methods?