Question

Create a capital budgeting analysis in Excel.

Please work on an Excel spreadsheet to conduct a capital budgeting analysis for the following project: 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. Tomado 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. 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 cach 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. • 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). Focus x W 29

Answer 1

WACC

WACC = (weight of debt * cost of debt) + (weight of common stock * cost of common stock)

cost of debt = YTM of bond * (1 - tax rate)

YTM is calculated using RATE function in Excel with these inputs :

nper = 6*2 (6 years to maturity with 2 semiannual coupon payments each year)

pmt = 1000 * 6% / 2 (semiannual coupon payment = face value * annual coupon rate / 2. This is a positive figure as it is an inflow to the bondholder)

pv = -906.15 (current bond price. This is a negative figure as it is an outflow to the buyer of the bond)

fv = 1000 (face value of the bond receivable on maturity. This is a positive figure as it is an inflow to the bondholder)

The RATE calculated is the semiannual YTM. To calculate the annual YTM, we multiply by 2. Annual YTM is 8.00%

cost of debt = YTM * (1 - tax rate)

cost of debt = 8.00% * (1 - 40%) ==> 4.80%

cost of equity = (next year dividend / current price per share) + growth rate.

next year dividend = last dividend * (1 + growth rate)

next year dividend = $2 * (1 + 5%) = $2.10

cost of equity = ($2.10 / $20) + 5%

cost of equity = 15.50%

WACC = (weight of debt * cost of debt) + (weight of common stock * cost of common stock)

WACC = (0.50 * 4.80%) + (0.50 * 15.50%)

WACC = 11.75%

Cash flows and NPV analysis

Decrease in contribution of standard trucks due to decline in units sold = decline in number of units sold * (sale price - variable cost)

Decrease in contribution of standard trucks due to decline in sales price = (expected sales if green truck not introduced - decline in number of units sold) * decline in sale price per unit

Operating cash flow (OCF) each year = incremental income after tax + depreciation

profit on sale of equipment at end of year 5 = sale price - book value

book value is zero as the equipment is fully depreciated

after-tax salvage value = salvage value - tax on profit on sale of equipment

NPV is calculated using NPV function in Excel

NPV is $1,802,550

IRR is calculated using IRR function in Excel

IRR is 13.13%