Description
This assignment should be completed in MS Excel There are four excel sheets attached.I have to upload the excel sheet that you will use once you accept the assignment.
Unformatted Attachment Preview
Your Company
Income Statement
For the Year Ended Dec. 31 2018
2018
Sales
3,074,000
Cost of Goods Sold
2,088,000
Gross Profit
986,000
Selling and G&A Expenses
294,000
Fixed Expenses
35,000
Depreciation Expense
239,000
EBIT
418,000
Interest Expense
93,000
Earnings Before Taxes
325,000
Taxes
94,000
Net Income
231,000
Tax Rate
28.92%
Your
Balan
As of D
Your Company
Balance Sheet
As of Dec. 31 2018
Assets
Cash
Accounts Receivable
Inventories
Total Current Assets
Gross Fixed Assets
Accumulated Depreciation
Net Fixed Assets
Total Assets
Liabilities and Owners’ Equity
Accounts Payable
Short-term Notes Payable
Accrued Expenses
Total Current Liabilities
Long-term Debt
Total Liabilities
Common Stock
Retained Earnings
Total Shareholder’s Equity
Total Liabilities and Owners’ Equity
2018
Ratios
Current Ratio
Quick Ratio
Inventory Turnover Ratio
Accounts Receivable Turnover Ratio
Average Collection Period
Fixed Asset Turnover
Total Asset Turnover
Total Debt Ratio
Long-Term Debt Ratio
LTD to Total Capitalization
Debt to Equity
Long-Term Debt to Equity
Gross Profit Margin
Operating Proft Margin
Net Proft Margin
Return on Total Assets
Return on Equity
Return on Common Equity
Your company Comparable Company
Analysis
Homework for Chapter 11: Problem # 2 in the text (Chapter 11)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 11_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Black Diamond, Inc., a manufacturer of carbon and graphite products for the
aerospace and transportation industries, is considering several funding alternatives
for an investment project. To finance the project, the company can sell 1,000 15year bonds with a $1,000 face value, 7% coupon rate. The bonds require an
average discount of $50 per bond and flotation costs of $40 per bond when being
sold. The company can also sell 5,000 shares of preferred stock that will pay a $2
dividend per share at a price of $40 per share. The cost of issuing and selling
preferred stocks is expected to be $5 per share. To calculate the cost of common
stock, the company uses the dividend discount model. The firm just paid a
dividend of $3 per common share. The company expects this dividend to grow at a
constant rate of 3% per year indefinitely. The flotation costs for issuing new
common shares are 7%. The company plans to sell 10,000 shares at a price of $50
per share. The company’s tax rate is 40%.
a) Calculate the company’s after-tax cost of long-term debt.
b) Calculate the Company’s cost of preferred equity.
c) Calculate the company’s cost of common equity.
d) Calculate the company’s weighted average cost of capital.
e) What is the company’s weighted average cost of capital without flotation costs?
WACC WITH FOLTATION COSTS
Source
Debt
Preferred
Common
Totals
Price
Units
Total Market Value
Weight
After-tax Cost
(a)
(b)
(c)
WACC =
(d)
Additional Bond Data
Additional Preferred Data
Additional Common Data
Tax Rate
40% Dividend
$2.00 Dividend 0
$3.00
Coupon Rate
7% Flotation
$5 Growth Rate
3%
Face Value
$1,000
Flotation
7%
Maturity
15
Flotation
$40
WACC WITHOUT FLOTATION COSTS
(e)
Source
Debt
Preferred
Common
Price
Units
Total Market Value
Weight
After-tax Cost
Totals
WACC =
Additional Bond Data
Additional Preferred Data
Additional Common Data
Tax Rate
40% Dividend
$2.00 Dividend 0
$3.00
Coupon Rate
7% Flotation
$5 Growth Rate
3%
Face Value
$1,000
Flotation
7%
Maturity
15
Flotation
$40
Homework for Chapter 12: Problem # 2 in the text (Chapter 12)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 12_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Tough Steel, Inc. is a processor of stainless steel products. The firm is considering replacing
an old stainless steel tube-making machine for a more cost-effective machine that can meet
the firm’s quality standards.
The old machine was acquired 2 years ago at an installed cost of $500,000. It has been
depreciated under the MACRS’s 5-year recovery period, and has a remaining economic
life of 5 years. It can be sold today for $350,000 before taxes, but if the firm decides to
keep it, it can be sold for $100,000 before taxes at the end of year 5.
The first option is Machine A, which can be purchased for $600,000, but will require
$30,000 in installation costs. This machine would be depreciated under the MACRS’s 3year recovery period. At the end of its economic life, the machine will have a salvage
value of $350,000 before taxes. This machine would require an investment in net working
capital of $100,000.
The second option is Machine B, which can be purchased for $550,000, but requires
$20,000 in installation costs. This machine would be depreciated under the MACRS’s 5year recovery period. At the end of its economic life, the machine would have a salvage
value of $330,000 before taxes. This machine requires no investment in net working
capital.
The firm has estimated the following EBIT for all three machines:
EBIT
Year Old machine Machine A Machine B
1
$90,000
$90,000
$120,000
2
$90,000
$10,000
$20,000
3
$120,000
$150,000
$120,000
4
$150,000
$230,000
$200,000
5
$150,000
$270,000
$200,000
The firm’s WACC is 14% and its tax rate is 40%.
a) Calculate the following cash flows for the old machine, machine A, and machine B:
initial investment,
annual after-tax cash flows for each year, and
the terminal cash flow.
b) Determine which machine is more profitable for the company based on
the payback period,
discounted payback period,
net present value,
profitability index,
internal rate of return, and
modified internal rate of return.
Tough Steel, Inc.
Replacement Analysis
Old Machine Machine A
Machine B
Price
$500,000
$600,000
$550,000
Installation Costs
$0
$30,000
$20,000
Original Life
7 years
5 years
5 years
Current Life
5 years
5 years
5 years
Salvage Value @ end of economic life
$100,000
$350,000
$330,000
Current Salvage Value
$350,000
Net Working Capital Investment
$0
$100,000
$0
MACRS’s recovery period
5 years
3 years
5 years
Tax Rate
40.00%
Required Return
14.00%
EBIT:
Year 0
Year 1
Year 2
Year 3
Old Machine
$90,000
$90,000 $120,000
Machine A
$90,000
$10,000 $150,000
Machine B
$120,000
$20,000 $120,000
MACRS Calculations
Year 0
Year 1
Year 2
Year 3
Old Machine
Machine A
Machine B
Operating Cash Flows
(a) Calculate the Cash Flows
Old Machine
Machine A
Machine B
Incremental Cash Flows
Machine A
Machine B
Initial Outlay & Terminal Cash Flow
Machine A
Machine B
ATCF
Machine A
Machine B
Payback Period
Discounted Payback
Net Present Value (NPV)
Profitability Index (PI)
Internal Rate of Return (IRR)
MIRR
(b) Determine which machine is more profitable for th
Machine A
Machine B Best Choice?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Machine A
#DIV/0!
#DIV/0!
#DIV/0!
#NUM!
#NUM!
#NUM!
#DIV/0!
#DIV/0!
#DIV/0!
Year 4
Year 5
$150,000 $150,000
$230,000 $270,000
$200,000 $200,000
alculations
Year 4
(a) Calculate the Cash Flows
Year 5
rmine which machine is more profitable for the company?
Homework for Chapter 12: Problem # 2 in the text (Chapter 12)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 12_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Tough Steel, Inc. is a processor of stainless steel products. The firm is considering replacing
an old stainless steel tube-making machine for a more cost-effective machine that can meet
the firm’s quality standards.
The old machine was acquired 2 years ago at an installed cost of $500,000. It has been
depreciated under the MACRS’s 5-year recovery period, and has a remaining economic
life of 5 years. It can be sold today for $350,000 before taxes, but if the firm decides to
keep it, it can be sold for $100,000 before taxes at the end of year 5.
The first option is Machine A, which can be purchased for $600,000, but will require
$30,000 in installation costs. This machine would be depreciated under the MACRS’s 3year recovery period. At the end of its economic life, the machine will have a salvage
value of $350,000 before taxes. This machine would require an investment in net working
capital of $100,000.
The second option is Machine B, which can be purchased for $550,000, but requires
$20,000 in installation costs. This machine would be depreciated under the MACRS’s 5year recovery period. At the end of its economic life, the machine would have a salvage
value of $330,000 before taxes. This machine requires no investment in net working
capital.
The firm has estimated the following EBIT for all three machines:
EBIT
Year Old machine Machine A Machine B
1
$90,000
$90,000
$120,000
2
$90,000
$10,000
$20,000
3
$120,000
$150,000
$120,000
4
$150,000
$230,000
$200,000
5
$150,000
$270,000
$200,000
The firm’s WACC is 14% and its tax rate is 40%.
a) Calculate the following cash flows for the old machine, machine A, and machine B:
initial investment,
annual after-tax cash flows for each year, and
the terminal cash flow.
b) Determine which machine is more profitable for the company based on
the payback period,
discounted payback period,
net present value,
profitability index,
internal rate of return, and
modified internal rate of return.
Tough Steel, Inc.
Replacement Analysis
Old Machine Machine A
Machine B
Price
$500,000
$600,000
$550,000
Installation Costs
$0
$30,000
$20,000
Original Life
7 years
5 years
5 years
Current Life
5 years
5 years
5 years
Salvage Value @ end of economic life
$100,000
$350,000
$330,000
Current Salvage Value
$350,000
Net Working Capital Investment
$0
$100,000
$0
MACRS’s recovery period
5 years
3 years
5 years
Tax Rate
40.00%
Required Return
14.00%
EBIT:
Year 0
Year 1
Year 2
Year 3
Old Machine
$90,000
$90,000 $120,000
Machine A
$90,000
$10,000 $150,000
Machine B
$120,000
$20,000 $120,000
MACRS Calculations
Year 0
Year 1
Year 2
Year 3
Old Machine
Machine A
Machine B
Operating Cash Flows
(a) Calculate the Cash Flows
Old Machine
Machine A
Machine B
Incremental Cash Flows
Machine A
Machine B
Initial Outlay & Terminal Cash Flow
Machine A
Machine B
ATCF
Machine A
Machine B
Payback Period
Discounted Payback
Net Present Value (NPV)
Profitability Index (PI)
Internal Rate of Return (IRR)
MIRR
(b) Determine which machine is more profitable for th
Machine A
Machine B Best Choice?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
Machine A
#DIV/0!
#DIV/0!
#DIV/0!
#NUM!
#NUM!
#NUM!
#DIV/0!
#DIV/0!
#DIV/0!
Year 4
Year 5
$150,000 $150,000
$230,000 $270,000
$200,000 $200,000
alculations
Year 4
(a) Calculate the Cash Flows
Year 5
rmine which machine is more profitable for the company?
Homework for Chapter 13: Problem # 1 in the text (Chapter 13)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 13_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Tebow Endeavors Inc. is evaluating two mutually exclusive investment
projects. The firm has estimated the following NPVs, IRRs, and PIs after
performing scenario analysis using the following probabilities for each
scenario:
Scenario Probability
NPVI
PII
IRRI
NPVII
PIII
1
5%
$1,259,058.48 1.68 36.30% $1,009,622.33 1.34
2
10%
$959,508.26 1.52 31.55%
$783,351.86 1.26
3
15%
$659,958.04 1.36 26.64%
$557,081.39 1.19
4
40%
$360,407.82 1.19 21.52%
$330,810.92 1.11
5
15%
$60,857.60 1.03 16.14%
$104,540.45 1.03
6
10%
($238,692.62) 0.87 10.38% ($121,730.02) 0.96
7
5%
($538,242.84) 0.71 4.10% ($348,000.49) 0.88
IRRII
27.19%
24.51%
21.81%
19.07%
16.29%
13.48%
10.62%
The financial staff is divided about which project is more convenient to
accept: I or II.
a) Determine the expected NPV, PI, and IRR for both projects. Which project
should be accepted based only on these results?
b) Determine the variance and standard deviation of the NPVs, PI, and IRR for
both projects. Which project appears to be riskier?
c) Determine the coefficient of variation of the NPV, PI, and IRR for both
projects. Which project appears to be riskier based on these results only?
d) Calculate the probability of a negative NPV, a PI less than one, and an IRR less
than the firm’s WACC of 15% for both projects.
e) Based on your results on parts (a), (b), (c), and (d), which project should be
accepted?
Homework for Chapter 11: Problem # 2 in the text (Chapter 11)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 11_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Black Diamond, Inc., a manufacturer of carbon and graphite products for the
aerospace and transportation industries, is considering several funding alternatives
for an investment project. To finance the project, the company can sell 1,000 15year bonds with a $1,000 face value, 7% coupon rate. The bonds require an
average discount of $50 per bond and flotation costs of $40 per bond when being
sold. The company can also sell 5,000 shares of preferred stock that will pay a $2
dividend per share at a price of $40 per share. The cost of issuing and selling
preferred stocks is expected to be $5 per share. To calculate the cost of common
stock, the company uses the dividend discount model. The firm just paid a
dividend of $3 per common share. The company expects this dividend to grow at a
constant rate of 3% per year indefinitely. The flotation costs for issuing new
common shares are 7%. The company plans to sell 10,000 shares at a price of $50
per share. The company’s tax rate is 40%.
a) Calculate the company’s after-tax cost of long-term debt.
b) Calculate the Company’s cost of preferred equity.
c) Calculate the company’s cost of common equity.
d) Calculate the company’s weighted average cost of capital.
e) What is the company’s weighted average cost of capital without flotation costs?
Homework for Chapter 12: Problem # 2 in the text (Chapter 12)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 12_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Tough Steel, Inc. is a processor of stainless steel products. The firm is considering replacing
an old stainless steel tube-making machine for a more cost-effective machine that can meet
the firm’s quality standards.
The old machine was acquired 2 years ago at an installed cost of $500,000. It has been
depreciated under the MACRS’s 5-year recovery period, and has a remaining economic
life of 5 years. It can be sold today for $350,000 before taxes, but if the firm decides to
keep it, it can be sold for $100,000 before taxes at the end of year 5.
The first option is Machine A, which can be purchased for $600,000, but will require
$30,000 in installation costs. This machine would be depreciated under the MACRS’s 3year recovery period. At the end of its economic life, the machine will have a salvage
value of $350,000 before taxes. This machine would require an investment in net working
capital of $100,000.
The second option is Machine B, which can be purchased for $550,000, but requires
$20,000 in installation costs. This machine would be depreciated under the MACRS’s 5year recovery period. At the end of its economic life, the machine would have a salvage
value of $330,000 before taxes. This machine requires no investment in net working
capital.
The firm has estimated the following EBIT for all three machines:
EBIT
Year Old machine Machine A Machine B
1
$90,000
$90,000
$120,000
2
$90,000
$10,000
$20,000
3
$120,000
$150,000
$120,000
4
$150,000
$230,000
$200,000
5
$150,000
$270,000
$200,000
The firm’s WACC is 14% and its tax rate is 40%.
a) Calculate the following cash flows for the old machine, machine A, and machine B:
initial investment,
annual after-tax cash flows for each year, and
the terminal cash flow.
b) Determine which machine is more profitable for the company based on
the payback period,
discounted payback period,
net present value,
profitability index,
internal rate of return, and
modified internal rate of return.
Homework for Chapter 13: Problem # 1 in the text (Chapter 13)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 13_Excel” TO SOLVE THE FOLLOWING PROBLEM.
Tebow Endeavors Inc. is evaluating two mutually exclusive investment
projects. The firm has estimated the following NPVs, IRRs, and PIs after
performing scenario analysis using the following probabilities for each
scenario:
Scenario Probability
NPVI
PII
IRRI
NPVII
PIII
1
5%
$1,259,058.48 1.68 36.30% $1,009,622.33 1.34
2
10%
$959,508.26 1.52 31.55%
$783,351.86 1.26
3
15%
$659,958.04 1.36 26.64%
$557,081.39 1.19
4
40%
$360,407.82 1.19 21.52%
$330,810.92 1.11
5
15%
$60,857.60 1.03 16.14%
$104,540.45 1.03
6
10%
($238,692.62) 0.87 10.38% ($121,730.02) 0.96
7
5%
($538,242.84) 0.71 4.10% ($348,000.49) 0.88
IRRII
27.19%
24.51%
21.81%
19.07%
16.29%
13.48%
10.62%
The financial staff is divided about which project is more convenient to
accept: I or II.
a) Determine the expected NPV, PI, and IRR for both projects. Which project
should be accepted based only on these results?
b) Determine the variance and standard deviation of the NPVs, PI, and IRR for
both projects. Which project appears to be riskier?
c) Determine the coefficient of variation of the NPV, PI, and IRR for both
projects. Which project appears to be riskier based on these results only?
d) Calculate the probability of a negative NPV, a PI less than one, and an IRR less
than the firm’s WACC of 15% for both projects.
e) Based on your results on parts (a), (b), (c), and (d), which project should be
accepted?
Homework for Chapter 14: Problem # 1 in the text (Chapter 14)
NOTE: PLEASE USE THE ATTACHED EXCEL FILE TITLED
“Homework for Chapter 14_Excel” TO SOLVE THE FOLLOWING PROBLEM.
The annual returns of three stocks for the past seven years are given in the
following table:
Year
2011
2012
2013
2014
2015
2016
2017
Stock
X
3.00%
9.00%
12.00%
19.00%
23.00%
5.00%
10.00%
Stock
Y
14.00%
7.00%
19.00%
13.00%
-7.00%
-3.00%
9.00%
Stock Z
19.00%
11.00%
5.00%
12.00%
11.00%
7.00%
14.00%
a) Determine the average return and the standard deviation of returns for each
stock. Which stock has the highest expected return and which one has the
highest risk?
b) Determine the correlation coefficient and the covariance between each pair of
stocks.
c) Determine the average return and the standard deviation of returns of equally
weighted portfolios consisting of two stocks (XY, YZ, and XZ) and three
(XYZ) stocks. How do the returns and standard deviations of the portfolios
compare to those of the individual stocks?
d) Create a chart that shows how the standard deviation of the two-stock portfolios
changes as the weight of one of the stocks changes.
e) Use the Solver to determine the minimum standard deviation that could be
obtained by combining each pair of two stocks, and also all three stocks into a
single portfolio.
Purchase answer to see full
attachment
