Linear Regression

Please analyze the following scenario:

"ABC Enterprises is a growing company in which employees must frequently drive company vehicles to meet clients for business purposes.Travel is only within a 2-hour radius of the main office, but employees frequently find themselves on the road for lunch and/or dinner. Currently, each employee is allotted 35 dollars each day to cover meals on the days when they travel to meet their clients. The money is just for the employees' meals (there is a separate budget if they take their clients out). They are also reimbursed for gas/mileage out of a separate budget. Employees have complained that this food allowance is not enough, especially when traveling to some of the more costly areas. A survey was taken to get recommendations for a new allotment. The response rate to the survey was about 65% of the employees at the company."

The vice president hypothesizes that the older employees are the ones who want more money for meals than the younger ones. We are going to look at whether or not there is evidence to support this claim.If the claim is true, then the company is thinking about having a smaller meal allowance and sending the younger employees out more than the older ones.

• Explain how to generate a scatter diagram with trendline in MS Excel from the data in that tab. Include the equation of the line on the graph (under "more options") along with the r squared value (coefficient of determination). (Data found at bottom)
• What is a trendline, and how does it help us interpret the data?
• Discuss the slope of that trendline. Is it positive, negative, undefined, or 0?What does it mean?
• Use the trendline equation to figure out how much money a 46-year-old employee would want.
• Use the trendline equation to figure out how much money an 91-year-old employee would want.
• How much trust do you put into the answers for the 46 year old and 91 year old? How does the coefficient of determination and the simple correlation coefficient (r), and interpolation vs. extrapolation provide you with some input on your response?

59 employees completed survey:

Age	Requested Amount	Male or Female Requested
21	80	F
31	50	F
22	90	F
32	75	F
25	120	F
47	50	M
39	50	M
26	200	M
30	70	F
28	70	F
48	50	M
53	50	F
33	40	F
27	80	M
54	50	M
45	50	M
38	50	F
55	50	F
49	50	M
26	110	F
61	50	F
63	50	F
59	50	F
46	40	F
22	75	F
21	85	M
36	70	M
31	60	M
47	55	M
50	65	F
21	65	F
26	75	M
25	85	F
45	50	M
51	50	F
50	55	F
24	65	F
37	55	M
30	80	M
54	40	M
57	50	M
60	50	M
61	45	M
49	50	M
39	60	M
55	50	M
47	50	F
51	55	M
38	75	F
44	50	M
58	60	F
60	50	F
51	60	M
43	55	M
30	55	M
21	75	F
25	75	F
33	80	M
34	95	M