Company Analysis Project - FIN 2000

docx

School

University of Guelph *

*We aren’t endorsed by this school

Course

2000

Subject

Finance

Date

Feb 20, 2024

Type

docx

Pages

Uploaded by GrandRainLlama27

The project's purpose is to estimate the cost of equity and the weighted average cost of capital for a real firm. You will apply some of the methods that you learn in the course; in particular, you will be estimating the cost of equity capital using the dividend valuation model (described in Chapter 7 of your text), estimating beta and using the Capital Asset Pricing Model (CAPM, described in Chapter 12), and calculating the Weighted-Average Cost of Capital (WACC, described in Chapter 13). This project will introduce you to some of the sources available for financial data, provide the opportunity for manipulating real data to calculate variables which are extremely important in finance, and allow practice in using Excel or another spreadsheet program. You work for the Gryph Company which is considering starting up a new division in an industry in which it currently has no operations. While one team has been assigned the task of determining the prospects of the new investment and the expected cash flows associated with it, you are charged with finding an appropriate rate for discounting those cash flows. Your boss will send you an emailed memo letting you know what company you should analyze. The company will be in the industry of the proposed new division. Your task is to determine this company's current Weighted-Average Cost of Capital. You will be collecting data related to this company, calculating the cost of equity capital in three different ways, and using those values to calculate the WACC. For simplicity we will assume that it is January 1, 2024. See memo here: ‘ Our company is planning an expansion in the banking industry. This is a new line of business for our company and therefore we cannot use our company cost of capital to analyze this investment. Instead, we need to determine a cost of capital that is appropriate for this new industry. In order to find an appropriate cost of capital, we should analyze a company that currently operates in the banking industry. As such, I would like you to determine the cost of equity capital and the weighted average cost of capital for Royal Bank . You should estimate the cost of equity capital in three ways: using the dividend growth model assuming constant growth in dividends, using the dividend growth model assuming a sustainable growth rate, and using the Capital Asset Pricing Model. Use each of your three estimates to determine the Weighted Average Cost of Capital. For more information, please consult the detailed instructions on CourseLink.’ Submit: Please submit your data and results for the two dividend growth models as a pdf file, including documentation so that the results could be replicated. Please submit your data and results for the Capital Asset Pricing Model and Weighted Average Cost of Capital as pdf files, including documentation so that the results could be replicated. In addition, please submit a one page memo summarizing your results (including your three estimates of the cost of equity capital and your three estimates of the Weighted Average Cost of Capital) and providing your recommendation for the cost of capital to be used in analyzing the investment, as well as a brief explanation of why you are making that recommendation.

The following is a marking rubric Link opens in a new window. that might be helpful in preparing your spreadsheets and in marking your peers. You should make sure that your spreadsheet contains your data, where you found the data (you do not need the complete URL, but you do need to provide enough of a website name that someone else could find the data), and your results (including formulas that you used and sample calculations so that someone else can understand what you did and could reproduce your results). Historical Growth: Data Required:  Quarterly dividends per share paid by your company (in Canadian Dollars ) for the period January 1, 2019 to December 31, 2023. Use the ex-dividend date (2 business days before the record date) as the date of the dividend. The date provided by Yahoo Finance is the ex- dividend date. o A good source is the company's website although the reported dividends may not have been adjusted for splits, so you will have to make the adjustment. o Another good source is Yahoo Finance Canada but it sometimes misses dividends, double lists dividends, or records them incorrectly, so it is best to verify by checking the company's website. o Only the regular quarterly dividends should be included. Do not include any extra or special dividends.  The December 29, 2023 closing stock price for your company. o This can be found on Yahoo Finance Canada , the Toronto Stock Exchange , or many other financial websites Calculations: 1. Calculate the annual dividends that your company paid. Sum the four quarterly dividends paid in each calendar year for each of the 5 years of data you have collected. i. In some cases the company may have changed its dividend payment dates so that you may get a year with 5 dividends and/or a year with 3 dividends. You may need to make an

adjustment so that you are always working with 4 dividends (i.e., move December up to January, or January back to December). ii. Some companies may have paid extra dividends. This will appear either as an added dividend payment or as an extra-large dividend that has been lumped with the regular dividend. If it looks like this has happened with your company you will need to check the appropriate annual report to determine if it was an extra or special dividend, in which case you should not include it in your calculations (but do still show it in your data and make a note that it was an extra dividend). iii. Make sure your data have been adjusted for splits. If you see the dividends have suddenly dropped by a large amount, it is likely that there has been a split and you will need to make an adjustment (for example, if there was a 2-for-1 split, you will need to divide all the dividends prior to the split by 2). 2. Calculate the annual growth rates of the dividends (i.e., the percentage change in annual dividends from one year to the next). 3. Calculate the average of your 4 annual growth rates. This is your value for g. 4. Estimate the total dividends that will be paid between January 2024 and December 2024, assuming that the firm maintains its current average annual growth rate. 5. Calculate the firm's expected rate of return using your calculated expected dividend, growth rate, and the unadjusted price for December 29, 2023. Sustainable Growth: Again, you will use the constant-growth dividend discount model to estimate your company's expected rate of return. This time, however, you will estimate the growth rate by calculating the sustainable growth rate. Data Required:  Most recently available financial statement information: Book Value of Equity (BE), Net Income (NI), Earnings per Share (use Diluted EPS Excluding Extraordinary Items), and Dividend per Share (Note that these last three must be from an annual income statement. You are collecting dividend per share again to make sure that it matches the time period used for the EPS).  These can be found at Yahoo Finance Canada , the Toronto Stock Exchange , SEDAR , or on the company's website.  The December 29, 2023 closing stock price for your company. Calculations:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

