Question

CAN YOU PLEASE ANSWER THESE USING EXCEL

To the Internal Revenue Service, the reasonableness of total itemized deductions depends on the taxpayer’s adjusted gross income.   Large deductions, which include charity and medical deductions, are more reasonable for taxpayers with large adjusted gross incomes.   If a taxpayer claims larger than average itemized deductions for a given level of income, the chances of an IRS audit are increased.   Data (in thousands of dollars) on adjusted gross income and the average or reasonable amount of itemized deductions follow:

           

Adjusted Gross Income ($1000s)	Reasonable Amount of Itemized Deductions ($1000s)
22	9.6
27	9.6
32	10.1
48	11.1
65	13.5
85	17.7
120	25.5

a.)        Develop a scatter diagram for these data with adjusted gross income as the independent variable.

            b.)        Use the least squares method to develop the estimated regression equation.

c.)        Estimate a reasonable level of total itemized deductions for a taxpayer with an adjusted gross income of $52,500.   If this taxpayer claimed itemized deductions of $20,400, would the IRS agent’s request for an audit appear justified?   Explain.

d.) Write the regression equation

e.) Interpret the regression constant and regression coefficient

f.) Forecast a value for the dependent variable, test the significant of the regression coefficient at an alpha level of .05, test the overall significant of the regression model, and interpret the coefficient of determination.

Answer 1

Solution-A:

Select the data>

Go to

Insert>scatter chart

Click on plus

Add on axis titles

Click on one observation

You will get

Reasonable Amount of Itemized Deductions=0.1613*Adjusted Gross Income+4.6768

slope=0.1613

y intercept=4.6768

SolutionB:

Data >Data analysis >Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.977174
R Square	0.954869
Adjusted R Square	0.945843
Standard Error	1.371565
Observations	7
ANOVA
	df	SS	MS	F	Significance F
Regression	1	199.0083	199.0083	105.7884	0.000149
Residual	5	9.405958	1.881192
Total	6	208.4143
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	4.67675	1.033394	4.52562	0.006251	2.020326	7.333175
Adjusted Gross Income	0.16131	0.015683	10.28535	0.000149	0.120994	0.201626

You will get

Reasonable Amount of Itemized Deductions=0.1613*Adjusted Gross Income+4.6768

slope=0.1613

y intercept=4.6768

Solution-c:

we have

Reasonable Amount of Itemized Deductions=0.1613*Adjusted Gross Income+4.6768

=0.1613*52.5+4.6768

=13.14505

=13.14505*1000

= 13145.05

itemized deductions of $20,400 is not reasonable

since predicted value is $13,145 only

d.) Write the regression equation

Reasonable Amount of Itemized Deductions=0.1613*Adjusted Gross Income+4.6768