Go or No-Go Manufacturing New Product - Scenario and Sensitivity Analysis on Project's Net Prese
- Chutian Li, Kelsey Brown
- Nov 16, 2016
- 3 min read
A Manufacturer planning to manufacture new type of LED (EnviroLamp) that are twice efficient and cost half. There are six critical inputs and three scenarios. Calculate the NPV, IRR, MIRR, PI, Payback and recommendation of the outlook of the project.
Step 1 - Create Scenarios with Scenario Manager
Step 2 - Calculate Cash Flow
2.a - Find all the factors impact NOPAT
2.b - "TA-DA" cash flow...
Step 3 - Calculate NPV, IRR, MIRR, PI.. Yup. Let excel do the work
Step 4 - Sensitivity Analysis with Data table
As the case outlines, there are three possible scenarios: Best Case, Worst Case, and Base Case.In both the Base Case and Best Case, the LAMP project is estimated to make money, and thus, be a positive project in the long run.
Based on the probabilities provided, LAMP has an 80% chance of making money in the long run. For the Base Case Scenario, which is weighted at 60%, LAMP’s NPV is $731,761. For the Best Case Scenario, which is weighted at 20%, LAMP’s NPV would be $5,128,889.
Base Case Information:
In the Base Case, the NPV is valued at $731,761. With an Internal Rate of Return of 18.5%, it appears that this project shows potential for future success. This means that, if everything goes as planned, the project/investment could earn almost 19%. Because we are dealing with positive cash flows throughout the entire project, a positive IRR indicates that the management should move forward with the LAMP project.
Additionally, the Profitability Index for the Base Case is above 1 (1.3), which is another sign that the project will be successful. This means that the project’s present value is greater than the cost of the project’s initial investment.
The Base Case has a payback period of only 3.5112 years, which is great considering the amount of money that will be required to start the project. In other words, the initial investment costs to set up the LAMP project would be covered in just over 3.5 years. Therefore, LAMP will have over 2 years to earn strictly profits on the project prior to the estimated termination date.
However, in order to consider the true time vale of money, we also evaluated the discounted pay-back period. For the Base Case, it was 4.36 years, which suggests that LAMP would still be profitable for over 1.5 years prior to project termination.
Best-Case Information:
In the Best Case, the NPV is valued at $5,128,889. With an IRR of 53.9%, this is a sign that management should definitely move forward with this project. In other words, this project could earn over 50%.
The Best Case PI is 3.04, which signifies that the PV of the LAMP project is significantly higher than the initial investments required to take on the project. Therefore, management should be motivated to move forward.
The payback period for the Best Case is only 2.2995 years, which is a great sign, because this indicates that the initial investments would be covered in less than half of the time that the project will be going.
All of the above data supports the decision to move forward and accept the LAMP project. While there is no guarantee that the project will be successful, with an 80% likelihood of making money in the long-run, both Chutian and Kelsey feel the investment is worth the risk.
Comments