Excel Project Assignment – The Blockbuster Statistical Project

 

The goal of the project is to get familiarize with the process of (a) collecting and (b) organizing both quantitative and qualitative data by means of visually relevant graphic statistical tools (tables, graphs, charts, etc.) in order to facilitate a primarily descriptive statistical analysis of the “blockbuster” phenomenon within the US-based Film Industry.

 

If properly done, the study will enable you to better understand how the Big Studios leverage their risk factor to ensure financial success of their entertainment product, and hence increase their understanding of the interaction between economic and artistic factors in the world of business entertainment.

 

The methodology and tools acquired while completing this project could then be used to replicate similar studies in other entertainment related fields such as e-games, music, TV shows, mobile apps, etc.

 

(1) Collecting Data:

 

You will collect data from www.boxofficemojo.com using the Box Office/All Time reports; additional information could be found on www.IMDB.com or by using the free I-phone app Box Office Hits.

For our study, we will use the most successful 50 movies theatrically released in the US, according to any of the above sources (preferably the www.boxofficemojo website).

 

(2) Organizing data:

 

You will initially organize the data in a 10-caption table, with the following headers:

1. Title

2. Year the movie was released

3. Studio that produced/distributed (used standard abbreviations in the industry: Disney, BV, WB, etc.)

4. Genre (using a seven-category distribution: (1) Animation [A], (2) Sci Fi [SF], (3) Fantasy [F], (4) Action/Adventure [AA], (5) Drama [D], (6) Comedy [C] (both adult and children), and (7) Horror [H].

5. IP, Intellectual Property behind (whether adaptation or not [y] or [n])

6. Rating

7. Budget (rounded to the nearest million)

8. Box-Office USA (domestic revenue generated through theatrical distribution; rounded to the nearest million)

9. Simple Profitability Ratio R1 (Box-Off/Budget, rounded to two decimals; how much the movie made for each dollar spent to produce).

10. Simple Profitability Ratio R2 (ROI Percentage=[Box-Off—Budget]/Budget, rounded to two decimals; what was the return of investment for each dollar spent to produce; this is just a simple formula that does not include distribution fees, P&A expenses, etc. To assess the financial profitability of a movie, one needs to use far more complicated formulas that go beyond the scope of this project).

 

See example below:

 

	Title	Year	Studio	Genre	IP	Rating	Budget	BO USA	R1	R2
1	Avatar	2009	Fox	SF	N	PG 13	280	761	2.72	1.72

 

 

(3) Visual Representation of Data

 

You will generate the following graphs:

 

1. A Pareto chart with the most financially successful genres (based of number of films).
2. A Pareto chart with the most financially successful genres (based on total revenue generated)

 

1. A pie chart with the genre distribution among the 50 blockbusters.
2. A pie chart that represents the Rating breakdown of the 50 blockbusters.
3. A pie chart that represents the IP breakdown of the 50 blockbusters.

 

1. A time series graph that would show the distribution of blockbusters from one year to another.

 

1. A Pareto chart with the most financially successful studios based on total box-office returns per studio.
2. A Pareto chart with the most successful studios based on total number of blockbuster movies released.

 

1. A histogram that represents a frequency distribution based on budget (how many blockbusters for each $100Mil spent).
2. A histogram that represents a frequency distribution based on box-office (how many blockbusters for each $100Mil in revenue).

 

1. A bar chart that presents the Top 20 most successful films based on R1.
2. A bar chart that presents the Top 20 most successful films based on R2.

 

To generate the charts, one needs to select from the table the two relevant columns to be correlated, and then choose from the Charts menu the relevant chart; Excel will then generate the chart automatically.

 

 

(4) Interpretation of Data

 

Finally you will write a one-paragraph (minimum 6 sentences) containing an interpretation of (at least) one chart/histogram of your choice from the 12 charts they have generated. You should choose the one that you find the most revealing and significant. When exploring the data, you should also focus on the insights that that particular chart reveals, and reflect on how would this particular chart affect their decisions as far as green-lighting a film, should you place yourself in the shoes of a studio executive that reviews such data.

 

(5) Grade breakdown

 

Collecting & Organizing Data       30 points

Visual Representation of Data       30 points (=12X2.5pts)

Interpretation of Data                    30 points

Excel Formatting & Grammar       10 points