Question

In: Finance

Colin was recently hired by Coleman Electronics as a junior budget analyst. He is working for...

Colin was recently hired by Coleman Electronics as a junior budget analyst. He is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee.

Colin has a B.S. in accounting from XXX (2015) and passed the CPA exam (2017). He has been in public accounting for several years. During that time he earned an MBA from Seattle U. He would like to be the CFO of a company someday--maybe Coleman Electronics-- and this is an opportunity to get onto that career track and to prove his ability.

As Colin looks over the financial data collected, he is trying to make sense of it all. He already has the most difficult part of the analysis complete -- the estimation of cash flows. Through some internet research and application of finance theory, he has also determined the firm’s beta.

Here is the information that Colin has accumulated so far:

The Capital Budgeting Projects

He must choose one of the four capital budgeting projects listed below:  

Table 1

t

A

B

C

D

0

      (19,000,000)

      (20,000,000)

      (14,000,000)

       (18,000,000)

1

         8,000,000

       11,000,000

         5,700,000

          3,600,000

2

         8,000,000

       10,000,000

         5,700,000

          7,600,000

3

         8,000,000

         8,000,000

         5,700,000

          5,600,000

4

         8,000,000

         4,000,000

         5,700,000

          5,600,000

Risk

Average

High

Low

Average

Table 1 shows the expected after-tax operating cash flows for each project. All projects are expected to have a 4 year life. The projects differ in size (the cost of the initial investment), and their cash flow patterns are different. They also differ in risk as indicated in the above table.

The capital budget is $20 million and the projects are mutually exclusive.

Capital Structures

Coleman Electronics has the following capital structure, which is considered to be optimal:

Debt  

50%

Preferred Equity

10%

Common Equity

40%

100%

   

Cost of Capital

Colin knows that in order to evaluate the projects he will have to determine the cost of capital for each of them. He has been given the following data, which he believes will be relevant to his task.

(1)The firm’s tax rate is 35%.

(2) Coleman Electronics has issued a 10% semi-annual coupon bond with 8 years term to maturity. The current trading price is $990.

(3) The firm has issued some preferred stock which pays an annual 10% dividend of $100 par value, and the current market price is $105.

(4) The firm’s stock is currently selling for $36 per share. Its last dividend (D0) was $3, and dividends are expected to grow at a constant rate of 6%. The current risk free return offered by Treasury security is 2.5%, and the market portfolio’s return is 12%. Coleman Electronics has a beta of 1.2. For the bond-yield-plus-risk-premium approach, the firm uses a risk premium of 3%.

(5) The firm adjusts its project WACC for risk by adding 1.5% to the overall WACC for high-risk projects and subtracting 1.5% for low-risk projects.

Colin knows that Coleman Electronics executives have favored IRR in the past for making their capital budgeting decisions. His professor at Seattle U. said NPV was better than IRR. His textbook says that MIRR is also better than IRR.   He is the new kid on the block and must be prepared to defend his recommendations.

First, however, Colin must finish the analysis and write his report. To help begin, he has formulated the following questions:

  1. What is the firm’s cost of debt?
  1. What is the cost of preferred stock for Coleman Electronics?
  1. Cost of common equity

(1) What is the estimated cost of common equity using the CAPM approach?

(2) What is the estimated cost of common equity using the DCF approach?

(3) What is the estimated cost of common equity using the bond-yield-plus-risk-premium approach?

(4) What is the final estimate for rs?

  1. What is Coleman Electronics’s overall WACC?
  1. Do you think the firm should use the single overall WACC as the hurdle rate for each of its projects? Explain.
  1. What is the WACC for each project? Place your numerical solutions in Table 2.
  1. Calculate all relevant capital budgeting measures for each project, and place your numerical solutions in Table 2.

Table 2

A

B

C

D

WACC

NPV

IRR

MIRR

  1. Comment on the commonly used capital budgeting measures. What is the underlying cause of ranking conflicts? Which criterion is the best one, and why?
  1. Which of the projects are unacceptable and why?
  1. Rank the projects that are acceptable, according to Colin’s criterion of choice.
  1. Which project should Colin recommend and why? Explain why each of the projects not chosen was rejected.

Instructions

1.Your answers should be Word processed, submitted via Canvas.

2.Questions 5, 8, 9, and 11 are discussion questions.

3.Place your numerical solutions in Table 2.

4.Show your steps for calculation questions.

Solutions

Expert Solution

Since the question has multiple parts, I have completed all the parts upto the presentation of Table 2 (WACC, NPV, IRR and MIRR).

_______

Part 1)

The cost of debt can be calculated with the use of Rate function/formula of EXCEL/Financial Calculator. The function/formula for Rate is Rate(Nper,PMT,-PV,FV) where Nper = Period, PMT =Payment (here, Coupon Payment), PV = Present Value and FV = Future Value (here, Bond Face Value).

Here, Nper = 8*2 = 16, PMT = 1,000*10%*1/2 = $50, PV = $990 and FV = $1,000.

Using these values in the above function/formula for Rate, we get,

After-Tax Cost of Debt = Rate(16,50,-990,1000)*2*(1-35%) = 6.62%

______

Part 2)

The cost of preferred stock is determined as below:

Cost of Preferred Stock = Annual Dividend/Current Stock Price*100 = (10%*100)/105*100 = 9.52%

______

Part 3)

The cost of common equity under each method is calculated as below:

1)

CAPM Approach

Cost of Common Equity = Risk Free Rate + Beta*(Market Rate of Return - Risk Free Rate) = 2.5% + 1.2*(12% - 2.5%) = 13.90%

______

DCF Approach

Cost of Common Equity = D0*(1+Growth Rate)/Current Stock Price + Growth Rate = 3*(1+6%)/36 + 6% = 14.83%

______

Bond-Yield-Plus-Risk-Premium

Cost of Common Equity = Bond Yield + Risk Premium = Annual Coupon/Current Bond Price*100 + Risk Premium = (1,000*10%)/990 + 3% = 13.10%

______

Part 4)

The final estimate for rs is arrived as follows:

rs (Final Estimate) = (Cost of Common Equity (CAPM Approach) + Cost of Common Equity (DCF Approach) + Cost of Common Equity (Bond-Yield-Plus-Risk-Premium))/3 = (13.90% + 14.83% + 13.10%)/3 = 13.94%

______

Part 5)

The Coleman Electronics’s overall WACC is calculated as below:

WACC = Proportion of Debt*After-Tax Cost of Debt + Proportion of Preferred Stock*Cost of Preferred Stock + Proportion of Common Equity*Cost of Common Equity

Substituting values in the above formula, we get,

WACC = 50%*6.62% + 10%*9.52% + 40%*13.94% = 9.84%

______

Part 6)

No, the firm shouldn't use the single overall WACC as the hurdle rate for each of its projects. It is because different projects carry different risks. WACC indicates the hurdle rate for an average risk project. Therefore, an adjustment may be required to each project's WACC to indicate/measure the risk associated with the project.

______

Part 7)

The WACC for each project is determined as below:

WACC (Project A) = WACC + Risk Adjustment = 9.84% + 0 = 9.84%

WACC (Project B) = WACC + Risk Adjustment = 9.84% + 1.5% = 11.34%

WACC (Project C) = WACC + Risk Adjustment = 9.84% - 1.5% = 8.34%

WACC (Project D) = WACC + Risk Adjustment = 9.84% + 0 = 9.84%

______

Part 8)

The NPV for each project is calculated as below:

NPV = Cash Flow Year 0 + Cash Flow Year 1/(1+Risk-Adjusted WACC)^1 + Cash Flow Year 2/(1+Risk-Adjusted WACC)^2 + Cash Flow Year 3/(1+Risk-Adjusted WACC)^3 + Cash Flow Year 4/(1+Risk-Adjusted WACC)^4

____

NPV (Project A) = -19,000,000 + 8,000,000/(1+9.84%)^1 + 8,000,000/(1+9.84%)^2 + 8,000,000/(1+9.84%)^3 + 8,000,000/(1+9.84%)^4 = $6,447,004.51

NPV (Project B) = -20,000,000 + 11,000,000/(1+11.34%)^1 + 10,000,000/(1+11.34%)^2 + 8,000,000/(1+11.34%)^3 + 4,000,000/(1+11.34%)^4 = $6,345,370.84

NPV (Project C) = -14,000,000 + 5,700,000/(1+8.34%)^1 + 5,700,000/(1+8.34%)^2 + 5,700,000/(1+8.34%)^3 + 5,700,000/(1+8.34%)^4 = $4,737,122.51

NPV (Project D) = -18,000,000 + 3,600,000/(1+9.84%)^1 + 7,600,000/(1+9.84%)^2 + 5,600,000/(1+9.84%)^3 + 5,600,000/(1+9.84%)^4 = -$350,215.65

______

Part 9)

IRR is the minimum rate of return acceptable from a project. IRR can be calculated with the IRR function/formula of EXCEL. The basic formula for calculating IRR is given as below:

NPV = 0 = Cash Flow Year 0 + Cash Flow Year 1/(1+IRR)^1 + Cash Flow Year 2/(1+IRR)^2 + Cash Flow Year 3/(1+IRR)^3 + Cash Flow Year 4/(1+IRR)^4

IRR is calculated with the use of EXCEL as follows:

where:

IRR (Project A) = IRR(B2:B6) = 24.68%

IRR (Project B) = IRR(C2:C6) = 28.01%

IRR (Project C) = IRR(D2:D6) = 22.82%

IRR (Project D) = IRR(E2:E6) = 8.98%

______

Part 10)

MIRR is calculated with the use of MIRR function/formula of EXCEL which is MIRR(values,finance_rate,reinvest_rate) as below:

where:

MIRR (Project A) = MIRR(B2:B6,9.84%,9.84%) = 18.16%

