Assignment:
Important Instructions
You may use any of Excel's features you wish, including the Data Analysis Toolpak. You are expected to work independently.
Develop your responses in the Excel file provided.Organize your responses in separate worksheets following the file's numbered worksheet tabs. Copy the required data into those worksheets as well. For example, for Problem 1, cut and paste the required data from the "Data" tab into Worksheet 1, and then provide your responses for parts (a)-(d) in that worksheet. Use text boxes to state your decisions for every hypothesis test, and for any explanations or interpretations requested.
The following problems refer to a sample of one-hundred California households compiled by the California Lottery Commission. Note that each household was sampled randomly and independently (e.g. there are no "matched pairs" in the sample). The data include:
Lottery: Annual expenditure on lottery tickets (in dollars)
Education: Years of education obtained by the head of household
Age: Age of the head of household
Children: Number of children in the household
Income: Annual household income (in dollars)
Area: 1 = Urban Area, 2 = Suburban Area, 3 = Rural Area
1. The Lottery is often accused of being a "tax on the poor and uneducated".
(a)Create a scatter diagram showing the relationship between lottery expenditure and annual income, with expenditure as the dependent variable. Label your axes.
(b)Create a scatter diagram showing the relationship between lottery expenditure and education, with expenditure as the dependent variable. Label your axes.
(c)Obtain the sample coefficient of correlation between lottery expenditure and income, and between lottery expenditure and education.
(d)Run a regression explaining lottery expenditure using allavailable information. What effect does moving from an Urban Area to a Rural Area have on lottery expenditure? What effect does having an additional child have on lottery expenditure?
(e)Predict lottery expenditure for a 35 year old HOH with 12 years of education, 3 children, $40,000 a year income, living an Urban Area.
(f)Based on your results, is the Lottery a "tax on the poor and uneducated"? Briefly explain.
2. Regional Analysis
(a)Construct a table showing the frequency distribution and the relative frequency distribution forthe number of residents in each area (Urban, Suburban, and Rural).
(b)Construct a bar chart illustrating the frequency distribution from part (a).
(c)Use Excel's "NORM.S.INV" function to obtain the following critical statistic: .
(d)Create a 95% confidence interval estimator for the proportion of California households that live in urban areas. Report the lower and upper confidence limits, and briefly interpret this interval estimator.
3. Lottery Ticket Expenditure Analysis
(a)At the 5% significance level, is there enough evidence to concludethat the variances of lottery expenditures in Urban and Rural areas are different?
(b)Using an appropriate test implied by your answer for part (b), is there enough evidence to conclude that the average household lottery expenditure in Rural areas is greater than the average household lottery expenditure in Urban areas?