Question Details

(solution) Hi Can you do this for me too? It is Target Corp Prepare DFCF


Hi  Can you do this for me too? It is Target Corp

Prepare DFCF Stock Valuation (Discounted Free Cash Flow Stock Valuation)

and EVA/MVA Analysis

(Rev. 071216)

Students may elect to use the eVal Financial Forecasting Model or Prof. Stevens? Excel Discounted Free Cash Flow Valuation Model for the DCF Stock Valuation part of this assignment. If the eVal model is selected, make sure that financial information is available for the company that is selected for the assignment (See: http://www.lundholmandandsloan.com). Similarly, if Prof. Stevens? model is used for the assignment make sure that financial information is available (e.g., Yahoo Finance, etc.). (Note: Alternatively, the ValuePro.net model may be used for the DCF valuationbut the final score for the assignment will be reduced by 20% if that alternative is selected.)

The actual Excel forecasting model (either eVal or Prof. Stevens? model) must be submitted with the assignment.

The instructor?s EVA/MVA Excel Model must be used for the EVA/MVA analysis. That model must be submitted with the assignment, too.

Assignment: DCF Valuation and EVA/MVA Analysis

Using the company that was selected for the class, there are two parts to this assignment:

1. DCF Company Valuation ?

Prepare a DCF Valuation of the selected company using either: (a) the eVal Excel Model and the projections developed in Week 2, or, (b) Professor Stevens? Excel Financial Forecasting Model. Both of the models have an embedded worksheet that accommodates the Discounted Free Cash Flow stock valuation.

Make sure that the stock valuation model is submitted with the written analysis.

Discuss, interpret and explain the assumptions (variables) used in the analysis.

Discuss, interpret and explain the results of the analysis. This involves comparing the intrinsic value of the company's stock valuation to the current stock price and carefully explaining any differences.

2. EVA & MVA Analysis -

Prepare an analysis of EVA and MVA for the selected company. An Excel model is provided by the instructor. Explain the results.

Make sure that the EVA/MVA Excel model is submitted with the written analysis.

CONTENT OF THE ANALYSIS

The analysis associated with the two parts of the assignment needs to include:

1. Explanation of the Variables Used in the DCF Valuation ? Explain and justify all of the variables used in the DCF valuation. These would include, but are not limited to, the growth rates, capitalization structure, cost of capital components, WACC, etc.

2. Explanation and Interpretation of DCF Valuation Results ? Explain the results of the DCF analysis. This should include an analysis of any difference between the results of the DCF analysis and the current stock price.

3. Explanation of the Data Used for the EVA Analysis and Interpretation of Results ? Present the results of the EVA analysis and explain what they mean. The analysis should discuss future EVA results and what variables can affect those results either positively or negatively.

4. Explanation of the Data Used for the MVA Analysis and Interpretation of Results - Present the results of the MVA analysis and explain what they mean. The analysis should explain a discussion of future MVA results and what variables can affect those results either positively or negatively.

Writing Instructions

The discussion portion of the analysis should be three to five pages in length, double spaced, and should employ APA style and format for reference citations. Supporting data (e.g., figures, tables, etc.) and references should be submitted limited to four separate attachments in an appendix after the written portion of the paper.

The paper should begin with a short introduction and then proceed to examine the four topics outlined in the previous section.

The subheadings used in the paper should be:

1. Introduction

2. Explanation of the Variables Used in the DCF Valuation

3. Explanation and Interpretation of DCF Valuation Results

(Including comparison and explanation of differences between the current stock price and results of the DCF valuation.)

4. Explanation of the Data Used for the EVA Analysis and Interpretation of Results

5. Explanation of the Data Used for the MVA Analysis and Interpretation of Results


Name of Company McCormick & Company (MKC) 2015 (04/12/2016)

 

Directions: Enter data in the yellow-coded cells.

 

Do not cut and paste or otherwise make changes

 

to the Excel spreadsheet. Estimate Basic Information & Growth Rates

 

Initial Free Cash Flow

 

Initial Period Annual Growth Rate Estimate

 

Terminal Annual Growth Rate Estimate

 

How Long is Initial Growth Period (Years)

 

Number of Shares of Common Stock

 

Tax Rate Estimated During Future Periods

 

Total Interest-Bearing Permanent Debt Estimated During Period

 

Total Assets $422.76

 

2.50%

 

2.00%

 

10

 

115.27

 

26.50%

 

$1,100.00

 

$4,510.00 Estimate Free Cash Flow

 

From Current Sales ($) Estimate Future Annual Sales ($)

 

From Current EBIT Margin (%) Estimate Future EBIT Margin (%)

 

EBIT

 

Plus - Future Annual Estimated Depreciation Expense

 

Less - Future Annual Estimated Capital Expenditures

 

Less - Net Annual Estimated Change (Invest.) in Working Capital

 

Equals Operating Cash Flow

 

Less Taxes

 

Equals Free Cash Flow $4,400.00

 

14.00%

 

$616.00

 

$110.00

 

$130.00

 

$10.00

 

$586.00

 

$163.24

 

$422.76 Estimate Weighted Average Cost of Capital

 

Cost of Equity Capital

 

Before Tax Cost of Debt Estimated During Future Periods

 

Market Risk Premium (RM-RF) Estimated During Future Periods

 

Debt Ratio (Total Debt/Total Assets) Estimated During Future Periods

 

Beta Estimated During Future Periods

 

Risk-free Rate (Use L-T Treasury Rate) Estimated During Future Periods

 

= Weighted Average Cost of Capital Year

 

Free Cash

 

Flow

 

Terminal

 

Value

 

Total FCF

 

Discounted

 

FCF

 

Net Present

 

Value of

 

FCF

 

Net Present

 

Value of

 

FCF Minus

 

Debt

 

Per Share

 

Value 0 1 2 3 5.800%

 

3.500%

 

5.500%

 

24.390%

 

0.600

 

2.500%

 

5.013% 4 5 Computer Calculates

 

See Note 1

 

See Note 2

 

Years 0 Up to 20 Be very careful with entering the Number of Shares.

 

Make sure that the decimal point is in the proper

 

location. Computer Calculates Computer Calculates

 

Computer Calculates - See Note 3

 

Computer Calculates Computer Calculates Computer Calculates Computer Calculates 6 7 8 9 $0 $433 $444 $455 $467 $478 $490 $503 $515 $528 $0 $433 $444 $455 $467 $478 $490 $503 $515 $528 $0 $413 $403 $393 $384 $375 $366 $357 $348 $340 10 11 12 13 Note: Terminal Growth Rate Must be less than WACC for

 

projections of 1 or more years; for valuations based on 0

 

years of projections, WACC must be > Growth Rate 15 16 17 18 19 20 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $18,322 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $18,863 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $11,566 $0 $0 $0 $0 $0 $0 $0 $0 $0 $0 $14,943 $13,843 14 $541 FREE CASH FLOW VALUATION MODEL $120.10 Notes:

 

1. Terminal Value is growing perpetuity of final year's Free Cash Flow [(FCF) / (WACC - Growth Rate)]

 

2. If Number of years of projected growth is "0", then Net Present Value of Free Cash Flows is growing perpetuity of Free Cash Flow [(FCF*(1+Growth Rate) / (WACC - Growth Rate)

 

3. Taxes are determined by multiplying the Tax Rate times EBIT.

 


Solution details:

Pay using PayPal (No PayPal account Required) or your credit card . All your purchases are securely protected by .
SiteLock

About this Question

STATUS

Answered

QUALITY

Approved

DATE ANSWERED

Sep 13, 2020

EXPERT

Tutor

ANSWER RATING

GET INSTANT HELP/h4>

We have top-notch tutors who can do your essay/homework for you at a reasonable cost and then you can simply use that essay as a template to build your own arguments.

You can also use these solutions:

  • As a reference for in-depth understanding of the subject.
  • As a source of ideas / reasoning for your own research (if properly referenced)
  • For editing and paraphrasing (check your institution's definition of plagiarism and recommended paraphrase).
This we believe is a better way of understanding a problem and makes use of the efficiency of time of the student.

NEW ASSIGNMENT HELP?

Order New Solution. Quick Turnaround

Click on the button below in order to Order for a New, Original and High-Quality Essay Solutions. New orders are original solutions and precise to your writing instruction requirements. Place a New Order using the button below.

WE GUARANTEE, THAT YOUR PAPER WILL BE WRITTEN FROM SCRATCH AND WITHIN A DEADLINE.

Order Now