Question

At Brokerage firms, Customer satisfaction (dependent variable) is thought to be a function of speed of transactions and the size of the company (independent variables). Using excel determine the additional satisfaction rating that a customer derives from working with a large (L) company. Enter the increase in the satisfaction rating below.

(Note: ignore the p-value associated with customer size for this analysis.)

Brokerage	Size	Speed	Overall Sat
Scottrade, Inc.	S	3.4	3.5
Charles Schwab	S	3.3	3.4
Fidelity Brokerage Services	L	3.4	3.9
TD Ameritrade	L	3.6	3.6
E*Trade Financial	S	3.2	2.9
(Not listed)	L	3.2	2.7
Vanguard Brokerage Services	S	3.8	2.8
USAA Brokerage Services	S	3.8	3.6
Thinkorswim	L	2.6	2.6
Banc of America Investment Services	L	1.0	2.0

Answer 1

We have followed following steps to perform regression analysis in excel:

1. On the Data tab, in the Analysis group, click the Data Analysis button.

2. Select Regression and click OK.

3.In the Regression dialog box, configure the following settings:

• Select the Input Y Range, which is your dependent variable. In our case, it's Customer satisfaction.
• Select the Input X Range, i.e. your independent variable. In our case, it's the years of speed and size.
• Check the Labels box if there are headers at the top of your X and Y ranges.
• Choose your preferred Output option, a new worksheet in our case.

4. Click OK and observe the regression analysis output created by Excel.

Please note for regression analysis we replaced S (Small Size) as 0 and L (Large Size) as 1 under Size column.

Following is the regression output that we obtained:

Based on regression output, we can write regression equation as:

Overall Sat. = 1.18 + 0.15*Size + 0.59*Speed

For Small company Size = 0, so regression equation for small company is:

Overall Sat. = 1.18 + 0.15*0 + 0.59*Speed

Overall Sat. = 1.18 + 0.59*Speed

For Small company Size = 1, so regression equation for large company is:

Overall Sat. = 1.18 + 0.15*1 + 0.59*Speed

Overall Sat. = 1.18 + 0.15 + 0.59*Speed = 1.33 + 0.59*Speed

Comparing regression equation of small and large company, we have see that additional satisfaction rating that a customer derives from working with a large (L) company is 0.15