Use the data file A3Q2.xlsx to answer this question. Air pollution control specialists in southern California monitor the amount of ozone, carbon dioxide, and nitrogen dioxide in the air on an hourly basis. The hourly time series data exhibit seasonality, with the levels of pollutants showing patterns that vary over the hours in the day. File A3Q2.xlsx contains the levels of nitrogen dioxide on July 15, 16, and 17 which were observed for the 12 hours from 6:00 a.m. to 6:00 p.m.

a. Construct a time series plot. What type of pattern exists in the data?

b. Use a multiple linear regression model with dummy variables as follows to develop an equation to account for seasonal effects in the data:

Hour1 = 1 if the reading was made between 6:00 a.m. and 7:00a.m.; 0 otherwise;

Hour2 = 1 if the reading was made between 7:00 a.m. and 8:00 a.m.; 0 otherwise;

Hour11 = 1 if the reading was made between 4:00 p.m. and 5:00 p.m., 0 otherwise

Note that when the values of the 11 dummy variables are equal to 0, the observation corresponds to the 5:00 p.m. to 6:00 p.m. hour. Recall that you can use the Excel IF function to define the dummy variables.

c. Using the equation developed in part (b), compute estimates of the levels of nitrogen dioxide for July 18 (the next day)

d. Let t = 1 to refer to the observation in hour 1 on July 15; t = 2 to refer to the observation in hour 2 of July 15; . . . ; and t = 36 to refer to the observation in hour 12 of July 17. Using the dummy variables defined in part (b) and the new variable t, develop an equation to account for seasonal effects and any linear trend in the time series.

e. Based on the seasonal effects in the data and linear trend estimated in part (d), compute estimates of the levels of nitrogen dioxide for July 18.

1	25
2	28
3	35
4	50
5	58
6	61
7	40
8	35
9	30
10	25
11	25
12	22
1	29
2	31
3	36
4	49
5	61
6	66
7	51
8	41
9	36
10	26
11	21
12	21
1	37
2	44
3	47
4	72
5	74
6	77
7	62
8	47
9	42
10	27
11	27
12	27
Hour	Level