Question

In: Statistics and Probability

(a) Create the correct time series plot. Which type of pattern exists in the data?

Consider the following time series data.

Quarter Year 1 Year 2 Year 3
1 4 6 7
2 0 1 4
3 3 5 6
4 5 7 8

(a) Create the correct time series plot. Which type of pattern exists in the data?

(b) Use a multiple regression model with dummy variables as follows to develop an equation to account for seasonal effects in the data. Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise. If required, round your answers to three decimal places. For subtractive or negative numbers, use a minus sign even if there is a + sign before the blank. (Example: -300) If the constant is 1, it must be entered in the box. Do not round intermediate calculations.

(c) Compute the quarterly forecasts for next year based on the model you developed in part (b). If required, round your answers to three decimal places. Do not round intermediate calculations.

(d) Use a multiple regression model to develop an equation to account for trend and seasonal effects in the data. Use the dummy variables you developed in part (b) to capture seasonal effects and create a variable t such that t = for Quarter 1 in Year 1, t = 2 for Quarter 2 in Year 1,... t = 12 for Quarter 4 in Year 3. If required, round your answers to three decimal places. For subtractive or negative numbers, use a minus sign even if there is a + sign before the blank. (Example: -300)

(e) Compute the quarterly forecasts for next year based on the model you developed in part (d). Do not round your interim computations and round your final answer to three decimal places.

(f) Find MSE of the model developed in part (b) and the model developed in part (d). Which one is the more effective model? If required, round your intermediate calculations and final answer to three decimal places.

---

If possible, for the tasks that require to use Excel, can you please help show me how to do those steps in Excel, especially how to create the time series plot in part a?

Thank you very much.

Solutions

Expert Solution

a)

data

t y
1 4
2 0
3 3
4 5
5 6
6 1
7 5
8 7
9 7
10 4
11 6
12 8

select data , go to insert -> recommended chart

in left side you will see graph of below type

there is positive trend and seasonality

b)

y t Q1 Q2 Q3
4 1 1 0 0
0 2 0 1 0
3 3 0 0 1
5 4 0 0 0
6 5 1 0 0
1 6 0 1 0
5 7 0 0 1
7 8 0 0 0
7 9 1 0 0
4 10 0 1 0
6 11 0 0 1
8 12 0 0 0

regress y on Q1,Q2 and Q3

SUMMARY OUTPUT          
           
Regression Statistics        
Multiple R 0.805906034        
R Square 0.649484536        
Adjusted R Square 0.518041237        
Standard Error 1.683250823        
Observations 12        
           
ANOVA          
  df SS MS F Significance F
Regression 3 42 14 4.941176 0.031487607
Residual 8 22.66667 2.833333    
Total 11 64.66667      
           
  Coefficients Standard Error t Stat P-value Lower 95%
Intercept 6.666666667 0.971825 6.859943 0.00013 4.42563347
Q1 -1 1.374369 -0.72761 0.4876 -4.169299541
Q2 -5 1.374369 -3.63803 0.006608 -8.169299541
Q3 -2 1.374369 -1.45521 0.183698 -5.169299541

y^= 6.667 - 1 Q1 -5 Q2 -2Q3

c)

quarter forecast
1 5.667
2 1.667
3 4.667
4 6.667

d)

now regress y on t, q1,q2 and q3

SUMMARY OUTPUT          
           
Regression Statistics        
Multiple R 0.988007993        
R Square 0.976159794        
Adjusted R Square 0.962536819        
Standard Error 0.469295318        
Observations 12        
           
ANOVA          
  df SS MS F Significance F
Regression 4 63.125 15.78125 71.65541 9.24E-06
Residual 7 1.541666667 0.220238    
Total 11 64.66666667      
           
  Coefficients Standard Error t Stat P-value Lower 95%
Intercept 3.416666667 0.428406053 7.9753 9.3E-05 2.403647
t 0.40625 0.041480238 9.79382 2.45E-05 0.308165
Q1 0.21875 0.402878254 0.542968 0.604002 -0.73391
Q2 -4.1875 0.392055911 -10.6809 1.38E-05 -5.11456
Q3 -1.59375 0.385416667 -4.13514 0.004376 -2.50512

y^= 3.417 +0.219 Q1 -4.188 Q2 -1.594 Q3 + 0.406 t

e)

t forecast
13 8.917
14 4.917
15 7.917
16 9.917

 


Related Solutions

