Chapter 3, Problem 1C

The motion picture industry is an extremely competitive business. Dozens of movie studios produce hundreds of movies each year, many of which cost hundreds of millions of dollars to produce and distribute. Some of these movies will go on to earn hundreds of millions of dollars in box office revenues, while others will earn much less than their production cost.

Data from 50 of the top box-office-receipt-generating movies are provided in the file Top50Movies. The following table shows the first 10 movies contained in this data set. The categorical variables included in the data set for each movie are the rating and genre. Quantitative variables for the movie’s release year, inflation- and noninflation-adjusted box-office receipts in the United States, budget, and the world box-office receipts are also included.

Managerial Report

Use the data-visualization methods presented in this chapter to explore these data and discover relationships between the variables. Include the following in your report:

1. 1. Create a scatter chart to examine the relationship between the year released and the inflation-adjusted U.S. box office receipts. Include a trendline for this scatter chart. What does the scatter chart indicate about inflation-adjusted U.S. box office receipts over time for these top 50 movies?
2. 2. Create a scatter chart to examine the relationship between the budget and the noninflation-adjusted world box office receipts. (Note: You may have to adjust the data in Excel to ignore the missing budget data values to create your scatter chart. You can do this by first sorting the data using Budget and then creating a scatter chart using only the movies that include data for Budget.) What does this scatter chart indicate about the relationship between the movie’s budget and the world box office receipts?
3. 3. Create a frequency distribution, percent frequency distribution, and histogram for inflation-adjusted U.S. box office receipts. Use bin sizes of $100 million. Interpret the results. Do any data points appear to be outliers in this distribution?
4. 4. Create a PivotTable for these data. Use the PivotTable to generate a crosstabulation for movie genre and rating. Determine which combinations of genre and rating are most represented in the top 50 movie data. Now filter the data to consider only movies released in 1980 or later. What combinations of genre and rating are most represented for movies after 1980? What does this indicate about how the preferences of moviegoers may have changed over time?
5. 5. Use the PivotTable to display the average inflation-adjusted U.S. box-office receipts for each genre–rating pair for all movies in the data set. Interpret the results.

To determine

1. Give a scatter chart to examine the relationship between the year released and the inflation-adjusted U.S. box office receipts.

2. Give a scatter chart to examine the relationship between the budget and the noninflation-adjusted world box office receipts.

3. Construct a frequency distribution, percent frequency distribution, and histogram for inflation-adjusted U.S. box office receipts. Give interpretation of the results.

4. Construct crosstabulation for movie genre and rating. Find the combinations of genre and rating that are most represented in the top 50 movie data. Find the combinations of genre that are most represented for movies after 1980.

5. Construct the average inflation-adjusted U.S. box-office receipts for genre-rating pair for all movies in the data set using a PivotTable. Give interpretation.

Answer to Problem 1C

1. The scatter chart of the year released and the inflation-adjusted U.S. box office receipts are as follows:

2. The scatter chart to examine the relationship between the budget and the noninflation-adjusted world box office receipts is as follows:

3. The frequency distribution and percent frequency distribution for inflation-adjusted U.S. box office receipts are given below:

The histogram for inflation-adjusted U.S. box office receipts using an Excel:

4. The crosstabulation for movie genre and rating for top 50 movies is given below:

The crosstabulation for movie genre and rating for the movies released after 1980 is as follows:

5. The average inflation-adjusted U.S. box-office receipts for genre-rating pair for all movies in the data set is given below:

Explanation of Solution

1.

Step-by-step procedure to obtain the scatter chart of the year released and the inflation-adjusted U.S. box office receipts using an Excel:

• Select the data of Year Released and U.S. Box Office Receipts (Inflation Adjusted Millions $).
• Select Insert.
• Choose Scatter under Charts.
• In Chart Elements, check Trendline.

Thus, the scatter chart of the year released and the inflation-adjusted U.S. box office receipts is obtained.

From the scatter chart, it is clear that there is slight decrease in inflation over years. However, there is no clear linear pattern observed.

2.

Step-by-step procedure to obtain the scatter chart to examine the relationship between the budget and the noninflation-adjusted world box office receipts using an Excel:

• Select the data of Budget (Non-Inflation Adjusted Millions $) and U.S. Box Office Receipts (Non-Inflation Adjusted Millions $).
• Select Insert.
• Choose Scatter under Charts.
• In Chart Elements, check Trendline.

