...an asset’s exposure (or vulnerability) to systematic risk and evaluates the degree to which an asset’s return is correlated with the market return. Beta is a key parameter in the Capital Asset Pricing Model (CAPM) which is most commonly used asset pricing model for the estimation of required rate of returns on a share (i.e. cost of equity=rE). This assignment consists of two parts. Part I directs you to work with real financial data in Excel and aims to take you through the basic exercise of estimating a company beta and computing statistics which can be evaluated as part of investment decision-making process. Part II requires you to write a report based on interpretations of the outputs obtained from the first part. In this manual, you will also find out how to download historical share price, market index data and interest rates from the web portal. ➢ Details of Questions PART 1 [30 marks] To successfully complete PART 1, please read each section carefully. You must read “Format and printing requirement” before you produce any outputs. I also encourage you to read the entire manual before you choose your companies for analysis. If you have read “Format and printing requirement”, then proceed. Provide your answers (or outputs) for the following questions. All the calculations and computations are required to be produced in Excel (This manual has been designed for you to do this assignment using...
Words: 3262 - Pages: 14
...Md.Reza-E- Mostafa ID # 2011-3-90-010 Md. Rakibul Islam ID # 2011-1-90-011 Sabiqun Nahar ID # 2011-1-90-005 Date of Submission: 11th December, 2012 LETTER OF TRANSMITTAL December 11, 2012 Md. Rafiqul Matin Course Instructor Investment Theory East West University Dear sir, The term paper writing in the assigned topic by you is a part if the course, Investment Theory to judge our understanding. In response to that our group prepared this report. As you will see this report tries to find out the term paper on the of the analysis of Investment Theory & Market Investment you asked us to conduct. Our study reveals some specific trends of how DSE basically operates it’s functions. Based on annual report and website of DSE, we have tried to find out the security trade functionality of 10 individual stocks regarding different category & sectors. According to it’s process, It will help you to understand the prevailing perception of how the securities are traded considering all factors & what steps DSE usually takes to maintain the whole trade functions. Thanks for giving us the opportunity to work on this report. It’s been a real education for us. If you have any question to ask us, please contact anyone of us. Sincerely yours Name ID Number Signature A. K. M. Mamunur Rashid 2011-2-90-005 ________________ Md.Reza- E- Mostafa 2011-3-90-010 ________________ Md...
Words: 3398 - Pages: 14
...SCIENCE, BHUBANESWAR. | CONTENTS Page No. * OBJECTIVES 3 * COMPANY PROFILES 4 i. Mahindra Finance ii. Unitech Ltd. * PROJECT METHODOLOGY AND ANALYSIS 5 * Objectives of the project * Scope of the project * Concepts * Excel Analysis * Computational Aspects * INTERPRETATION AND CONCLUSION 11 * REFERENCES 11 OBJECTIVES * To select the stocks of two companies from different sectors to invest. * To determine the beta value for each of the stocks for last financial year on the basis of National Stock Exchange (NSE) and interpretation of beta. * To determine a minimum risk portfolio by using the two stocks. Company Profiles Mahindra Finance Mahindra & Mahindra Financial Services Limited is one of the leading non-banking finance companies in India. It is among the top 500 Indian companies by market capitalization. For the fiscal ended 31 March 2011, Mahindra Finance recorded a standalone net profit of 463.10 Crore against 342.70 Crore in FY10-11. In the same period, Mahindra Finance’s consolidated net profit stood at 493.66 Crore compared to 356.09 Crore. Unitech Ltd. Established in 1972, Unitech is today a leading real estate developer in India. Known for...
Words: 743 - Pages: 3
...Date: July 12th, 2011 Student Name: Eleanor Luu Instruction Name: Professor Stephen Itnyre Course/Class: ACC115003VA016-16-001 Assignment 1: Stock Analysis This Stock Analysis assignment shows six (6) differences Stock companies: APPL (NASDAQ), NIKE (NYSE), SAP (NYSE), MSFT (NYSE), INTU (NASDAQ), and SGPYY (LSE) in daily historical price from 3/1/11 through 5/31/11. 1 - The summary various accounting systems that each firm provides: ➢ Peachtree is a Sage product that and use by SGPYY, MSFT, and SAP AG ➢ QuickBook is an Intuit product that use by APPL, NIKE, and INTU QuickBooks holds 89 percent of the small business accounting software market in the United States. However, Sage’s small business product, Peachtree, is another popular product that has always given QuickBooks a run for its money. They both have their strengths and weaknesses. Here are the key comparisons from a general standpoint. ❖ Peachtree • It offers a tight integration between desktop accounting and online store. • It’s the best choice for Web-minded small businesses. • Stronger inventory management • Job costing capabilities • Over 140 customizable reports • Work flow management • Scalability/multi-user performance • Through GAP checks for duplicates and suspicious activity • Can accommodate up to 40 users, 10 more than QB • Easy to navigate. • Installation and Configuration...
Words: 953 - Pages: 4
...The Measure of Risk - Stock Beta Stock Beta Calculator http://spreadsheetml.com/finance/stockbetacalculatorspreadsheet.shtml Copyright (c) 2008-2009, ConnectCode Pte Ltd. All Rights Reserved. ConnectCode accepts no responsibility for any adverse affect that may result from undertaking our training. No statements in this document should be construed or thought to represent investment advice of any type since the sole purpose of the explanation is to illustrate the technique. Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. All other product names are trademarks, registered trademarks, or service marks of their respective owners Table of Contents 1. 2. Stock Beta .............................................................................................................................. 1-1 Stock Beta Calculator ........................................................................................................... 2-2 2.1 Inputs ......................................................................................................................... 2-2 2.2 Outputs ....................................................................................................................... 2-2 Advance Stock Beta Calculator ........................................................................................... 3-3 Customizing the Stock Beta Calculator .............................................................................. 4-4...
Words: 2050 - Pages: 9
...be highlighted. These statements are the income statement, balance sheet, and the statement of cash flow. These statements are attached in Microsoft excel. Ratio Analysis Following this, a number of ratios were computed in order to show specific trends in 3M Company. This included ratios that measured short term solvency or liquidity, long-term solvency, asset management or turnover ratios, and profitability. Along with the attached ratios, a table below will illustrate the market value ratios. Sony Market Value Ratios | 2012 | 2013 | 2014 | Price to Earnings | 14.71 | 20.87 | 21.93 | Peg ratio | 2.47 | 3.24 | 1.91 | Price/Sales | 3.29 | 2.53 | 3.16 | Market/book | 7.20 | 4.12 | 5.27 | Tobin's Q | 1.81 | 2.75 | 3.37 | Enterprise Value - EBTDA | 12.13 | 9.74 | 11.79 | General Electric Market Value Ratios | 2012 | 2013 | 2014 | Price to Earnings | 15.96 | 16.82 | 21.38 | Peg ratio | 7.63 | 1.56 | 3.74 | Price/Sales | 1.46 | 1.62 | 1.85 | Market/book | 1.71 | 1.89 | 1.99 | Tobin's Q | .34 | .41 | .35 | Enterprise Value - EBTDA | 11.94 | 11.64 | 13.34 | According to this evaluation of these ratios it can be stated that 3M Company is fairly valued. When compared to General Electric which is a major competitor their ratios are close in proximity. However, in terms of the Price to sales and Market to book ratios, 3M Company has the higher ratios. All the other ratios are very close in proximity which indicates that 3M Company is fairly valued...
Words: 1039 - Pages: 5
...headings and all the units (% , million etc.) clearly indicated. A Word document should be submitted on the course website by one team member before the deadline: All calculations and estimates using Excel should be in formatted tables and inserted directly into the Word document. Note: From the Excel output, only include the important estimates and relevant statistics such as t-stats and standard errors. GRADIN RUBRIC Grade Item | Points out of 100 | Presentation including grammar and style, tables, follow instructions) | 20 | Q1 | 10 | Q2 | 10 | Q3 | 10 | Q4 | 20 | Q6 | 30 | Learning Objectives The case provides an opportunity to understand how capital market data and asset pricing model(s) can be used to estimate the cost of capital (or the required rate of return) for real investments. You do NOT have to write a formal report, but you should provide concise answers with all the assumptions stated clearly for the questions given below. * Estimate stock returns from prices by adjusting for stock splits and stock dividends * Use the EXCEL to estimate stock betas from regression models using the appropriate market returns * Estimate asset betas by unlevering equity betas * Estimate the appropriate inputs for the CAPM (Risk free rate, market risk premium, and beta) * Use of pure play companies to estimate cost of capital for firms * Learn how to use...
Words: 859 - Pages: 4
...economic value of the stock and the Weighted Average Cost of Capital (WACC) for capital budgeting. In recent years, the CAPM has been attacked as an incomplete model for explaining market pricing behavior, but academics and practitioners cannot agree on a good replacement. And so the CAPM remains an important model in practical investment and financial management decision making. Calculating Beta: The most important component in calculating the required return to shareholder (from the CAPM) is the company’s beta. The CAPM can be succinctly stated as: k s k RF k M k RF s k RF Market Risk Premium s [1] The original model was conceived of theoretically, and was expected to be forward looking. Careful reading of Sharpe’s original work show that the market assesses systematic risk looking at expected future covariance of the company’s returns with that of the overall market. It is assumed that these covariances are unbiased and efficient estimates of the observed relationships ex post facto. Traditionally the CAPM relationship is estimated using simple regression on historical outcomes, where ks is the y variable, and kM-kRF (or the market risk premium) is the only x variable. Care must be taken that the returns plugged into the regression are all for the same period. Calculated stock returns should be annualized if the risk-free rate is an annual rate. The market risk premium is merely the difference between the return to the market portfolio and...
Words: 3675 - Pages: 15
...investment practices of eight legendary investors to the theoretical methods considered in this class. The second is to give you an opportunity to work with real data, albeit over an unrealistically short time horizon. The third and most important goal is that the investor and stock selection process be interesting and fun. Please do not let it turn into a huge time commitment. The assignment is as follows. Part I 1. Go to the Project folder on our website. There you will find links to the biographies of 8 legendary investors. The chapters describing the investors come from John Train's books, The Money Masters and The New Money Masters. Browse the investor profiles and select an investor that interests you. 2. Read the chapter that describes your chosen investor. 3. Choose one stock that reflects your investor's stock selection style. Links to helpful stock-screening websites can be found in the Webliography on eCollege or External Links on Blackboard. Your stock's price must be reported on the Yahoo! Finance web site (link in Webliography or External Links) and have weekly price data going back to July 5, 2011. 4. Go to finance.yahoo.com and download the weekly price series for your stock. To do this, enter your stock's ticker symbol in the ‘Get Quotes’ box on the Yahoo! Finance home page and click on Go. Click on the link to Historical Prices on the left-hand side of the page, as shown in the graphic below. Select July 5, 2011 as your Start Date and...
Words: 679 - Pages: 3
...Task 1: Assessing loan options for Coca Cola. The company needs to finance $8,000,000 for a new factory in Mexico. The funds will be obtained through a commercial loan and by issuing corporate bonds. Here is some of the information regarding the APRs offered by two well-known commercial banks. Bank APR Number of Times Compounded National First Prime Rate: 3.25% + 7.75%= 11% Monthly Regions Best 12.57% Semiannually 1. Assuming that Coca Cola is considering loans from National First and Regions Best, what are the EARs for these two banks? (15 pts) Bank EAR National First 11.57% Regions Best 12.97% See Excel attachment for calculations 2. Based on your calculations above, which of the two banks would you recommend and why? Explain your rationale. (15 pts) Based on the calculations above I recommend Coca Cola obtain a loan from National First Bank. Although the decision can be based on the APR, the EAR is the most accurate interest rate because it takes into account compounding and expresses the true interest paid on the loan. The EAR for Regions Best Bank is 12.97% while National First Bank has an EAR of 11.57%, because National First Bank has the lower interest rate of the two it is the more logical choice since their loan option will charge less interest for the life of the loan. 3. Coca Cola has decided to take a $6,980,000 loan being offered by Regions Best at 8.4% APR for 7 years. What is the annual payment amount on this loan? Show your calculations...
Words: 1345 - Pages: 6
...Introduction: Generally shares are issued in the primary market and new issued shares are traded in the secondary market. BSE and NSE are two renowned stock exchanges which plays an important role in Indian stock market. Most of the big companies in India are listed in these stock exchanges. The companies are enlisted with these stock exchanges as per rules and regulation specified by particular stock exchanges (Greer and Kolbe, 2008). In Indian context the company distributes the shares in lieu of capital with the help of these stock exchanges. It is an essential pillar of private sector companies. Mallin and Ow–Yong (2009) commented that capital formation is the main function of stock exchanges. Beside the main function stock exchanges in India do lots of work. It provides a place where all the securities are traded. Security market provides a linkage between saving sector and corporate sector. Stock market generally provides the all the information about price and trading of the securities altogether. The author selects three big companies i.e. Reliance industries ltd, ONGC, Bharti Airtel Ltd, to compare as required in the assignment. In this report the author collects financial and statistical data of these companies and analyse them to make reasonable comments on the above companies (CACEIS, 2010). 2.0. Calculations of returns of stocks: Calculation of daily and monthly return of different companies shows in the Excel Sheet 1,2 3,4,5 and 6 [Available from:...
Words: 2176 - Pages: 9
...appendix in your textbook, Appendix C: Using Excel to Conduct Analysis, which may help you with running regressions in Microsoft Excel. You may also wish to use a basic statistics text for guidance if needed. I have also provided you with a table with the t distribution. If you have an older version of EXCEL and have not previously loaded the Analysis ToolPak, go to TOOLS, ADD-INS, Analysis Tool Pak. This will load the regression software that you will need. Then go to TOOLS, DATA ANALYSIS, Regression. Now you are ready to run regressions in EXCEL. Alternatively, if you have the most recent version of EXCEL, go to HELP and enter Load the Analysis ToolPak. Excel HELP will explain how to load the software. Once you have loaded the software you find it in DATA, DATA ANALYSIS, Regression. Now you are ready to run regressions in EXCEL 1. Measuring Systematic Risk: Beta Coefficients The management of a publicly traded firm is interested in determining the firm’s cost of equity capital using the security market line (SML) version of the capital asset pricing model (CAPM). Management has measured the weekly returns for the market (S&P 500), its own stock, and the risk-free rate. The returns were annualized. The annualized percentage returns for each of the last 20 weeks are provided. 1a. See data in Excel file provided with this assignment. Using Excel, determine the excess rate of return on the firm’s stock (firm return less risk-free return) and the...
Words: 1552 - Pages: 7
...contracts to hedge can reduce exposure to market risk over a period of time. This project covered both stock portfolios and bond portfolios. To illustrate this, the method of linear regression and least squares was used. We used linear regression to regress the spot rate against the futures contract return. To complete this project both EViews and Microsoft Excel was used. Summary of Points Stock Regression 2008-2009 Around 96% of the stock portfolio returns can be explained. This was calculated by finding the variance proportion, r2 in EViews. The high percentage suggests that our model is a strong fit for the data that was analyzed. We were also able to show that our estimated beta was reliably different than 0 and reliably different than 1. This was done through two-sided tests using a 95% significance level. The tcrit value was found using the excel function tinv() with 497 degrees of freedom. Testing for B=0, the t-statistic was 110.087, with the tcrit value being 1.964 this was a clear indication to reject the null because 110.087 is not in the range of -1.964 to 1.964. Testing for B=1, the t-statistic was -3.37853, with the tcrit value being 1.964 this was also a clear indication to reject the null because -3.37853 is not in the range of -1.964 to 1.964. After finding the optimal hedge ratio we calculated the dollar position for the futures market hedge to be $9,702,250. By using excel to compare the stock portfolio returns without hedging to the...
Words: 714 - Pages: 3
...appropriate discount rate and the lower the current value of the cash streams. STEP 6 – EXTRACT THE CAPITAL STRUCTURE FROM ANNUAL REPORT For calculating the discount rate, we require the proportion of Equity and Debt in the capital structure using our ABC example. For the capital structure calculations, annual reports of ABC have provided us with the following information on Debt and the Equity related items from the footnotes. The capitalization table of ABC company is as per below. ($ in MM) Short term Borrowings Revolver Bonds Of which Convertible Bonds Convertible Preferred Stock Due Date 15-Aug-09 31-May-10 31-Dec-12 31-May-10 1-Aug-12 31-Mar-14 31-Dec-10 5.2 14.2 80.0 12.0 7.0 Notes 3.2% coupon Due 5/31/2010 7.5% coupon, amortizing Bond Amortizing portion 4.5% coupon, conversion price $25, 1 bond of Fair value $100 converts into 4 shares 9.0 3% coupon, conversion price $20, 1 preferred stock of Fair value $18 converts into 1 share UNDERSTANDING THE CAPITAL STRUCTURE OF THE FIRM Short Term Borrowings: Short term borrowings is an account shown in the current liabilities portion of a company's balance sheet. This account is comprised of any debt incurred by a company that is due within one year. The debt in this account is usually made up of short-term bank loans taken out by a company. ABC needs to pay $5.2 million within one year along with the interest (coupon) of 3.2%. www.educorporatebridge.com Revolver Revolving...
Words: 2396 - Pages: 10
...headquarter is located in Düsseldorf, Germany. Since 1876, holds a well-known leading market positions in both industrial and consumer businesses. It employs around 47,000 people, from at least 6 different areas worldwide. The company has brands and technologies which focused in three globally operating areas: Laundry & Home Care, Beauty Care and Adhesive technologies (Henkel AG & Co. KGaA, 2012). From 2012 their sales values has recorded 3.410 million euros, gross profit of 1.073 million euros and a net income which had a major increase from 32 million euros made in 2011 to 591 million euros en 2012, following (Henkel AG & Co. KGaA, 2012). Fortunately, with a constant increase that the company have been having from its current assets, potential investor might be tempted to follow from close the progress of Henkel. This essay is also gives Henkel AG a much closer look of some of the financial values of the company in order to explain its progress to help having a better perspective of how are the performance management being a support for its evolution to incentive even more current and potential investors. Table of content Introduction 4 Cost of Equity 5 Market Beta 7 Cost of Debt 14 Weighted Average Cost of Capital (WACC) 17 Conclusion 18 References 19 Appendix 21 Introduction Due to the current economic status quo of business markets worldwide, many companies have lost their “feet”, in other worlds, the so assured...
Words: 4435 - Pages: 18
