Construction of a Complete Portfolio

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

Study Information

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

ETC

EPS = Where N = # shares of common outstanding

Operating Leverage

Financial Leverage

Total Leverage

Breakeven (B/E) and Leverage:

Uses of Breakeven Analysis

Operating B/E Point:

Q =

Financial Leverage:

Financial Risk

Measuring the Risk of Using Financial Leverage

Degree of Financial Leverage (DFL)

What does the number mean?

DFL =

Measuring Financial B/E Point:

Financial B/E Point = i +

Example 1:

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

$0 Debt

$500,000 $500,000 Common Equity*

$500,000 $500,000

*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

$500,000 $500,000

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 = ∑ (r_j) (Pr_j) r = ($-.309)(.25) + ($3.12)(.50) + ($6.549)(.25) = $3.12

^{j = 1}

Expected Variance Calculation:

σ²= ∑ (r_j– r̄)² (Pr_j) σ² = ($-.309 – $3.12)² (.25) + ($3.12 – $3.12)² (.50) + ($6.549 – $3.12)² (.25)

^{j = 1} = $5.879

Expected Standard Deviation Calculation:

σ = σ = = $2.425

Expected Coefficient of Variation:

CV = CV = = .777

EBIT-EPS Approach to Capital Structure:

EBIT-EPS Approach

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

EPS =

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)

Example 2:

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:

Indifference Point:

Indifference Point:

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:

EPS_A = EPS_BWhere 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:

EPS_C = EPS_D

What does the EBIT-EPS graph tell us?

Shortcoming of EBIT-EPS Analysis:

Estimating Value from Earnings:

P₀ =

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

Last Updated on October 22, 2019