6. Estimate the return on equity and the plowback ratio using the financial statement data you have collected. 7. Estimate the sustainable growth rate using the return on equity and the plowback ratio. 8. Estimate the total dividends that will be paid between January 2024 and December 2024, assuming that dividends grow at the sustainable growth rate. Use your previously calculated dividend for the 2023 calendar year from the Historical Growth section as your base. 9. Calculate the firm's expected rate of return using your calculated expected dividend, sustainable growth rate, and the unadjusted price for December 29, 2023. Report the data and results for these two sets of calculations on the DDM Template (or you can create your own). Make sure you include sources for your data and show the formulas you used (using variable names) as well as the calculations (using your numbers). You will have to convert your document to Portable Document Format (PDF) before you submit it to PEAR (a screenshot is not acceptable). This ensures that everyone will be able to access and read it. Make sure you check your file after the conversion – the conversion has the same effect as printing the document and the results are not always what you expect. You may find that you have to go back and adjust your formatting. Do this before you submit your file to PEAR. It is your responsibility to make sure that you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. Capital Asset Pricing Model (CAPM) Weight: 5.6% Submit : via the PEAR tool Due: Week 9 Format: PDF version of Excel spreadsheet for CAPM (see link above) Grading Rubric: Your submission will be marked by your peers based on the following categories (equal weighting for each): Data (inclusion and accuracy), Sources (provided and clear), Calculations (properly done), Documentation (formulas and sample calculations provided for the calculations), Graph (properly oriented, labelled, and includes best-fit line) and Presentation. In addition, you will be marked on the reviews you give your peers based on the quality of the comments that you provide. Note : Please refer to the Outline for exact due dates. In this section you will calculate the firm's expected rate of return using the capital asset pricing model. You will first need to calculate your company's

beta and then use that in the CAPM formula to get the expected rate of return. Data Required:  Monthly closing stock prices (in Canadian dollars ) for your company for the period January 1, 2020 to December 31, 2023. o A good source is Yahoo Finance Canada , as it allows you to search and download the entire period at once. o You can specify the date range, choose monthly prices, and download the information o Use the Close price rather than the Adjusted Close price. o NOTE : Yahoo Finance provides Open-High-Low-Close prices for each month and lists the date as the first trading day of the month. The closing price is from the last trading day of the month.  Monthly closing prices for the S&P/TSX Composite Index for the period January 1, 2020 to December 31, 2023. o A good source is Yahoo Finance Canada . The company symbol will be ^GSPTSE. You can also click on S&P/TSX to get to the page. o Yahoo Finance may be missing some of the data when you download monthly prices. You will need to look at the daily prices to fill in the missing values.  The yield on a 3-month Canada Treasury Bill for December 29, 2023. o You will find this at the Bank of Canada . o From the dropdown menu under "Statistics", choose "interest Rates", then choose "Treasury Bill Yields". Click on "Look up the past ten years of data" for these series. Select your date and choose Treasury Bills, 3 Month, Daily. You will be given the yield as a percentage. Calculations: 10. Calculate each of the monthly returns for your stock over the 4 years from January 2020 to December 2023 (i.e., percentage change in price from month end to month end). 11. Calculate each of the monthly returns for the S&P/TSX Composite Index over the same 4-year period. 12. Create a scatter plot using Excel that shows the returns on your company's stock and the returns on the market index. Each point will represent one month (see Figure 12.2 in your text). Plot the characteristic line on the graph (the trendline). Make sure to label the axes.