Construct a time series plot. What type of pattern exists in the data?
Consider the following time series data. Quarter Year 1 Year 2 Year 3 1 4 6 7 2 2 3 6 3 3 5 6 4 5 7 8 Construct a time series plot. What type of pattern exists in the data? The time series plot indicates a linear trend and a seasonal pattern Show the four-quarter and centered moving average values for this time series.   Compute seasonal indexes and adjusted seasonal indexes for the four quarters.
7. Construct a time series plot. Comment on what type of pattern exists in the data....
7. Construct a time series plot. Comment on what type of pattern exists in the data. Use a regression model with dummy variables as follow to explain sales: Qtr1 = 1 if quarter 1, 0 otherwise; Qtr2 = 1 if quarter 2, 0 otherwise; Qtr3 = 1 if quarter 3, 0 otherwise. Write out the model that you’ve estimated in equation form using the values of your estimates. Explain, in a sentence or two, what this model tells us. Use...
a. Which of the following is a correct time series plot for this data?
Consider the following time series data. Week 1 2 3 4 5 6 Value 19 14 17 12 18 14 a. Which of the following is a correct time series plot for this data? Plot 1 Plot 2 Plot 3 What type of pattern exists in the data? Vertical Horizontal Scatter b. Develop the three-week moving average forecasts for this time series. Compute MSE and a forecast for week 7 (to 2 decimals if necessary). MSE The forecast for week...
Perform an analysis of the data below. A time-series plot. Comment on the underlying pattern in...
Perform an analysis of the data below. A time-series plot. Comment on the underlying pattern in the time series. Using the dummy variable approach, build the forecasting model. Show your final time series table and regression equation. Using your model, forecast sales for January through December of the fourth year. Assume that January sales for the fourth year turn out to be $295,000. What was your forecast error? What can you do to resolve the owner's uncertainty in the forecasting...
Consider the following time series.   (a) Choose the correct time series plot.     (i)...
Consider the following time series.   (a) Choose the correct time series plot.     (i) (ii) (iii) (iv)     - Select your answer -Plot (i)Plot (ii)Plot (iii)Plot (iv)Item 1     What type of pattern exists in the data?   - Select your answer - Horizontal PatternDownward Trend PatternUpward Trend PatternItem 2     (b) Use simple linear regression analysis to find the parameters for the line that minimizes MSE for this time series.       Do...
For time series data, where time dependency exists, which of the 5 assumptions under which the Gauss Markov theorem holds is violated. Explain.
For time series data, where time dependency exists, which of the 5 assumptions under which the Gauss Markov theorem holds is violated. Explain.
I need to time series plot the following data. I am confused since there are multiple...
I need to time series plot the following data. I am confused since there are multiple sections (441, 442, 443, 444, 445, 452, 4521. This is just two years worth of the data. Year Month Period 441 442,443 444 445 448 452 4521 1992 Jan 1 1.84 1.85 1.83 0.88 2.58 2.39 2.60 1992 Feb 2 1.83 1.85 1.83 0.89 2.62 2.34 2.53 1992 Mar 3 1.87 1.90 1.83 0.88 2.63 2.35 2.53 1992 April 4 1.89 1.91 1.91 0.89...
(a) Construct a time series plot. (b)Develop the three-week moving average forecasts for this time series.
Consider the following time series data. Week 1 2 3 4 5 6 Value 17 12 14 10 16 13 (a) Construct a time series plot. (b)Develop the three-week moving average forecasts for this time series. (Round your answers to two decimal places.) Week Time Series Value Forecast 1 17 2 12 3 14 4 10 5 16 6 13 Compute MSE. (Round your answer to two decimal places.) MSE = What is the forecast for week 7? (c) Use...
make a plot of the data in the table below , plot position versus time time...
make a plot of the data in the table below , plot position versus time time squared (t2 ). make sure to label your graph properly.( Y axis title with units, X axis title with unit, title of the graph with equation , major tick marks. use data to plot the graph manually. use proper scal measurement position(m) time(s) time2 (s2 ) 1 0 0 0 2 5 1 1 3 21 2 4 4 46 3 9 5 82...
Create a histogram of this data with 15 bins. Create a box plot of this data.
7, 9, 8, 11, 14, 7, 11, 17, 18, 12, 10, 9, 16, 17, 15, 13, 7, 12, 7, 8, 14, 16, 20, 12, 11, 14, 22, 8, 10, 14, 15, 20, 17, 14, 12, 22, 12, 15, 17, 16, 9, 11, 16, 18, 11, 12, 11, 9, 11, 9, 13, 7, 12, 9, 19, 9, 8, 15, 12, 16, 16, 20, 21, 9, 11, 17, 17, 8, 11, 7, 10, 17, 13, 15, 14, 11, 19,10, 11, 11, 9,...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT