Term Project
Guideline:
This project is independent work instead of teamwork.
In each section, your submission is composed of two files:
(1) Calculations (in Excel document): Please complete all calculations in Excel and show me your calculation process.
(2) Analysis Report (in Word document): the report should include a thorough analysis based on calculations, graphs, or figures to explain statistical summaries or any other supporting documents you think are helpful to address the questions.
Company: Wells Fargo
Data Resources:
Last three years’ balance sheet, income statement, and statement cash flow of the public firm.
You may collect the data from Yahoo Finance http://finance.yahoo.com
The other website is http://www.sec.gov/edgar.shtml
U.S. Treasuries: http://www.ustreas.gov/offices/domestic-finance/debt-management/interest-rate/yield.shtml
Good sources for betas:
Value Line: http://valueline.com
Instructions:
About the Company
Provide one short paragraph to introduce the company:
a description of its products, customer base, major suppliers;
major events (merger and acquisition, divestitures, change of directors, credit rating changes, new product releases, etc) during the past three years;
a description of its strategy during the past three years.
Section 1: Financial Statement Analysis (50% of total credit)
Calculations (30% of total credit):
Measure your company’s financial ratios of the past three years from the 6 categories below.
Identify an industry competitor of your company, and do the same calculations for the competitor. Please compare each ratio and analyze if your company is at comparative advantages/disadvantages.
Liquidity:
Current Ratio
Quick Ratio
Efficiency:
Total Asset Turnover
Days Sales Outstanding
Leverage:
Total Liabilities to Total Assets
Times Interest Earned (TIE) Ratio
Profitability:
Profit Margins- Percentage of revenues remaining after deductions:
Return on Assets (ROA)
Market multiples:
Price Earnings (P/E) ratio
Earnings Per Share (EPS)
DuPont Ratio and explain. Please illustrate on each individual part of the DuPont Ratio (profit margin, turnover, and leverage).
Analysis Report (20% of total credit):
Based on your calculations, take a thorough analysis of the company’s financial health from the above six dimensions of financial ratios (liquidity, efficiency, leverage, profitability, market multiples, and DuPont Ratio Analysis.
Please write one paragraph for each dimension of financial ratios.
Section 2: Stock Valuation, WACC, and Investment Recommendations (50% of total credit)
Calculations(30% of total credit):
Calculate the company’s intrinsic stock valuation by DDM. Specifically, please find the D0 (this year’s dividend payment) and D1 (expected dividend payment next year), g (dividend growth rate), and r (cost of equity, i.e. the required rate of return on stock in the CAPM model.
If no dividend issued, please use the price multiple approach. (Hint: In Chapter 8 Stock Valuation, you have learned the price multiple approach. You may find the details in textbook Chapter 8 Table 8.1 Summary of Stock Valuation V. Valuation Using Multiples). Here’s the formula on textbook:
P = Industry Benchmark P/E ratio * Firm’s EPS. For example, to calculate Tesla’s intrinsic stock value, you need to multiply the industry’s P/E ratio with Tesla’s EPS. The industry-level P/E ratio data is posted under Course Home Term Project.
Calculate the company’s weighted average cost of capital (WACC). (Hint: You may get access to the cost of equity and cost of debt data for all public firms. Simply google search by stock ticker & cost of equity or cost of debt. Please cite the data source in references.)
Analysis Report(20% of total credit):
First, based on your calculations, take a thorough analysis of the company’s
Valuation and explain the assumptions of WACC, one paragraph for each. Specifically, is the company’s stock price currently undervalued or overvalued? Is the company’s WACC high or low in the industry?
Then, make a recommendation – would would you buy or sell the company’s stock? Especially, what do you expect the current high inflation environment and possible economic recession in 2023 on the company’s stock?
