please solve in excel and show the functions that has been used
Question 4 (based on Chapters 11 and 12) Lakeland Motors is contemplating an expansion of its production. The expansion would require a purchase and installation of a new machine with a cost of $400,000. The machine will be depreciated straight-line over 10 years with zero market value assumed at the end of the 10 year period. Nevertheless, the estimated duration of the expansion project is only 7 years, and the machine is expected to be sold for $175,000 at the end of year 7. The expansion project is expected to result in additional 10,000 units produced each year, and the expected selling price per unit is $35, while the expected variable cost per unit is $25. Use cost of capital of 7%, and a 20% tax rate. a) Using the information given above, compute the after-tax annual cash flows for years 0-7. Based on the resulting present worth (NPV) of the expansion project, should the project be accepted? Why? b) Perform sensitivity analysis, using a Monte Carlo simulation, with the following assumptions. Assume the selling price per unit is normally distributed, with $35 mean and $1.00 standard deviation. Assume the number of units sold is normally distributed, with 10,000 mean and 500 standard deviation. Assume the re-sale price of the machine at the end of year 7 is normally distributed with $175,000 mean and $2,000 standard deviation. Assume there is no correlation between the three random variables just described. Keep all of the other variables at their expected values. Re-compute the present worth (NPV) of the project 100 times (using 100 trials). Compute the average NPV, and determine the estimated cutoff point for the 5% of worst possible outcomes. Comment on the project uncertainty and the resulting desirability of the project. c) Perform sensitivity analysis, varying the number of units sold and the variable cost per unit. Construct a table showing the present worth (NPV) of the expansion project for all possible combinations of the number of units sold and the variable cost per unit, using the following values: number of units sold (6,000 7,000 8,000 9,000 10,000 11,000 12,000 13,000 14,000) and variable cost per unit (21, 22, 23, 24, 25, 26, 27, 28, 29). Keep all of the other variables at their expected values. Comment on the project's sensitivity to the number of units sold, and to the variable cost per unit. How does this sensitivity analysis affect the desirability of the project? Question 4 (based on Chapters 11 and 12) Lakeland Motors is contemplating an expansion of its production. The expansion would require a purchase and installation of a new machine with a cost of $400,000. The machine will be depreciated straight-line over 10 years with zero market value assumed at the end of the 10 year period. Nevertheless, the estimated duration of the expansion project is only 7 years, and the machine is expected to be sold for $175,000 at the end of year 7. The expansion project is expected to result in additional 10,000 units produced each year, and the expected selling price per unit is $35, while the expected variable cost per unit is $25. Use cost of capital of 7%, and a 20% tax rate. a) Using the information given above, compute the after-tax annual cash flows for years 0-7. Based on the resulting present worth (NPV) of the expansion project, should the project be accepted? Why? b) Perform sensitivity analysis, using a Monte Carlo simulation, with the following assumptions. Assume the selling price per unit is normally distributed, with $35 mean and $1.00 standard deviation. Assume the number of units sold is normally distributed, with 10,000 mean and 500 standard deviation. Assume the re-sale price of the machine at the end of year 7 is normally distributed with $175,000 mean and $2,000 standard deviation. Assume there is no correlation between the three random variables just described. Keep all of the other variables at their expected values. Re-compute the present worth (NPV) of the project 100 times (using 100 trials). Compute the average NPV, and determine the estimated cutoff point for the 5% of worst possible outcomes. Comment on the project uncertainty and the resulting desirability of the project. c) Perform sensitivity analysis, varying the number of units sold and the variable cost per unit. Construct a table showing the present worth (NPV) of the expansion project for all possible combinations of the number of units sold and the variable cost per unit, using the following values: number of units sold (6,000 7,000 8,000 9,000 10,000 11,000 12,000 13,000 14,000) and variable cost per unit (21, 22, 23, 24, 25, 26, 27, 28, 29). Keep all of the other variables at their expected values. Comment on the project's sensitivity to the number of units sold, and to the variable cost per unit. How does this sensitivity analysis affect the desirability of the project
