To see updated values hit th Panel A Va To change an input change one of the

# To see updated values hit th panel a va to change an

This preview shows page 52 - 55 out of 383 pages.

random variable into a normal random variable with the desired mean and standard deviation. To see updated values, hit th Panel A: Va To change an input, change one of the blue values in Columns C or D. To see an updated set of trial values, hit the F9 key. Inputs and key results will update for the current trial. Inputs from Sc Values for Column E used for Figure 7 in printed book. See this cell's Comment. Inputs for Simulation Probability Distributions Random Variables Used in Current Simulation Trial Expected Value of Input Standard Deviation of Input Standard Normal Random Variable Value Used in Current Trial Must be checked to put random variable in data table for simulation A B C D E F G H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
Units sold, Year 1 10,000 1,061 1.012 11,074 -0.045 10,000 % in units sold, after Year 1 Δ 15.00% 7.07% 0.904 21.39% 0.198 15.00% Sales price per unit, Year 1 \$1.50 \$0.18 −0.106 \$1.48 0.837 \$1.50 % in sales price, after Year 1 Δ 4.00% 4.00% Var. cost per unit (VC), Year 1 \$1.07 \$0.07 −0.165 \$1.06 -0.706 \$1.07 % in VC, after Year 1 Δ 3.00% 3.00% Nonvar. cost (Non-VC), Year 1 \$2,120 \$148 −1.479 \$1,900 0.277 \$2,120 % in Non-VC, after Year 1 Δ 3.00% 3.00% Project WACC 10.00% 10.00% Tax rate 40.00% 7.07% −1.686 28.08% 0.494 40.00% NOWC as % of next year's sales 15.00% 15.00% 0.65 Key Results Key Results Based on Current Trial Base NPV \$4,340 \$1,048 IRR 23.31% 13.79% MIRR 19.90% 12.78% PI 1.41 1.10 Payback 3.16 3.39 Discounted payback \$3.44 \$3.80 Panel B: Project Analysis for Current Trial in Simulation Using Inputs from Figure 11-7 Column F Intermediate Calculations 0 1 2 3 Unit sales 11,074 13,442 16,318 Sales price per unit \$1.48 \$1.54 \$1.60 Variable cost per unit (excl. depr.) \$1.06 \$1.09 \$1.12 Nonvariable costs (excl. depr.) ### ### ### Sales revenues = Units × Price/unit ### ### ### ### ### ### ### Basis for depreciation ### Annual depreciation rate (MACRS) 33.33% 44.45% 14.81% Annual depreciation expense ### ### ### Remaining undepreciated value ### ### ### Cash Flow Forecast Cash Flows at End of Year 0 1 2 3 Sales revenues = Units × Price/unit \$16,404 \$20,709 \$26,144 Variable costs = Units × Cost/unit \$11,719 \$14,653 \$18,321 Nonvariable costs (excluding depreciation) \$1,900 \$1,957 \$2,016 Depreciation \$2,695 \$3,594 \$1,198 Earnings before interest and taxes (EBIT) \$89 \$504 \$4,610 Taxes on operating profit (40% rate) \$25 \$142 \$1,294 Net operating profit after taxes \$64 \$363 \$3,315 Add back depreciation \$2,695 \$3,594 \$1,198 Equipment purchases −\$8,087 Salvage value Cash flow due to tax on salvage value (40% rate) Cash flow due to change in WC −\$2,461 −\$646 −\$815 −\$1,029 Opportunity cost, after taxes \$0 \$0 \$0 \$0 After-tax cannibalization or complementary effect \$0 \$0 \$0 Project net cash flows: Time Line −\$10,547 \$2,113 \$3,142 \$3,484 Project Evaluation Measures NPV \$4,340 IRR 23.31% MIRR 19.90% Profitability index 1.41 Payback 3.16 Assumed correlation between units sold in Year 1 and annual change in units sold in later years: r = NOWC t = 15%(Revenues t+1 ) A B C D E F G H 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
Discounted payback 3.44 Calculations for Payback Year: 0 1 2 3 Cumulative cash flows for payback ### ### ### ### Discounted cash flows for disc. payback ### ### ### ### Cumulative discounted cash flows ### ### ### ### How the Simulation Works Column input cell to "trick" Excel into updating random variables in Data Table: 1 Don't change th Note: If results are all zeros, go back to row 17 and "check" the box by clicking it with your cursor.

Want to read all 383 pages?

Want to read all 383 pages?

#### You've reached the end of your free preview.

Want to read all 383 pages?