BRM Individual Assignment
Submitted By: Nilotpal Ray PGP-12-198 Division C PGDM: Operations 2012-2014
Table of Contents: Sl. No.
Topic
1.1
Management Research Problem Statement
1
1.2
Business Research Problem Statement
1
2.1
2
2.2
Methodology & Decoding the Business Problem The Research problem: Part I
2.3
The Research Problem: Part II
2
2.4
Final Objective
2
3.1
Collection of Data
3
4.1
The Multiple Discriminant Analysis Model
3
4.2
Interpretation of SPSS Output
9
4.3
Test of the MDA Function
15
5.1
The CAPM-SML Equation
16
6.1
Comparing CAPM-SML with MDA Function Final Conclusion & Recommendation
17
7.1
Page No.
2
17
BRM Assignment: Name: Nilotpal Ray (Div-C, Operations) Group: 38 Domain: Power (Central Sector) Organization Chosen: National Thermal Power Corporation (NTPC) Limited
1.1 Management Research Problem: To study the risk & return towards investments in NTPC Limited Stocks in the Indian Stock Market (NSE). 1.2 Business Research Problem: To calculate the predicted return over NTPC Stocks as per the Capital Asset Pricing Model (CAPM) Security Market Lien taking CNX Nifty 50 as a basis of market yield and comparing it with a Multiple Discriminant Analysis Model based on historic returns on NTPC Stocks to address the following research problems: 1) To design a MDA model to predict the nature of return on stocks of NTPC Ltd. which is dependent on changes in macroeconomic and industry scenario like Coal Prices, Exchange Rates, IIP Index (electricity), GDP at factor cost, BV/MV ratio etc. 2) Comparing the MDA model with the CAPM Model for predicting the nature of returns on NTPC stocks.
1|Page
2.1 Methodology & Decoding the Business Problem: National Thermal Power Corporation Limited (NTPC Ltd.) is a public sector undertaking (PSU) by the Government of India. The company is responsible for catering to the electricity distribution all across India through various grids of capacities upto 750 KV. Being in the sector of power & electricity generation, the following factors have been identified, which is primarily assumed to influence the risk & return of the NTPC Ltd. stocks in the National Stock Exchange. Industry Variables 1. IIP Index (Electricity): X1 2. WPI (Coal): X2 3. (Book Value/Market Value) Ratio: X3
Macroeconomic Variables 1. GDP at Factor Cost: X4 2. Exchange Rates (USD vs. INR): X5
2.2 The Research Problem: Part I: In the first part, the research tries to formulate a Multiple Discriminant Analysis Model as per the following structure: Return on NTPC Stocks (Y: +1,-1) = f (X1, X2, X3, X4, X5) Y = C1X1+C2X2+C3X3+C4X4+C5X5+ C6 +ei Where, C1, C2…C6= Constants, ei = Error With this MDA Model, an attempt has been made to forecast the nature future returns on the NTPC Stocks. 2.3 The Research Problem: Part II: In the second part of the research, an attempt has been made to calculate the historic returns of NTPC Stocks w.r.t the CNX Nifty 50 Portfolio as per the Capital Asset Pricing Model (CAPM) and thereby calculate the risk coefficient (β) of the Security Market Line: E(Rj) = Rf + β [E(Rm) –Rf]
E(Rj) = Expected return on NTPC Stocks Rf = Risk free return β = risk coefficient E(Rm) = Market return on portfolio
2.4 Final Objective: Combining Part I & Part II: The final objective of this research is to see how efficient is the MDA Model in predicting the movement of the stocks of NTPC Ltd. for certain changes in macroeconomic & industrial variables i.e. to get a comparative scenario between the interpretations of the MDA Model and the CAPM Security Market Line for predicting the returns on NTPC stock in the Indian Equity Market.
2|Page
3.1 Collection of Data: Data required for the research were collected from reliable secondary sources. The following provides a list of them: Data Monthly Quotes for NTPC Stocks Monthly Quotes for CNX Nifty 50 10 yr yield on GOI Bonds IIP Index (Electricity) WPI Coal Book Value/Market Value GDP at factor cost Exchange Rates
Source Yahoo Finance Website -doEconomic Times RBI Database on current statistics -doYahoo Finance Website RBI Database on current statistics -do-
Excel sheets having the Data are attached with the report for reference. 4.1 The Multiple Discriminant Analysis Model: The Multiple Discriminant Analysis Model goes per the following structure: Return on NTPC Stocks (Y: +1,-1) = f (X1, X2, X3, X4, X5) Y = C1X1+C2X2+C3X3+C4X4+C5X5+ C6 +ei Where, C1, C2‌C6= Constants, ei = Error With the obtained values of Y (+1,-1) we are trying to predict the nature of return for investments in NTPC Stocks: Y= +1: Returns are Positive Y= -1: Returns are Negative To test the model, we do a Trendline plot for each of the variables X1 to X5. From the Trendline plot we calculate data for next three periods and test our MDA Model with the actual returns. Next part of the research goes with formulating the SML as per the CAPM stipulations, taking into account the historic returns of NTPC stocks w.r.t CNX Nifty 50 returns. Again we do the same test for the next three periods to test the strength of SML Regression Equation. Finally we present a comparative picture of the two outcomes. Y= +1 will imply that investments in NTPC Stocks will yield a positive return. Y= -1 will imply that investments in NTPC Stocks will yield a negative return.
3|Page
IIP Electricity 180.0
y = -0.5629x + 153.54 R² = 0.9027
160.0
140.0
120.0
100.0 IIP Electricity Linear (IIP Electricity)
80.0
60.0
40.0
20.0
0.0
1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94
4|Page
WPI Coal 250.0
y = 0.0093x2 - 1.9647x + 214.01 R² = 0.9472 200.0
150.0 WPI Coal Poly. (WPI Coal) 100.0
50.0
0.0 1
5|Page
4
7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94
BV/MV Ratio 0.7
y = 3E-07x3 + 7E-05x2 - 0.0093x + 0.587 R² = 0.7868 0.6
0.5
0.4 BV/MV Ratio Poly. (BV/MV Ratio)
0.3
0.2
0.1
0 1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94
6|Page
GDP Factor Cost 300.00
y = -1.0333x + 257.3 R² = 0.982 250.00
200.00
GDP Factor Cost
150.00
Linear (GDP Factor Cost)
100.00
50.00
0.00 1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94
7|Page
Exchange Rates 60
y = -2E-05x3 + 0.0057x2 - 0.4191x + 53.969 R² = 0.5058
50
40
Exchange Rates
30
Poly. (Exchange Rates)
20
10
0 1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58 61 64 67 70 73 76 79 82 85 88 91 94
8|Page
Forecasted Values: X1
X2
X3
X4
X5
P1 (Oct’12)
153.54
214.01
0.587
257.30
53.97
P2 (Nov’12)
154.10
215.98
0.596
258.33
54.43
P3 (Dec’12)
154.66
217.97
0.606
259.37
54.96
X1
X2
X3
X4
X5
P1 (Oct’12)
150.24
210.3
0.537
256.67
54.78
P2 (Nov’12)
153.67
210.3
0.547
257.42
55.52
P3 (Dec’12)
155.18
210.3
0.580
260.71
55.38
X1
X2
X3
X4
X5
P1 (Oct’12)
2.20%
1.76%
9.31%
0.25%
-1.48%
P2 (Nov’12)
0.28%
2.70%
8.96%
0.35%
-1.96%
P3 (Dec’12)
-0.34%
3.65%
4.48%
-0.51%
-0.76%
Actual Values:
Percentage Deviation:
Hence we see that our trendline models can be accepted. So next we will form the MDA Function and apply these values on it. 4.2 Interpretation of the MDA Function from SPSS Output: 4.2.1 Analysis Case Processing Summary Unweighted Cases Valid Excluded
Percent 95
100.0
Missing or out-of-range group codes
0
.0
At least one missing discriminating variable
0
.0
Both missing or out-ofrange group codes and at least one missing discriminating variable
0
.0
Total Total
N
0
.0
95
100.0
This implies that all observations provided in the data is valid and there are no missing values. The minimum ratio of valid cases (i.e., observations) to independent variables for
9|Page
discriminant analysis is 5 to 1, with a preferred ratio of 20 to 1. In this analysis, there are 95 valid cases and 5 independent variables. The ratio of cases to independent variables is 19 to 1, which satisfies the near maximum requirement. 4.2.2 Group Statistics Y -1
Mean X1 X2 X3 X4 X5
1
X1 X2 X3 X4 X5
Total
X1 X2 X3 X4 X5
Std. Deviation
Valid N (listwise)
Unweighted Weighted Unweighted 123.05957 16.2064756189 44680851 47 89550 0 142.36595 30.5429866467 74468085 47 32500 0 .44219340 .087737812510 47 211095 647 202.17276 28.5169291979 59574468 47 13630 0 45.835720 3.93080367697 47 14061590 3489 129.90208 15.8998526633 33333333 48 52250 0 153.51458 31.0555370030 33333333 48 22440 0 .41797318 .085467481693 48 568094 121 213.12416 28.2152790080 66666666 48 75290 0 45.725302 3.50176989556 48 56283070 1275 126.51684 16.3328389516 21052631 95 17850 0 147.99894 31.1470072833 73684211 95 64390 0 .42995581 .086992026845 95 907263 304 207.70610 28.7455438653 52631579 95 79390 0 45.779930 3.70074457563 95 20657700 8813
Weighted 47.000 47.000 47.000 47.000 47.000 48.000 48.000 48.000 48.000 48.000 95.000 95.000 95.000 95.000 95.000
This gives the distribution of the two test groups with a prior division of 0.5 randomly. Studying the group means can reveal some discriminant loadings across the groups.
10 | P a g e
4.2.3 Tests of Equality of Group Means Wilks' Lambda
F
df1
df2
Sig.
X1
.956
4.315
1
93
.041
X2
.968
3.111
1
93
.081
X3
.980
1.858
1
93
.176
X4
.963
3.540
1
93
.063
X5
1.000
.021
1
93
.885
This table gives the results of hypothesis testing that the two group means are equal and there is no discriminancy between the two groups. The table applies the test to the contribution of each IV. Here we see that X1, X2, X4 rejects the null hypothesis at 10% level of significance. But, X3 & X5 fails to reject the null hypothesis. This discrepancy may be due to the fact that we are handling macroeconomic data and they are highly influenced by a lot of social and behavioral aspects. Hence, an absolute fit of macroeconomic variable in a statistical model is very rare. 4.2.4 Pooled Within-Groups Matrices X1 Correlation
X2
X1
1.000
.924
X2
.924
1.000
X3
.100
.211
X4
.952
.940
X5
.589
.702
X3 .100
X4
X5
.952
.589
.211
.940
.702
1.000
-.032
.416
-.032
1.000
.582
.416
.582
1.000
This table gives the correlation coefficients between the IV’s. We see X1 is closely related to X2, X4 and X5. 4.2.5 Test Results Box's M F
8.240 Approx.
.518
df1
15
df2
34789.162
Sig.
.933
Tests null hypothesis of equal population covariance matrices.
Homogeneity of variances of the independent variables across the groups of the dependent variable is tested by Box's M. The null hypothesis is that the group variancecovariance matrices are equal. If we fail to reject the null hypothesis and conclude that the variances between groups are equal, it would imply that there is strong homogeneity, across groups. This, in turn, implies that the independent variables have failed to discriminate the groups formed by the dependent variable. Put differently, MDA is unsuccessful. In our case though it may seem that the null hypothesis is failed to be rejected, this discrepancy may be due to the fact that we are handling
11 | P a g e
macroeconomic data and they are highly influenced by a lot of social and behavioral aspects. Hence, an absolute fit of macroeconomic variable in a statistical model is very rare. Hence, we move ahead with our MDA and finally we would like to check what percentage of the total observations is correctly classified by the MDA Function. 4.2.6 Eigenvalues
Function 1
Eigenvalue
% of Variance
.122(a)
Canonical Correlation
Cumulative %
100.0
100.0
.330
a. First 1 canonical discriminant functions were used in the analysis.
This table explains how efficient is the MDA Function to discriminate the DV as a function of the IV’s. Here the value of (Canonical Correlation) 2 goes as 11%. So literally only 11% of the discrimination can be explained by the discriminant function. But, finally we would like to check what percentage of the total observations is correctly classified by the MDA Function. 4.2.7 Wilks' Lambda
Test of Function(s) 1
Wilks' Lambda .891
Chi-square 10.428
df
Sig. 5
.064
Wilk's Lambda captures the within group variability, as compared to the total variability. Total variability can be of two types: within group and between groups. Wilk's Lambda is represented as WSS/TSS = Within Group Sum of Squares / Total Sum of Squares. For MDA to make sense within group variability should be minimized and between groups variability should be high. Hence, a low Wilk's Lambda value is preferred. The null hyp is this case is high within group variability, i.e., high WSS. If the null hyp is strongly rejected, we conclude that the indep variables have been able to successfully discriminate the two groups of the dep vairable (i.e., satisfied and dissatisfied customers). However, this is an overall study and does not give any idea about the discriminatory power of each and every independent variable. Here, though we are able to reject the null hypothesis at a significance level of 10%, but we have a high Wilk’s Lambda. This may be due to the fact that we are dealing with macroeconomic variables which have a lot of social and behavioral aspects linked to it. Finally, we are interested in knowing what percentage of the total observations is explained by the MDA Function.
12 | P a g e
4.2.8 Structure Matrix Function 1 X1
-.616
X4
-.558
X2
-.523
X3
.404
X5
.043
Pooled within-groups correlations between discriminating variables and standardized canonical discriminant functions .Variables ordered by absolute size of correlation within function.
The structure matrix table shows the correlations of each variable with each discriminate function. These Pearson coefficients are structure coefficients or discriminant loadings. They serve like factor loadings in factor analysis. By identifying the largest loadings for each discriminate function the researcher gains insight into how to name each function. Here, we see that X1, X2, X3, and X4 are substantially loaded in the Discriminant Function. 4.2.9 Canonical Discriminant Function Coefficients Function 1 X1
-.105
X2
-.069
X3
10.110
X4
.097
X5
.153
(Constant)
-7.967
Unstandardized coefficients
This defines the Multiple Discriminant Function:
Y = 0.153 (X5) + 0.097 (X4) + 10.110 (X3) - 0.069 (X2) - 0.105 (X1) -7.967
The discriminant function coefficients b or standardized form beta both indicate the partial contribution of each variable to the discriminate function controlling for all other variables in the equation. They can be used to assess each IV’s unique contribution to the discriminate function and therefore provide information on the relative importance of each variable. If there are any dummy variables, as in regression, individual beta weights cannot be used and dummy variables must be assessed as a group through hierarchical DA running the analysis, first without the dummy variables then with them.
13 | P a g e
The difference in squared canonical correlation indicates the explanatory effect of the set of dummy variables. 4.2.10 Functions at Group Centroids Function Y -1
1 .349
1
-.342
Unstandardized canonical discriminant functions evaluated at group means
The group centroids table gives us the basis of discriminating Y = +1 from Y = -1. By substituting the values of IV’s X1 to X5, we will get a value for Y. Closer the value of Y to a group centroid, closer is the classification to that group. 4.2.11 Classification Results (a) Predicted Group Membership
Original
Count
Y -1 1
%
-1
Total
1 29
-1 18
47
18
30
48
-1
61.7
38.3
100.0
1
37.5
62.5
100.0
a 62.1% of original grouped cases correctly classified.
The classification table, also called a ‘confusion table’ gives us an estimate of the final power of the MDA Function. Here we see that we are able to classify 62.1% of the original grouped cases. Considering the variability of macroeconomic data (social, political, behavioral etc.) this is a good hit percentage. The independent variables could be characterized as useful predictors of membership in the groups defined by the dependent variable if the cross-validated classification accuracy rate was significantly higher than the accuracy attainable by chance alone, called the Proportional Chance Criterion. Operationally, the classification achieved by MDA, including the cross-validated classification accuracy rate should be 25% or more high than the proportional by chance accuracy rate. The proportional by chance accuracy rate was computed by squaring and summing the proportion of cases in each group from the table of prior probabilities for groups (0.5² + 0.5² = 0.5). The criteria (thumb-rule) for a useful model is 25% greater than the by chance accuracy rate (1.25 x 50% = 62.5%). Here we are just near to that stipulated value. Hence, we can conclude that our MDA Function has been able to classify the dataset satisfactorily.
14 | P a g e
4.3 Test of the MDA Function: We have devised the MDA Function as follows: Y = 0.153 (X5) + 0.097 (X4) + 10.110 (X3) - 0.069 (X2) - 0.105 (X1) -7.967 The Group Centroids are: Y
Function 1 .349 -.342
-1 1
Testing Independent Variables are as follows: X1
X2
X3
X4
X5
P1 (Oct’12)
153.54
214.01
0.587
257.30
53.97
P2 (Nov’12)
154.10
215.98
0.596
258.33
54.43
P3 (Dec’12)
154.66
217.97
0.606
259.37
54.96
Test Results goes as follows: X1
X2
X3
X4
X5
Y
Classification
P1 (Oct’12)
153.54
214.01
0.587
257.30
53.97
0.294
-1
P2 (Nov’12)
154.10
215.98
0.596
258.33
54.43
0.361
-1
P3 (Dec’12)
154.66
217.97
0.606
259.37
54.96
0.448
-1
Interpretation of results goes as follows: Y
Classification
Interpretation
Actual Returns
P1 (Oct’12)
0.294
-1
Returns are negative
-1.18%
Pass
P2 (Nov’12)
0.361
-1
Returns are negative
0.37%
Fail
P3 (Dec’12)
0.448
-1
Returns are negative
-3.55%
Pass
Hence, our model gives correct results in 66.67% of the cases. 15 | P a g e
Verdict
5.1 The CAPM-SML (Security Market Line) Equation: The CAPM Model derives the equation of the Security Market Line as follows: E(Rj) = Rf + β [E(Rm) –Rf]
E(Rj) = Expected return on Shoppers Stop Stocks Rf = Risk free return β = risk coefficient Themsteps of calculating and Growth Rate from the CAPM Model are E(R ) = Market return on WACC portfolio enumerated as below: Step 1: Calculating Historic Returns of Shopper’s Stop: This was done by taking historic data (monthly) from Yahoo Finance and thereby applying the formula (see attached excel sheet): % Return = (Present Month closing value of stock) – (Previous Month closing value of stock) Previous Month closing value of Stock
Step 2: Calculating Historic Returns of a portfolio: This was done by taking the CNX Nifty 50 as a indicator of the Market. The historic returns were calculated upto year 2002 (see attached excel sheet): % Return = (Present Month closing value of Nifty 50) – (Prev. Month closing value of Nifty 50) Previous Month closing value of Nifty 50
Step3: Calculating β (Risk Coefficient):β, the risk coefficient was calculated by plotting the Market Returns on X axis and Stock Returns on Y Axis and then taking the slope of the best fit straight line:
Y (Security) y = -0.7491x + 0.0061
0.4 0.3 0.2 0.1
Y (Security) Linear (Y (Security))
0 -0.3
-0.2
-0.1
-0.1 -0.2 -0.3
16 | P a g e
0
0.1
0.2
0.3
0.4
Step 4: Calculating Expected Rate of Return on Equity: Risk free rate (Yeild on 10yr GOI Bond) Beta Return on Market Equity Risk Premium = (Return on Market - Risk Free Rate)
8.18% As per Economic Times 13.12.2012 -0.74907 20.59% 12.41%
Expected Return on Equity
-1.11%
6.1 Comparing CAPM-SML with MDA Function: The outcomes from the MDA Function and from CAPM Equation and a comparative picture between them are enumerated as below: SML Returns
Verdict
Returns are negative
-1.11%
Pass
-1
Returns are negative
1.11%
Pass
-1
Returns are negative
-1.11%
Pass
MDA(Y)
Classification
MDA Interpretation
0.294
-1
0.361
0.448
P1 (Oct’12)
P2 (Nov’12)
P3 (Dec’12) 7.1 Final Conclusion:
Hence we see that our MDA Model is successfully able to classify the nature of returns from the NTPC Stocks. The securities of NTPC have historically generated negative returns as we see from the SML Equation. For this reason, it has a negative beta (β) and inspite of being a low risk security, no shareholder will like to keep NTPC Stocks as a part of his/her portfolio. Recommendations for Management: I. II. III.
Deleverage the capital structure of the firm with more equity and less debt. Invest in safe havens and generate higher returns for shareholders. Devise a proper dividend distribution policy to generate shareholder interest.
The hit ratio of our MDA Function though stands at 62.1%, it is able to predict the nature of returns from NTPC stocks based on some very volatile macroeconomic factors such as IIP Electricity, WPI Coal, BV/MV Ratio, GDP at Factor Cost and Exchange Rates. Since macroeconomic factors are influenced heavily by social, political and behavioral aspects, it is very difficult to fit them in a rigid statistical model. Keeping that in view, a 62.1% hit ratio is decent enough. ---17 | P a g e