A1-5) [15 points] The file P03_22.xlsx lists financial data on movies released from 1980 to 2011 with budgets of at least $20million.

1. [5 points] Reduce the size of this data set by deleting all movies with a budget of more than $100 million. Also, delete all movies where US Gross and/or Worldwide Gross is listed as Unknown.
2. [5 points] For the remaining movies, create a table of correlations between the variables Budget, US Gross, and Worldwide Gross. Comment on the results.
3. [5 points] For the movies remaining after part a, create a scatterplot of Worldwide Gross (Y axis) versus US Gross and another scatterplot of US Gross (Y axis) versus Budget. Briefly explain any patterns you see in these scatterplots. Do they seem to be consistent with the corresponding correlations?

A1-6) [15 points] The file P03_22.xlsx lists financial data on movies released from 1980 to 2011 with budgets of at least $20 million.

1. [5 points] Create three new variables, Ratio1, Ratio2, and Decade. Ratio1 should be US Gross divided by Budget, Ratio2 should be Worldwide Gross divided by Budget, and Decade should list 1980s, 1990s, or 2000s, depending on the year of the release date. If either US Gross or Worldwide Gross is listed as "Unknown," the corresponding ratio should be blank. (Hint: For Decade, use the YEAR function to fill in a new Year column.

Then use a lookup table to populate the Decade column.)

1. [5 points] Use a pivot table to find counts of movies by various distributors. Then go back to the data and create one more column, Distributor New, which lists the distributor for distributors with at least 30 movies and lists Other for the rest. (Hint: Use a lookup table to populate Distributor New, but also use an IF to fill in Other where the distributor is missing.)
2. [5 points] Create a pivot table that shows average and standard deviation of Ratio1, broken down by Distributor New, with Decade in the Filters area. Repeat part c for Ratio2. Comment on any striking results.

A1-7) [15 points] Supermarket Transactions.xlsx contains over 14,000 transactions made by supermarket customers over a period of approximately two years. For this problem, consider this data set the population of transactions.

1. [5 points] If you were interested in estimating the mean of Revenue for the population, why might it make sense to use a stratified sample, stratified by product family, to estimate this mean?
2. [5 points] Suppose you want to generate a stratified random sample, stratified by product family, and have the total sample size to be 250. If you use proportional sample sizes, how many transactions should you sample from each of the three product families?
3. [5 points] Using the sample sizes from part b, generate a corresponding stratified random sample. What are the individual sample means from the three product families? What are the sample standard deviations?

A1-8) [15 points] The file P02_35.xlsx contains data from a survey of 500randomly selected households. For this problem, consider this data set a simple random sample from all possible households, where the number of households in the population is well over 1,000,000.

1. [5 points] Create a new variable, Total Income, that is the sum of First Income and Second Income, and for each of the four variables Total Income, Monthly Payment, Utilities, and Debt, find the sample mean.
2. [5 points] What are the (approximate) standard errors of the estimates in part b? How can you interpret these standard errors? Be as specific as possible. Is the finite population correction required? Why or why not?
3. [5 points] Is it likely that the estimate of Total Income in part b is accurate to within $1500? Why or why not?