Thus, the scatter chart to examine the relationship between the budget and the noninflation-adjusted world box office receipts is obtained.

From the output, it is clear that as the budget increases, the noninflation-adjusted world box office receipts also increase.

3.

Step-by-step procedure to create frequency distribution and percent frequency distribution for inflation-adjusted U.S. box office receipts using an Excel:

• Select Insert > PivotTable.
• In Select a table or range, select the data of U.S. Box Office Receipts (Inflation Adjusted Millions $) and click OK.
• In PivotTable Fields, move U.S. Box Office Receipts (Inflation Adjusted Millions $) to Rows and Σ Values.
• Right click on a value from Row Labels.
• Enter 100 in By.
• Click on U.S. Box Office Receipts (Inflation Adjusted Millions $) from Σ Values.
• Select Value Field settings.
• In Summarize value field by, choose Count and click OK.
• Again, move U.S. Box Office Receipts (Inflation Adjusted Millions $) to Rows and Σ Values.
• Click on U.S. Box Office Receipts (Inflation Adjusted Millions $) from Σ Values.
• Select Value Field settings.
• In Show Values As, choose % of Grand Total and click OK.

Thus, the frequency distribution and percent frequency distribution are obtained.

Step-by-step procedure to obtain histogram for inflation-adjusted U.S. box office receipts using an Excel:

• Select the data of class interval and percent frequency.
• Select Insert.
• Choose Clustered Column under Charts.
• Click on a bar in the graph.
• In Format Data Series, enter Gap width as 0%.

Thus, the histogram is obtained.

From the distribution table and histogram, it is clear that the frequency for the lowest inflation-adjusted U.S. box office receipts value is the highest. As the value of inflation-adjusted U.S. box office receipts increases, the frequency decreases. The frequency is very low (2%) for the inflation-adjusted U.S. box office receipts value from 1,393 to 1,593.5. This values seem to be outlier.

4.

Step-by-step procedure to obtain crosstabulation for movie genre and rating for top 50 movies using an Excel:

• Select Insert > PivotTable.
• In Select a table or range, select the data of Rating and Genre and click OK.
• In PivotTable Fields, move Rating to Rows, Genre to Columns, and Genre to Σ Values.
• Click on Genre from Σ Values.
• Select Value Field settings.
• In Summarize value field by, choose Count and click OK.

Thus, the crosstabulation for movie genre and rating for top 50 movies is obtained.

From the crosstabulation of movie genre and rating for top 50 movies, it is observed that the combination of G and Animated (=8) is most represented in the top 50 movie data.

Step-by-step procedure to obtain crosstabulation for movie genre and rating for the movies released after 1980 using an Excel:

• Select the data and choose Filter under Sort & Filter.
• Click on the drop-down box in Year Release column.
• Select Number Filters and choose Greater than.
• In Is greater than, enter 1980.
• Select Insert > PivotTable.
• In Select a table or range, select the filtered data of Rating and Genre and click OK.
• In PivotTable Fields, move Rating to Rows, Genre to Columns, and Genre to Σ Values.
• Click on Genre from Σ Values.
• Select Value Field settings.
• In Summarize value field by, choose Count and click OK.

Thus, the crosstabulation for movie genre and rating for the movies released after 1980 is obtained.

From the crosstabulation of movie genre and rating for the movies released after 1980, it is observed that the combination of PG-13 and SciFi/Fantasy (=6) is most represented.

Also, over the time changes, the number of dramas released became reduced. The rating of G and PG becomes high.

5.

Step-by-step procedure to construct the average inflation-adjusted U.S. box-office receipts for genre-rating pair for all movies in the data set using an Excel:

• Select Insert > PivotTable.
• In Select a table or range, select the filtered data of U.S. Box Office Receipts (Inflation Adjusted Millions $), Rating, and Genre and click OK.
• In PivotTable Fields, move Rating to Rows, Genre to Columns, and U.S. Box Office Receipts (Inflation Adjusted Millions $) to Σ Values.
• Click on Genre from Σ Values.
• Select Value Field settings.
• In Summarize value field by, choose Average and click OK.

Thus, the average inflation-adjusted U.S. box-office receipts for genre-rating pair for all movies in the data set is constructed.

From the table, it is clear that the average U.S. box-office receipts are the highest for the genre-rating pair of G and Drama. Also, it is the lowest for G and Action.