3.0. Core application of data analytics

3.2. Financial management

Carry out sensitivity analysis and scenario analysis in project evaluation

Sensitivity analysis and scenario analysis are essential techniques in project evaluation, offering valuable insights into a project's robustness and potential risks. Sensitivity analysis involves varying key input variables one at a time to assess how sensitive the project's outcomes (such as NPV or IRR) are to changes in these variables. This helps identify which factors have the most significant impact on the project's financial performance.

Scenario analysis, on the other hand, explores different combinations of input variables to simulate various possible future scenarios. By creating multiple scenarios with different assumptions, you can assess the project's performance under different conditions, allowing for a more comprehensive understanding of its risk profile.

Both sensitivity and scenario analysis are powerful tools that provide a clearer picture of how a project may perform in the face of uncertainty, helping decision-makers make more informed choices and mitigate potential risks.

How to carry out sensitivity analysis and scenario analysis in project evaluation

Sensitivity Analysis:

1. Identify Key Variables: Determine the project's key input variables that have the most significant impact on financial outcomes like NPV or IRR. These variables could include sales revenue, production costs, discount rates, or inflation rates.
2. Define Ranges: For each identified variable, specify a range of values or scenarios to test. Consider both optimistic and pessimistic values within a reasonable range. For instance, you might test a sales revenue increase of 10% and a decrease of 10%.
3. Calculate Outcomes: Recalculate project outcomes (e.g., NPV or IRR) for each combination of input variables within the defined ranges. You can use spreadsheet software like Excel to automate these calculations.
4. Analyze Results: Examine how changes in input variables affect project outcomes. Identify which variables have the most significant impact and understand the project's sensitivity to these changes.

Scenario Analysis:

1. Define Scenarios: Create a set of scenarios that represent different possible future conditions or events. Scenarios should vary key assumptions, such as market conditions, economic factors, or regulatory changes. For example, you might create scenarios for a bullish market, a recession, and a stable economy.
2. Model Scenarios: Adjust input variables to reflect the assumptions of each scenario. Calculate project outcomes (e.g., NPV or IRR) for each scenario. This can be done using spreadsheet software or specialized financial modeling tools.
3. Evaluate Impacts: Analyze the results of scenario analysis to understand how the project performs under various conditions. Assess the best and worst-case scenarios, as well as the likelihood of each scenario occurring.
4. Risk Mitigation: Based on the insights gained from scenario analysis, develop risk mitigation strategies and contingency plans. Consider how to adapt the project to different scenarios to ensure its viability and success.
5. Decision-Making: Use the information gathered from sensitivity and scenario analyses to make informed decisions about the project. Determine whether the project remains financially viable and assess the level of risk tolerance.

Example

Initial Assumptions:

• Initial Investment: $100,000
• Expected Annual Revenue: $50,000
• Annual Operating Costs: $20,000
• Discount Rate: 10%
• Project Timeline: 5 years

Sensitivity Analysis:

1. Identify Key Variable: Let's identify annual revenue as a key variable affecting project outcomes.
2. Define Ranges: Test optimistic (e.g., $55,000) and pessimistic (e.g., $45,000) values for annual revenue.
3. Calculate Outcomes: Calculate NPV and IRR for both revenue scenarios:
   (a) Optimistic Revenue Scenario:
   • NPV = $32,677.54
   • IRR = 22.11%
   (b) Pessimistic Revenue Scenario:
   • NPV = -$5,230.33
   • IRR = 7.93%
4. Analyze Results: Sensitivity analysis reveals that the project is sensitive to changes in annual revenue. Higher revenue leads to significantly better NPV and IRR, while lower revenue reduces the project's financial attractiveness.

Scenario Analysis:

1. Define Scenarios:
   a. Bullish Scenario (Optimistic):
   • Annual Revenue: $55,000
   • Operating Costs: $20,000
   b. Base Scenario (Initial Assumptions):
   
   • Annual Revenue: $50,000
   • Operating Costs: $20,000
   c. Bearish Scenario (Pessimistic):
   
   • Annual Revenue: $45,000
   • Operating Costs: $20,000
2. Model Scenarios: Calculate NPV and IRR for each scenario over the project's 5-year timeline.
   (a). Bullish Scenario:
   • NPV = $32,677.54
   • IRR = 22.11%
   (b). Base Scenario:
   • NPV = $13,723.60
   • IRR = 15.24%
   (c). Bearish Scenario:
   • NPV = -$5,230.33
   • IRR = 7.93%
3. Evaluate Impacts: Scenario analysis reveals that the project's financial performance varies significantly under different revenue scenarios. The bullish scenario is the most favorable, while the bearish scenario is the least attractive.
4. Risk Mitigation: Based on scenario analysis, consider strategies to mitigate risks in the bearish scenario, such as cost-cutting measures or marketing efforts to increase revenue.
5. Decision-Making: Use the insights gained from sensitivity and scenario analyses to make an informed decision. If the project's viability is acceptable in both the base and bearish scenarios, it may be a reasonable investment.

Working with excel to carry out Sensitivity analysis in projects

NPV

We will create different revenue scenarios from cells H2 to H11 and a discount rate from 11 to M1. In cell H1, we will put the NPV formula,then use the what-if analysis and data table to get the NPV for the various scenarios.

NOTE:Pressing F4 (or using the dollar sign "$") in Excel or similar spreadsheet software allows you to lock a cell reference when copying or dragging a formula. This is useful when you want to keep a specific cell reference constant while allowing others to change relative to their new positions.

IRR

The IRR is essentially the break-even discount rate for a project. If the actual discount rate (the cost of capital) used for evaluation is lower than the IRR, the project is expected to be financially viable (positive NPV). If it's higher than the IRR, the project is expected to be unprofitable (negative NPV). The relationship between the discount rate and IRR is crucial in evaluating the feasibility and attractiveness of investment projects.

These analyses help decision-makers understand the project's sensitivity to changes in key variables and assess how it performs under different conditions, ultimately aiding in making well-informed investment decisions.