Aveces Airline has collected data for the percentage of available seats occupied on its flights per quarter for the past 5 years. Aveces has also collected data for the average percentage fare discount for each of these quarters, as follows:

Year Quarter Seat Occupancy(%) Average Fare Discount(% )

1 1 63 21

2 75 34

3 76 18

4 58 26

2 1 59 18

2 62 40

3 81 25

4 76 30

3 1 65 23

2 70 28

3 78 30

4 69 35

4 1 59 20

2 61 35

3 83 26

4 71 30

5 1 60 25

2 66 37

3 86 25

4 74 30

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.

b)Develop linear regression models relating seat occupancy to discount fares to forecast seat occupancy for each quarter in year 6. This will require that you calculate slope and intercept figures of discount % vs. seat occupancy % for each quarter given the data above.Then, calculate the forecast of seat occupancy given a fare discount of 20% for quarter 1, 36% for quarter 2, 25% for quarter 3, and 30% for quarter 4.

c)Compare the forecasts developed in (a) and (b) and indicate which one appears to be the best.

Please use excel, show formulas, and how to get forecast. Screenshot will help