This project utilizes the “Real Estate – Base” database. The purpose is twofold:-Build critical thinking skills needed to structure data analysis appropriately for effective decision making.-Analyze available data practically and skillfully in order to build an explanatory regression model. The Real Estate - Base database includes the following variables for 101 homes (* NOTE: These variables are shown as qualitative variables within the database):

a.*Unit# (An assigned database key)

b. *Type(H = House, C = Condo/Apartment)

c.*Location(1 through 10 – voting district where located)

d. *U/S/R(Urban vs. Suburban vs. Rural location)

e. Price(The price the house ended up selling for in 2017)

f.Sq. Ft.(Heated/Cooled & Attached square footage)

g. Lot (Acres)(Acreage of property)

h. Garage(Number of attached covered and/or enclosed parking positions

)i.BRs(Number of qualified bedrooms)

j.Baths(Number of bathrooms – no tub or shower indicated as .5)

k.*Pool(No=No Access; HA=Shared Pool; AG=Above Ground; IG=In Ground)

l.Age(Age of home in rounded year at end of 2017)

1. Create the following charts in Excel using the charting tools and the indicated variables in “Real Estate - Base.xlsx”:

a.Create a new tab in the spreadsheet called “Scatterplots”. After creating each Scatterplot on the original tab, move it to the Scatterplot tab you created.

b. Create a Scatterplot using the variables Price and Sq. Ft.c.Create a Scatterplot using the variables Price and Lot (Acres).d. Create a Scatterplot using the variables Price and Garage.e. Create a Scatterplot using the variables Price and BRs.f.Create a Scatterplot using the variables Price and Baths.g.Create a Scatterplot using the variables Price and Age.

2. What sort of relationship do you see between these variables based on the scatterplots?a.Between Price and Sq. Ft. (Circle)?No relationship Weak Moderate Strongb. Between Price and Lot (Circle)?No relationship Weak Moderate Strongc.Between Price and Garage (Circle)?No relationship Weak Moderate Strongd. Between Price and BRs (Circle)?No relationship Weak Moderate Stronge. Between Price and Baths (Circle)?No relationship Weak Moderate Strongf.Between Price and Age (Circle)?No relationship Weak Moderate Strong

3. In the Excel spreadsheet provided, using the Data Analysis Add-in, run a regression analysis with Price as the Dependent Variable and Lot, Garage and BRs as the Independent Variables and select to have Excel create a new tab called “Regression Model”.

4. Provide the following from the “Excel Model”:

a. Coefficient of Determination (R-squared)___________________b. Y-Intercept for the Regression Model___________________c.Slope value for X1 (Lot)___________________d. Slope value for X2 (Garage)___________________e. Slope value for X3 (BRs)___________________ Phase 2 of the Project (Critical Thinking and SAS® Model)

5. Do you think we need all three current Independent variables in our Regression model to predict changes in Price (Circle)? Yes No Explain: _______________________________________________________________________________________________________________________________________________________________________________________________________________________________________

6. Which variable(s) would you remove (Circle)?Lot SizeGarage BRs

7. Of the following variables in the spreadsheet, which variable would you select next to add to the model (i.e., you think it would create a stronger prediction of Price)?Type Location U/S/R Sq. Ft. Baths Pool Age

8. Run a SAS Regression Model on the Real Estate – Base database using Price as the Dependent Variable (Y) and include the original Independent Variables (minus any you removed in step 6) and adding the variable you chose in step 7. Print your model output

9. Provide the following from the SAS Model:a.Coefficient of Determination (R-squared).________________________b. Y-Intercept for the Regression Model________________________c.Slope value for each of your Independent Variables.________________________

10. Did your SAS model provide a stronger Coefficient of Determination (Circle)? Yes No Critical Thinking Question:

11. A large real estate company is trying to use similar data plus their own sales data to forecast total sales for the coming year for each of their agents and they have pulled data from their Finance records. They are trying to assemble the best data to build a Regression model. a.Would it make sense to use the same data as we used above in the SAS model? Why or why not?____________________________________________________________________________________________________________________________________________________________________b. Recommend three data elements you think they probably have available to help them predict sales for each of their sales people.1. ______________________________________________2. ______________________________________________3. ____________________________________________

