Question
PLEASE SOLVE IN EXCEL :) SHOW ANSW AND FORMULA;) Year Unit Sales 1 84,000 2 98,000 3 113,000 4 106,000 5 79,000 Production of the
PLEASE SOLVE IN EXCEL :) SHOW ANSW AND FORMULA;)
Year Unit Sales 1 84,000 2 98,000 3 113,000 4 106,000 5 79,000
Production of the implants will require $1,500,000 in net working capital to start and additional net working capital investments each year equal to 15 percent of the projected sales increase for the following year. Total fixed costs are $3,400,000 per year, variable production costs are $265 per unit, and the units are priced at $395 each. The equipment needed to begin production has an installed cost of $17,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 and has a required return on all its projects of 18 percent. Based on these preliminary project estimates, what is the NPV of the project? What is the IRR?
Help Me solve it... can you please provide the formulas and answer Thank You!!
| Year 1 unit sales | 84,000 | ||||||||
| Year 2 unit sales | 98,000 | ||||||||
| Year 3 unit sales | 113,000 | ||||||||
| Year 4 unit sales | 106,000 | ||||||||
| Year 5 unit sales | 79,000 | ||||||||
| Initial NWC | $ 1,500,000 | ||||||||
| Additional NWC/year | 15% | ||||||||
| Fixed costs | $ 3,400,000 | ||||||||
| Variable cost per unit | $ 265 | ||||||||
| Unit price | $ 395 | ||||||||
| Equipment cost | $ 17,000,000 | ||||||||
| Salvage value (% of price) | 20% | ||||||||
| Tax rate | 35% | ||||||||
| Required return | 18% | ||||||||
| MACRS depreciation | 14.29% | ||||||||
| 24.49% | |||||||||
| 17.49% | |||||||||
| 12.49% | |||||||||
| 8.93% | |||||||||
| Output area: | |||||||||
| Requirement: | |||||||||
| Year | 0 | 1 | 2 | 3 | 4 | 5 | |||
| NWC | $ | ||||||||
| Ending book value | $ | ||||||||
| Sales | $ | ||||||||
| Variable costs | |||||||||
| Fixed costs | |||||||||
| Depreciation | |||||||||
| EBIT | $ | ||||||||
| Taxes | |||||||||
| Net income | $ | ||||||||
| Depreciation | |||||||||
| Operating cash flow | $ | ||||||||
| Net cash flows | |||||||||
| Operating cash flow | $ - | $ | |||||||
| Change in NWC | |||||||||
| Capital spending | - | - | - | - | |||||
| Total cash flow | $ | ||||||||
| Cumulative cash flow | $ | ||||||||
| Payback calculation | |||||||||
| Net present value | |||||||||
| Internal rate of return | |||||||||
| Payback period | |||||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started