SQM Assignment

Semester 1

Sajjadur Rahman Khan (Sun)

ID: 0609270
Answer 1

Project A: Poultry

Yea NCF PV DCF at Total PV DCF at

r (00 Facto 10% Facto 20%
0) r at (000) r at (000)
10% 20%
1 250 0.909 227.27 227.27 0.833 208.32
1 5 5 3 5
2 320 0.824 264.45 491.72 0.694 222.20
6 5 4 8
3 380 0.751 285.49 777.21 0.578 219.90
3 5 7 6
4 450 0.683 307.35 1084.5 0.482 217.03
65 3 5
5 480 0.620 298.03 1382.5 0.401 192.91
9 2 97 9 2
1382.5 1060.3
97 86

Payback Period:

Initial investment was £1300000, thus the payback period is achieved

in the 5th year.

Net Present Value at 10%:

Total DCF-Initial Investment

(1382.597×1000)-1300000
=1382597-1300000

=82597

Profitability Index:
Total DCF
Initial Investment

(1382.597 ×1000)
¿
1300000

1382597
¿
1300000

¿ 1.064

NPV at 20%:

(1060.386×1000)-1300000

=1060386-1300000
¿−239614

IRR:
NVP1
IRR=r 1+ ×(r 2 −r 1)
NVP 1−NVP2
 82597
¿ 10+ ×(20−10)
82597−(−239614)

82597
¿ 10+ ×10
322211

¿ 10+0.2563 ×10

¿ 10+2.563

¿ 12.563

Project B: Real Estate

Year NCF PV DCF at Total PV DCF at

(000) Factor 10% Factor 20%
at 10% (000) at 20% (000)
1 650 0.9091 590.915 590.915 0.8333 541.645
2 650 0.8264 537.16 1128.075 0.6944 451.36
3 420 0.7513 315.546 1443.621 0.5787 243.054
4 680 0.6830 464.44 1908.061 0.4823 327.964
5 700 0.6209 434.63 2342.691 0.4019 281.33
2342.691 1845.353

Payback Period:

Initial Investment was £2300000, thus the payback period was

achieved in the 5th year.

Net Present Value:

Total DCF-Initial Investment

¿ ( 2342.691× 1000 )−2300000

¿ 2342691−2300000

¿ 42691

Profitability Index:
Total DCF
Initial Investment

(2342.691 ×1000)
¿
2300000

2342691
¿
2300000

¿ 1.0186

NPV at 20%:
( 1845.353 ×1000 )−2300000

¿ 1845353−2300000

¿−454647

IRR:
NVP1
IRR=r 1+ ×(r 2 −r 1)
NVP 1−NVP2
 42691
¿ 10+ ×(20−10)
42691−(−454647)

42691
¿ 10+ × 10
497338

¿ 10+0.0858 ×10

¿ 10+0.858

¿ 10.858

Project C: Retail

Year NCF PV DCF at Total PV DCF at

(000) Factor 10% Factor 3% (000)
at 10% (000) at 3%
1 290 0.9091 263.639 263.639 0.9708 281.532
2 345 0.8264 285.108 548.747 0.9426 325.197
3 550 0.7513 413.215 961.962 0.9152 503.36
4 580 0.6830 396.14 1358.102 0.8885 515.33
5 600 0.6209 372.54 1730.642 0.8626 517.56
1730.642 2142.979

Payback Period:

Initial Investment was £1800000, but the payback period was not
achieved even in the 5th year.
Net Present Value at 10%:

Total DCF-Initial Investment

( 1730.642× 1000 )−1800000

¿ 1730642−1800000

¿−69358

Profitability Index:
Total DCF
Initial Investment

(1730.642× 1000)
¿
1800000

1730642
¿
1800000

¿ 0.962

NVP at 3%:
( 2142.979 ×1000 )−1800000

¿ 2142979−1800000

¿ 342970

IRR:
NVP1
IRR=r 1+ ×(r 2 −r 1)
NVP 1−NVP2
 342970
¿ 3+ ×(10−3)
342970−(−69358)

342970
¿ 3+ ×7
412328

¿ 3+0.8318 ×7

¿ 3+5.8225

¿ 8.8225

Project D: Hotel

Year NCF PV DCF at Total PV DCF at

