Concept explainers
Quarterly revenue ($ millions) for Twitter for the first quarter of 2012 through the first quarter of 2014 are shown below (adexchange.com, April 2015):
- a. Construct a time series plot. What type of pattern exists in the data?
- b. Using Excel or Minitab, develop a linear trend equation for this time series.
- c. Using Excel or Minitab, develop a quadratic trend equation for this time series.
- d. Compare the MSE for each model. Which model appears better according to MSE?
- e. Use the models developed in parts (b) and (c) to forecast revenue for the tenth quarter.
- f. Which of the two forecasts in part (e) would you use? Explain.
a.
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.
b.
Find the linear trend equation for the data.
Answer to Problem 25E
The linear trend equation is
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
c.
Find the quadratic trend equation for the data.
Answer to Problem 25E
The quadratic trend equation is
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
d.
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:
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.
e.
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
The forecast revenue for the tenth quarter using the linear model is obtained as follows:
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
The forecast revenue for the tenth quarter using the quadratic model is obtained as follows:
Thus, the forecast revenue for the tenth quarter using the linear model is 262.614.
f.
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.
Want to see more full solutions like this?
Chapter 17 Solutions
Modern Business Statistics with Microsoft Office Excel (with XLSTAT Education Edition Printed Access Card)
- Table 6 shows the year and the number ofpeople unemployed in a particular city for several years. Determine whether the trend appears linear. If so, and assuming the trend continues, in what year will the number of unemployed reach 5 people?arrow_forwardConvert the following annual trend equation on a monthly basis : Y = 10.6 + 0.8X + 0.64 Xarrow_forwardDescribe the three major categories of data (time-series, cross-sectional, and panel).arrow_forward
- The following data report total, monthly U.S. gasoline sales in millions of dollars from January 2017 to December 2018. (To find the data, go to the site https://www.census.gov/retail/index.html#mrts, find “Monthly Retail Trade Report,” then select “Time Series/Trend Charts.”) d. Evaluate a seasonally adjusted trend forecasting model. What is the MAD? (Round your answers to 3 decimal places.)arrow_forwardthe data contained in the datafile named crudecost shows the U.S. refiner acquisition cost of crude oil in dollars per barrel (energy information administration website,February 3, 2014).a. construct a time series plot. What type of pattern exists in the data?arrow_forwardWrite the equation of a trend line.arrow_forward
- In Colorado, sales of medical marijuana began in November 2012; however, the Department of Revenue did not report tax collection data until February of 2014. The accompanying data file includes monthly revenue from medical and retail marijuana tax and fee collections as posted in the Colorado state accounting system. Use the linear trend model (no seasonality) to forecast the tax revenue for November and December of 2018. Date Revenue February/14 3,519,756 March/14 4,092,575 April/14 4,980,992 May/14 5,273,355 June/14 5,715,707 July/14 6,522,085 August/14 7,407,450 September/14 7,741,167 October/14 7,232,870 November/14 7,642,800 December/14 7,465,568 January/15 8,558,141 February/15 8,802,295 March/15 9,099,395 April/15 9,979,643 May/15 10,617,311 June/15 11,326,452 July/15 10,856,584 August/15 12,811,437 September/15 13,181,758 October/15 11,656,736 November/15 11,290,012 December/15 12,231,410 January/16 13,247,434 February/16…arrow_forwardUsing the following time series data of U.S. new car sales ($ millions), respond to the following items. (The data is sourced from Find "Monthly Retail Trade Report," then select "Time Series/Trend Charts" to find the https://www.census.gov/retail/index.html#mrts. data for U.S. new car sales.). Year $ Sales (Millions) 2009 2010 $ 486,565 549,473 2011 608,792 2012 672,550 2013 735,374 2014 785,142 2015 842,120 2016 2017 2018 877,743 911,157 943,538arrow_forwardCryptocurrencies have rapidly become an important alternative to traditional currencies for many types of transactions. Etherium, one of the most prominent cryptocurrencies, has rapidly appreciated in value. Daily Etherium trading information for the first 332 days of 2021. It includes the following variables: Date Day – Day of the year, used to assess trend over time Volume (US $) – Daily trading volume Opening Price (US $) – Opening price for daily trading Price Change (US $) – Daily change in price from opening to close 1. Determine the sample correlation coefficient, r, between Volume and Price Change. Test the alternative hypothesis that Volume has a linear relationship to Price Change. Specifically, what are the test statistic and the p-value for that test statistic? For α = .05, what do you conclude about the relationship between the variables? (reminder: the T.DIST.2T function requires input of a positive test statistic)arrow_forward
- Using the following time series data of U.S. new car sales ($ millions), respond to the following items. (The data is sourced from https://www.census.gov/retail/index.html#mrts. Find “Monthly Retail Trade Report,” then select “Time Series/Trend Charts” to find the data for U.S. new car sales.). Year $ Sales (Millions) 2009 486,565 2010 549,473 2011 608,788 2012 672,550 2013 735,374 2014 785,142 2015 842,122 2016 877,743 2017 911,157 2018 943,538 b. Test for autocorrelation using the 0.05 significance level. (Negative amounts should be indicated by a minus sign. Round your answers to 2 decimal places.) Year $ Sales (Millions) Predicted Residuals Lagged Residuals Squared Difference Squared Residuals 2009 486,565 2010 549,473 2011 608,788 2012 672,550 2013 735,374 2014 785,142 2015 842,122 2016 877,743 2017 911,157 2018 943,538…arrow_forwardFor the Texas Shipping Company, the monthly percentages of all shipments received on time over the past 12 months are 82, 86, 87, 89, 84, 85, 84, 86, 75, 95, 90, and 92. a. Construct a time series plot (you can include it in the work file question at the end, not here). Identify what type of pattern, if any, exists in the data? Compare the three-month moving average forecast with an exponential smoothing forecast for alpha= 0.25 and answer the following: b. MSE for 3-month moving average method c. MSE for exponential-smoothing method : d. Which method should be preferred? Answer "3-month MA" or "Exponential Smoothing": e. What is the forecast for next month using the preferred method?arrow_forwardThe table and graph below both display the percentage of people in the United States who do not have health insurance over several different years. Data downloaded on 2/19/2020 from https://ourworldindata.org/health-meta. 30.0% 25.0% € 20.0% 15.0% 10.0% 5.0% Year 0.0% Part 1 24.3% 14.5% 1960 1970 1980 1963 1968 1994 2012 Percent without Health Insurance 24.3% 14.5% 15.8% 14.7% Part 2 Use the table to answer the questions in each part below. 15.8% Part 3 Which variable is the independent variable and which is the dependent variable? Choose the correct response below. You only get one submission. T 14.7% 1990 2000 2010 2020 Year O Percent of people without health insurance is the independent variable and year is the dependent variable. O Year is the independent variable and percent of people without health insurance is the dependent variable. Average Rate of Change In what unit is the average rate of change in the percentage of people without health insurance measured? You get two…arrow_forward
- Glencoe Algebra 1, Student Edition, 9780079039897...AlgebraISBN:9780079039897Author:CarterPublisher:McGraw HillBig Ideas Math A Bridge To Success Algebra 1: Stu...AlgebraISBN:9781680331141Author:HOUGHTON MIFFLIN HARCOURTPublisher:Houghton Mifflin Harcourt
- Trigonometry (MindTap Course List)TrigonometryISBN:9781305652224Author:Charles P. McKeague, Mark D. TurnerPublisher:Cengage LearningHolt Mcdougal Larson Pre-algebra: Student Edition...AlgebraISBN:9780547587776Author:HOLT MCDOUGALPublisher:HOLT MCDOUGAL