Spreadsheet Exercise

The purpose of this part of the assignment is to use the tools we covered throughout the course to determine if a project is desirable. Youll begin by constructing a proforma income statement using the information given to estimate the cash flows of the project. After youve done that, youll estimate the cost of capital and finally, youll use the tools of capital budgeting, NPV, IRR, etc. to determine if the project is desirable.

Aguilera Acoustics, Inc. (AAI), projects unit sales for a new seven-octave voice emulation implant as follows:

Year Unit Sales

1 105,000

2 227,000

3 125,000

4 108,000

5 94,000

Total fixed costs are $1,500,000 per year, variable production costs are 80% of sales revenues, and the units are priced at $345 each.

The equipment needed to begin production has an installed cost of $25,000,000.Because the implants are intended for professional singers, this equipment is considered industrial machinery and thus qualifies as seven-year MACRS property.In five years, this equipment can be sold for about 20 percent of its acquisition cost.AAI is in the 35 percent marginal tax bracket.Also, assume that the firm must make a $5 million investment at the beginning of the project (period 0) and will recover the working capital once the project ends.

Construct a pro-forma income statement to estimate operating cash flows over the life of the project.

I have created video tutorials on how to create a pro forma financial statement.I have changed some of the numbers so it is not exactly like the videos Ive provided.

Proforma Template http://youtu.be/QaEjEDAArVM

Depreciationhttp://www.youtube.com/watch?v=9wk0aWwduoY&feature=youtu.be

Proforma income statement videohttp://www.youtube.com/watch?v=IQRlXiXBjcE&feature=youtu.be

Below is information the firms capital structure.Use the information given to compute the weighted average cost of capital.Do this in the same spreadsheet that you computed the operating cash flows.

Book Value of Debt: $1,500,000,000

Market Value of Debt: $2,750,000,000

Book Value of Equity: $1,500,000,000

Market Value of Equity: $3,750,000,000

Beta: 1.25

Risk free rate: 5.25%

Expected return on the market: 14.25%

YTM on AAIs debt: 8.75%

Tax rate: 35%

Two videos for doing capital budgeting using the proforma income statement.They are pretty much the same, but you may find one more helpful than the other.

https://www.youtube.com/watch?v=abk-LknKCM8 (This one uses MACRS depreciation)

https://www.youtube.com/watch?v=5M8yLNWxTWk (This one probably goes into a little more detail)

a.Using the information from the previous parts of this assignment, compute the NPV and IRR for this project.

b.Find the NPV and IRR if AAI can only sell the product for $325.

c.Find the NPV and IRR if the variable costs fall to 78% and price is $345.