Question

In: Statistics and Probability

OC Music Company has been in business for 4 years. Data about the sales of each...

OC Music Company has been in business for 4 years. Data about the sales of each quarter were collected below. The manager wants use these data to forecast sales of the 5th year. Please copy and paste it to Excel and run regression analysis. Answer the following 4 questions. Please also upload your EXCEL file on Titanium, otherwise you will NOT receive any point for this problem.

Year

Quarter

Sales

1

Q1

7

Q2

2

Q3

4

Q4

10

2

Q1

6

Q2

3

Q3

8

Q4

14

3

Q1

10

Q2

3

Q3

5

Q4

16

4

Q1

12

Q2

4

Q3

7

Q4

22

Question 16

Not yet answered

Points out of 10.00

Flag question

Question text

Develop a model for trend and seasonality. Please clearly define your variables. How many independent variables do you have in your regression?

Answer:

Question 17

Not yet answered

Points out of 10.00

Flag question

Question text

What is the intercept in your estimated regression model? Rounded to two decimal places.

Answer:

Question 18

Not yet answered

Points out of 10.00

Flag question

Question text

Use the model to forecast for sales of last quarter in the 5th year. Rounded to two decimal places.

Answer:

Question 19

Not yet answered

Points out of 10.00

Flag question

Question text

Calculate the MAE for this time series forecast. Rounded to two decimal places.

Answer:

Solutions

Expert Solution

Using Excel, input data in the following manner:

Year Q1 Q2 Q3 Q4 Sales
1 1 0 0 0 7
1 0 1 0 0 2
1 0 0 1 0 4
1 0 0 0 0 10
2 1 0 0 0 6
2 0 1 0 0 3
2 0 0 1 0 8
2 0 0 0 0 14
3 1 0 0 0 10
3 0 1 0 0 3
3 0 0 1 0 5
3 0 0 0 0 16
4 1 0 0 0 12
4 0 1 0 0 4
4 0 0 1 0 7
4 0 0 0 0 22

Go to Data, select Data Analysis, choose Regression. Put Year, Q1,Q2, Q3 and Q4 in X input range. Put Sales in Y input range. Tick Residuals.

Coefficients Standard Error t Stat P-value
Intercept 11.1875 1.673458103 6.685258 5.46E-05
Year 1.725 0.49892881 3.457407 0.006149
Q1 -6.75 1.57775143 -4.27824 0.001616
Q2 -12.5 1.57775143 -7.92267 1.28E-05
Q3 -9.5 1.57775143 -6.02123 0.000128
Q4 0 0 65535 --

16. Sales = 11.19 + 1.73*Year - 6.75*Q1 - 12.5*Q2 - 9.5*Q3

Independent variables: Q1, Q2, Q3, Year (4)

Dependent variable: Sales

17. Intercept = 11.19

18. Forecast for last quarter of 5th year:

Sales = 11.19 + 1.73*5 - 6.75*0 - 12.5*0 - 9.5*0 = 19..84

19.

Sales Predicted Residuals Absolute Error
7 6.163 0.837 0.837
2 0.413 1.588 1.588
4 3.413 0.587 0.587
10 12.913 -2.913 2.913
6 7.888 -1.888 1.888
3 2.138 0.863 0.863
8 5.138 2.863 2.863
14 14.638 -0.638 0.638
10 9.613 0.387 0.387
3 3.863 -0.862 0.862
5 6.863 -1.863 1.863
16 16.363 -0.363 0.363
12 11.338 0.663 0.663
4 5.588 -1.588 1.588
7 8.588 -1.588 1.588
22 18.088 3.913 3.913
Total 23.4

MAE = 23.4/16 = 1.463


Related Solutions

