Question

In: Statistics and Probability

Year Quarter Sales 1 1 10 1 2 31 1 3 43 1 4 16 2...

Year Quarter Sales
1 1 10
1 2 31
1 3 43
1 4 16
2 1 11
2 2 33
2 3 45
2 4 17
3 1 13
3 2 34
3 3 48
3 4 19
4 1 15
4 2 37
4 3 51
4 4 21

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

b. Use the following dummy variables to develop an estimated regression equation to account for seasonal effects and any linear trend in the data: Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise. Compute the forecast of bike sales for quarter 1 of next year.

c. Compute the forecast of bike sales for quarter 2 of next year.

d. Compute the forecast of bike sales for quarter 3 of next year.

e. Compute the forecast of bike sales for quarter 4 of next year.   

Solutions

Expert Solution

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

There is a horizontal seasonal pattern in the data.

b. Use the following dummy variables to develop an estimated regression equation to account for seasonal effects and any linear trend in the data: Qtr1 = 1 if Quarter 1, 0 otherwise; Qtr2 = 1 if Quarter 2, 0 otherwise; Qtr3 = 1 if Quarter 3, 0 otherwise. Compute the forecast of bike sales for quarter 1 of next year.

The estimated regression equation is:

y = 13.25 -4.5*Qtr 1 + 16.5*Qtr 2 + 29*Qtr 3 + 0.5*t

The forecast of bike sales for quarter 1 of next year is 17.25.

c. Compute the forecast of bike sales for quarter 2 of next year.

The forecast of bike sales for quarter 2 of next year is 38.75.

d. Compute the forecast of bike sales for quarter 3 of next year.

The forecast of bike sales for quarter 3 of next year is 51.75.

e. Compute the forecast of bike sales for quarter 4 of next year.

The forecast of bike sales for quarter 4 of next year is 23.25.

The data is:

Year Quarter y Qtr 1 Qtr 2 Qtr 3 t
1 1 10 1 0 0 1
1 2 31 0 1 0 2
1 3 43 0 0 1 3
1 4 16 0 0 0 4
2 1 11 1 0 0 5
2 2 33 0 1 0 6
2 3 45 0 0 1 7
2 4 17 0 0 0 8
3 1 13 1 0 0 9
3 2 34 0 1 0 10
3 3 48 0 0 1 11
3 4 19 0 0 0 12
4 1 15 1 0 0 13
4 2 37 0 1 0 14
4 3 51 0 0 1 15
4 4 21 0 0 0 16

The output is:

0.998
Adjusted R² 0.998
R   0.999
Std. Error   0.674
n   16
k   4
Dep. Var. y
ANOVA table
Source SS   df   MS F p-value
Regression 2,990.0000 4   747.5000 1644.50 3.44E-15
Residual 5.0000 11   0.4545
Total 2,995.0000 15  
Regression output confidence interval
variables coefficients std. error    t (df=11) p-value 95% lower 95% upper
Intercept 13.2500
Qtr 1 -4.5000 0.4900 -9.184 1.72E-06 -5.5784 -3.4216
Qtr 2 16.5000 0.4827 34.186 1.61E-12 15.4377 17.5623
Qtr 3 29.0000 0.4782 60.642 3.03E-15 27.9474 30.0526
t 0.5000 0.0377 13.266 4.12E-08 0.4170 0.5830
Predicted values for: y
Qtr 1 Qtr 2 Qtr 3 t Predicted
1 0 0 17 17.250
0 1 0 18 38.750
0 0 1 19 51.750
0 0 0 20 23.250

Please give me a thumbs-up if this helps you out. Thank you!


Related Solutions

  Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales...
  Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2   Budgeted unit sales 50,000 65,000 115,000 70,000 80,000 90,000   Selling price per unit $7 per unit             1 Chapter 7: Applying Excel 2 3 Data Year 2 Quarter Year 3 Quarter 4 1 2 3 4 1 2 5 Budgeted unit sales 50,000 65,000 115,000 70,000 80,000 90,000 6 7 � Selling price per unit $8 per unit 8 � Accounts receivable, beginning balance...
