Compare State Spending on Education. Key for REGION is as follows: 1 = Northeast, 2 = Midwest, 3 = South, 4 = North Fiscal Year 2015 Amounts in $ billion State Alabama Alaska Arizona Arkansas California Colorado Connecticut Delaware Florida Georgia Hawaii Idaho Illinois Indiana Iowa Kansas Kentucky Louisiana Maine Maryland Massachusetts Michigan Minnesota Mississippi Missouri Montana Nebraska Nevada New Hampshire New Jersey New Mexico New York North Carolina North Dakota Ohio Oklahoma Oregon Pennsylvania Rhode Island South Carolina South Dakota Tennessee State Spending 6.6 1.8 4.9 3.3 31 5.5 3.6 1.9 10.6 8.4 3.4 1.2 8.9 6.9 3.5 2.5 5.7 4.4 1 4.8 6.6 10.1 6.4 2.7 5.2 1 2.1 1.6 1.2 9.9 2.5 13.3 10.2 1.3 8.6 4.4 3.8 11.2 1.2 5.2 0.7 5.9 Local Spending 8.3 2 9.9 5.2 79.7 8.9 9.5 2.1 29.5 18 -0.1 2.2 29.1 10.6 7.4 6.1 7.2 8.7 3.2 13.8 14.6 13.9 11.3 5.4 11.1 1.7 4.4 4.1 3 26.7 4.1 61 20 1.7 23.5 6.4 7.5 26.3 2.3 5.7 1.4 9.9 Population (million) 4.9 0.7 6.8 3 39.2 5.4 3.6 0.9 20.2 10.2 1.4 1.7 12.9 6.6 3.1 2.9 4.4 4.7 1.3 6 6.8 9.9 5.5 3 6.1 1 1.9 2.9 1.3 9 2.1 19.8 10 0.8 11.6 3.9 4 12.8 1.1 4.9 0.9 6.6 REGION 3 4 4 3 4 4 1 3 3 3 4 4 2 2 2 2 3 3 1 3 1 2 2 3 2 4 2 4 1 1 4 1 3 2 2 3 4 1 1 3 2 3 Texas Utah Vermont Virginia Washington West Virginia Wisconsin Wyoming 24.4 4.9 0.9 9.2 8.3 2.5 6.4 0.7 64.9 5.1 1.6 15.7 12.9 3.5 12.4 2 27.4 3 0.6 8.4 7.2 1.8 5.8 0.6 3 4 1 3 4 3 2 4 2012 election r r r r d d d d d r d r d r d r r r d d d d d r r r r d d d d d r r d r d d d r r r r r d d d r d r Compare State Spending on Education. The data given in tab one can be used to compare education spending by state. We cannot use the data as given, however. For example, to compare Texas and Vermont purely on total amount spent would give a false impression of how much each state spends on education because of varied population size. To make a more valid comparison we can calculate spending per capita by dividing total spent by state population. (although this is total state population and not just students it will give a much better basis for comparison). Question1 (5 points): Create a column showing spending per capita. Please be sure and note that spending is in billions and population is in millions. Also note that there are two spending columns which need to be combine Place this column in the right part of this spreadsheet. Question 2 (10 points): Provide descriptive statistics for the per capita spending. Calculations using EXCEL functions must be shown. Explain what each item represents, or give a definition in your own words. Mean: Median: Mode: Range: Standard Deviation: Comparing the mean to the median, is this data set likely skewed right, skewed left, or s Question 3 (5 points): Using the EXCEL functions provide: Lower Quartile (Q1): Upper Quartile (Q3): Interquartile Range: Question 4 (5 points): Your textbook gives a method for finding potential outliers in a set of data using the values found in Question 3. Determine whether there are any low outliers or high outliers in the duration data. First, provide the lower fence and upper fence (limits) for outliers. Lower fence (limit): Upper fence (limit): Are there low outliers (items below the lower fence)? Are there high outliers (items above the upper fence)? Note that there could be multiple outliers on either side, or there may be none on either side. te spends g total spent will give a nding is in billions h need to be combined. ght, skewed left, or symmetric? Question 5 (5 points): Produce a column to the right which eliminates any outliers. Make sure your column is sorted as well. Question 6 (10 points): Complete the following table by creating 8 bins and classifying the data accordingly: Spending Frequency Question 7 (10 point its): Create a relative frequency histogram from the above data . Be sure to label your chart with meaningful axis titles and a chart title. Place your histogram below (not to the side to the problem) Question 8 (10 points): Create a scatter plot (below) to explore the relationship between state and local spending on education. Make local spending the dependent variable. Make sure you give your scatter plot a clear title and axis labels. Question 9 (10 points): Using EXCEL calculate the slope and intercept and give the resulting regression equation. slope intercept regression line Question 10 (10 points): A regression line can be created for any two variables. Doing a correlation describes whether or not there is a meaningful relationship. Using an EXCEL function describe the correlation between the two. Is this a strong or weak correlation? Question 11 (10 points): Based on your regression line from question 10, what amount would you predict for local spending if a s $40 billion. What might be some reasons that using that prediction would give you hesitation? ocal spending sion equation. dict for local spending if a state spends Question 12 (15 points): Before the housing market collapsed and the tech bubble imploded the average state spending per pupil was $3500. Does it differ in today's economy? Use the data column you created to answer the question using a hypothesis test. Make your decision using an alpha of .05. Null Hypothesis Alternate Hypothesis Z (be sure and show calculations) Rejection Region Conclusion Question 13 (5 points): Give a 99% confidence interval for the mean spending per pupil