Part B: Scatter Plot and Multiple Regression Analysis
Instructions: Complete Part B subparts 1 and 2 below.
1. Create Scatter Plots using Table 2 data for each of the four independent variables versus the dependent variable in their corresponding tabs. Write a one-sentence conclusion for each scatter plot regarding the apparent linearity. Enter your tables on the respective worksheet tabs below, with your conclusion included with each.
3. Use the Excel Multivariable Regression feature and the Table 2 data to develop a best fit linear equation of the form Y = a + b1 X1 +b2X2 + b3X3 + b4X4, where Y is the profit, X1 is the sales level, X2 is the store size, X3 is the number of product lines carried, and X4 is the distance from the nearest competitor.
Run the complete regression analysis on a separate spreadsheet and past the results here. Expand as necessary.[An example of the proper format can be found in section 6.2 of your text.]
Insert your regression results into this table and then copy those results into the table in Part C.
Regression Equation Variables Table
y Profits	Intercept	b1 Coefficient	b2 Coefficient	b3 Coefficient	b4 Coefficient
y

Table 2 - Expanded Sales and Profits
Store #	Profits ($000s)	Sales ($000s)	Size (000s sq ft)	Lines	Distance (mi)
1	42.13	748.82	6.0	150	0.1
2	6.32	140.78	1.4	75	0.1
3	38.47	702.11	5.0	170	0.5
4	-0.32	41.54	1.0	75	0.0
5	3.65	96.85	1.2	75	0.2
6	7.77	166.93	1.5	75	0.5
7	4.31	109.05	1.3	75	0.3
8	4.53	263.92	1.1	80	0.4
9	-2.69	50.84	1.1	75	0.0
10	3.22	90.08	1.2	75	0.6
11	9.03	190.59	1.4	80	0.5
12	-2.59	91.75	1.2	75	0.0
13	6.39	141.57	1.4	80	0.3
14	24.39	377.04	3.5	160	1.2
15	13.93	198.69	1.5	100	0.7
16	2.13	62.78	1.3	75	0.1
17	17.48	265.28	2.1	110	0.9
18	7.21	91.8	1.3	85	0.3
19	15.62	231.6	2.5	120	0.9
20	33.61	548.31	4.5	200	0.5