本次加拿大代写是关于Excel财务分析报告相关的一个Assignment
To be done in groups of four students
Please submit assignment report to me by email, ideally PDF format. DO NOT SUMBIT ZIP FILES. One report per group, please ensure all report contain student names and numbers and group number.
Company valuation:
The main objective of this assignment is to come up with equity valuation of two publicly traded companies using fundamental analysis approach.
1. Pick any industry
2. Pick two public companies in an industry; make sure they are reasonably equal in size (market capital). Try and pick the company that you are working for.
3. Get financial data for these companies for the past three years. You can acquire these data either by going directly to the companies’ website or through www.sedar.com. Use these data to do financial ratios analysis as per Chapter 3 of Ross, Fundamentals of Corporate Finance.
The 10-page report, double-spaced should include the following:
1. Executive summary
2. Industry analysis e.g. Porter’s five forces analysis: a. Barriers to entry b. Powers of suppliers c. Power of buyers (customers) d. substitutes e. Industry rivalry. This analysis is used to determine what level of profitability to expect. http://en.wikipedia.org/wiki/Porter_five_forces_analysis
3. Financial analysis
Ratio analysis
* Company financial information for the last three years. Just enough to compute financial ratios as shown in Chapter 3 in your text book
* Compare these ratios to industry averages.
* What do these trends indicate about the financial health of the company? Are they positive or negative indicators of future stock price?
Common size analysis
* See your accounting text on how to prepare common size statements.
Pro forma financial statements
Prepare forecasted financial statements for the next three years. Use your accounting text- book for guide
a. Balance sheet
b. Income statements
Try and pick a company with relatively less complex financial statements
Income statement
* Identify the major drivers e.g. what are the factors that drive revenue and COGS
* What is the trend of revenues?
* Based on past history, what percentage increase (or decrease) do you expect in sales, Cost of goods sold?
* Significant changes in a/cs receivables, payables?
* Depreciation and amortization, what changes do you expect?
* Admin expenses
* Interest on debt?
* Does the company expect to divest off any assets, gain or loss for such disposition?
* Changes in income tax rates for the future
* Calculate net income. This item is used in calculating P/E multiple. Calculate EPS. This multiple is used in forecasting stock price.
* Dividends payable. Will they remain the same? Look for clues from corporate press releases or from other media sources. A change in stock dividend policy can have a significant impact on the stock price.
Forecasting Balance sheets
Assets
* Has there been any change in cash position? Look at major capital expenditures.
* Inventories. Inventory build up or liquidation of inventories in the future. Strategic analysis (see above) will help here.
* Property plant and equipment. Increase or decrease in future
Liabilities
* Forecasting changes in accounts payables.
* Long term debt; look for clues from company’s financial statements, press releases and other media information.
* Shareholders equity: Is the company planning any new equity issue in the future?
* Retained earnings are forecasted from the income statement.
4. Forecasting stock value
From the above information, forecast stock price based on:
* Dividend growth model see chapter 8 of your text book
* P/E multiple
* P/CF multiple
* P/Sales multiple
* P/book value
* Discounting company’s forecasted free cash flow and arriving at equity value, using WACC as the discount rate
P = price of a share. E = earnings per share. CF = cash flow (use operating cash flow). When forecasting stock price look at future earnings and cash flow to make your prediction. Calculate the average P/E and P/cash flow multiple from past data. Forecast next year’s earnings and cash flow. Multiply next year’s earnings and cash flow by P/E and P/cash flow multiple to arrive at the forecasted stock price.
5. Calculate company’s WACC; see Chapter 14 of your textbook. For companies with preferred share in their capital structure include that as well. http://www.theglobeandmail.com/report-on-business/
Type in company stock trading symbol e.g. ABX for Barrick, then look up Beta value for that company
6. Conclusions
7. Recommendations
Based on your above fundamental analysis, is the market correctly valuing the company’ equity? What is your recommendation, buy, sell or hold?
8. References
9. Appendix
Additional information. You may add any additional information that is relevant, in order to complete your analysis
Note:
Free cash flow = after tax operating income – (capital expenditures-depreciation + Change in working capital)
Terminal Value= D1/(r-g). Where D1 = terminal free cash flow; r= WACC and g = % growth in free cash flow
* Do visit the Rotman School of Management Library to access Bloomberg terminal for financial data such as Industry ratios and other related financial data needed for your analysis