OC Music Company has been in business for 4 years. Data about the sales of each...
OC Music Company has been in business for 4 years. Data about the sales of each quarter were collected below. The manager wants use these data to forecast sales of the 5th year. Please copy and paste it to Excel and run regression analysis. Answer the following 4 questions. Year Quarter Sales 1 Q1 7 Q2 2 Q3 4 Q4 10 2 Q1 6 Q2 3 Q3 8 Q4 14 3 Q1 10 Q2 3 Q3 5 Q4 16 4...
Costello Music Company has been selling electronic organs over the past 5 years. Sales were initially...
Costello Music Company has been selling electronic organs over the past 5 years. Sales were initially low but have grown over time. Ray Costello, the owner of the company, wants to predict the sales for the upcoming year using the number of sales that Costello Music Company has experienced. The sales totals are given in the following table. Year Q1 Q2 Q3 Q4 Total 1 4 2 1 5 12 2 6 4 4 14 28 3 10 3 5...
Costello Music Company has been selling electronic organs over the past 5 years. Sales were initially...
Costello Music Company has been selling electronic organs over the past 5 years. Sales were initially low but have grown over time. Ray Costello, the owner of the company, wants to predict the sales for the upcoming year using the number of sales that Costello Music Company has experienced. The sales totals are given in the following table. Year Q1 Q2 Q3 Q4 Total 1 4 2 1 5 12 2 6 4 4 14 28 3 10 3 5...
Music Inc. has been selling music DVDs for 10 years and now wants to enter the...
Music Inc. has been selling music DVDs for 10 years and now wants to enter the Movie DVD business. They have entered a deal with paramount pictures to sell on DVD all of their future and past movies. The accounting department has estimated the cost of the DVD, Packaging, and Copyright fees for each DVD to be $5.00. The fixed cost for machinery, buildings & grounds, and overhead to be $30,000 per year. If Music Inc. sells each DVD for...
Ross Company has been in business for several years, during which time it has been profitable....
Ross Company has been in business for several years, during which time it has been profitable. For each of those years, Ross reported (and paid taxes on) taxable income in the same amount as pretax financial income based on the following revenues and expenses: Revenues Expenses 2012 $182,000 $150,000 2013 220,000 170,000 2014 253,000 180,000 2015 241,000 196,000 Ross was subject to the following income tax rates during this period: 2012, 20%; 2013, 25%; 2014, 30%; and 2015, 25%. During...
Windwave Ornamental Grass Company (WOGC) has been in business for forty years. The business was started...
Windwave Ornamental Grass Company (WOGC) has been in business for forty years. The business was started by George Reed as a small producer of ornamental grass used for soil erosion and to provide a maintenance-free ground cover along highway medians and banks. Windwave focuses on two types of grass: Fireweed and Blue Haze. When motorists pass by an area planted in Fireweed, they see a tall grass that resembles embers glowing as the red-bladed grass waves in the wind. Fireweed...
own standard and premium labels. The company has been in business for many years and is...
own standard and premium labels. The company has been in business for many years and is a “household name”. Its Denver soup plant has a capacity of 120,000 cases/mo, but has been operating at a normal volume of 85,000 cases/mo. Hinds has been approached by Mondo Mart, a large discount retailer, about producing a line of soups under a Mondo Mart house label. Mondo would initially place an order for 15,000 cases/mo, with the understanding that the order will be...
Leonard Company has been in business for 3 years. In 2011, it reported a loss of...
Leonard Company has been in business for 3 years. In 2011, it reported a loss of $10,000 on trading securities. In 2012, there was a $5,000 gain. Finally, in 2013 the company had a $20,000 loss. As of the beginning of 2014 the company owned 15,000 shares of Shimkus Corp., 2,000 shares of Newton Corp., and 100 shares of Lathrop, Inc. The original cost of the shares was $450,000, $210,000, and $115,000, respectively. During 2014, Leonard sold 5,000 shares of...
A lot of research has been done about the importance of exposing children to classical music...
A lot of research has been done about the importance of exposing children to classical music because it causes their cerebellum to be larger. In terms of academia, I think for some people listening to music will make a difference in the test scores, however, I also wonder if the type of music will impact the results not just the volume. If you were to actually conduct this study, which research design would you use?
Kempton owns a plumbing repair service company that has been in business for thirty years in...
Kempton owns a plumbing repair service company that has been in business for thirty years in Shreveport Louisiana (population 198,675). The company employs 50 repairmen and repairwomen that work in two person teams doing plumbing repair jobs. Kempton’s son Myron is going to be taking over the business and has lots of ideas about improving incentives. Equipped with what he has learned in the MBA program at LSU, he has established that (1) customer satisfaction, (2) capacity utilization (the plumbing...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT