Finding the Value
of Operations (VOP Modeling)
The accompanying Excel spreadsheet contains a template
for calculating your firm’s valuation, using modeling techniques developed in
this class. Using your firm from the pharmaceutical industry AND BAXTER as a
comparison, please complete the following:
Section 1:Firm
Valuation Analysis (millions of dollars except per share data)
In this section, input key financials in the highlighted
green cells for the firms assigned to you in this course. Use your first
project’s financials as a guide. You may need to calculate some of the
following financial statement information or review the annual reports again.
Carefully input:
·
Tax rate
·
Debt (D)
·
Number of shares
(n)
·
Stock price per
share (P)
·
EBIT
·
NOPAT
·
Free Cash Flow
(FCF)
·
Growth rate in FCF
Within this section, also import the cost of debt, beta,
the risk-free rate, and the market risk premium. Lastly, import the value of
any short-term (ST) investments. The formulas will guide you to your final
answer from Section 1, the intrinsic price per share.
Section 2: The Hamada Equation
In this section, your new beta should be calculated
automatically. Compare the levered versus the unlevered beta. Please notice
that if your firm holds no debt with a tax-deductibility feature, then levered
and unlevered beta should be the same.
Section 3: Estimating the Firm’s Optimal Capital
Structure
In this section, you are provided with floating rates of
debt, ranging from 0% to 60%, in 10% increments. To complete this section,
please provide the following:
·
Locate, from
Section 1 and Section 2, the percentage weight of debt within your firm. Highlight
that percentage weight in Section 3 (in blue). Link the cell for the cost of
debt to the appropriate cell on Line 79 within your Excel sheet. Then, link the
rest of the relevant financial statement items to that row and column. The
resulting WACC should match your result from Section 1 (approximately).
·
Rd:On Line 79,
play the role of a financial manager. Adapt the cost of debt as the weight of
debt increases in the portfolio. So, for example, if your firm currently has a
cost of debt of 5% and its portfolio is based upon a weight of debt of 10%,
keep that cell fixed. Manually adjust the remaining cells on Line 79.
·
The remaining
cells, beta, the cost of equity, WACC, etc. should adjust automatically as you
vary the cost of debt, rd.
·
Carefully review
your results with respect to (6) WACC, (7) Value of Operations, (11) Stock
price, (12) Net income, and (13) EPS. You may need to alter equations where
necessary.
What’s Due?
·
Tab 1: Entitled
this tab the name of your first firm. For example, if you are evaluating Merck,
entitled it Merck. This tab should contain the valuation model presented above.
·
Tab 2: Entitled
this tab, Firm-Ratios (e.g., Merck-Ratios). Create a table, from the financial
statements, of key financial ratios. Use your textbook as a guide. Financial
ratios are subdivided into various categories (e.g., liquidity, asset
management) so select (at least) 2 ratios from each category. Your Excel
document should also highlight net income, free cash flow, NOPAT, EBIT, etc.
for the past 3 years.
·
Tab 3: Entitled
this tab the name of the comparison firm for this class. This tab should
contain the valuation model presented above.
·
Tab 4: Entitled
this tab Firm-Ratios (e.g., BAX-Ratios). Again, create a table of financial
ratios.
·
Word Document: Your
Word document should be approximately 5-10 pages in length. Please answer the
following questions:
·
Assess each firm’s
financial strength using financial ratios and valuation metrics. Indicate
whether analysts are likely to view the firm positively and indicate why. You
may wish to use Mergent Online or Value Line via our library to gain analysts’
insight into each firm.
·
Using your text and
the aforementioned spreadsheets, discuss the expected impact of debt on each of
your firms’ value and risk. Use both the APV and adjusted discount value.
Particularly, discuss the impact on WACC, Beta, Value, and stock price.
·
Email this document
in the following file format, Lastname-Firm1 (e.g., Medic-AOL-part2.docx)