In this assignment, the band Tenacious D has hired you as a consultant, paying you an exorbitant fee to help them increase their concert revenues on their next tour. In order to do this, you will run a cross-section regression that attempts to explain concert revenues using data from a recent 40 concert tour (there was one concert in 40 different states).
Follow these steps to do the assignment:
Make sure you use the right data set (explained above), and put it into a page in a Microsoft Excel workbook. Label or name this page in the workbook “data set _” where _ is the letter of your data set.
The variable definitions are:
REVENUE = revenue from each Tenacious D concert, in dollars.
AD = advertising costs for each concert, in dollars.
STADIUM = the maximum capacity of each stadium for each concert.
ALBUMS = the number of albums bought by download, compact disc, or vinyl by Tenacious D in the area of the concert in the 6 months prior to the show.
RADIO = an index indicating how often Tenacious D’s songs are played on the radio in the area of each concert. RADIO ranges from 1 to 5, with 1 meaning Tenacious D’s songs are rarely played on the radio in that area. A value of 5 indicates that Tenacious D songs are played so often that you can’t avoid hearing them.
DAYCODE= “SunThu” if the concert is held on a Sunday through a Thursday; “FriSat” if the concert is held on a Friday or a Saturday.
POPK = the population in each state, in thousands of people.
First, we want to study the interaction between how radio play of the band’s songs affects revenues, and whether having the concert on a Friday or Saturday night has something to do with revenues. For this part of the assignment, use the data as given. Think of the concerts as being divided up into 5 categories, according to the value of RADIO. The first set of concerts are the ones where RADIO=1, the second category is the concerts for which RADIO=2, etc. On the “data set _” page, create (only) one pivot table that displays:
a. The average REVENUE for concerts held on Fridays and Saturdays for each of the five RADIO categories.
b. The average REVENUE for concerts NOT held on Fridays and Saturdays for each of the five RADIO categories.
c. The average REVENUE for concerts in each of the five RADIO categories, regardless of whether the show was on a Friday or Saturday or not.
You must have a pivot table to receive credit for this part, not just the correct answers.
Also on the “data set _” page, create a scatter chart that displays ADVERTISING on the x-axis and REVENUE on the Y-axis. Be sure to label the axes on the chart.
On a new page named “final data,” prepare the data to run or estimate the following model by generating the variables in the equation below (for full credit, excel formulas should be used.)
REVENUE/POPK = B0 + B1AD/POPK + B2STADIUM/POPK + B3ALBUMS/POPK
+ B4RADIO + B5WEEKEND + e
Where WEEKEND =1 if the concert was on a Friday or Saturday night, and it is 0 otherwise.
Everything else is as defined on the previous page.
Note: The ticket price is always the same, so it is not included. Some of the variables in the regression are divided by POPK. Note that your spreadsheet page named “final data” should contain the values you will use for the regression and the formulas you used to convert the data from the “data set _” spreadsheet to the variables you will use in the regression.
Estimate the regression model written above with your data from the “final data” page. The results of this regression should appear on a different spreadsheet page named “results.”
Answer the following questions, typing the answers in a Microsoft Word document.
NOTE: If your regression results contain something that looks like, say 5.83E-5, that means 5.83×10-5 or 0.0000583. If it was 5.83E5, that would mean 5.83×10+5 or 583,000.
Q1. What conclusions can you make from the pivot table results?
Q2. Interpret the slope estimate for AD/POPK.
Q3. Comment on the significance level for AD/POPK and how that affects your interpretation of the slope estimate.
Q4. How well does the regression fit the data? How do you know?
Q5. Based on these results, what business advice would you give Tenacious D?