Sensitivity tables let one sensitize up to two variables within a financial model in order to see how changes to those variables effect a single final outcome. Usually this is used for producing a range of IRRs, NPVs, and in general valuation modeling. The official name for what this is called in Excel is a data table and in the last year I started integrated this technique in a few models. This template bundle package includes all the files I've done that include such a technique.
$99.00 USD
(Financial Model Templates with Data Tables Bundle)
Templates Included:
- Zero Down Real Estate Investing
- Mobile App Ad Spend Optimizer
- IRR Sensitivity for General Real Estate Acquisition
- Leveraged Buyout (LBO)
- Equipment Purchase Cost Benefit Analysis
- WACC (weighted average cost of capital)
- 21 Manufacturing Data Tables
- Coffee Shop
- Royalty Stream Comparison
Why are Sensitivity Analysis Data Tables Useful in Excel?
Being able to see the output result of the entire model compared side by side as variables change is cumbersome to do without data tables. The logic in the table actually runs the entire model on the back end and gives you just the final output you are trying to solve for. The only other way you can do this is manually building many versions of the model and adjusting the variables yourself to see what the result is.
About the Sensitivity Analysis Structure of Each Model Above:
- IRR Sensitivity for General Real Estate Acquisition - This build was inspired by a client that wanted to see the effect of occupancy rates on IRR. So, I built a general model to analyze a real estate deal where all the inputs are on a single tab. The first data table shows IRR as the occupancy rates change and the exit cap rate. The second table displays IRR as the vacancy and exit month change, and the third table displays IRR as the exit month and exit cap rate change. This produced a matrix of 100 IRRs that adjust as other variables in the model are set.
- Leveraged Buyout (LBO) - In this financial model, the data table sensitized the amount of leverage used and the acquisition price. Based on those two variables changing, you will be able to see the resulting IRR. What's nice about these modeling tools is that your reference doesn't necessarily have to be an editable input. For example, in this model I have a bit of logic that goes into the final purchase price, but I can still sensitize that final purchase price in the sensitivity analysis and see what the final output would be if that single data point changes per the sensitivity.
- WACC (weighted average cost of capital) - This model is used to understand a reasonable discount rate to use within a DCF Analysis. There are many key inputs that need to be configured to produce a reasonable rate. This model has two data tables. The first sensitizes the expected return of market and risk-free rate in order to see how those two things changing will affect the WACC. Since DCF is often tied to this calculation, I made the second table sensitize the same variables (expected return / risk-free rate) and then show the resulting NPV range of a set of cash flows if the WACC produced in each permutation was used on it.
- 21 Manufacturing Data Tables - The set of input models on this work simple and are made for high volume manufacturing or retail sales businesses that sell products. The main goal is to analyze break-even pricing, revenues, and volume as well as show resulting profits when key variables change such as wages, worker count, productivity, fixed costs, variable costs, and more.
- Coffee Shop - This is a general startup financial model where I sensitized text inputs. The model can adjust sales volumes and pricing on a low, base, and high selection. So, in this sensitivity table I made the three sensitivity drivers the thing that makes up the rows and columns. So, without having to change any variable manually, you can see how the IRR of the entire project changes when you have low sales and high pricing or high sales and low pricing or any combination.
Billable Hour Promotion: If you pay for 1 hour of billable work here, you can get 1 $45 template at no charge (scales up for as many credit hours as you buy). Just email me which one(s) you want: jason@smarthelping.com