The TS Corporation has budgeted sales for the year as follows: Quarter 1 2 3 4  Sales...
The TS Corporation has budgeted sales for the year as follows: Quarter 1 2 3 4  Sales 10,000 12,000 14,000 16,000The ending inventory of finished goods for each quarter should equal 25% of the next quarter's budgeted sales in units. Four pounds of raw materials are required for each unit produced. Raw materials on hand at the start of the year total 4,200 pounds. The raw materials inventory at the end of each quarter should equal 10% of the next quarter's...
Chapter 9: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1...
Chapter 9: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales 40,000 60,000 100,000 50,000 70,000 80,000 • Selling price per unit $8 per unit • Accounts receivable, beginning balance $65,000 • Sales collected in the quarter sales are made 75% • Sales collected in the quarter after sales are made 25% • Desired ending finished goods inventory is 30% of the budgeted unit sales of the next quarter • Finished...
Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1...
Chapter 8: Applying Excel Data Year 2 Quarter Year 3 Quarter 1 2 3 4 1 2 Budgeted unit sales        40,000         60,000      100,000      50,000         70,000         80,000 • Selling price per unit $8 per unit • Accounts receivable, beginning balance $65,000 • Sales collected in the quarter sales are made 75% • Sales collected in the quarter after sales are made 25% • Desired ending finished goods inventory is 30% of the budgeted unit sales...
Quarter Year 1 Year 2 Year 3 1 5 8 10 2 1 3 7 3...
Quarter Year 1 Year 2 Year 3 1 5 8 10 2 1 3 7 3 3 6 8 4 7 10 12 (A) What type of pattern exists in the data? a. Positive trend, no seasonality b. Horizontal trend, no seasonality c. Vertical trend, no seasonality d. Positive tend, with seasonality e. Horizontal trend, with seasonality f. Vertical trend, with seasonality (B) Use a multiple regression model with dummy variables as follows to develop an equation to account for...
Subject x y 1 16 25 2 14 31 3 10 16 4 5 18 5...
Subject x y 1 16 25 2 14 31 3 10 16 4 5 18 5 10 22 Find the linear correlation coefficient.
Stumps Larvae 2 10 2 30 1 12 3 24 3 36 4 40 3 43...
Stumps Larvae 2 10 2 30 1 12 3 24 3 36 4 40 3 43 1 11 2 27 5 56 1 18 3 40 2 25 1 8 2 21 2 14 1 16 1 6 4 54 1 9 2 13 1 14 4 50 - How far off are you from the actual number of Beetle larvae from a cottonwood tree with 5 stumps? -  Does it make sense to use a linear regression model here? Write...
unit sales will be 10,000 in quarter 1; 12,000 in quarter 2; 14,000 in quarter 3;...
unit sales will be 10,000 in quarter 1; 12,000 in quarter 2; 14,000 in quarter 3; and 18,000 in quarter 4. Management desires to have an ending finished goods inventory equal to 10% of the next quarter's expected unit sales. What is budgeted production in units for quarter 2?
Consider the following time series data. Quarter Year 1 Year 2 Year 3 1 2 4...
Consider the following time series data. Quarter Year 1 Year 2 Year 3 1 2 4 5 2 4 5 8 3 1 3 4 4 7 9 10 (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 -Positive trend pattern, no seasonalityHorizontal pattern, no seasonalityNegative trend pattern, no seasonalityPositive trend pattern, with seasonalityHorizontal pattern, with...
Quarter Year 1 Year 2 Year 3 Year 4 Year 5 1 20 37 75 92...
Quarter Year 1 Year 2 Year 3 Year 4 Year 5 1 20 37 75 92 176 2 100 136 155 202 282 3 175 245 326 384 445 4 13 26 48 82 181 Question 3 Again ignore any trend or seasonality in the data.  Suppose the company uses exponential smoothing to make forecasts.   What are the forecasts for periods Q2 Year 1 through Q4 Year 5 assuming alpha = 0.3. Assume that the forecast for Q1 Year 1 was...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT