Sensitivities and What-If Analysis.  Sensitivities can be performed manually by adding or manipulating different cases as in the Inputs, Cases, and Scenarios section.  Watching the outputs (which are helpful to have referenced around the workbook) while changing the inputs is a quick and effective way to understand changes.  Excel also contains several tools that are useful in understanding sensitivities that are discussed in detail below.  This site does not yet cover Monte Carlo simulations or probabilistic inputs.  It is straight forward to use programs like @Risk and Crystal Ball to substitute probabilistic inputs for static inputs. 

  1. Goal Seek.  A simple tool for analysis and sensitivity is goal seek.  Goal seek plugs different values into one cell while attempting to achieve a set value in another cell.  Goal seek is not the most stable or best performing function for this task.  If your model is complex and has a lot of circular references, it may not be able to find an answer.  It does typically work and is quick, however.  Open the valuation example sheet.  Place your cursor on a figure you would like to determine.  Choose cell B166, Equity value as determined by the NOPLAT CV DCF.  Type Alt + t, g (or Data tab on ribbon > Data Tools > What-if analysis > Goal Seek).  In the “to” value, enter 6000.  Try changing growth rate (cell C175).  You may not find a solution (but typing in 13% will get you close).  Return cell C175 to 10%.  Now, select cell C83, Enterprise value in EBITDA, launch goal seek and input 4000 by changing cell B82, the EBITDA multiple.  Goal seek should quickly find a solution.  Goal seek works best with “direct” or short, non-iterative formulas.
  2. Solver.  Make sure you have installed the add-in (Excel Tips).  Now, use solver to find the growth rate necessary for a $6,000 equity value.  Select cell B166.  Open solver (Data tab on ribbon > Analysis section > Solver button), select the “value of:” radio button, enter 6000 in the box, click in the by changing cells box, click the button on the right of the box, and select growth rate (C175) (or type it in).  Press the button with the arrow on the right of the box to return to Solver.  Press solve.  It works!  Solver is better.  Now, on to the real power (and danger) of solver.  In the “By Changing Cells:” box, add a comma behind your first cell and enter other inputs.  Solver can use multiple inputs to find a solution.  Then in the subject to constraints box, you can click on cells to add constraints to the solution, such as maximum dollars of revenue, credit ratios, max and min margins, growth rates, etc.  Solver can also maximize and minimize values (such as debt) rather than seek a particular value.  This can be helpful in determining debt mixes and maximums when senior and subordinated facilities have different covenants. 
  • Be very careful when using solver.  Always be mindful of business reality and try not to fall into the trap of back-solving models to reach popular conclusions (like purchase price).  However, Solver can be very useful to understand limits and what-would-I-need-to-believe scenarios. 
  1. Tables.  Tables are a very useful output technique.  Especially when there is disagreement on the value of inputs.  A table provides a constant look at the results of an output at various inputs.  Return to the valuation example sheet.  Building your own table will help you remember the technique.  Go near the bottom of the sheet and enter “=B166” (equity value) in cell B195.  Then, below and beside this value, enter 10%,12%,14%,15% into column B rows 196-199.  Then enter 20%,22%,25%,30% in row 195 column C-F.  Select cells C195-F199 (click and drag over the area of made by the row and column).  Now, click on Data tab > Data Tools > What-if Analysis < Data table.  For the Row Input cell enter Gross Margin (C176) and enter Growth Rate (C175) for the Column input cell.  Then press OK.  Wow!  You now have the equity value at varying growth rates and gross margins.  This will automatically update when you change other variables or scenarios (as long as calculations (including tables) are set to automatic).  Obviously, you will want to include labels for users.  It is also helpful for reading to make the “home” formula (B195) white text to hide it and to use conditional formatting on the results to highlight those with color or shading that are across a boundary (such as asking price).  Tables need to have inputs on the same sheet as the table.  Tables will therefore end up on the input tab/sheet even though they are outputs.  It is a simple matter to reference them on an output sheet, however.  Tables can be either one or two variable.