# Spreadsheet Modeling and Regression Analysis

Spreadsheet Modeling and Regression Analysis

Student’s Name

Institutional Affiliation

Spreadsheet Modeling and Regression Analysis

Part 1 Quiz 1

1). Use scenario manager to report the scenario summary report for the impact of the change in correlation on your portfolio standard deviation.

Correlation

Correlation is the interrelationship between two variables. It can be positive, negative or no correlation.

Scenario1. -100%

This means that there is a negative perfect relationship between standard deviation and Expected return. If standard deviation changes by (-100%) then expected returns will change proportionally in the opposite direction. i.e. an increase in standard deviation results to a decrease in expected returns in the same proportion.

Scenario2. 0%

It means that there is no connection or relationship standard deviation and expected returns. If standard deviation changes you cannot make a prediction about the movement of the expected returns.

Scenario3. 100%

It means that there is a positive perfect relationship between the standard deviations and expected returns. If standard deviation changes by 100% then the expected returns will change proportionally in the same direction i.e. an increase in standard deviation results to an increase in expected returns in the same proportion.

Part 1 Quiz 2

2).portfolio standard deviation=wf*sf+wt*st+(2wfwt*covariance f,t)

COV(f,t)=Correlation*Sf*St

=30%*30%*40%

=36% or 0.36

Portfolio Standard Deviation=Wf St+ WtSt+ 2W1W2COVft

Facebook W=10%

Weight allocated to twitter=100%-10%=90%

S=0.12*0.3+0.92*0.4+ 2*0.1*0.9*0.36=0.3918

=39.18%

Facebook W=20%

Weight allocated to twitter=100%-20%=80%

S=0.22*0.3+0.8*20.4+2*0.2*0.8*0.36=0.3832

=38.32%

Ffacebook W=30%

Weight allocated to twitter=100%-30%=70%

S=0.32*0.3+0.72*0.4+2*0.3*0.7*0.36=0.374

=37.4%

Table

weights Std Deviation

facebook twitter 10% 90% 39%

20% 80% 38%

30% 70% 37%

Part 2

The following data is obtained from United Arab Emirates recruiting company, basing its HIRING criterion on variables GRADE, TUCE and GRADE; where HIRING is the dummy variable.

> Emirates=read.table(“c:/xyz/emirates.csv”,sep=”,”,header=T)

> Emirates

GPA TUCE GRADE HIRING 2.66 20 C 0 2.89 22 B 0 3.28 24 B 1 2.92 12 B 0 4 21 A 0 2.86 17 B 0 3.03 25 B 0 3.92 29 A 1 2.63 20 C 0 3.32 23 B 1 3.57 23 A 1 3.26 25 B 1 3.53 26 A 1 2.78 19 B 0 HIRING= 1 if the applicant was hired and 0, if not.

TUCE- Applicant’s grade on test of a subject

GPA- Applicant’s Grade Point Average

> attach(Emirates)- Makes the individual data variables in the set to be accessed in the R- interphase.

1.) > mean(GPA)

[1] 3.189286

> mean(TUCE)

[1] 21.85714

> mean(HIRING)

[1] 0.4285714

> sd(GPA)- sd stands for standard deviation

[1] 0.441927

> sd(TUCE)

[1] 4.221869

> cor(GPA,TUCE)- cor implies the correlation between any pair of variables variables.

[1] 0.5705486

> cor(GPA,HIRING)

[1] 0.591206

> cor(TUCE,HIRING)

[1] 0.6690258

2.) Correlation matrix comprises of the following:

> var(GPA)

[1] 0.1952995

> var(TUCE)

[1] 17.82418

> var(HIRING)

[1] 0.2637363

> cov(GPA,TUCE)

[1] 1.064505

> cov(GPA,HIRING)

[1] 0.1341758

> cov(TUCE,HIRING)

[1] 1.450549GPATUCEHIRING

GPA0.19529951.0645050.1341758

TUCE1.06450517.824181.450549

HIRING0.13417581.4505490.2637363

3.) > model=lm(GPA~TUCE)- lm implies a linear model.

> model

Call:

lm(formula = GPA ~ TUCE)

Coefficients:

(Intercept) TUCE

1.88392 0.05972

> summary(model)

Call:

lm(formula = GPA ~ TUCE)

Residuals:

Min 1Q Median 3Q Max

-0.44837 -0.29053 -0.03823 0.25142 0.86191

Coefficients:

Estimate Std. Error t value Pr(>|t|)

(Intercept) 1.88392 0.55173 3.415 0.00513 **

TUCE 0.05972 0.02482 2.407 0.03312 *

—

Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.3778 on 12 degrees of freedom

Multiple R-squared: 0.3255, Adjusted R-squared: 0.2693

F-statistic: 5.792 on 1 and 12 DF, p-value: 0.03312

Comment: The linear model is significant at 5% level of significant.

> model1=lm(GPA~HIRING)

> model1

Call:

lm(formula = GPA ~ HIRING)

Coefficients:

(Intercept) HIRING

2.9712 0.5088

> summary (model1)

Call:

lm (formula = GPA ~ HIRING)

Residuals:

Min 1Q Median 3Q Max

-0.34125 -0.19781 -0.09625 0.05656 1.02875

Coefficients:

Estimate Std. Error t value Pr(>|t|)

(Intercept) 2.9712 0.1312 22.654 3.24e-11 ***

HIRING 0.5088 0.2004 2.539 0.026 *

—

Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.371 on 12 degrees of freedom

Multiple R-squared: 0.3495, Adjusted R-squared: 0.2953

F-statistic: 6.448 on 1 and 12 DF, p-value: 0.02597

Comment: The linear model is significant at 5% level of significant.

> model1=lm(TUCE~HIRING)

> model2=lm(TUCE~HIRING)

> model2

Call:

lm(formula = TUCE ~ HIRING)

Coefficients:

(Intercept) HIRING

19.5 5.5

> summary(model2)

Call:

lm(formula = TUCE ~ HIRING)

Residuals:

Min 1Q Median 3Q Max

-7.500 -1.750 0.250 1.375 5.500

Coefficients:

Estimate Std. Error t value Pr(>|t|)

(Intercept) 19.500 1.155 16.887 9.95e-10 ***

HIRING 5.500 1.764 3.118 0.00888 **

—

Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 3.266 on 12 degrees of freedom

Multiple R-squared: 0.4476, Adjusted R-squared: 0.4016

F-statistic: 9.723 on 1 and 12 DF, p-value: 0.008884

Comment: The linear model is not significant at 1%,5% level of significant but significant at 10%.

4 and 5.)

> model3=lm(GPA~TUCE+HIRING)

> model3

Call:

lm(formula = GPA ~ TUCE + HIRING)

Coefficients:

(Intercept) TUCE HIRING

2.32455 0.03316 0.32635

> summary(model3)

Call:

lm(formula = GPA ~ TUCE + HIRING)

Residuals:

Min 1Q Median 3Q Max

-0.3578 -0.1727 -0.1087 0.1215 0.9790

Coefficients:

Estimate Std. Error t value Pr(>|t|)

(Intercept) 2.32455 0.65204 3.565 0.00443 **

TUCE 0.03316 0.03276 1.012 0.33307

HIRING 0.32635 0.26928 1.212 0.25093

—

Signif. codes: 0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.3706 on 11 degrees of freedom

Multiple R-squared: 0.405, Adjusted R-squared: 0.2968

F-statistic: 3.743 on 2 and 11 DF, p-value: 0.05754

Comment: The linear model is neither significant at 1%, 5% nor at 10% level of significant because of the much larger p-value.