13. Calculate the standard deviation of your company's returns, the standard deviation of S&P/TSX returns, and the correlation coefficient of S&P/TSX and company returns. 14. Calculate beta as the slope of the characteristic line on your graph (see a sample spreadsheet in section 12.1 of your text). 15. Using the value of beta that you calculated, the December 29, 2023 yield on a three-month treasury bill for the risk-free rate, and 7 percent as the market risk premium (the average market risk premium over the last 90 years), calculate the expected rate of return based on the capital asset pricing model. Report the data and results for these two sets of calculations on the CAPM Template. Make sure you include sources for your data and show the formulas you used (using variable names) as well as the calculations (using your numbers - note for the return calculation you only need to show one sample calculation). You should show the excel formulas that you used for standard deviation, correlation, and beta. Include a graph of your data and make sure you label the axes. You will have to convert your document to Portable Document Format (PDF) before you submit it to PEAR (a screenshot is not acceptable). This ensures that everyone will be able to access and read it. Make sure you check your file after the conversion – the conversion has the same effect as printing the document and the results are not always what you expect. You may find that you have to go back and adjust your formatting. Make sure you check the formatting before you submit to PEAR. It is your responsibility to make sure that you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. Weighted Average Cost of Capital (WACC) Weight: 4.7% Submit : via the PEAR tool Due: Week 9 Format: PDF version of Excel spreadsheet for WACC (see link above) Grading Rubric: Your submission will be marked by your peers based on the following categories (equal weighting for each): Data (inclusion and accuracy), Sources (provided and clear), Calculations (properly done), Documentation (formulas and sample calculations provided for the calculations), and Presentation. In addition, you will be marked on the reviews you give your peers based on the quality of the comments you provide. Note : Please refer to the Outline for exact due dates.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

In this section you will calculate a return on debt for your firm and use that with the expected returns on equity that you calculated in the DDM and CAPM parts above to calculate a weighted average cost of capital. You will get three different values, one for each method you used to calculate the cost of equity. Data Required:  Most recently available annual financial statement information: Long- Term Debt and Number of Shares Outstanding. o These can be found at Yahoo Finance Canada , the Toronto Stock Exchange , SEDAR , or on the company's website.  Your company's debt rating for Senior Debt. o Sometimes this will be listed on the company's website. Some other sites to check are Moody's (moodys.com), FitchRatings (fitchratings.com), or S&P Global (standardandpoors.com). These sites may require registration, but they are free. A google search may also turn up the debt rating.  The yield on a 10-year Government of Canada Bond for December 29, 2023. o You will find this at the Bank of Canada .  The closing stock price for your company on the date of the annual report. Calculations: 16. To get the yield on the firm's debt, assume that the credit spread (i.e., the extra yield over the equivalent term government bond) is as given in Ratings, Interest Coverage Ratios, and Default Spread data Link opens in a new window . Add the appropriate spread for your company's debt rating to the yield on 10-year Government of Canada bonds on December 29, 2023. If you cannot find a debt rating for your company, you can assume that it has a BBB rating. 17. Calculate the values of debt, equity, and the firm. o Use the value of long-term debt from the most recent statement of financial position for the value of debt. o Calculate the value of equity using the number of shares outstanding and the actual price from the date of the most recent annual report. o The value of the firm will be the sum of the debt and equity (ignore any preferred stock). o Also calculate the proportions of debt and equity to make it easier to check your WACC calculations.

18. Calculate the weighted average cost of capital (WACC) for your firm three ways. a. Once using the expected rate of return on equity from the constant-growth dividend discount model – historical growth, b. Once using the expected rate of return on equity from the constant-growth dividend discount model – sustainable growth, and c. Once using the expected rate of return on equity from the capital asset pricing model (CAPM). d. Use the book value of long-term debt and the market value of equity and assume your company has a 26 percent corporate tax rate. Report the data and results for these two sets of calculations on the WACC Template. Make sure you include sources for your data and show the formulas you used (using variable names) as well as the calculations (using your numbers). Also make sure that you report the values for expected rate of return on equity that you determined from the previous parts of this project. You will have to convert your document to Portable Document Format (PDF) before you submit it to PEAR (a screenshot is not acceptable). This ensures that everyone will be able to access and read it. Make sure you check your file after the conversion – the conversion has the same effect as printing the document and the results are not always what you expect. You may find that you have to go back and adjust your formatting. Make sure you check the formatting before you submit to PEAR. It is your responsibility to make sure you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. Summary Memo Weight: 3.7 % Due: Week 9 Submit via the PEAR tool Format: PDF document Grading Rubric: Your memo will be marked by your peers based on the following categories (equal weighting for each): Content (does it include the 3 rates of return, 3 WACC values, and a recommended return), Purpose (does it explain why this work has been done), Explanation (does it explain why the particular return has been chosen), and Writing (well-organized, clear, free of grammar and spelling errors). In addition, you will be marked on the reviews you give your peers based on the quality of the comments you provide.

Write a one-page (at most) memo to your boss reporting your findings.  Include an opening segment that states the problem and purpose of your memo (your boss receives lots of memos and needs to know what this one is about). Make sure you state the name of the company you have analyzed and its industry.  Provide the three expected rates of return that you calculated for your company.  Provide the three weighted-average costs of capital that you calculated.  Provide a recommendation for what discount rate your company should use in evaluating their proposed investment.  Provide a brief explanation of why you are recommending that rate. There is no correct answer to what you should recommend for a discount rate. You should, however, provide a good explanation for why you are recommending the rate you have chosen. Submit this memo as a PDF Document to the PEAR tool. This memo should be in proper business format (with the exception that you should not include your name on the memo, as the PEAR evaluations should be anonymous). It is your responsibility to make sure you have properly uploaded the correct file to PEAR. If you are having technical difficulties, you will need to contact CourseLink support. The following is a detailed marking rubric to use in your evaluations:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version