Determine the intrinsic value per share for Steel Dynamics using the DCF model. Must use Thomson Reuters to collect information!!. Add the following sheets to your valuation workbook: -Income Statement -Balance Sheet -Cash Flow Statement -Ratio Anal

20190306004711dcf_stld_homework__2_3.xlsx

Home >Business & Finance homework help >Determine the intrinsic value per share for Steel Dynamics using the DCF model. Must use Thomson Reuters to collect information!!. Add the following sheets to your valuation workbook: -Income Statement -Balance Sheet -Cash Flow Statement -Ratio Anal

WACC

				NAME:
Cost of Capital		Enter the date of your calculation	Calculate the WACC for Steel Dynamics Inc. using the same methodology we covered in class
Enter inputs in yellow cells.			with data from Thomson Reuter's Eikon.
Key Output of Valuation:			On the column C under Suggested Sources in orange cells enter the detailed source as illustrated for cell C10.
	Estimated WACC =	ERROR:#DIV/0!
			What is Steel Dynamics' WACC ?
Inputs to Estimate WACC	Inputs	Suggested Sources
			Show the formulas in the orange section referencing the cells in green.
Enter Current Market Value for Equity			Make the calculations in the turquoise cells.		(3 points)
Current stock price		Thomson Reuters/Overview/
Number of shares common stock outstanding
Market value of common stock	$0
Enter Current Market Value for Long-Term Debt
Estimated value of long-term debt
Enter Current Market Value for Short-Term Debt
Estimated value of short-term debt
Enter Current Market Value for Preferred Stock
Estimated value of lpreferred stock	$0 Author: If you can observe the number of shares of preferred stock and the current price of the firm's preferred stock, then enter the market value for preferred stock here. Otherwise, use the default value, which is the amount of preferred stock shown on the Actual balance sheet.
Estimate Percent of Firm that will be Financed by Long-term Debt
Current percent of firm financed with long-term debt	ERROR:#DIV/0!
Target percent financed with long-term debt=	ERROR:#DIV/0! Author: The default value is the current percent financed with long-term debt, unless the current value is less than 15% or greater than 40%.
Estimate Percent of Firm that will be Financed by Preferred Stock
Current percent of firm financed with preferred stock	ERROR:#DIV/0!
Target percent financed with preferred stock =	ERROR:#DIV/0! Author: The default value is equal to the current value.
Estimate Percent of Firm that will be Financed by Short-term Debt
Current percent of firm financed with short-term debt	ERROR:#DIV/0!
Target percent financed with short-term debt =	ERROR:#DIV/0! Author: The default value is equal to the current value, unless the combined short-term and long-term debt exceed 40%, in which case the short-term debt is set so that the combined rate is 40%.
	Author: If you can observe the market value for short-term debt, then enter it here. Otherwise, use the default value, which is the amount of short-term debt shown on the Actual balance sheet C36+C38+C39.	Author: If you can observe the number of shares of preferred stock and the current price of the firm's preferred stock, then enter the market value for preferred stock here. Otherwise, use the default value, which is the amount of preferred stock shown on the Actual balance sheet.	Estimate Cost of Equity
Beta=
Risk-free rate=
Market risk premium =
Cost of equity=	0.00%
Estimate Cost of Long-term Debt
Bond rating for company's debt
Spread for bond rating
Tax rate=
Cost of long-term debt=	0.00%
After-tax cost of long-term debt=	0.00%
Inputs to Estimate Cost of Preferred Stock
Yield on preferred stock
Current coupon rate (preferred dividend/preferred stock)	0.00%
Cost of preferred stock = Author: This is the yield on any existing preferred stock or the coupon rate on new preferred stock.	Author: The default value is the current percent financed with long-term debt, unless the current value is less than 15% or greater than 40%.	0.00%	Judgment, based on yield on preferred and/or current coupon rate.
Inputs to Estimate Cost of Short-term Debt
Prime rate
	Author: The default is 6%.	Adjustment to prime	0.00% Author: The default is zero.	Judgment, based on risk of company.
Cost of short-term debt Author: This is the prime rate plus the adjustment.	Author: The default value is equal to the current value.	0.00%
After-tax cost of short-term debt	0.00%
Estimated WACC	ERROR:#DIV/0!
Notes:
Use only the recommended sources as directed.

