Question

In: Statistics and Probability

The quarterly sales data (number of book sold) for Christian book over the past three years...

The quarterly sales data (number of book sold) for Christian book over the past three years in California follow:

    Year 1               Year 2               Year 3

  1.   1690              1800                  1850
  2.   940                 900                   1100
  3.   2625              2900                  2930
  4.   2500             2360                   2615

1. Construct a time series plot. What type of pattern exists in the data? This was not shown in the WebEx example I provided. But you can simply create this graph in Excel (Go to INSERT and look for charts under INSERT Tab). Basically you need to provide a 2-dimensional graph showing the trend of book sales over the given time period. The vertical line represents sale for Christian book, while the horizontal line represents quarter.

2. Use the following dummy variables to develop an estimated regression equation to account for any seasonal effects in the data: Quarter1=1 if the sales data point is in Quarter 1, otherwise Quarter 1=0; Quarter 2=1 if the sales data point is in Quarter 2, otherwise, Quarter 2=0; Quarter 3=1 if the sales data point is in Quarter 3, otherwise Quarter 3=0.

3. Compute the quarterly forecasts for next year.

4. Let t=1 to refer to the observation in quarter 1 of year 1; t=2 to refer to the observation in quarter 2 of year 1;,,,, and t=12 to refer to the observation in quarter 4 of year 3. Using the dummy variables defined in part (2) and t, develop an estimated regression equation to account for seasonable effects and any linear trend in the time series. Based upon the seasonal effects in the data and linear trend, compute the quarterly forecasts for next year.

Solutions

Expert Solution

Ans-  

Year 1 Year 2 Year 3
1690 1800 1850
940 900 1100
2625 2900 2930
2500 2360 2615

(1)- Here the line graph make by excel where the vertical line represents sale for Christian book, while the horizontal line represents quarter for the given data.

(2)-

sales Period Quarter 1 Quarter 2 Quarter 3 Quarter 4
1690 1 1 0 0 0
940 2 0 1 0 0
2625 3 0 0 1 0
2500 4 0 0 0 1
1800 5 1 0 0 0
900 6 0 1 0 0
2900 7 0 0 1 0
2360 8 0 0 0 1
1850 9 1 0 0 0
1100 10 0 1 0 0
2930 11 0 0 1 0
2615 12 0 0 0 1

here for this data below are given the regression output

SUMMARY OUTPUT
Regression Statistics
Multiple R 0.989744
R Square 0.979592
Adjusted R Square 0.846939
Standard Error 124.9667
Observations 12

and

Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
Intercept 2491.667 72.14954 34.53476 5.4E-10 2325.29 2658.044 2325.29 2658.044
Quarter 1 -711.667 102.0349 -6.97474 0.000116 -946.959 -476.374 -946.959 -476.374
Quarter 2 -1511.67 102.0349 -14.8152 4.24E-07 -1746.96 -1276.37 -1746.96 -1276.37
Quarter 3 326.6667 102.0349 3.20152 0.012584 91.37388 561.9595 91.37388 561.9595

hence the estimated regression line is Y= sales , and X = quarter

(3) quarterly forecast for next year

Quarter 1 1780
Quarter 2 980
Quarter 3 2818.333
Quarter 4 2491.667

Related Solutions

The quarterly sales data (number of copies sold) for a college textbook over the past three...
The quarterly sales data (number of copies sold) for a college textbook over the past three years follow: Year Quarter Sales 1 1 1690 2 940 3 2625 4 2500 2 1 1800 2 900 3 2900 4 2360 3 1 1850 2 1100 3 4 2930 2615 a. Construct a time series plot. What type of pattern exists in the data?   b. Show the four‐quarter and centered moving average values for this time series. c. Compute the seasonal and...
The quarterly sales data (number of copies sold) for a college textbook over the past three...
The quarterly sales data (number of copies sold) for a college textbook over the past three years follow. Quarter Year 1 Year 2 Year 3 1 1690 1800 1850 2 940 900 1100 3 2625 2900 2930 4 2500 2360 2615 a. Construct a time series plot. What type of pattern exists in the data? b. Show the four-quarter and centered moving average values for this time series. c. Compute the seasonal and adjusted seasonal indexes for the four quarters....
The quarterly sales data (number of copies sold) for a college textbook over the past three...
The quarterly sales data (number of copies sold) for a college textbook over the past three years follow. Click on the datafile logo to reference the data. quarter year 1 year 2 year 3   1 1690 1800 1850 2 940 900 1100 3 2625 2900 2930 4 2500    2360    2615 e. Deseasonalize the time series (to 3 decimals). Year Quarter Deseasonalized Value 1 1 2 3 4 2 1 2 3 4 3 1 2 3 4 f....
The quarterly sales (number of books sold) over the past 3 years are as shown below....
The quarterly sales (number of books sold) over the past 3 years are as shown below. Year Quarter Period Sales 1 1 1 1700 1 2 2 1000 1 3 3 2700 1 4 4 2500 2 1 5 1800 2 2 6 900 2 3 7 2900 2 4 8 2400 3 1 9 1800 3 2 10 1100 3 3 11 2900 3 4 12 2600 Construct a time series plot for this data. Paste the time series...
Suppose that the following are the quarterly sales data for the past 7 years. 1.Construct a...
Suppose that the following are the quarterly sales data for the past 7 years. 1.Construct a time series plot and develop linear trend equation using the original data (with seasonal components). 2.Calculate 4-quarter moving average values for this time series (column E). 3.Calculate centered moving average values and seasonal indexes (column F and G). 4.Calculate seasonal indexes (column J) for the four quarters. 5.Copy seasonal indexes (column J) to column O, and calculate deseasonalized number sold (column P). 6.Construct a...
Here are quarterly data for the past two years, from these data, prepare a forecast for...
Here are quarterly data for the past two years, from these data, prepare a forecast for the upcoming year using decompostion. foreccast years 9 to 12. FORECAST ERROR? Period Actual period Actual 1 300 5 416 2 540 6 760 3 885 7 1191 4 580 8 760
3.       Here are quarterly data for the past two years. From these data, prepare a forecast...
3.       Here are quarterly data for the past two years. From these data, prepare a forecast for the upcoming year using linear regression. Period Actual 1.00 300.00 2.00 540.00 3.00 885.00 4.00 580.00 5.00 416.00 6.00 760.00 7.00 1191.00 8.00 760.00 What are the forecasts for periods 9, 10, 11, and 12?
The number of loaves of bread sold per day by an organic bakery over the past...
The number of loaves of bread sold per day by an organic bakery over the past five years can be treated as a random variable that is normally distributed. This distribution has a mean of 77.5 and a standard deviation of 14.4 loaves. Suppose a random sample of 36 days has been selected. Determine the probability that the average number of loaves sold in the sample of days exceeds 80 loaves. First find the standard error of the mean. Now...
It appears that over the past 50 years, the number of farms in the United States...
It appears that over the past 50 years, the number of farms in the United States declined while the average size of farms increased. The following data provided by the U.S. Department of Agriculture show five-year interval data for U.S. farms. Use these data to develop the equation of a regression line to predict the average size of a farm (y) by the number of farms (x). Discuss the slope and y-intercept of the model. Year Number of Farms (millions)...
It appears that over the past 50 years, the number of farms in the United States...
It appears that over the past 50 years, the number of farms in the United States declined while the average size of farms increased. The following data provided by the U.S. Department of Agriculture show five-year interval data for U.S. farms. Use these data to develop the equation of a regression line to predict the average size of a farm (y) by the number of farms (x). Discuss the slope and y-intercept of the model. Year Number of Farms (millions)...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT