Question

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: GPA 2.4 2.9 3.1 3.2 3.0 2.9 2.8 3.6 3.2 YEAR SEMESTER Fall Winter Spring Fall Winter Spring Fall Winter Spring Freshman Sophomore Junior

Forecasting Models.

This is one problem that requires the use of Excel newly created, blank Excel file, with no use of copy and pasting anything into the spreadsheet or use any kind of a template.

(a) Calculate the forecast of Jeannette's GPA for the fall semester of her senior year using a 3-period moving average.

(b) Calculate the forecast of Jeannette's GPA for the fall semester of her senior year using a 3-period weighted moving average, with the weights 1, 3, and 5 (with 5 being for the most recent data).

(c) Calculate the forecast of Jeannette's GPA for the fall semester of her senior year using exponential smoothing with an alpha value of 0.20. Assume that Jeannette had forecast her first semester GPA to be a 3.0 before starting college.

(d) Calculate the forecast of Jeannette's GPA for the fall semester of her senior year using linear regression.

(e) Use the MAD to determine which forecasting model is expected to perform the best.

When answering the questions round all forecasts and MAD values to 2 decimal places.

Thank you.

Answer 1

We first create the following table in excel

Year 1 Freshman Fall 2 Semester GPA 2.4 2.9 3.1 3.2 3.0 2.9 2.8 3.6 3.2 2 Winter 4 Sprin 4 Sophomore Fall Winter Sprin Fall Winter Sprin Fall 7 Junion 10 9 10 Senior

a) 3 period moving average is calculated as

$\text{Moving average}=\frac{\sum \text{GPA of past 3 terms}}{3}$

The first average we can calculate is for Sophomore, Fall. The 3 week moving average is

$\begin{align*} \text{Moving average for Fall, Sophomore}&=\frac{\sum \text{GPA of past 3 terms}}{3}\\ &=\frac{(\text{GPA-Freshman,Fall})+(\text{GPA-Freshman,Winter})+(\text{GPA-Freshman,Spring})}{3}\\ &=\frac{2.4+2.9+3.1}{3}\\&=2.80 \end{align*}$We calculate the rest as below

3 period moving average forecast GPA 2.4 2.9 3.1 3.2 Year Semester Fall Winter Sprin Freshman 3 2 SUM D2:D4)/3 SUM(D3:DS)/3 SUM(D4:D6)/3 -SUM(D5:D7)/3 SUM(D6:D8)/3 SUM(D7:D9)/3 SUM(D8:D10)/3 Sophomore Fall Winter Sprin Fall Winter Spring Fall 2.9 2.8 3.6 3.2 Junior 10 9 11 10 Senior

and get these forecasts

3 period moving average forecast 2.4 2.9 3.1 3.2 3.0 2.9 2.8 3.6 3.2 1 t Year 2 1Freshman Fall Semester GPA 2 Winter Sprin 2.80 3.07 3.10 3.03 2.90 3.10 3.20 5 4Sophomore Fall Winter Sprin Fall Winter Sprin Fall 8 7 Junior 10 9 11 10 Senior

The forecast of Jeannette's GPA for the fall semester of her senior year is 3.20

b) 3 period weighted moving average is calculated as

$\text{Moving average}=\frac{\sum \text{weight}\times (\text{GPA of past 3 terms})}{\text{sum of weights}}$

The first average we can calculate is for Sophomore, Fall. The 3 week weighted moving average is

$\begin{align*} \text{Weighted moving average for Fall, Sophomore}&=\frac{\sum \text{weight}\times \text{GPA of past 3 terms}}{\text{sum of weights}}\\ &=\frac{1\times (\text{GPA-Freshman,Fall})+3\times (\text{GPA-Freshman,Winter})+5\times (\text{GPA-Freshman,Spring})}{1+3+5}\\ &=\frac{1\times 2.4+3\times 2.9+5\times 3.1}{9}\\&=2.96 \end{align*}$

We calculate the rest as below

get these forecasts

The forecast of Jeannette's GPA for the fall semester of her senior year is 3.29

c) The exponential smoothing forecast with $\begin{align*}\alpha=0.2 \end{align*}$ is

$\begin{align*}F_{t+1}&=\alpha \text{GPA}_t+(1-\alpha)F_t\\ &=0.2\text{GPA}_t+(1-0.2)F_t\\ \end{align*}$

where $\begin{align*}F_{t+1}, F_t\\ \end{align*}$ are forecasts for period t+1 and t respectively and $\begin{align*}GPA_t \end{align*}$ is the observed value of GPA for period t

the first period for which we can get the exponential forecast is for t=2, Freshman, Winter. The assumption is the the forecast for term Freshman fall $\begin{align*}F_1=3.0 \end{align*}$

For example the forecast for Freshman, Winter (t=2) is

$\begin{align*}F_{2}&=0.2\text{GPA}_1+(1-0.2)F_1\\ &=0.2\times 2.4+(1-0.2)3.0\\ &=2.88 \end{align*}$

The rest are below

get these forecasts

The forecast of Jeannette's GPA for the fall semester of her senior year using exponential smoothing with an alpha value of 0.20 is 3.09

d) the regression model that we want to estimate is

$\begin{align*}\text{GPA}=\beta_0+\beta_1t+\epsilon \end{align*}$

where $\begin{align*}\beta_0 \end{align*}$ is the intercept of the regression line

$\begin{align*}\beta_1 \end{align*}$ is the slope coefficient for time t

$\begin{align*}\epsilon\stackrel{iid}\sim\mathcal{N}(0,\sigma^2) \end{align*}$ is a random error

We estimate the regression using data-->data analysis--->regression

Regression 3 period 3 period weighted moving movingExponential average vge smoothing Input Input Y Range: Input X Range: SD$1:SD$10 SA$1:SA$10 Constant is Zero Semester GPA forecast forecast forecast 1 t Year 2 1Freshman Fall 3 2 Help 2.4 2.9 Labels [ Confidence Level: 195 % Winter Spring3. 2.88 Output options 2.93 2.98 2.99 2.97Residuals 5 4 Sophomore Fall 6 5 3.2 3.0 2.9 2.8 3.6 3.2 2.80 3.07 3.10 3.03 $B$14 O Output Range: ONew Worksheet Ply: ONew Workbook Winter Sprin Fall Winter Sprin Fall 3.13 8 7Junior 2.97 2.93Residuals 3.07Standardized Residuals Line Fit Plots 3.09 10 9 11 10 Senior 12 13 14 3.10 3.26 3.29 3.20 Normal Probability Normal Probability Plots

get these

SUMMARY OUTPUT Regression Statistics Multiple R R Square Adjusted RS Standard Err Observation 0.6094 0.3714 0.2815 0.2793 9 ANOVA Ms Significance F Regression Residual Total 1 0.322666667 0.322667 4.1350692 0.081492385 7 0.546222222 0.078032 0.868888889 Coefficients Standard Error tstat P-value Lower 95%Uer 95% 0.2029 13.03086 3.651E-06 2.164574719 3.124314 0.03612.033487 0.0814924 -0.01194175 0.158608 Intercept 2.644 0.073

the estimated regression line is

$\begin{align*}\widehat{\text{GPA}}=2.644+0.073t \end{align*}$

For example we can forecast the GPA for Freshman, Fall (t=1) using

$\begin{align*}\widehat{\text{GPA}}=2.644+0.073\times 1=2.72 \end{align*}$

The calculations are below

get these values

3 period weighted 3 period average forecast Exponential smoothing forecast moving moving average Forecast using linear Year Semester GPA forecast regression Freshman Fall 2.4 2.9 3.1 3.2 2.644+0.073 A2 2.644+0.073 A3 2.644+0.073 A4 2.644+0.073 A5 2.644+0.073 *A6 2.644+0.073 A7 2.644+0.073 A8 2.644+0.073 A9 2.644+0.073 A10 2.644+0.073 A11 -0.2 D2+1-0.2) G2 -0.2 D3+1-0.2) G3 3 2 Winter Sprin Sophomore Fall Winter Sprin Fall Winter Sprin Fall SUM (D4:D6)/3 SUM(D5:D7)/3 (D5*1+D6*3+D7*5)/(1+-0.2*D7+(1-0.2)*G7 SUM(D6:D8)/3 (D4*1+D5*3+D6*5)/(1+-0.2 D6+(1-0.2)*G6 2.9 2.8 3.6 3.2 Junior D6 1+D7*3+D8*5)/1+-0.2 D8+1-0.2)*G8 10 9 11 10 Senior -SUM(D8:D10)/3 (D8*1+D9*3+D10*5)/(1-0.2*D10+(1-0.2) G10

3 period 3 period weighted moving moving Exponential using Forecast average average smoothing linear 1 t Year 2 1 Freshman Fall Semester GPA forecast forecast forecast 2.4 2.9 3.1 3.2 3.0 2.9 2.8 3.6 3.2 2.88 2.88 2.93 2.98 2.99 2.97 2.93 3.07 3.09 regression 2.72 2.79 2.86 2.94 3.01 3.08 3.16 3.23 3.30 3.37 Winter 4 Spring 5 4 Sophomore Fall 2.80 3.07 3.10 3.03 2.90 3.10 3.20 2.96 3.13 3.08 2.97 2.86 3.26 3.29 6 Winter Sprin Fall Winter Spring Fall 87Junior 10 9 11 10 Senior

the forecast of Jeannette's GPA for the fall semester of her senior year using linear regression is 3.37

e) Mean Absolute Deviation (MAD) is calculated using

