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.