please use Excel

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.

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

managerial Report

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

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?

1. 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?
2. 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?
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. 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
5. 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 prefer- ences 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