This is part two of a three part series.  In part one, we learned how Excel can be used to calculate the IRR on an investment.  In part two, we will perform a sensitivity analysis of the IRR based on modifying certain key inputs. 

The key assumptions for the cash flow and IRR in this model are the revenue growth rate and the estimated gross margin.  We can see how IRR will be impacted by simply changing the inputs for each of these assumptions on the worksheet.  If we change the revenue growth rate from 5% to 6% (cell D5 in the worksheet), the IRR will move from 11.4% to 12.1%. 

What if we wanted to see the different IRR outcomes for a range of revenue growth from 2% to 8% and Gross Margin rates between 28% and 43%?  Excel provides a powerful function that allows us to change two variables at the same time and see a wide range of results.  By using the Data|Table command in Excel, we can see all the different possibilities in a data table.  We will start by setting up the data table parameters on the worksheet as illustrated below:

Once we have set up the parameters (Revenue Growth in column U and Gross Margin in Row 3), select the cell that has the outcome we want to analyze and reference it to the cross section of Column U and Row 3.  We want to analyze the IRR which is calculated in cell I28.  We enter "=I28" in cell U3 and press enter. 

Highlight the data table area (cells U3 to AB10) and choose Data|Table from the Excel Menu.  The row input is the Gross Margin input (Cell D6) and the Column input is the Revenue Growth input (Cell D5). 

As a result the Data function will provide the following sensitivity table.

To pressure test the accuracy of the table, we can look at the logic behind it.  As Gross Margin or Revenue Growth rates increase, the IRR should also increase.

In the final posting, I will address how to model when there are multiple variables that may have ranges of potential outcomes.