This week, we will practice applying concepts from Chapters 1-3. Submit a Word document with your responses and be sure to also attach any relevant Excel files with your submission.
Chapter 1 Exercises
1. The sensitivity analysis in Example 1.3 was on the response rate. Suppose now that the response rate is known to be 8%, and the company wants to perform a sensitivity analysis on the number mailed. After all, this is a variable under direct control of the company. Create a one-way data table and a corresponding line chart of profit versus the number mailed, where the number mailed varies from 80,000 to 150,000 in increments of 10,000. Does it appear, from the results you see here, that there is an optimal number to mail, from all possible values, that maximizes profit?
2.Continuing the previous problem, use Goal Seek for each value of number mailed (once for 80,000, once for 90,000, and so on). For each, find the response rate that allows the company to break even. Then chart these values, where the number mailed is on the horizontal axis, and the breakeven response rate is on the vertical axis. Explain the behavior in this chart.
3.In Example 1.3, the range E9:E11 does not have a range name. Open your completed Excel file and name this range of Costs. Then look at the formula in cell E12. It does not automatically use the new range name. Modify the formula so that it does. Then select cell G4 and paste the new list of range names over the previous list.
Chapter 2 Exercises
1.An airline analyst wishes to estimate the proportion of all American adults who are afraid to fly because of potential terrorist attacks. To estimate this percentage, the analyst decides to survey 1500 Americans from across the nation. Identify the relevant sample and population in this situation.
2.The number of children living in each of a large number of randomly selected households is an example of which data type? Be specific.
3.Does it make sense to construct a histogram for the state of residence of randomly selected individuals in a sample? Explain why or why not.
4.The file P02_35.xlsx contains data from a survey of 500 randomly selected households.
1.Indicate the type of data for each of the variables included in the survey.
2.For each of the categorical variables in the survey, indicate whether the variable is nominal or ordinal, and why.
3.Create a histogram from two of the numerical variables in the data set. Indicate whether each of these distributi ons is skewed or symmetric.
4.Find the maximum and minimum debt levels for the households in this sample.
5.Find the indebtedness levels at each of the 25th, 50th, and 75th percentiles.
6.Find and interpret the interquartile range for the indebtedness levels of these households.
Chapter 3 Exercises
1.Suppose you have customer data on whether they have bought your product in a given time period, along with various demographics on the customers. Explain how you could use pivot tables to see which demographics are the primary drivers of their “yes/no” buying behavior.
2.The file P03_54.xlsx lists data for 593 movies released in 2011. Obviously, some movies are simply more popular than others, but success in 2011, measured by 2011 gross or 2011 tickets sold, could also be influenced by the release date. To check this, create a new variable, Days Out, which is the number of days the movie was out during 2011. For example, a movie released on 12/15 would have Days Out equal to 17 (which includes the release day). Create two scatterplots and corresponding correlations, one of 2011 Gross (Y-axis) versus Days Out and one of 2011 Tickets Sold (Y-axis) versus Days Out. Describe the behavior you see. Do you think a movie’s success can be predicted very well just by knowing how many days it has been out?