Chapter 17.4, Problem 25E

Quarterly revenue ($ millions) for Twitter for the first quarter of 2012 through the first quarter of 2014 are shown below (adexchange.com, April 2015):

1. a. Construct a time series plot. What type of pattern exists in the data?
2. b. Using Excel or Minitab, develop a linear trend equation for this time series.
3. c. Using Excel or Minitab, develop a quadratic trend equation for this time series.
4. d. Compare the MSE for each model. Which model appears better according to MSE?
5. e. Use the models developed in parts (b) and (c) to forecast revenue for the tenth quarter.
6. f. Which of the two forecasts in part (e) would you use? Explain.

To determine

Construct the time series plot.

Explain the type of pattern.

Answer to Problem 25E

The time series plot is given below:

The pattern that appears in the graph is the upward trend.

Explanation of Solution

Calculation:

The given data shows the quarterly revenue for the first quarter of 2012 to the first quarter of 2014.

Software procedure:

Step-by-step software procedure to draw the time series plot using EXCEL:

• Open an EXCEL file.
• In column A, enter the data of Quarter, and in column B, enter the corresponding values of Revenue.
• Select the data that are to be displayed.
• Click on the Insert Tab > select Scatter icon.
• Choose a Scatter with Straight Lines and Markers.
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart and enter Time Series Plot.
• Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
• Enter Quarter in the dialog box.
• Select Axis Title > Primary Vertical Axis Title > Rotated Title.
• Enter Revenue in the dialog box.

From the output, the line of graph represents the increase of revenue from year to year.

Thus, the pattern of the graph represents the upward trend.

To determine

Find the linear trend equation for the data.

Answer to Problem 25E

The linear trend equation is $T_t=10.944+25.167t$.

Explanation of Solution

Calculation:

The linear trend equation is to be obtained.

Software procedure:

Step-by-step software procedure to find the linear trend equation using EXCEL:

• Open an EXCEL file.
• In column A, enter the data of Quarter, and in column B, enter the corresponding values of Revenue.
• Select the data that are to be displayed.
• Click on the Insert Tab > select Scatter icon.
• Choose a Scatter with Straight Lines and Markers.
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart and enter Time Series Plot.
• Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
• Enter Quarter in the dialog box.
• Select Axis Title > Primary Vertical Axis Title > Rotated Title.
• Enter Revenue in the dialog box.
• Right Click at any point in the time series plot and select Add Trendline.
• Select Linear under TrendLine Options.
• Choose Display Equation on Chart.

Output using EXCEL is given below:

From the output, the linear trend equation is $\underset{\_}{T_t=10.944+25.167t}$.

To determine

Find the quadratic trend equation for the data.

Answer to Problem 25E

The quadratic trend equation is $\underset{\_}{T_t=53.286+2.0714t+2.3095t^2}$.

Explanation of Solution

Calculation:

The quadratic trend equation is to be obtained.

Software procedure:

Step-by-step software procedure to find the linear trend equation using EXCEL:

• Open an EXCEL file.
• In column A, enter the data of Quarter, and in column B, enter the corresponding values of Revenue.
• Select the data that are to be displayed.
• Click on the Insert Tab > select Scatter icon.
• Choose a Scatter with Straight Lines and Markers.
• Click on the chart > select Layout from the Chart Tools.
• Select Chart Title > Above Chart and enter Time Series Plot.
• Select Axis Title > Primary Horizontal Axis Title > Title Below Axis.
• Enter Quarter in the dialog box.
• Select Axis Title > Primary Vertical Axis Title > Rotated Title.
• Enter Revenue in the dialog box.
• Right Click at any point in the time series plot and select Add Trendline.
• Select Polynomial and enter Order as 2 under TrendLine Options.
• Choose Display Equation on Chart.

Output using EXCEL is given below:

From the output, the quadratic trend equation is $\underset{\_}{T_t=53.286+2.0714t+2.3095t^2}$.

To determine

Identify the model that appears better according to MSE.

Answer to Problem 25E

The quadratic model that appears better according to MSE.

Explanation of Solution

Calculation:

The formula for finding mean square error (MSE) is as follows:

$MSE=\frac{\text{Residual sum of squares}}{\text{Degrees of freedom}}$

For linear trend model:

Software procedure:

Step-by-step procedure to obtain MSE using EXCEL:

• In column A, enter the data of Quarter, and in column B, enter the corresponding values of Revenue.
• In Data, select Data Analysis and choose Regression.
• In Input Y Range, select Revenue.
• In Input X Range, select Quarter.
• Select Labels.
• Click OK.

Output obtained using EXCEL is given below:

From the output, the MSE is 408.5556.

For Quadratic trend model:

Software procedure:

Step-by-step procedure to obtain MSE using EXCEL:

• In column A, enter the data of Quarter, in column B, enter the data of Quarter Square and in column C, enter the corresponding values of Revenue.
• In Data, select Data Analysis and choose Regression.
• In Input Y Range, select Revenue.
• In Input X Range, select Quarter and Quarter Square.
• Select Labels.
• Click OK.

Output obtained using EXCEL is given below:

From the output, MSE is 202.8413.

Here, MSE for the quadratic trend equation is less than MSE for the linear trend equation. Hence, the quadratic model provides more accurate forecasts based on MSE.

To determine

Find the forecast revenue for the tenth quarter using the models developed in Parts (b) and (c).

Answer to Problem 25E

The forecast revenue for the tenth quarter using the linear model is 262.614.

The forecast revenue for the tenth quarter using the quadratic model is 304.95.

Explanation of Solution

Calculation:

From the results of Part (b), the trend line equation is $T_t=10.944+25.167t$.

The forecast revenue for the tenth quarter using the linear model is obtained as follows:

$\begin{array}{c}{T}_t=10.944+25.167t\\ T_{10}=10.944+25.167\left(10\right)\\ =10.944+251.67\\ =262.614\end{array}$

Thus, the forecast revenue for the tenth quarter using the linear model is 262.614.

From the results of Part (b), the trend line equation is $T_t=53.286+2.0714t+2.3095t^2$.

The forecast revenue for the tenth quarter using the quadratic model is obtained as follows:

$\begin{array}{c}{T}_t=53.286+2.0714t+2.3095t^2\\ T_{10}=53.286+2.0714\left(10\right)+2.3095\left({10}^2\right)\\ =53.286+20.714+230.95\\ =304.95\end{array}$

Thus, the forecast revenue for the tenth quarter using the linear model is 262.614.

To determine

Identify the forecast that is used in Part (e).

Answer to Problem 25E

The forecast revenue for the tenth quarter using the quadratic models is used.

Explanation of Solution

From Part (d), MSE for the quadratic trend equation is less than MSE for the linear trend equation. Hence, the quadratic model provides more accurate forecasts based on MSE. Thus, the forecast revenue for the tenth quarter using the quadratic models is used.