Question

According to the USDA Economic Research Center (Links to an external site.), "Any student in a participating school can get an NSLP lunch regardless of the student's household income." Using the Kids Count data provided, calculate regression analyses with scatter diagrams and trendlines in Excel, and compare the differences and similarities between children with at least one parent unemployed and children from low-income working families. One regression analysis will compare total public school students receiving free or reduced price lunches in Indiana with at least one parent unemployed and the other regression analysis will compare total public school students receiving free or reduced price lunches in Indiana with children from low-income working families. Both worksheets should show a Summary Output to include multiple R, R square, adjusted R square, standard error, and number of observations. In addition, include ANOVA calculations and the residual output.

Do you see a trend and if so, what is it? What does the data suggest about working or not working and the influence on student free/reduced lunches? How does Community Eligibility Provision (CEP) potentially impact future data sets?

Answer 1

sollution; from the given the data...... Scatterplot: Select data-> Insert-> Charts(Scatterplot) -> Add trendline -> Model(Linear) -> Select R2 and trendline display

Method : Enter data as shown -> go to Data tab -> go to Analysis -> select Data Analysis -> Select Regression -> Enter Y and X range -> OK

You will get the following output.

Regression 1:

Regression 1	one unemployed parent	Y
2008	98000	408625
2009	198000	435039
2010	196000	472076
2011	172000	489137
2012	163000	501230
2013	127000	508582
2014	95000	512114
2015	69000	511677
2016	62000	502452
2017	58000	493243

Scatter plot :

Table for Regression 1:

Regression Statistics
Multiple R	0.322966515
R Square	0.10430737
Adjusted R Square	-0.007654209
Standard Error	35296.18799
Observations	10

Anova for 1:

ANOVA
	df	SS	MS	F	Significance F
Regression	1	1.16E+09	1.16E+09	0.931635	0.362705349
Residual	8	9.97E+09	1.25E+09
Total	9	1.11E+10

Estimates:

	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	508974.0935	28734.12	17.71323	1.06E-07	442713.0891	575235.1	442713.1	575235.1
X Variable 1	-0.20643452	0.213875	-0.96521	0.362705	-0.699630368	0.286761	-0.69963	0.286761

Regression 2:

Scatter plot:

Table for R2 :

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.780121
R Square	0.608588
Adjusted R Square	0.559662
Standard Error	23389.23
Observations	10
ANOVA
	df	SS	MS	F	Significance F
Regression	1	6.8E+09	6.8E+09	12.43883	0.007767
Residual	8	4.38E+09	5.47E+08
Total	9	1.12E+10
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	10022.48	134418.6	0.074562	0.942394	-299947	319992.3	-299947	319992.3
X Variable 1	0.559129	0.158534	3.526873	0.007767	0.193549	0.92471	0.193549	0.92471

As it can be seen ,

R Square

0.608588237

(Regression2)

>

R Square

0.104307

(Regression 1)

Regression2 is significant using (F cal > F critical). Therefore, lower income families has an effect on  total public school students receiving free or reduced price lunches in Indiana rather than having one unemployed parent.

Please rate my answer and comment for doubt. thank you