Arrow Air is a regional East Coast airline that has collected data for the percentage of available seats occupied on its flights for four quarters(1) JanuaryMarch, (2) AprilJune, (3) July September, and (4) OctoberDecemberfor the past 5 years. Arrow Air also has collected data for the average percentage fare discount for each of these quarters, as follows:

Part (a) Develop a seasonally adjusted forecast model for seat occupancy. Forecast seat occupancy for year 6 by using a linear trend line forecast estimate for seat occupancy in year 6.

Part (b) Develop linear regression models relating seat occupancy to discount fares in order to forecast seat occupancy for EACH QUARTER separately in year 6. Assume a fare discount of 20% for quarter 1, 36% for quarter 2, 25% for quarter 3, and 30% for quarter 4.

Part (c) Compare the forecasts developed in (a) and (b) and indicate which one appears to be the best. Just an intuitive assessment would be enough.

PLEASE INCLUDE STEP BY STEP DIRECTIONS FOR CALCULATIONS, CONSTRAINTS USING THE SOLVER TOOL ON EXCEL, AND SET UP/LAYOUT FOR PROBLEM IN EXCEL.

\begin{tabular}{cccc} \hline Year & Quarter & \begin{tabular}{c} Seat \\ Occupancy \\ (%) \end{tabular} & \begin{tabular}{c} Average \\ Fare Discount \\ (%) \end{tabular} \\ \hline 1 & 1 & 63 & 21 \\ & 2 & 75 & 34 \\ & 3 & 76 & 18 \\ 2 & 4 & 58 & 26 \\ & 1 & 59 & 18 \\ & 2 & 62 & 40 \\ & 3 & 81 & 25 \\ 3 & 4 & 76 & 30 \\ & 1 & 65 & 23 \\ & 2 & 70 & 28 \\ 4 & 3 & 78 & 30 \\ & 4 & 69 & 35 \\ & 1 & 59 & 20 \\ & 2 & 61 & 35 \\ & 3 & 83 & 26 \\ 5 & 4 & 71 & 30 \\ & 1 & 60 & 25 \\ & 2 & 66 & 37 \\ & 3 & 86 & 25 \\ & 4 & 74 & 30 \\ \hline \end{tabular}