1 / 14

Sensitivity, Breakeven and Indifference Analyses

Sensitivity, Breakeven and Indifference Analyses. Additional Topics: Internal Rate of Return, Inflation and Interest Rate. Internal Rate of Return. IRR: Internal Rate of Return Defined as the discount rate that sets NPV to 0 NPV = PV of future CFs – CF 0

dominy
Download Presentation

Sensitivity, Breakeven and Indifference Analyses

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Sensitivity, Breakeven and Indifference Analyses Additional Topics: Internal Rate of Return, Inflation and Interest Rate

  2. Internal Rate of Return • IRR: Internal Rate of Return • Defined as the discount rate that sets NPV to 0 • NPV = PV of future CFs – CF0 • NPV = -CF0 + CF1/(1+r)1 + CF2/(1+r)2 + .. +CFt/(1+r)t • NPV = 0 = -CF0 + CF1/(1+IRR)1 + CF2/(1+IRR)2 + .. +CFt/(1+IRR)t • Can only be solved using an iterative method • Excel function IRR() • NPV Profile • A graph showing the relationship between NPV and discount rate

  3. IRR Example • Multiple Cash Flows • Including special forms such as annuities • E.g. • Bond Cash Flows • Price Today • Coupon Payments • Face (Par) Value at Maturity

  4. Sensitivity Analysis • Sensitivity Analysis • Change one input variable at a time • Similar to taking partial derivative • With Excel’ data table, we can analyze impact on multiple output (decision) variables simultaneously

  5. Sensitivity Analysis using Data Table • Data Table • Organization • 1 input • Row or Column • Basic sensitivity analysis • Allow multiple output variables • 2 inputs • Change 2 input variables simultaneously • Allow only one output variable • Modifying Data Table • Must delete entire table range • Key • Model must be flexibly coded • A useful tool for checking model integrity in addition to providing sensitivity analysis

  6. Breakeven versus Indifference • Breakeven • A minimum hurdle • Indifference • Choosing among alternatives • Breakeven and indifference are similar • There is always an alternative: DO NOTHING • Modeling Tips • Create a Net gain variable for indifference analysis • E.g. Net gain of project A = NPVA – NPVB • Indifference -> NPVA = NPVB • Breakeven -> Net gain of project A = 0

  7. Sales Quantity • Accounting Breakeven analysis often focus on sales quantity • Pro forma statements are typically sales-driven • (Accounting) Breakeven Sales Quantity • QBE = FC / (p – vc) • FC is fixed costs, p is unit price, vc is unit variable costs • Cash Flow Breakeven • Sales level resulting in $0 operating cash flows • QBECF = (FC - Depreciation expense) / (p – vc) • NPV Breakeven • Sales level resulting in $0 NPV • No closed form solution

  8. Goal Seek • Goal Seek • Useful for simple models: e.g. linear model • Can only search for solutions to a specific value • Cannot do general search, such as finding the maximum • Set Cell • This is usually the decision variable • E.g. Accounting breakeven -> Net profit • NPV breakeven -> NPV • Retirement Planning -> Ending Balance • Should be a cell containing a formula • To value • E.g. Breakeven -> 0 • Can modify to include target profit or bequest amount • By Changing • Should be an input variable to the model • Can only change one cell • E.g. Sales quantity • Annual Savings

  9. Solver • Add-ins • More powerful than Goal Seek • Can solve for maximum or minimum in addition to a specific value • Can handle nonlinear functions • Note: A nonlinear function need not be complicated. E.g. MAX(0,A1) is a nonlinear function • Set target cell • Similar to “Set Cell” in goal seek

  10. Solver (continued) • Equal to • Max, Min, or value • For breakeven analysis, equal to 0 • Changing cells • Can change multiple cells simultaneously • If changing more than one cell, you usually need to impose constraints. Otherwise, there may be many possible solutions. • E.g. x + y = 3. The number of possible solutions for x and y are infinite. If x and y are restricted to positive integers, the number of possible solutions reduce significantly. • E.g. Your goal is to find the breakeven NPV. If changing cells include both unit price and unit variable costs, there will not be a unique solution.

  11. Solver (continued) • Subject to the Constraints • Depends on the nature of the problem • We will discuss constraints more later in portfolio optimization

  12. Inflation and Interest Rate • Fisher Relation • Inflation Premium: real versus nominal rates • Fisher Relation: • (1 + Nominal) = (1 + real) x (1 + inflation rate) • Can be approximated by • Nominal rate  real rate + expected inflation

  13. Cash Flow and Discount Rate • Use real discount rate for “real” cash flows, i.e. cash flows that have not been adjusted for inflation • Use nominal discount rate for nominal cash flows • Accounting data usually contains “mixed” cash flows • E.g. revenue and cash expenses are usually current, i.e. these are nominal CFs • Depreciation is based on historic price and has not been adjusted for the effects of inflation

  14. Consequences of using the wrong discount rate • Use real discount rate for nominal cash flows • Overstates PV when inflation rate > 0 • Invest in poor projects/investments • Use nominal discount rate for cash flows that have not been adjusted for inflation • Understates PV when inflation rate > 0 • Reject valuable projects/investments • Inflation rate is usually positive

More Related