$\begin{align*}\text{MAD}=\frac{\sum \left|\text{GPA}-\text{Forecast}\right|}{n} \end{align*}$

To compare the MAD, we only have forecasts from Sophomore, fall to Junior, Spring for each of the forecasts methods. Hence we will use only those 6 values.

The following calculates the MAD

Absolute Deviation 3 period period movingweight Exponentiausing average ed forecast movingforecast regression average forecast forecast Forecast 3 period weighted I smoothing linear 3 period moving moving average Exponential Forecast using 2 GPA 3 2.4 4 2.9 5 3.1 smoothing forecast linear regression 2.644+0.0 0.2 D3+(1- 2.644+0.0 -0.2 D4+1-0-2.644+0.0 -SUM(D3: (D310.2 D5+1--2.644+0.0 ABS(SD6-E6 ABS(SD6-F6) 8 2.9 9 2.8 10 3.6 11 3.2 12 13 -SUM(D5:D510.2 D7+1--2.644+0.0 ABS(SD8-E8 ABS(SD8-F8) -SUM(D6:(D6*10.2*D8 (1-2.644+H0.07-ABS(SD9-E9) ABS(SD9-F9) A -SUM/D7:d=(D7*1+ 0.2"D9+( 1-(-2.644+0.0 =ABS(SD10-E10) |=ABS(SD10-F10) |=ABS(SD10-G10) SUM D8:4:08*1+1:0.2"DICH 1 -2.644+0.0 BS(SD11-E11 ABS(SD11-F11 ABS(SD11-G11) -SUM(D9:q=1D9*1+1=0.2*D11+11-2.644+0.0 ABS(SD9-G9) -ABS(ŞD9-H9) -ABS(SD10-H10 BS(SD11-H11) MAD AVERAGE(16:11) -AVERAGE(U6:J11) -AVERAGE(K6:K11) -AVERAGEL6L11)

get these values

We can see that the Linear model has the least MAD.

Linear regression model is expected to perform the best.