USE EXCEL FOR CALCULATIONS AND SHOW FORMULAS

Question 4 (based on Chapter 12) Angola Superheros, Inc. is considering launching a production of a new superhero toy. The production and sales are expected to last 4 years. The project would require a new machine, with a cost of $1,000,000. The machine would be depreciated using the straight-line method over 8 years to zero value after year 8. After 4 years, at the end of the project, the machine is expected to be sold for $30,000. The company estimates that 40,000 toys would be sold annually, at a selling price of $20 per unit. The variable cost of producing each unit is estimated to be $5. In addition, the company will have to pay fixed costs equal to $35,000 each year. The relevant cost of capital is 14%, and the company faces a 25% marginal tax rate. a. Compute the project's cash flows in years 0-4, calculate the net present worth of the project and its internal rate of return, and determine whether the project should be accepted. Explain your answer. b. Run a Monte Carlo simulation, varying the variable cost per unit, the annual fixed cost, and the number of units sold. Assume that the variable cost per unit has a mean of $5 and standard deviation of $0.20, that the annual fixed cost has a mean of $35,000 and standard deviation of $2,000, and that the number of units sold annually has a mean of 40,000 and standard deviation of 1,500, and that all three variables follow normal distribution. Compute the present worth of the project for 100 simulation runs. Based on the simulation results, compute the average net present worth and the threshold for the 5% of worst possible outcomes. Comment on the of the project's net present worth, and whether the project should be accepted. C. Use the data table function to construct a table showing the net present worth of the project, while varying the selling price per unit and the cost of capital. Use the following selling prices in your table: 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, and 25. Use the following cost of capital in your table: 9%, 10%, 11%, 12%, 13%, 14%, 15%, 16%, 17%, 18%, and 19%. Comment on the sensitivity of the project's present worth to the cost of capital and the selling price per unit. Question 4 (based on Chapter 12) Angola Superheros, Inc. is considering launching a production of a new superhero toy. The production and sales are expected to last 4 years. The project would require a new machine, with a cost of $1,000,000. The machine would be depreciated using the straight-line method over 8 years to zero value after year 8. After 4 years, at the end of the project, the machine is expected to be sold for $30,000. The company estimates that 40,000 toys would be sold annually, at a selling price of $20 per unit. The variable cost of producing each unit is estimated to be $5. In addition, the company will have to pay fixed costs equal to $35,000 each year. The relevant cost of capital is 14%, and the company faces a 25% marginal tax rate. a. Compute the project's cash flows in years 0-4, calculate the net present worth of the project and its internal rate of return, and determine whether the project should be accepted. Explain your answer. b. Run a Monte Carlo simulation, varying the variable cost per unit, the annual fixed cost, and the number of units sold. Assume that the variable cost per unit has a mean of $5 and standard deviation of $0.20, that the annual fixed cost has a mean of $35,000 and standard deviation of $2,000, and that the number of units sold annually has a mean of 40,000 and standard deviation of 1,500, and that all three variables follow normal distribution. Compute the present worth of the project for 100 simulation runs. Based on the simulation results, compute the average net present worth and the threshold for the 5% of worst possible outcomes. Comment on the of the project's net present worth, and whether the project should be accepted. C. Use the data table function to construct a table showing the net present worth of the project, while varying the selling price per unit and the cost of capital. Use the following selling prices in your table: 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, and 25. Use the following cost of capital in your table: 9%, 10%, 11%, 12%, 13%, 14%, 15%, 16%, 17%, 18%, and 19%. Comment on the sensitivity of the project's present worth to the cost of capital and the selling price per unit