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.
Needs help with similar assignment?
We are available 24x7 to deliver the best services and assignment ready within 6-8 hours? Order a custom-written, plagiarism-free paper
Get Answer Over WhatsApp
Order Paper Now
