Reference no: EM132326275
Assignment General Motors Company
You will estimate the Weighted Average Cost of Capital (WACC) for the publicly traded company you selected for the corporate governance paper. The first step for completing this project is to extract financial data from FactSet. The financial statements from FactSet contain many years of data. Balance Sheet and Income Statement data should be from the most recent fiscal year provided by FactSet. Market data should be the most current data provided by FactSet. (Note: make sure you have the correct units for your data. FactSet often lists numbers in millions or billions of dollars rather than in dollars.) The ‘FactSet Intro for WACC' video will help you locate the data within FactSet. Replicate the below tables in an Excel worksheet named ‘WACC', and populate the tables with the corresponding data collected from FactSet. Tables need to be fully populated, even with zero values. All calculations need to be formula driven and complete to create a template (i.e. even if a value is zero for your company, you need to include this Excel cell in your calculation/formula in case you were hypothetically studying another firm in the future the calculations would all be updated automatically without additional work).
|
Table 1 - Balance Sheet, Income Statement, and Selected Market Data
|
|
|
Market value of common equity (market capitalization)
|
|
|
Book value of common equity (common equity on balance sheet)
|
|
|
Beta (5 year, raw)
|
|
|
Market value of preferred equity (total preferred from: Overviews>Capital Structure>Enterprise Value)
|
|
|
Book value of preferred equity (preferred stock (carrying value) from balance sheet)
|
|
|
Cost of preferred equity (weighted average YTW from Table 3 below)
|
|
|
Book value of debt (Notes/Bonds on balance sheet)
|
|
|
Market value of debt (sum of market values in Table 2 below)
|
|
|
Pre-tax cost of debt (weighted average of YTW on existing debt)
|
|
|
Effective tax rate estimate (income taxes/EBT; EBT=EBIT-Interest Expense)
|
|
|
Other needed data
|
|
|
3-month Treasury Bill Rate
|
|
| |
Table 2 - Bond Data
|
|
|
|
|
|
Bond CUSIP
|
YTW (we will use this for YTM)
|
Price (these are percents)
|
Amount Outstanding
|
Market Value (price*amount outstanding)
|
Weighted Yield
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table 3 - Preferred Equity Data
|
|
|
Security ID
|
YTW
|
Value
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Using the information that you gathered in the above tables, fill in the following table:
|
|
4 - WACC
|
|
Cost of equity using CAPM if market risk premium is 7%
|
|
|
Weight of common equity using market values
|
|
|
Weight of preferred equity using market values
|
|
|
Weight of debt using market values
|
|
|
Weight of common equity using book values
|
|
|
Weight of preferred equity using book values
|
|
|
Weight of debt using book values
|
|
|
WACC using market values
|
|
|
WACC using book values
|
|
Answer the following questions:
1) What date and time did you access the market data from FactSet?
2) You calculated WACC using both book values and market values. Which is better to use and why?
3) Use the ‘correct' WACC (refer to question #2) from Table 3 as the discount rate, and calculate the NPV and IRR of the proposed project from the Capital Budgeting Project presented in Module 2. In other words, combine the cash flows from the Capital Budgeting Project with the discount rate from this project to calculate NPV and IRR. Indicate your recommendation whether to move forward with the project.
Attachment:- Cash flow.rar