Question

Please solve all answers on Excel and show step by step how you get the WACC answer.

  Tornado Motors is a major producer of sport and utility trucks. It is a family owned company,

started by Jane Biscayne in 1935, at the height of the Great Depression. Today the firm produces 3 lines

of trucks. These include a standard, no-frills short bed pickup truck (Model A), a mid-size version

(Model B

)

and a larger, heavy-duty work truck (Model C). Janet Biscayne, the founder’s grand-daughter

is the current CEO. She has asked you to provide a financial analysis of an idea that originated in the

company’s engineering department. The engineers have developed a “green” version of the smallest of

Tornado’s trucks – Model A. The green version would be made of recycled materials and would run on a

hybrid engine. Based on the results of a large market analysis, the CEO believes that there would be a

sizable demand for the green version of the Model A truck. Tornado pays an average tax rate of 40%. It

requires a 15% return on investments of this character.

The engineering department estimates that it would cost $40 million to purchase the additional

equipment necessary to convert an existing facility to production of the green trucks. This equipment is

expected to last 5 years and could be sold as salvage for $2 million at the end of its useful life. The full

cost of the equipment will be depreciated on a straight-line basis over 5 years. The marketing department

estimates that with a sticker price of $35,000 sales of the green trucks would be 6,000, 7,500, 8,000,

8,500 and 8,700 in each of the next five years. The green trucks are expected to cost $29,500 each to

produce. In addition, fixed costs associated with the production of the new trucks would be $15 million

per year and the truck production would require an additional initial one-time investment in Net Working

Capital of $5 million.

In addition, you are given the following information:

o

The company spent $4 million developing a prototype for the hybrid engine that would

be used in the green truck. In addition, the company spent $5 million on a marketing

study to evaluate demand for the green version.

o

The marketing department warns that it expects that production of the green trucks would

adversely affect sales of the current standard version of the Model A trucks as some of

the buyers who would have bought the standard model will be attracted to the green

version. The standard version costs $24,500 to produce and sells for $29,000. If the

green version is not introduced, the company expects to sell 9,000 units per year for the

next five years. However, with competition from the green version, annual sales of the

standard version are expected to decline by 500, 700, 1,000, 1,200 and 1,500 units in

each of the next 5 years. Furthermore, marketing expects that the company will have to

lower the price by $1,000 in order to achieve this level of sales.

o

The company’s stock is currently sold at $20.00 per share. It paid $2 dividend per share

last year. It is expected that the dividend growth rate will be 5% each year in the future.

The company’s bond has a 6-year bond outstanding. It is currently priced at $906.15. The

bond pays 6% coupon semi-annually. The par value of the bond is $1,000. The

company’s market value debt-equity ratio is 0.5 (D/E=0.5)

Answer 1

Calculation of WACC

Cost of equity

Using constant growth model of dividends to estimate cost of equity,

ke=(Next Dividend/Current market price)+Growth rate (of dividends)

where, Next dividend=D0*(1+g)=2*(1+0.05)=2.1

ie.(( 2*1.05)/20)+5%=

15.50%

After-tax cost of bond

Using the formula to find the present value,ie.current market Price of bonds,

Price=PV of its future cash flows=PV of all its future coupon cash flows+PV of face value to be received at maturity----both discounted at the effective cost of debt (which we need to find out)

Price of the bond=(Pmt.*(1-(1+r)^-n)/r)+(FV/(1+r)^n)

where, price is given as $ 906.15

Pmt.= The semi-annual coupon in $ , ie. 1000*6%/2= $ 30

r= the effective rate of interest or YTM --that we need to find----??

n= no.of coupon period still to maturity, ie. 6 yrs. *2= 12

FV= face value, ie. $ 1000

So, plugging in these values in the formula,

906.15=(30*(1-(1+r)^-12)/r)+(1000/(1+r)^12)

Solving for r, we get the semi-annual before-tax cost/Yield as

3.9999%

Now, the annual before-tax yield/cost=

(1+3.9999% )^2-1=

8.1598%

so, the annual After-tax cost of the bond=

Before-tax cost*(1-Tax Rate)

8.1598%*(1-40%)=

4.90%

Now, the

WACC= (Wtd.*kd)+(wt.e*ke)

ie.(0.5/1.5*4.90%)+(1/1.5*15.5%)=

11.97%

NPV analysis
Year	0	1	2	3	4	5
1.Cost of addl. Eqpt.	-40000000
2.NWC reqd. & recovered	-5000000					5000000
3.After-tax salvage(2000000*(1-40%))						1200000
4.Sales volume		6000	7500	8000	8500	8700
5.Sales $ at $ 35000 each		210000000	262500000	280000000	297500000	304500000
6.Cost to produce at $ 29500 each		-177000000	-221250000	-236000000	-250750000	-256650000
7.Fixed costs		-15000000	-15000000	-15000000	-15000000	-15000000
8.Depreciation(40 mln./5)		-8000000	-8000000	-8000000	-8000000	-8000000
9.Cannibaliastion effect(due to redn. In contn.) (separate table)		-10750000	-11450000	-12500000	-13200000	-14250000
10.Incremental EBT (sum 5 to 9)		-750000	6800000	8500000	10550000	10600000
11.Incl.Tax at 40%(10* 40%)		300000	-2720000	-3400000	-4220000	-4240000
12.Incl. NOPAT (10+11)		-450000	4080000	5100000	6330000	6360000
13.Add Back: depn. (same row 8)		8000000	8000000	8000000	8000000	8000000
14.Incl.Opg. cash flow(12+13)		7550000	12080000	13100000	14330000	14360000
15.Total annual FCFs(1+2+3+14)	-45000000	7550000	12080000	13100000	14330000	20560000
16.PV F at 11.97%(1/1.1197^Yr.n)	1	0.89310	0.79762	0.71235	0.63620	0.56819
17.PV at 11.97%(15*16)	-45000000	6742877.56	9635263.1	9331818.02	9116738.07	11681933.2
18.NPV (sum of row 17 )	1508630
19.IRR(of FCF row)	13.13%

The project Is RECOMMENDED as it returns POSITIVE NPV when the projected CFs are discounted at the WACC of 11.97%

As it is given in the question that
the CEO requires a 15% return on investments of this character.
the NPV is calculated as below
15.Total annual FCFs(1+2+3+14)	-45000000	7550000	12080000	13100000	14330000	20560000
16.PV F at 11.97%(1/1.1197^Yr.n)	1	0.86957	0.75614	0.65752	0.57175	0.49718
17.PV at 11.97%(15*16)	-45000000	6565217.39	9134215.5	8613462.64	8193224.01	10221953.7
18.NPV (sum of row 17)	-2271927

Workings:

Cannibalisation effect
Year	0	1	2	3	4	5
1.Normal sales volume		9000	9000	9000	9000	9000
2.Sales $ at 29000/unit		261000000	261000000	261000000	261000000	261000000
3.V.C( 1* 24500/each)		-220500000	-220500000	-220500000	-220500000	-220500000
4.Contribution(2+3)		40500000	40500000	40500000	40500000	40500000
5.Reduction/cannibalised units		500	700	1000	1200	1500
6.Reduced volume(1-5)		8500	8300	8000	7800	7500
7.Sales $ at 28000/unit		238000000	232400000	224000000	218400000	210000000
8.V.C (6* 24500/each)		-208250000	-203350000	-196000000	-191100000	-183750000
9.Contribution(7+8)		29750000	29050000	28000000	27300000	26250000
10.Reduction in contn.(4-9)		10750000	11450000	12500000	13200000	14250000