Homework answers / question archive / ETB1100 Assignment:  A Regression Analysis The Value of Linear Relationships for Decision Making in Business The Brief:   Musicians have always relied on concert tours for their income but ConcertMerchBiz, a company that sells merchandise at concerts, is more interested in how many t-shirts they can sell!  ConcertMerchBiz believe that the more expensive the concert tickets are, the less the fans have left to spend on merchandise

ETB1100 Assignment:  A Regression Analysis The Value of Linear Relationships for Decision Making in Business The Brief:   Musicians have always relied on concert tours for their income but ConcertMerchBiz, a company that sells merchandise at concerts, is more interested in how many t-shirts they can sell!  ConcertMerchBiz believe that the more expensive the concert tickets are, the less the fans have left to spend on merchandise

Statistics

Share With

---

ETB1100 Assignment:  A Regression Analysis

The Value of Linear Relationships for Decision Making in Business

The Brief:

 

Musicians have always relied on concert tours for their income but ConcertMerchBiz, a company that sells merchandise at concerts, is more interested in how many t-shirts they can sell!  ConcertMerchBiz believe that the more expensive the concert tickets are, the less the fans have left to spend on merchandise.

 

To investigate their belief, ConcertMerchBiz collected the data from a random sample of 30 concerts that took place in the previous year.

 

             

Ticket price and the total amount of money spent on merchandise was recorded for each of these concerts.  The variables are labelled

• Merchandise Sales ($) (Y)
• Ticket Price ($) (X)

 

and can be found in the data file labelled MerchMakesMoney.xlsx in the ASSIGNMENTS section on Moodle, and is to be used to answer the questions listed here.

 

Assume that all data is approximately normally distributed.

 

Of primary interest here, is the relationship between ticket price and merchandise sales. 

Whilst the ability of multiple regression modelling to analyse the relationship between a dependent variable and numerous (multiple) independent variables, makes it the perfect tool for decision making in business, it is beyond the scope of this unit (worth doing a second year Stats unit just for this! J).  Instead, we will use EXCEL’s simple linear regression, which restricts the modelling to using just one independent variable at a time.

 

 

 

DATA PREPARATION AND ‘EXCEL HYGIENE’.

 

In all lecture examples involving the use of EXCEL, as well as the solutions to tutorial questions, I have been very particular about how to format the data, clean up the output (e.g. adjust to four decimal places, label everything, edit the charts etc etc).  This is because this ‘EXCEL hygiene’ is essential in the workplace and also highly valued.  Generating output is easy, consistently ensuring it is clearly labelled and easy to identify, understand and track, is more difficult, simply because it takes more time.  This time is worth the investment and will be expected in your report.  

 

           

QUESTION ONE:                                                                                  (6+4+4 = 14 marks)

 

Begin by using the Random Sampling procedure demonstrated in both the lecture and tutorials in Week 10, to select a RANDOM SAMPLE of 25 observations from your data and copy and paste all FOUR variables (Concert, Ticket Price, Merchandise Sales, Random Number) into a separate worksheet labelled, ‘Sample_25’, in columns B, C, D and E respectively.  In column A, you are to number the rows (1-25) and label this column ‘Observation’.

 

1. Include a screenshot of this Sample_25 worksheet here to demonstrate you have sampled correctly.  Label this as EXHIBIT 1 and include a relevant title.

 

2. ConcertMerchBiz believe that the Ticket Price ($) and the total amount of money spent on Merchandise Sales ($) are linearly related.  In order to investigate if a linear relationship is a reasonable assumption, use EXCEL’s scatterplot option to produce a graph of these two variables.  Include the line of best fit (DO NOT INCLUDE R² – it is not to be discussed here).

 

Label this graph as EXHIBIT 2 with a relevant title and remember to optimise its presentation via the various formatting options available. 

 

3. Based ONLY on the scatterplot you produced as Exhibit 2, does a linear relationship seem reasonable?  If so, is it a positive or negative slope?  Provide evidence for your answer and interpret what this means in context of this question.

 

 

QUESTION TWO:                                                                                                (4+4 = 8 marks)

 

Regardless of your answer in Question One, now assume that a linear relationship is reasonable.  

 

1. Using the Regression Analysis procedure in EXCEL, produce a simple linear regression model with the following requirements:

 

• • Select 99% Confidence Level in the Output Options.
  • Report all values to 4 decimal places where relevant.
  • Provide the Summary Output labelled as EXHIBIT 3 with an appropriate title.

 

2. Based on this output, state the equation of this regression model (correct to 4 decimal places), remembering to define the variables.

 

 

QUESTION THREE:                                                                                                                    (6 marks)

 

Before interpreting this model, it is first essential to determine whether or not it is a true representation of the relationship that exists between Ticket Price and Merchandise Sales in the population.  To do this, a hypothesis test of significance is required.

 

(a) Using a 5% level of significance, determine whether or not this relationship between the Ticket Price and Merchandise Sales is a statistically significant, linear relationship.  Ensure that you clearly state your hypothesis, show ALL steps, ALL working AND interpret your conclusion IN CONTEXT of this question.

 

 

QUESTION FOUR:                                                               (11+5+6+2+3+5 = 32 marks)

Report to Management

 

Assuming now that the model you have identified is statistically significant, provide the following in a short report to management:

 

1. State and provide an interpretation of the Y intercept, b₀ and the slope coefficient, b₁.

 

2. State and interpret the coefficient of determination for this model, in context of this question.

 

3. Use the regression model developed in (a) to predict the merchandise sales expected when the ticket price for a concert is set at $100.  Show your working AND provide an interpretation of your answer.

 

4. Do you expect this prediction to be reasonable/valid?  Explain why.

 

5. From the Summary Output provided in EXHIBIT 3, state AND interpret the 95% confidence interval estimate of the population slope beta, correct to 4 decimal places.

 

(f)  

i) State the lower and upper limit of a 99% confidence interval estimate of the population slope beta.  You are NOT required to provide an interpretation. ii) Describe how this interval is different from your answer in (e), iii) AND what that difference means.

 

 

QUESTION FIVE:                                                                                    (2+14 = 16 marks)

 

ConcertMerchBiz believe that the more expensive the concert tickets are, the less the fans have left to spend on merchandise.  Consequently, from the very beginning, they have insisted that across all the concerts they are involved with, the proportion of tickets sold that cost more than $100, should be less than 25%.  In the past, this had been agreed to by the musicians and their management, but in more recent years, ConcertMerchBiz believe management have not been keeping their promise.  ConcertMerchBiz suspect that the proportion of tickets sold costing more than $100, now exceeds 25%.

             

 

1. To investigate ConcertMerchBiz’s suspicions, begin by referring to the random sample of 25 observations you produced in QUESTION ONE and presented in Exhibit 1, and count how many of the tickets sold cost more than $100.  Now calculate what proportion (p) of the sample this represents.

 

2. Now use this information to help you test ConcertMerchBiz’s claim that recently, the proportion of all tickets sold costing more than $100, now exceeds 25%.  Use the p-Value Approach and a 5% level of significance.  Ensure that you clearly state your hypotheses, show ALL steps, ALL working AND interpret your conclusion in context of this question – that is, are management keeping their promise?

 

 

PRESENTATION:                                                                                                               (4 marks)

 

There are 4 marks available for presentation.  These marks will be awarded for things such as:

Easy to read; logical flow of answers; cohesive report; answers clearly labelled; appropriate font size, borders, colour choice, labelling of graphs, care in spelling, grammar and punctuation.