Date	Auber monthly trips (mill)
Jan-18	374.5	Assume today's date is Dec 31, 2021. To answer the following questions, use Date and Auber monthly trips (mill) provided in sheet 'Auber monthly trips'
Feb-18	374.7
Mar-18	412.5	Obtain the seasonal indexes for Jan to Dec based on the past FOUR years (2018-2021) in yellow cells below.
Apr-18	403.1	Obtain the Seasonally Adjusted Actual for Jan '21 to Dec '21 in orange cells below.
May-18	418.4	Using only 2021 data and Exponential Smoothing with Trend (=0.3, =0.5), forecast Trips (mill) for Jan '22 in light blue cell below.
Jun-18	418.2	Obtain MSE for 2021 in gray cell below.
Jul-18	447.5	Using Excel's Solver, obtain the optimal parameters ( and ) that minimize MSE for 2021. GENERATE SOLVER'S ANSWER REPORT
Aug-18	443.7
Sep-18	450.4
Oct-18	472.9		Actual	Actual	Actual	Actual	Seasonal Index	Seasonally Adj Actual
Nov-18	452.1		2018	2019	2020	2021		2021
Dec-18	431.3	Jan
Jan-19	528.3	Feb
Feb-19	532.8	Mar
Mar-19	513.3	Apr
Apr-19	479.1	May
May-19	500.3	Jun
Jun-19	500.3	Jul
Jul-19	570.5	Aug
Aug-19	625.9	Sep
Sep-19	638.9	Oct
Oct-19	693.8	Nov
Nov-19	683.7	Dec
Dec-19	669.2	Annual
Jan-20	672.8
Feb-20	691.9
Mar-20	672.8	Import Trips actual data and Seasonal Indexes from sheet 2
Apr-20	213.3
May-20	201.9		Actual	Actual	Actual	Actual	Seasonal Index		Forecast
Jun-20	226.7		2018	2019	2020	2021			2022
Jul-20	384.5	Jan
Aug-20	371.9	Feb
Sep-20	358.7	Mar
Oct-20	402	Apr
Nov-20	402	May
Dec-20	411.2	Jun
Jan-21	390.8	Jul
Feb-21	424.4	Aug
Mar-21	404.4	Sep
Apr-21	478	Oct
May-21	441.9	Nov
Jun-21	449	Dec
Jul-21	582.9	Annual
Aug-21	622.9
Sep-21	621.5
Oct-21	581.2	Using Year and annual trips 2018-2021, obtain the LINEAR equation to estimate trend from the Excel's Scatter Diagram
Nov-21	633.1
Dec-21	620.1	Paste Scatter Diagram --------------------------------------------------------------------------------------------------------------------------------->
		Using the linear trend equation, forecast annual trips (mill) for year 2022 (enter answer in orange cell below)
		Enter Forecast Annual Trips (mill) for 2022 ------>
		Using the seasonal indexes obtained and your forecast for Year 2022, forecast monthly trips (mill) for Year 2022
		(enter values in appropriate orange cells above)

Assume today's date is Dec 31, 2021. To answer the following questions, use Date and Auber monthly trips (mill) provided in sheet 'Auber monthly trips' Obtain the seasonal indexes for Jan to Dec based on the past FOUR years (2018-2021) in yellow cells below. Obtain the Seasonally Adjusted Actual for Jan 21 to Dec 21 in orange cells below. Using only 2021 data and Exponential Smoothing with Trend (a=0.3, B=0.5), forecast Trips (mill) for Jan '22 in light blue cell below. Obtain MSE for 2021 in gray cell below. Using Excel's Solver, obtain the optimal parameters (a and ) that minimize MSE for 2021. GENERATE SOLVER'S ANSWER REPORT Seasonal Seasonally Actual Actual Actual Actual Index Adj Actual Forecast 2018 2019 2020 2021 2021 2022 Jan 374.5 528.3 672.8 390.8 1.0129425 385.80669