Question

4-Excel Problems (short project): suppose x is number of years passed year 2010, y is the cost of average electrical cars in Dollars. The following data for cost of the electric car and the year is give below. X y 22500 2 23600 3 24400 4 25300 6 27800 7 28400 a Enter the above data into an Excel fine. Use Excel to find the slope and the y-intercept of best fit line (regression line). b-what is equation of this regression line using the slope and y-intercept you found in part a, above. c-use the equation you found in part b, what is the price of electric car for year 2015 and year 2010

Answer 1

First ente3r the data int excel file. now goto data menu on the menu bar now click on data analysis (which is present on the top right corner) under that select regressen select appropriate data and click ok. You will get the results.

The excel results are as follows.

Regression Statistics
Multiple R	0.997103
R Square	0.994214
Adjusted R Square	0.992767
Standard Error	199.0661
Observations	6

ANOVA
	df	SS	MS	F	Significance F
Regression	1	27234824	27234824	687.2738	1.26E-05
Residual	4	158509.3	39627.33
Total	5	27393333

	Coefficients	Standard Error	t Stat	P-value
Intercept	21471.43	168.24	127.62	2.26E-08
X ( slope)	1007.45	38.42	26.21	1.26E-05

a)

Slope = 1007.453

Y - Intercept = $ 21471.43

b) y = 21471.43 + 1007.45 X

c) the cost of electric cars in 2015

It is given that the base year as 2010

0 is considered as 2010 hence 2015 should be considered as 5

The car price in 2010 will be = $21471.43

The price of car in 2015 (x = 5)

Y = 21471.43 + 5*1007.45 = $26508.68