3 Cumulative CF from 00 124200 45000 47100 IF Function to find

3 cumulative cf from 00 124200 45000 47100 if

This preview shows page 20 - 26 out of 51 pages.

3 Cumulative CF from Row 53 (195,000) (124,200) (45,000) 47,100 IF Function to find payback FALSE FALSE 2.49 Part 2. Depreciation and Amortization Schedule Accum'd Year Initial Cost 1 2 3 Deprn 175,000 Equipment Deprn Rate 33.0% 45.0% 15.0% Equipment Deprn, Dollars $57,750 $78,750 $26,250 $162,750 Ending Bk Val: Cost - Accum'd Deprn $12,250 Years
Chapter 12:  Cash Flow Estimation and Risk Analysis Comprehensive/Spreadsheet Problem 67
f. 68 Comprehensive/Spreadsheet Problem Chapter 12:  Cash Flow Estimation and Risk Analysis % Deviation % Deviation from Units NPV from NPV Base Case Sales $4,014 Base Case WACC 4,014 -20% 92,000 -$40,600 -20% 8.0% $11,569 -10% 103,500 -$18,293 -10% 9.0% $7,733 0% 115,000 $4,014 Base Case 0% 10.0% $4,014 10% 126,500 $26,321 10% 11.0% $407 20% 138,000 $48,628 20% 12.0% -$3,093 % Deviation % Deviation from Variable NPV from Sales NPV Base Case Costs $4,014 Base Case Price $4,014 -20% $1.56 $70,935 -20% $2.60 -$40,600 -10% $1.76 $37,475 -10% $2.93 -$18,293 0% $1.95 $4,014 Base Case 0% $3.25 $4,014 10% $2.15 -$29,446 10% $3.58 $26,321 20% $2.34 -$62,907 20% $3.90 $48,628 % Deviation % Deviation from Fixed NPV from Equipment NPV Base Case Costs $4,014 Base Case Cost $4,014 -20% $56,000 $24,904 -20% $140,000 $27,294 -10% $63,000 $14,459 -10% $157,500 $15,654 0% $70,000 $4,014 Base Case 0% $175,000 $4,014 10% $77,000 -$6,431 10% $192,500 -$7,625 20% $84,000 -$16,875 20% $210,000 -$19,265 WACC VARIABLE COSTS EQUIPMENT COST SALES PRICE FIXED COSTS UNIT SALES Sensitivity Analysis ($50,000) ($30,000) ($10,000) $10,000 $30,000 $50,000 $70,000 -20% -10% 0% 10% 20% NPV VC Equipment Cost FC Sales Price WACC
g. Note that "best-case" values for variable costs, fixed costs, WACC, and equipment cost are  20% less than base-case values, while the "worst-case" values for variable costs, fixed costs,  WACC, and equipment cost are 20% higher than base-case values. Chapter 12:  Cash Flow Estimation and Risk Analysis Comprehensive/Spreadsheet Problem 69 Deviation from Sales Variable Unit Fixed Equipment Base Case Price Costs Sales Costs WACC Cost -20% ($40,600) $70,935 ($40,600) $24,904 $11,569 $27,294 -10% ($18,293) $37,475 ($18,293) $14,459 $7,733 $15,654 0% $4,014 $4,014 $4,014 $4,014 $4,014 $4,014 10% $26,321 ($29,446) $26,321 ($6,431) $407 ($7,625) 20% $48,628 ($62,907) $48,628 ($16,875) ($3,093) ($19,265) Range 89,228 133,842 89,228 41,779 14,662 46,559 NPV at Different Deviations from Base b. Continuous Approximation Probability Density (227,902) 0 26,093 324,244 NPV ($) 4,014 Squared Deviation Sales Variable Unit Fixed Equipment Times Probability Price Costs Sales Costs WACC Cost NPV Probability 25% $3.90 $1.56 138,000 $56,000 8% $140,000 $324,244 22223605763 50% $3.25 $1.95 115,000 $70,000 10% $175,000 $4,014 243729670 25% $2.60 $2.34 92,000 $84,000 12% $210,000 ($227,902) 16128328834 38595664267 Expected NPV = sum, prob times NPV $26,093 Standard Deviation = Sq Root of column H sum $196,458 Coefficient of Variation = Std Dev / Expected NPV 7.53 Worst Case Base Case Best Case Scenario a. Probability Graph Probability 50% 25% (227,902) 0 26,093 324,244 NPV ($) Most Likely Mean of distribution 4,014
70 Comprehensive/Spreadsheet Problem Chapter 12:  Cash Flow Estimation and Risk Analysis

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture