Guidelines for Construction of a Complete Portfolio
In this project, you are required to construct an optimal complete portfolio using two methods, Markowitz portfolio optimization procedure and Treynor-Black procedure. Below are guidelines you can use when doing the project. You are not necessary to follow the guidelines step by step exactly.
- Download adjusted end of month stock price dataset during the past five years for seven stocks across seven different industries in U.S. Also download the monthly dataset for S&P500 index and T-bills for the corresponding time periods.
- Convert all the datasets from the monthly price to monthly returns for the same time period.
- Determine the risk-free rate (T-bill rate could be considered as risk-free rate).
- Determine the expect return & standard deviation for market portfolio (E(rm) = A*σm2).
- Estimate market (S&P500 index) risk premium for next month.
- Calculate monthly expected returns for each stock.
- Read Appendices A and B and using the template provided and to construct Correlation matrix, Covariance matrix, Bordered Covariance Matrix for Equally Weighted Portfolio and for Target Return Portfolio.
- Create minimum variance portfolio frontier/portfolio efficient frontier.
- Construct optima risky portfolio and optimal complete portfolio using the Markowitz Portfolio Optimization Model and Excel build-in functions. For details of the application, you can read Appendices A and B on Pages from #232 to #244.
- Estimate Expected return\beta\residual standard deviation for each stock.
- Using Treynor-Black procedure and end-of-chapter problem #8-17 as the example/reference when constructing an optimal active portfolio, an optimal risky portfolio, and an optimal complete portfolio. Notice that the problem #8-17 uses four stocks when constructing the active portfolio. You need to use seven stocks you selected when constructing the active portfolio. You also need to answer all questions listed on Problem #8-17 with your computed results.
- All calculations should use excel build-in functions as much as possible.
- The complete portfolios should be able to change the weight of each asset automatically whenever any input changes (such as target expected return changes, estimated market return changes, and coefficient of risk aversion changes).
Chapter 13: Leverage and Capital Structure
NOTE: Covering very little Operating Leverage and no Total Leverage. I’ve added more theory explanation for Financial Leverage.
Fixed Operating Costs
Fixed Financing Costs
Association of Leverage to the Income Statement:
Sales Sales (Q)(P)
– CGS (Both FC & VC) Operating – TVC (Q)(VC)
Gross Profits Leverage – FC
– Op. Expenses (Both FC & VC) EBIT Total
EBIT – Int
– Interest Financial EBT Leverage
EBT Leverage – Tax
– Tax NI
NI – PD
– PD ETC
EPS = Where N = # shares of common outstanding
Breakeven (B/E) and Leverage:
Uses of Breakeven Analysis
Operating B/E Point:
Measuring the Risk of Using Financial Leverage
Degree of Financial Leverage (DFL)
What does the number mean?
Measuring Financial B/E Point:
Financial B/E Point = i +
The Holistic Pet Food Company expects operating profits to be $500,000 next year. The firm currently has $2,000,000 in bonds outstanding with a 6% coupon rate and 15,000 shares of preferred stock outstanding with a par value of $100 and a dividend rate of 7%. There are 500,000 shares of common stock outstanding. Holistic Pet Food Company is in the 21% marginal tax rate.
- Find the firm’s EPS.
- Find the DFL for Holistic Pet Food and analyze where the firm is to its financial B/E point.
- Find the financial B/E point for the firm and prove the answer is correct.
The Firm’s Capital Structure:
Components of the Capital Structure
Debt Ratio (DR):
Choosing an Optimal Capital Structure:
Example using the DR to determine an Optimal Capital Structure:
Book Example 13.18 starting on page 574:
Tying the example together using the DR of 30% for Cooke Company:
Current Structure for the Corporation (Example 13.18, Page 575) is a Debt Ratio of 0%
TA TD + Equity
$500,000 $500,000 Common Equity*
*25,000 shares @ $20 ( = 25,000 Shares)
Using Example 13.10:
Capital Structure changing the Debt Ratio to 30%:
TA TD + Equity
$150,000 Debt ($500,000 x .30)
$500,000 350,000 Common Equity
Shares of common = = 17,500
From Table 13.11, Page 576
Interest Rate for 30% DR = 6%. Therefore interest expense = $150,000 x .06 = $9,000
Combining Table 13.9, Page 574 and Table 13.12, Page 577 at the DR = 30%
Probability of Sales .25 .50 .25
Sales Revenue $400,000 $600,000 $800,000
– FC – 200,000 – 200,000 – 200,000
– VC – 200,000 – 300,000 – 400,000
EBIT 0 100,000 200,000
-Interest – 9,000 – 9,000 – 9,000
Taxable Income (EBT) – 9,000 91,000 191,000
Tax (.40) + 3,600 – 36,400 – 76,400
NI – 5,400 54,600 114,600
EPS = -$.309 $3.12 $6.549
Using the Formulas from Chapter 8:
Expected EPS Calculation:
_ n _
r = ∑ (rj) (Prj) r = ($-.309)(.25) + ($3.12)(.50) + ($6.549)(.25) = $3.12
j = 1
Expected Variance Calculation:
σ2= ∑ (rj– r̄)2 (Prj) σ2 = ($-.309 – $3.12)2 (.25) + ($3.12 – $3.12)2 (.50) + ($6.549 – $3.12)2 (.25)
j = 1 = $5.879
Expected Standard Deviation Calculation:
σ = σ = = $2.425
Expected Coefficient of Variation:
CV = CV = = .777
EBIT-EPS Approach to Capital Structure:
Data required for the Analysis:
Two coordinates required for each capital structure to draw the EBIT-EPS graph:
- Find the EPS for each capital structure at a given level of EBIT
- Find the financial B/E Point for each capital structure.
Financial B/E Point = i + ()
This represents where the plan will cut through the horizontal axis (EPS = $0)
The Camelot Corporation currently has 100,000 shares of common stock outstanding with a market price of $40 per share. It also has $1 Million in 6% bonds. The company is considering a $2 Million expansion program that it can finance with three options. Option 1 is to sell all common stock at $40/share; Option 2 is to sell preferred stock at 7% or Option 3 is to finance the expansion half with common stock at $40 per share and half with 8% bonds. Assume a 30% tax rate.
- For an expected EBIT level of $750,000 after the expansion program, calculate the two coordinates necessary to graph the EBIT-EPS approach for each option.Note
- Construct the EBIT-EPS graph showing the three financing optionsNote
- Calculate the DFL for each option at the $750,000 level of EBITNote
Solution for Graph (part b)
- b. Graph:
Calculation of the Indifference Point:
When one or both of the plans has preferred stock in the capital structure, the calculation of the indifferent point is:
EPSA = EPSB Where the unknown factor is EBIT.
When neither plan causing the intersection has preferred stock, a short-cut calculation to finding the indifference point can be used:
EPSC = EPSD
What does the EBIT-EPS graph tell us?
Shortcoming of EBIT-EPS Analysis:
Estimating Value from Earnings:
Final Comments on Selecting the Best Capital Structure:
A few other factors to consider:
Answers to Assigned Homework Problems:
P13-11: a) $1.275 b) $2.175 c) $2.835
P13-14: a) 1.50 b) Two coordinates: (EBIT = $67,500; EPS = $1.80) and (EBIT = $22,500; EPS = $0) Graph c) $1.929 d) Two coordinates: (EBIT = $67,500; EPS = $1.40) and (EBIT = $32,500; EPS = $0) e) Theory Answer
P13-19: @10%: Debt = $100,000, Equity = $900,000; @40%: Debt = $400,000, Equity = $600,000 NOTE: This is only two of the answers, ask if you have a question on the rest.
P13-20: a) -$15,000, $15,000, $45,000 b) EPS = -$1.62, $.18, $1.98; CV = $6.325 c) EPS = -$.60, $.60, $1.80; CV = $1.265 d) Theory Answer
P13-22: a) A’s two coordinates: (EBIT = $60,000; EPS = $8.69) and (EBIT = $16,000; EPS = $0); B’s two coordinates: (EBIT = $60,000; EPS = $10.27) and (EBIT = $34,000; EPS = $0) b) Graph c) To answer the question, the indifference point must be found. EBIT = $52,000. d) Theory question e) Theory question
P13-23: a) A’s two coordinates: (EBIT = $50,000; EPS = $2.625) and (EBIT = $15,000; EPS = $0); B’s two coordinates: (EBIT = $50,000; EPS = $2.28) and (EBIT = $12,000; EPS = $0).
- b) Graph c) Theory Answer d) To answer the question, the indifference point must be EBIT = $27,000. e) Theory Answer
P13-24: a) $5.00, $5.459, $5.986, $6.145, $5.75 b) $41.667, $41.992, $42.757, $38.406, $28.75. c) Theory Answer