Data Analysis and Decision Modelling Oz Assignments
Delivery in day(s): 4
The Capital Asset Pricing Model helps us to determine the risk associated with the project work. It further helps us to understand the return that is expected from the project. This model helps us to work out the theoretical rate of return of an asset. This model takes into consideration the market risk represented by Beta in the financial industry, the risk free rate which is theoretical in nature along with the expected rate of return prevailing in the market. This model is based on the presumption that in a project the risk and return are directly proportional to each other.
The CAPM model works on the following assumptions:
1. The investors prefer projects that will maximise their wealth and give them higher return. Thus, they prefer project with higher return and standard deviations.
2. The investor can borrow or lend any amount at risk free rate.
3. No restriction exists in terms of short sales of financial asset.
4. The expectations of the investors from the market are same.
5. There are no transaction costs.
6. There are no taxes.
7. The financial assets number is provided and the same is fixed.
8. The financial assets can be sold in parts and the same can be sold at any time in the market. (Wagner, 2014)
In the given case, Henkel is a German based company. The company is specialised in consumer goods and its headquarters is established in Düsseldorf, Germany. The company is well known internationally because of its brands which includes Loctite, Persil, and Fa. Now, thus the company is based in Germany, in order to calculate the risk free rate of return, the EuroDenominated Bonds yields will be taken into consideration considering the best period yield that is to be selected.
As per the latest credit rating provided by Standard and Poor in long run, the company carries a rating of “A”. Further, being the company has been operating under the going concern principle which states that the management is required to consider the bond with longer maturity duration to consider the risk free rate of return. Thus considering these points, taking the current credit rating of the company into consideration with maximum maturity for the bond, the risk free rate comes close to 5.01.(Henkel, 2009).
 Laundry  Cosmetics  Adhesives  










 USD  USD  GBP  EUR  USD  USD  USD  USD  USD 
 CLXN  PGN  ULVRLN  BEIFF  CLN  ELN  MMMN  AVYN  FULN 










Beta  0.03  0.03  1.07  0.01  0.03  0.02  0.03  0.01  0.02 




















Debt to equity ratio for Henkel AG  0.31 


