(000) Factor 10% Factor 20%
at 10% (000) at 20% (000)
1 165 0.9091 150.0015 150.0015 0.8333 137.4945
2 180 0.8264 148.752 298.7535 0.6944 124.992
3 250 0.7513 187.825 486.5785 0.5787 144.675
4 290 0.6830 198.07 684.6485 0.4823 139.867
5 350 0.6209 217.315 901.9635 0.4019 140.665
901.9635 687.6935

Payback Period:

Initial investment was £850000, thus the payback period is achieved in

5th year.
Net Present Value at 10%:

Total DCF-Initial Investment

( 901.9635 ×1000 ) −850000

¿ 9010635−850000

¿ 51963.5

Profitability Index:
Total DCF
Initial Investment

(901.9635 ×1000)
¿
850000

901963.5
¿
850000

¿ 1.0612

NPV at 20%:
( 687.6935 ×1000 ) −850000

¿ 687693.5−850000

¿−162306.5

IRR:
NVP1
IRR=r 1+ ×(r 2 −r 1)
NVP 1−NVP2
 51963.5
¿ 10+ ×(20−10)
51963.5−(−162306.5)

51963.5
¿ 10+ ×10
214270

¿ 10+0.2425 ×10

¿ 10+2.425

¿ 12.425

Answer 2

(i) (a)

Mortgage Housing
Year Interest Sales XY X2 Y2
rate (X) Index (Y)
1982 12 80 960 144 6400
1983 10 90 900 100 8100
1984 8 105 840 64 11025
1985 6 115 690 36 13225
1986 7 125 875 49 15625
1987 8 120 960 64 14400
1988 10 115 1150 100 13225
1989 12 100 1200 144 10000
1990 14 85 1190 196 7225
1991 13 70 910 169 4900
1992 11 80 880 121 6400
∑x=111 ∑y=1085 ∑xy=10555 ∑x2=1187 ∑y2=110525
Product moment coefficient correlation-
Sxy
r=
√ Sxx Syy

Where,
Sxy=∑ xy−(∑ x ∑ y ÷ n)

Sxx=∑ x 2−(∑ x ∑ x ÷ n)

Syy=∑ y 2−(∑ y ∑ y ÷ n)

Thus,
Sxy=10555−(111× 1085÷ 11)

¿ 10555−(120435 ÷11)

¿ 10555−10948.64

¿−393.64

Sxx=1187−(111 ×111÷ 11)

¿ 1187−( 12321÷ 11 )

¿ 1187−1120.09

¿ 66.90

Syy=110525−(1085 ×1085 ÷ 11)

¿ 110525−(1177225 ÷ 11)
¿ 110525−107020.45

¿ 3504.55

Therefore,
Sxy
r=
√ Sxx Syy
−393.64
r=
√(66.90 ×3504.55)

−393.64
r=
√ 234454.395

−393.64
r=
484.20

r =−0.81

(i) (a)

Mortgag Housing
Year e interest sales Rank 1 Rank 2 d d2
rate(x) index(y)
1982 12 80 8.5 2.5 6 36
1983 10 90 5.5 5 0.5 0.25
1984 8 105 3.5 7 3.5 12.25
1985 6 115 1 8.5 7.5 56.25
1986 7 125 2 11 9 81
1987 8 120 3.5 10 6.5 42.25
1988 10 115 5.5 8.5 3 9
1989 12 100 8.5 6 2.5 6.25
1990 14 85 11 4 7 49
 1991 13 70 10 1 9 81
1992 11 80 7 2.5 4.5 20.25
∑d2=393.5

Spearsman’s rank correlation coefficient-

6 ∑ d2
r =1− 2
n(n −1)

6 × 393.5
r =1−
11×(11 2−1)

2361
r =1−
11× 120

2361
r =1−
1320

r =1−1.79

r =−0.79

(ii) There is a strong negative correlation between the two variables-

mortgage interest rate and housing sales index.
Answer 3

Class- Frequ Mid- Cumulative fx X2 fx2

