There are 6 questions.
The assignment must be posted in an excel file with the formulas.
Title | Year Released | Budget (Inflation Adjusted Millions $) | World Box Office Receipts (Inflation Adjusted Millions $) | U.S. Box Office Receipts (Inflation Adjusted Millions $) | Rating | Genre | Budget (Non- Inflation Adjusted Millions $) | World Box Office Receipts (Non- Inflation Adjusted Millions $) | U.S. Box Office Receipts (Non- Inflation Adjusted Millions $) |
Gone With the Wind | 1939 | 13 | 3,242 | 1,650 | G | Drama | 3 | 391 | 199 |
Star Wars | 1977 | 20 | 2,468 | 1,426 | PG | SciFi/ Fantasy | 11 | 798 | 461 |
The Sound of Music | 1965 | — | 1,145 | 1,145 | G | Musical | — | 163 | 163 |
E.T. | 1982 | — | 1,970 | 1,132 | PG | SciFi/ Fantasy | — | 757 | 435 |
Titanic | 1997 | 100 | 3,636 | 1,096 | PG-13 | Drama | 200 | 2,185 | 659 |
The Ten Com- mandments | 1956 | 184 | 1,053 | 1,053 | G | Drama | 14 | 80 | 80 |
Jaws | 1975 | 26 | 1,865 | 1,029 | PG | Action | 12 | 471 | 260 |
Doctor Zhivago | 1965 | 96 | 973 | 973 | PG-13 | Drama | 11 | 112 | 112 |
The Jungle Book | 1967 | — | 1,263 | 871 | G | Animated | — | 206 | 142 |
Snow White and the
Seven Dwarfs |
1937 | 5 | 854 | 854 | G | Animated | 1 | 185 | 185 |
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. Create a scatter chart to examine the relationship between the noninflation-adjusted 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 (Non- Inflation Adjusted Millions $) and then creating a scatter chart using only the movies that include data for Budget (Non-Inflation Adjusted Millions $).] What does this scatter chart indicate about the relationship between the movie’s budget and the world box-office receipts?
3. Create a scatter chart to examine the relationship between the inflation-adjusted budget and the inflation-adjusted world box-office receipts. What does this scatter chart indicate about the relationship between the movie’s inflation-adjusted budget and the inflation-adjusted world box-office receipts? Is this relationship different than what was shown for the noninflation-adjusted amounts? If so, why?
4. 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?
5. 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?
6. 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.