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
eforehand, 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 a
angement 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 XXXXXXXXXX. 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 co
ectly and you answer all the questions
consistently, the formatting and a
angement of your file would not affect your grade.
I. Financial Forecasting (20 pts)
Forecast 2019 (or 2020 if your past data is XXXXXXXXXXIncome 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
e 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: XXXXXXXXXX
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
ating. 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 bo
ow
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 cu
ent 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.