RapidKnowHow

Business Leader : Sensitivity Analysis

RapidKnowHow > Create a Sensitivity Analysis for ROI

Creating a sensitivity analysis in Excel is a systematic process that involves inputting your data, defining your variables, and then using Excel tools like data tables to visualize the outcome variations.
Below is a step-by-step guide to creating a sensitivity analysis in Excel, followed by an example of selecting and commenting on the best option.

Step-by-Step Guide to Creating Sensitivity Analysis in Excel

Step 1: Set Up Your Excel Template

  1. Open Excel and create a new worksheet.
  2. Enter Your Base Case Values:
  • In A1, type “Initial Investment”, and in B1, type 10000.
  • In A2, type “Optimistic Revenue”, in B2 type 15000.
  • In A3, type “Realistic Revenue”, in B3 type 12000.
  • In A4, type “Pessimistic Revenue”, in B4 type 12000.
  • In A5, type “Optimistic Costs”, in B5 type 5000.
  • In A6, type “Realistic Costs”, in B6 type 6000.
  • In A7, type “Pessimistic Costs”, in B7 type 8000.

Calculate Net Profit and ROI:

  • In A9, type “Net Profit (Optimistic)”, in B9, type =B2-B5.
  • In A10, type “ROI (Optimistic)”, in B10, type =B9/B1*100.
  • Repeat this for Realistic and Pessimistic scenarios:
  • For Net Profit (Realistic) in A11 and ROI (Realistic) in A12:
  • =B3-B6 (Net Profit)
  • =B11/B1*100 (ROI)
  • For Net Profit (Pessimistic) in A13 and ROI (Pessimistic) in A14:
  • =B4-B7 (Net Profit)
  • =B13/B1*100 (ROI)

Format your ROI cells as a percentage for readability.

Step 2: Create a Sensitivity Analysis Table

Create a New Table:

  • In D1, type “Revenue Change (%)”, and in E1, type “Optimistic ROI”, F1 for “Realistic ROI”, G1 for “Pessimistic ROI”.

Input Revenue Change Scenarios:

  • Below D1, in D2 to D6, enter the percentage changes: -20%, -10%, 0%, 10%, 20%.

Use Data Table for ROI Calculation:

  • In cell E2, enter the formula that adjusts optimistic revenue based on the percentage in D2:
  • =($B$2*(1+D2)) - $B$5

In E2, the formula for ROI would be:

  • =E2/$B$1 *100

Extend E2 Downwards to E6:

  • Click on the bottom right corner of cell E2 and drag down to fill in for E3 to E6.

Fill in Realistic and Pessimistic Columns:

  • Repeat the same process in columns F and G, adjusting for realistic and pessimistic revenues using the respective base revenue values.

Step3: Visualize Sensitivity Analysis

Create a Chart:

  • Highlight the range from D1 to G6.
  • Go to the Ribbon, click on “Insert” and select “Insert Line or Area Chart” to visualize how ROI changes with revenue adjustments.

Step4: Select and Comment on the Best Option

Analyze the Results:

  • Look at the calculated ROI values in your sensitivity table.
  • Identify which scenario (Optimistic, Realistic, Pessimistic) has the highest ROI under the various revenue change percentages.

Comment on the Best Option:

  • Based on the analysis, summarize your findings. For instance:
  • If the Optimistic scenario consistently delivers higher ROI values even under revenue reductions or in less favorable scenarios, it may be the best option.
  • Conversely, if the Realistic scenario provides a notably stable ROI with lower risk, it could be favored for its reliability.

Example Commentary:

  • “The sensitivity analysis shows that under a -10% revenue change, the Optimistic scenario yields a robust ROI of 80%, compared to 56% for the Realistic scenario and 44% for the Pessimistic scenario.
    However, even with maximum revenue adjustment (20%), the Optimistic scenario still achieves an ROI of 40%. Thus, if maximizing returns is the priority, the Optimistic scenario remains the best option. On the other hand, if risk is a major concern, the Realistic scenario presents a balanced approach with stable returns.”