Interval ency point Frequency
(f) (x) (F)
30-31 3 30.5 3 91.5 930.25 2790.75
31-32 7 31.5 10 220.5 992.25 6945.75
32-33 33 32.5 43 1072.5 1056.25 34856.25
33-34 26 33.5 69 871 1122.25 29178.5
34-35 24 34.5 93 828 1190.25 28566
35-36 20 35.5 113 710 1260.25 25205
36-37 18 36.5 131 657 1332.25 23980.5
37-38 15 37.5 146 562.5 1406.25 21093.75
38-39 4 38.5 150 154 1482.25 5929
150 5167 178545.5

(a) (i)

Cumulative Frequency
160

140

120

100

80

60

40

20

0
30-31 31-32 32-33 33-34 34-35 35-36 36-37 37-38 38-39
(b) Standard Deviation:
∑ f x2
√ ∑f
−¿

178545.5
¿
√ 150
−¿

¿ √ 1190.31−¿

¿ √ 1190.31−1186.57

¿ √ 3.737

¿ 1.933

Q1(upper quartile)
n+1
4

150+1
4

37.75

Q1 class- (32-33)
i n+1
Q 1=l+ ( −F)
f 4

1 150+1
¿ 32+ ( −10)
33 4

1
¿ 32+ (37.75−10)
33

1
¿ 32+ ×27.75
33

¿ 32+0.84090

¿ 32.84090
Q3(lower quartile):
3(n+1)
4

3(150+1)
4

453
4

113.25

Q3 class- (36-37)
i 3 ( n+1 )
Q 3=l+ ( −113)
f 4

1 3 ( 150+1 )
¿ 36+ ( −113)
18 4

1 3 ( 151 )
¿ 36+ ( −113)
18 4

1
¿ 36+ ×(113.25−113)
18

1
¿ 36+ × 0.25
18

¿ 36+0.01388

¿ 36.01388

Importance of Standard Deviation in Business: According to www.bnp-chronicle.com “A

standard deviation is a measure of variability around a mean. If it is assumed that the
observations of a given characteristic, or value, cluster around the mean in a normal fashion,
the computed standard deviation has a very convenient property: 68.0 percent of the values
fall within plus or minus one standard deviation from the mean; 95.5 percent of the values fall
within plus or minus two standard deviations from the mean; and 99.7 percent of the values fall
within plus or minus three standard deviations from the mean.”

Answer 4
SUMMARY OUTPUT

Regression Statistics
Multiple R 0.7686403
R Square 0.5908079
Adjusted R Sq 0.4738959
Standard Erro 3.3186009
Observations 10

ANOVA
df SS MS F Significance F
Regression 2 111.30821 55.654107 5.05344 0.043827351
Residual 7 77.091786 11.013112
Total 9 188.4

CoefficientsStandard Error t Stat P-value Lower 95% Upper 95%Lower 95.0%Upper 95.0%
Intercept 16.423984 4.3510146 3.774748 0.00694 6.135469093 26.712498 6.1354691 26.712498
x1 0.9306723 0.2949431 3.1554297 0.01603 0.233242633 1.628102 0.2332426 1.628102
x2 -0.073868 0.1188885 -0.621322 0.554074 -0.35499472 0.2072585 -0.354995 0.2072585

RESIDUAL OUTPUT

Observation Predicted Y Residuals

1 16.586362 0.4136377
2 19.570371 3.4296293
3 21.65332 -1.65332
4 24.075996 -6.075996
5 18.373839 0.6261612
6 19.319154 2.6808465
7 20.279439 0.7205613
8 26.498673 1.5013273
9 24.194119 1.8058805

(i) Estimated regression equation: y=a+b1x1+b2x2

(ii) In regression with multiple independent variables, the cofficient tells us how much the
dependent variable is expected to increase when that independent variable increases by
one, holding all the othe independent variables constant.
(iii) Cofficeint x1 has estimated standard error of 0.29494314, t-statistic of 3.155429679 and P-
value of 0.016030158. Here, P<α, P(0.016030158)<α(0.05). It is therefore statistically
significant at significance level α=0.05 as Pis less than 0.05.
(iv) We test the
Ho: β1=0 and β2=0 versus
Ha: at least one β1 or β2 doesnot equal to 0.
 From the anova table F-test statistics is 5.05344047 with P-value of 0.043827351.
Since the P-value is less than α=0.05 we reject the null hypothesis.
We conclude that the parameters are statistically significant at the significance level of 5%.

Bibliography
http://www.bnp-chronicle.com/2011/03/importance-standard-
deviation.html