MIRR (Project B) = MIRR(C2:C6,11.34%,11.34%) = 19.28%

MIRR (Project C) = MIRR(D2:D6,8.34%,8.34%) = 16.53%

MIRR (Project D) = MIRR(E2:E6,9.84%,9.84%) = 9.30%

______

Tabular Representation:

A B C D
WACC 9.84% 11.34% 8.34% 9.84%
NPV $6,447,004.51 $6,345,370.84 $4,737,122.51 -$350,215.65
IRR 24.68% 28.01% 22.82% 8.98%
MIRR 18.16% 19.28% 16.53% 9.30%

_____

Notes:

There can be a difference in final answers for all parts on account of rounding off values.


Related Solutions

Phil was recently hired by Select Network Inc. as a junior budget analyst. He is working...
Phil was recently hired by Select Network Inc. as a junior budget analyst. He is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee.   Phil has a B.S. in accounting from CWU (2007) and passed the CPA exam (2008). He has been in public accounting for 2 years. During that time he earned an MBA from Seattle U. He would like...
Phil was recently hired by Select Network Inc. as a junior budget analyst. He is working...
Phil was recently hired by Select Network Inc. as a junior budget analyst. He is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee.   Phil has a B.S. in accounting from CWU (2007) and passed the CPA exam (2008). He has been in public accounting for 2 years. During that time he earned an MBA from Seattle U. He would like...
Ronald was recently hired by Highland Equipment Inc. as a junior budget analyst. He is working...
Ronald was recently hired by Highland Equipment Inc. as a junior budget analyst. He is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee. Ronald has a B.S. in accounting from CWU (2016) and passed the CPA exam (2018). He has been in public accounting for several years. During that time he earned an MBA from Seattle U. He would like...
Lindsey was recently hired by Swift Ltd. as a junior budget analyst. She is working for...
Lindsey was recently hired by Swift Ltd. as a junior budget analyst. She is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. She must give her analysis and recommendation to the capital budgeting committee. Lindsey has a B.S. in accounting from CWU (2014) and passed the CPA exam (2017). She has been in public accounting for several years. During that time she earned an MBA from Seattle U. She would like to...
FIN 370 AOL ASSIGNMENT CASE ANALYSIS: The Case of the Junior Analyst Name:_______________________ Colin was recently...
FIN 370 AOL ASSIGNMENT CASE ANALYSIS: The Case of the Junior Analyst Name:_______________________ Colin was recently hired by Coleman Electronics as a junior budget analyst. He is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. He must give his analysis and recommendation to the capital budgeting committee. Colin has a B.S. in accounting from CWU (2015) and passed the CPA exam (2017). He has been in public accounting for several years. During...
You were recently hired by a firm as a project analyst.
You were recently hired by a firm as a project analyst. The owner of the firm is unfamiliar with financial analysis and wants to know only what the expected dollar return is per dollar spent on a given project. Which financial method of analysis will provide the information that the owner requests? a. Internal rate of return b. Modified internal rate of return c. Net present value d. Profitability index e. Payback
Assume that you were recently hired as assistant to Jerry Lehman, financial VP of Coleman Technologies....
Assume that you were recently hired as assistant to Jerry Lehman, financial VP of Coleman Technologies. Your first task is to estimate Coleman’s cost of capital. Lehman has provided you with the following data, which he believes is relevant to your task: The firm’s marginal tax rate is 40%. The current price of Coleman’s 12 % coupon, semiannual payment, noncallable bonds with 15 years remaining to maturity is $1,153.72. New bonds would be privately placed with no flotation cost. The...
Assume that you were recently hired as assistant to Jerry Lehman, financial VP of Coleman Technologies....
Assume that you were recently hired as assistant to Jerry Lehman, financial VP of Coleman Technologies. Your first task is to estimate Coleman’s cost of capital. Lehman has provided you with the following data, which he believes is relevant to your task: The firm’s marginal tax rate is 40%. The current price of Coleman’s 12 % coupon, semiannual payment, noncallable bonds with 15 years remaining to maturity is $1,153.72. New bonds would be privately placed with no flotation cost. The...
Assume that you were recently hired at Lilo Plc as a junior accountant and were asked...
Assume that you were recently hired at Lilo Plc as a junior accountant and were asked to help prepare the financial reports for the year ended 31 December 2019. In addition, the head of the accounting department wanted to assess your knowledge about the financial statements, hence, requested you to prepare a report. The company’s trial balance is shown below, together with the guide on the format and content of the report: Debit (£) Credit (£) Sales 72,813 Purchases 35,108...
Hailey just started her first job at Whatcom Co. as a junior budget analyst. She is...
Hailey just started her first job at Whatcom Co. as a junior budget analyst. She is working for the Venture Capital Division and has been given for capital budgeting projects to evaluate. She must give her analysis and recommendation to the capital budgeting committee. Hailey has a B.S. in accounting from WWU (2007) and passed the CPA exam (2008). She has been in public accounting for 2 years. During that time, she earned an MBA from Seattle U. She would...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT