Qnt. 5040The Prescott Film Studio Regression Case Study | Complete Solution

Qnt. 5040 – Mini Report
Regressions: The Prescott Film Studio Regression Case Study, 2015
Excel File Needed: Prescott Film Studio - Regression Data 2015

Introduction to the Case Study

Porky Bacon is the principal film director and part owner in the Prescott Film Studios located in west Prescott.  He has been directing for over 2 decades and has, among his other film awards, won the prestigious Board of Directors Sigma Award at the 2014 Bangladeshi Film Industry ceremony.  His movie, Wasn’t it a Crying Shame TM, was a big hit overseas and earned well over $10 million in its first year after release.

Mr. Bacon has been wondering if he could predict the first year’s box office revenue if he knew several things about a film:

1.    What did it cost to produce the file in millions?
2.    What did the company spend to promote the file also in millions?
3.    If the movie was made from a book, how much did the book make in the first year of release (also in millions)?

When you are completed with this case study you will be able to complete the following table which you will include in your mini report.

Table One.  Projected Box Office Receipts for the Prescott Film Studio

Movie Number    Total Production Costs for this movie
(in millions)    Total Promotional Costs for this movie
(in millions)    
Total Book Sales
(in millions)    
Total Box Office Receipts for First Year (in millions)
1    $9.50    $1.75    $4.55    $
2    $6.00    $0.90    $1.30    $
3    $14.00    $6.95    $10.90    $

Your Tasks

Porky has asked you, his financial adviser, to help him with this project.  He has a list of 10 movies that his studio made last year that also were based on a book.  These values, all in millions, are found in the Excel file provided by your kind professor.  Using these data you will help Mr. Bacon with the following:

1.    Complete a one variable summary for all 10 films in the four categories of revenue or expenses.
a.    What do these statistics tell you about the films?  Be specific and look at the range, standard deviation, the IQR, the mean versus the median and other statistics from this output.
2.    Complete a single histogram on all four of these data (as a group, not individually) and analyze the results.
a.    What do these histograms tell you about the revenue or expenses for each of these films as a group?
b.    Be sure to add the trendline to each of these and include the equation and r-squared value on each.  What do these tell you about these data when compared to the total box office revenue?
3.    Complete 4 Q-Q plots for these four variables.
a.    What do these plots tell you about the data?  Is it relatively normal or not and why?  
4.    Complete a single box and whisker plot with all four of the exams on it.  (Hint: Expand the box and whisker plots and you will see more detail in them.)
a.    First explain what a box and whisker plot is, and how to understand it.  
b.    What do these box and whisker plots tell you about the revenues and expenses and how do they compare to each other?
5.    Complete a multiple regression using the total box office receipts as the dependent variable, and the other revenue or expenses as the independent variables.
a.    Do any of the variables exceed the alpha for this test?  If so, remove that/those variables and rerun the regression.  Explain why you removed any of these variables in your report.
b.    What does the r-squared value tell you about this data?
c.    What does the standard error of the estimate tell you about the data?
d.    According to the f-ratio, is this regression significant or not and why?
e.    Using the coefficients generate predict the first year box office revenues for the three films found in Table 1 above.  (You are to complete the column found on the right in yellow and include this table in your mini report.)  NOTE: If you removed any of the variables then the estimated revenue should be based only on the significant variables.
6.    What general things can you tell professor Mr. Bacon about the types of films that he should produce?
a.    Do such things as the amount a book earns before it is made into a movie make a difference?  Why or why not?
b.    Does the amount the studio spends on promotion make a difference in the box office revenue?  Why or why not?
c.    Does the cost of production make a difference to eventual first year revenues?  Why or why not?

Remember to develop your mini report as shown in the chats for this assignment.  All work is to be 1 & ½ spaces, ¾ inch margins, with all tables, graphs and figures having a number and a title and explain in the report.  Include an embedded Excel file showing your work, but note this file will not be graded and is only used to demonstrate that you did the actual work for this case study.

# # #

Adding a Trendline to a Graph in Word

First, create the XY scatterplot graph in Excel as shown by your professor during the chats.

Next right click on any data point in the graph you just created.  Notice that one option available is to Add Trendline.  Select that option (1).

Next you are presented with several options, select linear (2) for the type of trendline you want and also check to display the equation and the r-squared value (3) on the graph and select Close (4).

You can now right click on the graph and paste it into your Word document.  

Be sure to explain to the reader how to read and understand the graph, what the trendline means, and what the equation and r-squared values each mean.