I need for this project to be done on Excel as an attachment that I can save to my computer.
hw___13_help.docx
Unformatted Attachment Preview
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. You’ll begin by constructing
a proforma income statement using the information given to estimate the cash flows
of the project. After you’ve done that, you’ll estimate the cost of capital and finally,
you’ll 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
1
2
3
4
5
Unit Sales
105,000
227,000
125,000
108,000
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 I’ve provided.
Proforma Template http://youtu.be/QaEjEDAArVM
Depreciation http://www.youtube.com/watch?v=9wk0aWwduoY&feature=youtu.be
Proforma income
statement video http://www.youtube.com/watch?v=IQRlXiXBjcE&feature=youtu.be
Below is information the firm’s 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
Market Value of Debt
Book Value of Equity
Market Value of Equity
Beta
Risk free rate
Expected return on the market
YTM on AAI’s debt
Tax rate
$1,500,000,000
$2,750,000,000
$1,500,000,000
$3,750,000,000
1.25
5.25%
14.25%
8.75%
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.
…
Purchase answer to see full
attachment
Recent Comments