Create a column showing spending per capita
Question # 00447131
Posted By:
Updated on: 12/22/2016 12:28 AM Due on: 12/22/2016
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
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 Please enter the name(s) of students who worked on this assignment. 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).
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 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 6 (10 points):
Complete the following table by creating 8 bins and classifying the data accordingly:
Spending Frequency
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
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 Please enter the name(s) of students who worked on this assignment. 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.
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:
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.
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)
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.
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
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?
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
$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
-
Rating:
/5
Solution: Create a column showing spending per capita