Question

The following example describes the expenditure (in dollars) on recreation per month by employees at a certain company, and their corresponding monthly incomes: Using R cloud and provide the codes

Expenditure($): 2400,2650,2350,4950,3100,2500,5106,3100,2900,1750

Income($): 4120,50100,52000,66000,44500,37700,73500,37500,56700,35600

Using the equation for the linear regression that you calculated, estimate the monthly income of an employee at this company who spends 5000 dollars per month on recreation. What is the accuracy of your prediction?

Answer 1

Important formula:

Error= Actual -forecast

Absolute error= absolute value of error

Square of error=error^2

Mean Absolute Deviation= average of absolute error

Root Mean square error=square root of (average of square of error)

Intercept and slope are calculated using Excel formula

1	B	C	D	E	F	G	H
2	Expenditure	Income	Forecast	Intercept	Error	Absolute error	Square of error
3	2400	4120	37558.87	8542.87	-33438.87	33438.87	1118158026.88
4	2650	50100	40581.37	Slope	9518.63	9518.63	90604317.08
5	2350	52000	36954.37	12.09	15045.63	15045.63	226370982.10
6	4950	66000	68388.37		-2388.37	2388.37	5704311.26
7	3100	44500	46021.87		-1521.87	1521.87	2316088.30
8	2500	37700	38767.87		-1067.87	1067.87	1140346.34
9	5106	73500	70274.41		3225.59	3225.59	10404430.85
10	3100	37500	46021.87		-8521.87	8521.87	72622268.30
11	2900	56700	43603.87		13096.13	13096.13	171508620.98
12	1750	35600	29700.37		5899.63	5899.63	34805634.14
13						Mean Absolute Deviation	Root Mean square error
14						9372.45	13166.76

Income forecast for expenditure of $5000=8542.87+(12.09*5000)

Income forecast for expenditure of $5000=68992.87

Here, RSME value is 13166.76, RSME or Root mean square error measures accuracy of the model, it is also basis for comparing this value to same of other model, the model with lowest value of RSME should be chosen for further situation and considered as best model.

Excel formula:

1	B	C	D	E	F	G	H
2	Expenditure	Income	Forecast	Intercept	Error= Actual -forecast	Absolute error= absolute value of error	Square of error=error^2
3	2400	4120	=8542.87+(12.09*B3)	=INTERCEPT(C3:C12,B3:B12)	=C3-D3	=ABS(F3)	=F3^2
4	2650	50100	=8542.87+(12.09*B4)	Slope	=C4-D4	=ABS(F4)	=F4^2
5	2350	52000	=8542.87+(12.09*B5)	=SLOPE(C3:C12,B3:B12)	=C5-D5	=ABS(F5)	=F5^2
6	4950	66000	=8542.87+(12.09*B6)		=C6-D6	=ABS(F6)	=F6^2
7	3100	44500	=8542.87+(12.09*B7)		=C7-D7	=ABS(F7)	=F7^2
8	2500	37700	=8542.87+(12.09*B8)		=C8-D8	=ABS(F8)	=F8^2
9	5106	73500	=8542.87+(12.09*B9)		=C9-D9	=ABS(F9)	=F9^2
10	3100	37500	=8542.87+(12.09*B10)		=C10-D10	=ABS(F10)	=F10^2
11	2900	56700	=8542.87+(12.09*B11)		=C11-D11	=ABS(F11)	=F11^2
12	1750	35600	=8542.87+(12.09*B12)		=C12-D12	=ABS(F12)	=F12^2
13						Mean Absolute Deviation= average of absolute error	Root Mean square error=square root of (average of square of error)
14						=AVERAGE(G3:G12)	=SQRT(AVERAGE(H3:H12))