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
orchestra answered 1 year agoRelated 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
Latest Questions
- For fatty acid synthesis what would be the substrate if you are making an even number...
- Write a javascript program that computes the total cost for a five year car lease. The...
- sara is a 50-year-old woman who lives in a poor village in india. unfortunately, sara’s health...
- What is your opinion on the use of AI technology? Is it necessary or is it...
- For this problem, carry at least four digits after the decimal in your calculations. Answers may...
- Why is it okay to speculate on Currency?
- What is the budgeting cycle and master budget? Why should companies use a master budget?
ADVERTISEMENT