Unit #	Type	Location	U/S/R		Sq. Ft.		Lot (Acres)	Garage	BRs	Baths	Pool	Age	Price
1	H	10	R		1100		2	0	2	1	No	27	$ 54,000
2	H	2	U		1875		0.25	1	3	2	No	26	$ 98,000
3	H	5	S		1350		0.25	0	2	1.5	AG	82	$ 125,700
4	H	6	S		2612		0.5	2	3	2	No	11	$ 250,000
5	H	9	S		2190		0.5	1	3	2	No	17	$ 411,500
6	H	1	U		1800		0.25	0	3	1	No	21	$ 56,500
7	H	3	S		1605		0.25	2	3	2	HA	6	$ 289,500
8	H	7	R		2199		12	2	3	2.5	No	72	$ 420,000
9	H	4	S		2120		0.4	2	3	2	No	15	$ 199,800
10	C	8	U		900		0	0	2	2	HA	4	$ 249,900
11	H	10	R		1950		1	1	2	2	No	12	$ 77,000
12	H	2	U		1420		0.5	0	2	2	No	16	$ 78,600
13	H	5	S		2090		0.75	2	3	2	IG	22	$ 199,800
14	H	6	S		2770		0.5	2	3	2.5	HA	9	$ 279,500
15	H	9	S		3650		1	3	5	5	HA	4	$ 842,000
16	H	1	U		1600		0.25	1	3	1.5	No	28	$ 66,720
17	H	3	S		2288		0.5	2	3	2	No	11	$ 311,450
18	H	7	R		2000		1.5	2	3	2	IG	21	$ 311,520
19	H	4	S		1880		0.25	1	3	2	IG	9	$ 187,500
20	C	8	U		980		0	1	2	1.5	HA	5	$ 311,750
21	H	10	R		3011		3	1	4	2	AG	35	$ 98,000
22	H	2	U		2980		0.4	2	3	2	No	4	$ 112,000
23	H	5	S		1850		0.25	0	3	2	No	11	$ 146,850
24	H	6	S		3520		0.5	3	4	2.5	IG	3	$ 301,500
25	H	9	S		3300		0.75	3	4	3.5	HA	9	$ 690,000
26	H	1	U		1905		0.5	1	3	1.5	AG	37	$ 71,200
27	H	3	S		2850		0.25	2	3	2	No	5	$ 275,000
28	H	7	R		3250		10	3	4	2	No	2	$ 598,230
29	H	4	S		1900		0.4	2	3	2	No	3	$ 176,500
30	C	8	U		1150		0	1	3	2.5	HA	0	$ 405,200
31	H	10	R		2015		1.5	1	3	1.5	No	38	$ 68,521
32	H	2	U		2190		0.66	1	3	2.5	IG	16	$ 101,500
33	H	5	S		1750		0.66	1	3	1.5	No	22	$ 117,650
34	H	6	S		2190		1	2	3	2.5	HA	8	$ 266,000
35	H	9	S		3450		0.75	2	3	3	IG	6	$ 601,500
36	H	1	U		1064		0.5	0	2	1.5	No	31	$ 39,800
37	H	3	S		2540		0.75	2	4	2.5	No	9	$ 401,500
38	H	7	R		4200		5	3	5	2.5	No	4	$ 782,000
39	H	4	S		1980		0.66	2	3	2	HA	8	$ 201,500
40	C	8	U		850		0	0	2	2	HA	6	$ 199,650
41	H	10	R		1865		14	1	3	2	No	17	$ 119,500
42	H	2	U		1750		0.75	1	3	2	AG	21	$ 88,420
43	H	5	S		1700		0.5	2	3	2	No	15	$ 188,500
44	H	6	S		2045		0.5	1	3	2	No	8	$ 231,100
45	H	9	S		2700		0.5	2	3	2.5	No	15	$ 485,200
46	H	1	U		1550		0.75	1	3	2	No	29	$ 48,999
47	H	3	S		2390		0.5	2	4	2	No	13	$ 366,500
48	H	7	R		2050		9	2	3	2	No	17	$ 356,420
49	H	4	S		1830		0.25	1	2	2	No	8	$ 157,650
50	C	8	U		1014		0	1	2	2	HA	2	$ 288,500
51	H	10	R		1450		0.5	0	2	1	No	36	$ 49,874
52	H	2	U		1800		0.5	2	3	2.5	No	9	$ 91,640
53	H	5	S		2015		0.75	1	3	2	No	12	$ 179,500
54	H	6	S		1950		0.5	2	3	2.5	No	4	$ 189,500
55	H	9	S		2888		0.5	2	4	2.5	No	4	$ 532,800
56	H	1	U		2012		0.4	1	3	2	No	16	$ 52,100
57	H	3	S		2450		0.5	2	3	2.5	No	7	$ 399,500
58	H	7	R		3450		4	2	3	2.5	No	37	$ 388,600
59	H	4	S		2200		0.4	2	3	2.5	No	2	$ 175,800
60	C	8	U		1050		0	1	2	2	HA	1	$ 301,500
61	H	10	R		2220		8	2	3	2	IG	21	$ 95,400
62	H	2	U		1995		0.5	1	2	1.5	No	15	$ 96,888
63	H	5	S		2100		1	2	3	2	No	36	$ 171,630
64	H	6	S		2750		0.75	2	3	2	IG	7	$ 207,500
65	H	9	S		3120		0.75	2	4	3	HA	2	$ 577,900
66	H	1	U		1011		0.25	0	2	2	No	14	$ 49,875
67	H	3	S		2120		0.5	2	2	2	HA	6	$ 247,800
68	H	7	R		3890		22	3	4	3.5	IG	3	$ 497,500
69	H	4	S		2100		0.66	2	4	2.5	HA	4	$ 205,000
70	C	8	U		1250		0	2	3	2.5	HA	0	$ 469,800
71	H	10	R		1090		2.5	0	3	1	AG	35	$ 77,000
72	H	2	U		1900		0.4	1	2	2	No	4	$ 91,400
73	H	5	S		1040		0.25	1	2	2	IG	3	$ 152,800
74	H	6	S		3850		1	2	4	3	HA	7	$ 401,500
75	H	9	S		2950		0.5	2	3	3	IG	1	$ 505,000
76	H	1	U		1000		0.4	1	2	2	AG	25	$ 58,700
77	H	3	S		2850		0.5	1	3	2.5	IG	2	$ 285,235
78	H	7	R		2740		75	2	3	2	IG	15	$ 675,500
79	H	4	S		1850		0.25	1	2	2	No	4	$ 188,760
80	C	8	U		900		0	1	2	2	HA	1	$ 302,900
81	H	10	R		2950		11	2	3	2	AG	5	$ 171,680
82	H	2	U		1640		0.75	1	2	2	No	7	$ 84,600
83	H	5	S		1800		0.8	2	3	2	No	2	$ 166,900
84	H	6	S		3200		0.75	3	4	2.5	HA	7	$ 366,900
85	H	9	S		2400		0.5	2	3	2	No	9	$ 411,960
86	H	1	U		2200		0.5	2	3	2	No	17	$ 68,900
87	H	3	S		3300		0.5	2	4	2.5	HA	8	$ 297,600
88	H	7	R		4350		11	2	4	3	No	3	$ 524,700
89	H	4	S		1800		0.4	2	3	2	No	12	$ 181,500
90	C	8	U		940		0	1	2	2	HA	7	$ 312,800
91	H	10	R		1750		4	1	2	2	No	37	$ 88,520
92	H	2	U		1490		0.5	0	3	1.5	No	32	$ 79,450
93	H	5	S		1500		0.5	1	3	2	No	17	$ 151,960
94	H	6	S		2175		1	2	3	2	No	11	$ 302,900
95	H	9	S		2550		0.5	2	3	2.5	No	11	$ 489,650
96	H	1	U		850		0.25	0	2	1.5	No	12	$ 64,995
97	H	3	S		2752		1	2	3	2.5	HA	6	$ 400,500
98	H	7	R		4540		18	2	5	3	No	14	$ 711,000
99	H	4	S		1590		0.5	1	2	2	IG	9	$ 172,450
100	C	8	U		980		0	1	3	2	HA	2	$ 345,900
101	H	10	R		1275		2	1	2	1.5	No	24	$ 81,400