Probl STLD

NAME:

(6 points)

Use the DCF model for the Steel Dynamics to update the valuation using the most recent data available in Thomson Reuters and Bloomberg.

Attach to this workbook: Income Statement, Balance Sheet, Statement of Cash Flows. Also attach other relevant documents such as the Annual report 10-K , Research reports you used. Only if you used them.

On the Steel Dynamics-DCF tab, in the bright yellow section explain how did you choose those inputs corresponding to: Revenue Growth Rate,Operating Profit Margin, depreciation,

Equity based compensation, Deferred Income Taxes, Gains/Losses on PP&E,Capital Expenditure, Terminal EBITDA, Terminal FCF Growth Rate.

Explain how did you choose the Baseline terminal EBITDA multiple. Insert a screenshot of the data to the right (like in the picture to the right).

Explain how did you choose the terminal FCF growth rate. Insert a screeshot of the data.

What is the Implied Share Price?

Would you invest in this stock? Why ?

What is the impact of a simulataneous change in the Terminal EBITDA multiple and the Discount Rate on to the implied share price? Show your sensitivity analysis table.

Steel Dynamics-DCF

Discounted Cash Flow Analysis - Steel Dynamics, Inc.

Ticker

Date

3/5/19

($ in Millions Except Per Share Data)

Exchange

Steel Dynamics, Inc. - DCF Assumptions & Output:

Company Name:

Steel Dynamics, Inc.

Use Multiples Method for TV?

Current Share Price:

PV of Terminal Value:

Effective Tax Rate:

Sum of PV of Free Cash Flows:

Discount Rate (WACC):

Author: Would normally come from WACC sheet we used in class or WACC analysis based on comps

Implied Enterprise Value:

Explain how did you choose

Terminal EBITDA Multiple?

Baseline Terminal EBITDA Multiple:

% of Implied EV from Terminal Value:

ERROR:#DIV/0!

Baseline Terminal Value:

Implied Terminal FCF Growth Rate:

ERROR:#DIV/0!

Less: Net Debt:

Implied Equity Value:

Explain how did you choose

Baseline Terminal FCF Growth Rate:

Diluted Shares Outstanding:

Terminal FCF Growth Rate?

Baseline Terminal Value:

Implied Terminal EBITDA Multiple:

Implied Share Price from DCF:

Premium / (Discount) to Current:

Historical

Projected

Steel Dynamics, Inc. - FCF Projections:

2016

2017

2018

2019

2020

2021

2022

2023

Explain how did you choose those assumptions corresponding to question 1 of Probl-STLD

Revenue:

Revenue Growth Rate:

Operating Income:

Operating Margin:

ERROR:#DIV/0!

Less: Taxes, Excluding Effect of Interest:

Net Operating Profit After Tax (NOPAT):

-0

Adjustments for Non-Cash Charges:

Depreciation & Amortization:

% Revenue:

ERROR:#DIV/0!

Impairment Charges:

Equity-Based Compensation:

% Revenue:

ERROR:#DIV/0!

0.0%

Deferred Income Taxes:

% Book Taxes on Income Statement:

0.0%
Author: Book tax figures from Income Statement on 10-K.

Author: =Tot LT Debt + Current Portion of LT Debt -Cash -Restricted Cash- Convertible debt if it is converted to equity

Author: Book tax figures from Income Statement on 10-K.

(Gain) / Loss on PP&E Disposal:

Total Non-Cash Adjustments:

-0

Changes in Working Capital:

Decrease / (Increase) in Accounts Receivable:

Decrease / (Increase) in Inventory:

Decrease / (Increase) in Other Assets:

Increase / (Decrease) in Accounts Payable:

Increase / (Decrease) in Income Tax Payable:

Increase / (Decrease) in Accrued Expenses:

Net Change in Working Capital:

-0

% Change in Revenue:

% Revenue:

Less: Capital Expenditures:

% Revenue:

ERROR:#DIV/0!

Unlevered Free Cash Flow:

$ -0

FCF6/(WACC-g)

Growth Rate:

N/A

EBITDA:

$ -0

Growth Rate:

N/A

Sensitivity - Implied Share Price from DCF Analysis :

Sensitivity - Implied Share Price from DCF Analysis - :