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

# 3 cumulative cf from 00 124200 45000 47100 if

• Notes
• 51
• 100% (8) 8 out of 8 people found this document helpful

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