Docstoc

Npv Spreadsheet Template - PowerPoint

Document Sample
Npv Spreadsheet Template - PowerPoint Powered By Docstoc
					Project Analysis and Forecast Risk


ADVANCE-Managerial Finance
Class Notes for Chapter 11
D.B. Hamm—updated Jan. 2006
Evaluating NPV Estimates—The
Basic Problem
   Basic Problem—How reliable is our NPV
    estimate for new project(s) under
    consideration?
    – Projected vs. actual cash flows
    – Forecasting risk—possibility that errors in
      projected cash flows will lead to incorrect
      decisions
    – Also called ―estimation risk‖ (same)
   ―What If‖ analysis may help us evaluate and
    minimize forecasting/estimation risk
―What If‖ Analysis (overview)

   Scenario analysis
    – Ask basic ―What if?‖ questions and rework NPV
      estimates
       • Worst case—good start point—what is the
         minimum NPV for the project?
       • Best case—upper limit bound of project NPV
       • Base case—most likely outcome assumed
         (probably some midpoint between best & worst)
―What If‖ Analysis (continued)

   Sensitivity analysis—
    – Impact on NPV and/or IRR when one variable is
      changed (up or down) and other variables remain
      at ―base case‖
    – If our estimate of NPV or IRR is very sensitive
      (changes significantly) to relatively small changes
      in some component, forecasting risk for that
      variable is high
―What If‖ Analysis (slide 3):

   Simulation analysis
    – Combine scenario and sensitivity analysis to
      calculate impact of varying changes
    – Use of a computer (spreadsheet or other software)
      is essential
    – Still may be impossible to forecast every possible
      combination of variables, but should give us some
      trends
Illustration:
Wally's Widget Works
New Project Estimate                            Scenario
                                        Base        Worst        Best
Unit Sales                              6,000        4,500      7,500
x Selling price per unit                  $15          $15        $15
Sales Revenue                         $90,000      $67,500   $112,500
-Variable Costs at $8 per unit       -$48,000     -$36,000   -$60,000
Contribution Margin                   $42,000      $31,500    $52,500
- Fixed costs (other than depr.)     -$12,000     -$12,000   -$12,000
- Depreciation                       -$11,000     -$11,000   -$11,000
EBIT                                  $19,000       $8,500    $29,500
Taxes @ 40%                           -$7,600      -$3,400   -$11,800
Net Income                            $11,400       $5,100    $17,700

OCF (EBIT+ Depr-Taxes)               $22,400       $16,100    $28,700

Pres. Value (4 yrs x above at 12%)   $68,037      $48,901    $87,172
Less Original Investment             -$60,000     -$60,000   -$60,000
NPV                                   $8,037     ($11,099)   $27,172
IRR                                   18.22%        2.89%     32.15%

 Once our template is set up we may rerun with any
 variations required
Break-Even Analysis (1):
   Fixed and Variable Costs
    – VC varies with quantity produced/sold
    – FC remains constant (in relevant range)
    – Separate depreciation (D) for cash flow purposes
    TC = VC + FC + D
    – Or S = v x Q + FC+D
   Therefore S – VC – FC – D = 0
    at break even point (―accounting break even‖)
   Accounting break even occurs where net income
    from project = 0
Break Even Analysis (2):

   Since S – VC – FC – D = 0 at break even
   And since S = p x Q (selling price x quantity)
   And VC = v x Q (vc per unit x Q)
   Then (p x Q)-(v x Q) – FC – D = 0
   Finally accounting break even quantity is:
             Q = FC + D
                   p -v
Accounting Break Even (illustration)
Selling price per unit = $20, variable cost = $11 per
unit, fixed costs other than depreciation = $60,000 and
depreciation = $20,000. Find accounting break even
quantity:
Q = FC       +D     /p -v
Q = 60,000 + 20,000 / 20 -11
Q = 80,000 / 9
Q = 8,889 units
Cash Flow Break Even:
Operating cash flow: OCF = EBIT + Depr – Taxes
In these illustrations we will assume Taxes = 0
(calculating break even on a pre-tax basis), so
OCF = EBIT + D
OCF =( S –VC – FC – D) + D
OCF = (P x Q)-(v x Q) – FC
OCF = Q (p-v) - FC
Q (break even) = FC (without depr.)
                  p–v
Cash flow break even occurs where project OCF = 0
Cash Flow B/E (illustration):
Using previous illustration:
Selling price per unit = $20, variable cost = $11 per
unit, fixed costs other than depreciation = $60,000 and
depreciation = $20,000. Find cash flow break even
quantity
Q = FC / p – v
Q = 60,000 / 20 – 11
Q = 60,000 / 9
Q = 6,667 units
Note: B/E quantity for cash flow is less than required for
accounting break even, but project at cash b/e only can
never pay back its original investment. IRR = -100%
Financial Break Even:
   Financial break even occurs when NPV of
    project = 0
    – Discounted payback = project life
    – Project NPV = 0
    – Project IRR = required rate of return
   Formula for break even:

      Q = FC + OCF*
             p–v
      *Where OCF results in a zero NPV
Financial B/E (illustration):
Our previous project seeks a 12% return over 5 years.
Original investment was $100,000. Required OCF per
year would therefore be OCF = 100,000 / 3.6048 (see
table for PV annuity factor @ 12% for 5 periods)
OCF = $27,741 ( 100,000 / 3.6048 rounded to nearest $1)
Q = FC + OCF / p – v
Q = 60,000 + 27,741 / 20 – 11
Q = 87,741 / 9
Q = 9,749 units (considerably more than cash flow b/e,
even more than accounting b/e, but this now factors
recovery of original capital investment at 12% over 5 yrs)
Problems (group case):




 PAUSE FOR CLASS CASE:
Operating Leverage

Operating leverage is the degree to which a
 project relies on fixed costs

Degree of operating leverage = % change in
 OCF relative to % change in quantity sold

DOL = 1 + (FC/OCF)
Operating Leverage (illustration)

In the case just worked, OCF at base case = $30,000
and FC=$40,000 (output is 14,000 units)
DOL = 1 + (40,000/30,000)
DOL = 1 + 1.3333
DOL = 2.333
Thus a 1% increase in units sold would generate a
2.33% increase in OCF in the base case range. Vice
versa, a 1% decrease in sales = 2.33% decrease in
OCF.
Operating Leverage (conclusion)
 DOL will decline if Q increases substantially. At best
 case scenario DOL = 1+(40,000/45,000) = 1.889
 Conversely at worst case scenario DOL = 1 +(40,000
 / 15,000)=3.667
 This is because as fixed costs decline as a percent of
 operating cash flow (quantities sold increases and
 OCF therefore increases, but fixed costs stay
 constant), the leverage effect diminishes. If fixed
 costs as a % of OCF increases (as when sales decline,
 thus OCF declines, but fixed costs don’t change),
 leverage effect increases.
End of Ch. 11 Presentation

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:223
posted:7/20/2010
language:English
pages:18
Description: Npv Spreadsheet Template document sample