Conclusion

Following these steps, you should have a comprehensive sensitivity analysis in Excel to assess the ROI under varying revenue and cost scenarios.
Analyzing the results will help you make informed decisions based on performance variability across different market conditions.

Calculate the Sensitivity Risk of each Option Scenario and comment

To calculate the Sensitivity Risk of each option scenario (Optimistic, Realistic, and Pessimistic), you can measure the volatility of ROI relative to changes in revenue and costs. One effective method is to calculate the standard deviation of ROI values across the different scenarios and revenue changes. This will give you an idea of the risk associated with each option.

Step-by-Step Calculation of Sensitivity Risk

1. Gather the ROI values

Assuming you have already created a sensitivity analysis table as described in the previous message, you would have multiple ROI values for each scenario based on different revenue changes.

For example:

  • Optimistic ROI Results: (in percentage)
  • Revenue Change -20%: 90%
  • Revenue Change -10%:80%
  • Revenue Change 0%: 70%
  • Revenue Change10%:50%
  • Revenue Change 20%: 30%
  • Realistic ROI Results:
  • Revenue Change -20%:60%
  • Revenue Change -10%: 55%
  • Revenue Change0%:50%
  • Revenue Change 10%: 45%
  • Revenue Change20%:40%
  • Pessimistic ROI Results:
  • Revenue Change -20%: 40%
  • Revenue Change -10%:35%
  • Revenue Change 0%: 30%
  • Revenue Change10%:25%
  • Revenue Change20%:20%

2. Calculate Standard Deviation of ROI for Each Scenario

You can use the STDEV.S function in Excel to calculate the standard deviation of the ROI results:

  • Optimistic Risk Calculation:
  • In a cell, enter: =STDEV.S(E2:E6) (assuming E2 to E6 contains the Optimistic ROI values).
  • Realistic Risk Calculation:
  • In another cell, enter: =STDEV.S(F2:F6) (assuming F2 to F6 contains the Realistic ROI values).
  • Pessimistic Risk Calculation:
  • In another cell, enter: =STDEV.S(G2:G6) (assuming G2 to G6 contains the Pessimistic ROI values).

Example Standard Deviation Calculation

Using hypothetical ROI values for demonstration:

  • Optimistic ROI (Assumed Sample Values):90%, 80%,70%, 50%,30%
  • Standard Deviation = 24.66 (example calculation)
  • Realistic ROI (Assumed Sample Values):60%,55%, 50%, 45%,40%
  • Standard Deviation = 7.91 (example calculation)
  • Pessimistic ROI (Assumed Sample Values):40%, 35%,30%, 25%, 20%
  • Standard Deviation = 7.91 (example calculation)

3. Compare the Risks

Risk Assessment:

  1. Optimistic Scenario:
  • Standard Deviation:24.66
  • Comment: This scenario shows the most sensitivity to changes in revenues, suggesting that while it offers high returns under favorable conditions, it also poses risks of significant losses under adverse conditions.
  1. Realistic Scenario:
  • Standard Deviation: 7.91
  • Comment: This scenario demonstrates a balance between risk and reward, with stable ROI values. It suggests that the returns are relatively consistent even in the face of revenue changes, making it a safer choice for risk-averse decision-makers.
  1. Pessimistic Scenario:
  • Standard Deviation:7.91
  • Comment: Like the Realistic Scenario, this option shows low volatility; however, it presents lower ROI values. The lack of sensitivity signifies that it might not be worth the investment due to lower returns overall. It is the least volatile, but also the least rewarding.

Conclusion

In conclusion, the analysis indicates that the Optimistic Scenario carries the highest risk due to its high standard deviation, highlighting both potential gains and significant exposure to losses.
The Realistic Scenario is the best option for investors looking for a balance between risk and reward, while the Pessimistic Scenario, though low in volatility, fails to justify investment due to lower returns.
Investors must weigh their risk tolerance against potential returns when selecting the best option.

Sharing is Caring! Thanks!

Related Posts