bu= be(1+D/E)  0.04  0.04  1.41  0.01  0.04  0.02  0.04  0.02  0.02 
Working is shown in excel attached… (Please refer)
For computing, the rate realised with most appropriate maturity for the debt fund shall be the intermediate interest cost of the debt funds earned by invested of the fund before maturity shall be reinvested at the current rate prevailing in the market (Dhaliwal, et. al., 2011). This, in turn, helps in computing the total amount earned at the time of maturity of investments. Current yield is the least demanding sort of respect register and is frequently the yield most speculators concentrate on in assessing the allure of a particular security or security subsidize. The most appropriate treasury rate which is taken by the Henkel AG would be at A as described by the rating of standards and poor’s credit analysis. The rate at particular ratings would be 3.81 % with 5 years maturing period of the debt bond.
Current yield relates the yearly coupon intrigue got from a cling to its market value, along these lines giving a sensible gauge of the wage created from the security at a particular point in time. While selecting the treasury rate it is to keep in consideration that nighter risk get enhanced with the decrease in ratings given by various credit agencies nor the return factor on the debt fund becomes too low. This will help in taking a moderate risk return investment fund. It creates a contract that requires the company to pay the interest cost to the lender of the fund. The par value of the debt bond indicates the face value of the bond that is value stated on the bond paper. The current yield provides information regarding the amount of interest cost that a debt will incur in the particular span of time. Most probably this rate is for the annual period for one year time. Further, it does not provide and accurate measure of debt’s total expected return (Krishnamurthy & VissingJorgensen, 2012).
On the yields &rating tab of the spreadsheet, there is differentiation given by the Standards and poor’s on the various risk and return parameters. In most of the cases rating provided by the rating agency is A expect in scenarios wherein may 09 and Dec 03 rating shows little fluctuation and ratings of the debt fund has been improved with rate reflected by A and A+ for the respective period. The respective returns on the basis of above ratings are
Rating  1Y  2Y  5Y  10Y  20Y 
AAA  0.56  1.29  3.00  4.51  5.25 
AA  0.97  1.63  3.47  4.83  5.60 
A+  0.99  1.65  3.49  4.85  5.62 
A  1.05  1.71  3.61  4.97  5.71 
A–  1.39  1.97  3.81  5.23  5.97 
To interpolate between the nearest rating company should take the difference in the cases of two scenarios with A + and A or A or A (Henkel, 2009).
A & A+  0.40  0.32  0.31  0.38  0.35 
A & A  0.34  0.26  0.19  0.25  0.26 
There is not much difference in the rate chart with the change in ratings of the agencies. The company should opt for medium range debt funds. In the present instance, this is 5 years as there is the least variance reflected in that particular range of time. A rising term structure indicates that the market expects the yield on a 5year debt fund is approximately same as of twoyear debt fund. Investors do expect to earn if the bond is held till maturity. Companies often issue debt with an option to be viewed as debt plus an options trade. Duration is the measure which quantifies the sensitivity of debt unit price with the change in respective interest rate or change in ratings provided by various rating agencies. But flat term expectations reflect an expectation that future interest rates will be unchanged at present interest rates. It considers just coupon intrigue and does not represent capital increases or misfortunes that could be acknowledged by the financial specialist, nor the impacts of aggravating accordingly of reinvested premium.
While seeking the financial data of 2009 it has been observed that Henkel paid tax at a marginal rate of 31 %. The same has been applied to take tax advantage of debt instruments. The cost of availing the debt funds in form of interest cost shall be allowed as a tax deduction the financial statement of the company. The most relevant decision while selecting the debt funds depends upon interest rate and tax rate applicable to the organisation. Interest decision can be taken effectively framing out debt policy. This model considers that the investment decision and interest decision of a firm are interrelated. An organisation while selecting its debt policy should select the appropriate debt capital structure while seeking in fact investment policy for valuation of the overall cost of capital.
For finding out the cost of capital weights or proportion of respective wellsprings of capital needed to be calculated. For calculating such data, primarily total capital needed to be calculating by adding up total equity and debt capital. In this particular case of Henkel total of debt and debt equivalent is73,300.4 & total of equity capital is 358,156.8 with the total capital structure of 431,457.2. While calculating the total data of debt and equity respectively percentage of debt in total capital is 17 % and percentage of equity in total share capital is 83 %. To find overall cost of capital cost of equity is to be multiplied with the respective proportion of equity in total capital and cost of debt is to be multiplied with the respective proportion of debt in total capital. After that overall cost of capital is calculated by adding up both the figures.
Formulae for calculation of weighted average cost of capital
(Brusov, et. al., 2011)
Here E refers to the market value of total equity which is 358,156.8 and D here above refers to the market value of total debt which is 73,300.4. V here refers to the total present capital structure of the company. To find respective weights of the capital market value of equity is divided by overall capital and same with debt structure of the company. After calculation of particular weights of both debt and equity, these are multiplied with their rates describing the cost of capital and cost of debt. There is an additional advantage of tax benefit in case of debt structure. Seeking wider approach an organization funds its advantages by obligation or with equity value. Weighted average cost of capital is expenses incurred for availing of these sorts of financing. While adapting a weighted proportion of both debt and equity as wellsprings of availing funds along these lines organisation can find out the amount of premium an organization owes, every dollar it accounts.
Also, more elevated amounts of profits increment the levels of use. It shows up that supervisor in partnerships with more elevated amounts of use separate the profit strategies from subsidizing approaches. After that correlation is created between the two factors such as debt and equity funds out of the total capital structure of the company. Risk attained by the business organisation cannot be reduced to a certain extent just because there is a situation where two positively correlated units in the capital structure are accumulated or combined. However choosing the same approach with two negatively correlated assets will result in a positive outcome, this will result in a subsequent reduction of the business risks. Though in the real term the situation of both positive and negative correlation is very rare and it is difficult to witness such cases. Therefore, they pay profits without assessing the level of use. It is most likely on the grounds that there is compacted rivalry among partnerships which appropriate profits to draw in financial specialists regardless of their expanded obligation. While calculating the Beta for an organisation this will help in measurement of systematic risk in an organisation. Relevancy of capital structure is not required for the ascertainment of the Beta factor. It is a barometer which depicts the change in the capital structure of the company due to respective change in the external market index conditions. CAPM reduces the risk of the business organisation but it is very difficult to find out actual risk premium in unitary terms which might deviate with the other related calculations. Market efficiency and high returns desire from the investors are the two basic assumptions of CAPM model. This further describes the share of different wellsprings of capital among the total availed capital of the organisation.
The above assignment related to the financial analysis of the company Henkel AG group. In the above mentioned report analyst evaluated the company’s financial data and compared it to market index. In the later part of the report there is discussion about the treasury rate at which it is feasible to value the current cost of the debt. The risk in the selected portfolio can be reduced to a certain extent by accumulating assets or seeking the total capital structure of the organisation. The investor of the debt portfolio or equity portfolio has to seek the above CAPM model just to go for the rational approach; this will ultimately result in maximum outcome with lowest perceived risk taken in the portfolio.
Bali, T.G., Engle, R.F. and Tang, Y., 2016, “Dynamic conditional beta is alive and well in the cross section of daily stock returns”, Management Science..
Barberis, N., Greenwood, R., Jin, L. and Shleifer, A., 2015, “XCAPM: An extrapolative capital asset pricing model”, Journal of Financial Economics.
Brusov, P., Filatova, T., Orehova, N. and Brusova, N., 2011, “Weighted average cost of capital in the theory of Modigliani–Miller, modified for a finite lifetime company”, Applied Financial Economics, 21(11).
Dempsey, M., 2013, “The capital asset pricing model (CAPM): the history of a failed revolutionary idea in finance?”, Abacus, 49(S1).
Dhaliwal, D., Hogan, C., Trezevant, R. &Wilkins, M., 2011, “Internal control disclosures, monitoring, and the cost of debt”, The Accounting Review, 86(4).
Gurufocus, 2017, “Henkel AG & Co KGaA (OTCPK:HENOY) Trailing Dividend Yield”. Available at http://www.gurufocus.com/term/yield/HENOY/Dividend%2BYield/Henkel%2BAG%2B%2526%2BCo%2BKGaA
Henkel, 2009, “Credit Ratings”. Available athttp://www.henkel.com/investorsandanalysts/creditorrelations/creditratings
Krishnamurthy, A. & VissingJorgensen, A., 2012, “The aggregate demand for treasury debt”, Journal of Political Economy, 120(2).