Submit a Word document with your responses and be sure to also attach any relevant Excel files with your submission.
Chapter 1 Exercises
The sensitivity analysis in Example 1.3 (attached) 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?
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.
In Example 1.3 (attached), 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
A researcher is interested in determining whether there is a relationship between the weekly number of room air-conditioning units sold and the time of year. What type of descriptive chart would be most useful in performing this analysis? Explain your choice.
Explain why the standard deviation would likely not be a reliable measure of the variability of a distribution of data that includes at least one extreme outlier.
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.
The Wall Street Journal CEO Compensation Study analyzed CEO pay from many US companies with fiscal year 2008 revenue of at least $5B that filed their proxy statements between October 2008 and March 2009.
The data are in file P02_30.xlsx. (attached)
Create histograms to gain a clearer understanding of the distributions of annual base salaries and bonuses earned by the surveyed CEOs in fiscal 2008. How would you characterize these histograms? (hint: there should be a histogram for salaries and one for bonuses)
Find the annual salary below which 75% of all given CEO salaries fall. (Hint: use the third quartile)
Find the annual bonus above which 55% of all given CEO bonuses fall. (Hint: use =percentile(range, 0.55)
Determine the range of the middle 50% of all given total direct compensation figures. For the 50% of the executives that do not fall in this middle 50% range, is there more variability in total direct compensation to the right than to the left? Explain. (Hint: quartile range is Q3 – Q1)
Chapter 3 Exercises
The file P03_54.xlsx (attached) 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 – Chapter 3 Help
Remember:
Verify your data is correct! You should check to ensure you only have films released in 2011. Otherwise your results will be skewed.
The scatter plots can be finicky; one day I’ll figure out why it doesn’t play nice, but the video below shows you how to get them to appear the way you want them to for this assignment
if you turn in a spreadsheet that looks like the right answer but is simply hard coded values, I will be able to tell. When I grade, I click into cells to ensure you understood the formulas involved and followed the correct process.
