Project part 2 based on project part 1.
1 FIN 4424 Instructor: Advanced Topics in Financial Management Vladimir Gatchev PROJECT: PART II Due no later than 9:00pm on Monday, April 13, 2020 This is an individual project, so each student should turn in their own project. Identical projects will be graded as one and the final points will be evenly split between the authors. The third exam may have questions about the project so that I can check how involved each one of you was in the project. Unless you have obtained permission from me beforehand, no projects or resubmission will be accepted after I have posted the grades on webcourses. You will need to email me one Excel file and one Word copy of your write-up The Excel file may have several spread sheets. The first several spread sheets will be the ones you did for the first part of the project so use the file for the first part and build on it. You can decide on the arrangement of the remaining spread sheets, just make sure that you use cell references whenever possible. The name of the file should start with your last name (e.g., SMITH.xls, SMITH_ALEX2.xlsx, etc). You should email me the excel file at
[email protected]. Include your name in the email. Look at the second case study on Intel and AMD for an example of how you can organize your file. However, you can organize your file in a different way if you prefer to do so. As long as you perform all the analyses correctly and you answer all the questions consistently, the formatting and arrangement of your file would not affect your grade. I. Financial Forecasting (20 pts) Forecast 2019 (or 2020 if your past data is 2017-2019) Income Statements, Balance Sheets, and Statement of Cash Flows of both firms. Use the percent of sales method (Ch. 5) to forecast the IS and BS statements. The forecasted Statement of Cash Flows should be calculated based on the forecasted IS and BS as in the first part of the project. Make sure to include the historic financial statements (Part I of project) in your Excel file. Also, make sure to use cell references to the historic financial statements if you are using historic data in your analysis. Suggestion: If you want, you can add the forecasted statements in the same spread sheet as the historic statements--just add a column and forecast the items there. When you forecast Sales, use the trend in Sales for the 3 years of data you have. Also, create a graph(s) that shows the Sales of the firms for the 3 years of past data and the forecasted Sales. Note that if the firm pays dividends, you will also have to get dividends data for the firms in order to forecast the Balance Sheet. Total dividends can be found in the most recent annual statement of cash flows from Bloomberg (or http://finance.yahoo.com/). mailto:
[email protected] http://finance.yahoo.com/ 2 II. Collecting Stock Prices and Returns Data (5 pts) For the two (2) selected firms, get monthly historic prices for the past five (5) years. If the firm has less than 5 years of data, use as much data as is available. Using the prices, calculate the monthly historic returns for the two stocks. You should find historic prices using the Bloomberg terminals (or http://finance.yahoo.com/). If you use yahoo, once you go to the ticker symbol of your stock, you can select “Historical Data” and use the following menu to select the date range and the frequency of the prices. Note that you can directly open your data in Excel using the link that is at the bottom of the web-page. III. Estimating the CAPM for the Two Firms (5 pts) Estimate the CAPM regression for the two stocks. You will need the historic risk free rate and the historic returns on S&P 500 to do that. There is an excel file in the case study folder that provides data on treasuries for your convenience. You can use yahoo to get the prices of S&P 500 (just like you did for your two firms). When estimating the CAPM, use the return on S&P500 as the market return and the return on treasuries as the risk-free rate. IV. Valuing the Stocks (40 pts) Use the Operating Free Cash Flows Model to value the two stocks. You can find the model and its application in the second case study. First, you will need to calculate historic Operating Free Cash Flows and forecast future Operating Free Cash Flows. If forecast cash flows are negative, you can assume positive cash flows in the future to be 5% of Sales. If a different assumption seems more reasonable to you, use it. Second, you will need to calculate the WACC of the firm. Use the estimated CAPM model from Section III above to calculate the required rate of return on equity. Use the “Risk- Free Rate + Default Spread” formula for the required rate of return on the firm’s debt. See the very last page of the second case study for information on how to obtain the credit rating. If you cannot find the credit rating of your firm, assume that it is BBB. If a different assumption seems more reasonable to you, use it. Use the table on p.3 of ch.10 notes for the credit spreads. It is up to you to decide whether to use the constant growth, the two-stage growth, or the three-stage growth models. http://finance.yahoo.com/ 3 V. Discussion (write-up) (30 pts) The write-up would be on a separate Word file that you will have to print and hand to me no later than the due date and time of the project. For this part of the project, you should use the information you have gathered and the analyses you have performed for both firms and for both projects 1 and 2. To get credit, your answers should be consistent with your analyses. • Write your name on the write-up. • Provide short background information for each firm. What is their line of business? What are the competitive advantages and disadvantages of each firm as compared to the other? • In the forecasted financial statements, do you forecast a deficit or a surplus for each of the two firms? How could the firms balance the balance sheet? • Suppose that you are a potential lender to both firms. Both firms want to borrow money from you at the same interest rate. Which firm would you more likely lend the money to? Why? Explain. • Overall, which firms’ management team does a better job at running the firm? Why? Explain. • Suppose that you are an investor and you plan to buy some shares in one of the two firms. Given the current market prices of the two firms and given your analyses, which firm is a better long-term investment? Why? Explain. • In your write-up, include a printout of the ratio analysis spreadsheet (only that spreadsheet) from Project 1.