# Npv Spreadsheet Template - PowerPoint

Document Sample

```					Project Analysis and Forecast Risk

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