Inputs from Sc Inputs for Simulation Probability Distributions Random Variables

Inputs from sc inputs for simulation probability

This preview shows page 94 - 96 out of 547 pages.

Inputs from Sc 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 Assumed correlation between units sold in Year 1 and annual change in units sold in later years: r =
Image of page 94
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 9,326 10,101 10,940 Sales price per unit $1.37 $1.42 $1.48 Variable cost per unit (excl. depr.) $0.99 $1.02 $1.05 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 $12,730 $14,339 $16,152 Variable costs = Units × Cost/unit $9,247 $10,316 $11,508 Nonvariable costs (excluding depreciation) $1,993 $2,052 $2,114 Depreciation $2,511 $3,349 $1,116 Earnings before interest and taxes (EBIT) −$1,021 −$1,379 $1,413 Taxes on operating profit (40% rate) −$421 −$569 $583 Net operating profit after taxes −$600 −$810 $831 Add back depreciation $2,511 $3,349 $1,116 Equipment purchases −$7,535 Profit from salvage value Cash flow due to tax on salvage value (40% rate) Cash flow due to change in WC −$1,909 −$241 −$272 −$306 Opportunity cost, after taxes $0 $0 $0 $0 After-tax cannibalization or complementary effect $0 $0 $0 Project net cash flows: Time Line −$9,444 $1,670 $2,267 $1,640 Project Evaluation Measures NPV -$1,267 =NPV(E69,F111:I111)+E111 IRR 4.61% =IRR(E111:I111) MIRR 6.11% =MIRR(E111:I111,E69,E69) Profitability index 0.87 =NPV(E69,F111:I111)/(-E111) Payback 3.74 =PERCENTRANK(E120:I120,0,6)*I119 Discounted payback #VALUE! =PERCENTRANK(E122:I122,0,6)*I119 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 NOWC t = 15%(Revenues t+1 ) We use a Data Table to perform the simulation (the Data Table is below shaded in lavender). When the Data Table is updated, it will ins random variables for each of the inputs we allow to change in Figure 11-7 above, run the analysis in Panel B above, and then save the N trial. (We also save the input variables for each trial so that we can verify that they are behaving as we expect.) We set the first column Table (the variable to be changed in each row) to numbers from 1-100. We don't really use these numbers anywhere in the analysis, bu Data Table to treat these as the Column inputs, Excel will recalculate all items in the Data Table, including the random inputs and the re In other words, we "trick" Excel into doing a simulation. We tell Excel to insert each of the Column inputs in the Data Table into the cell below this box. This cell isn't linked to anything else, but each time Excel updates a row of the Data Table, all the random values will be Excel normally updates all values in a Data Table each time any cell that is related to the Data Table changes. In our case, we have rando in the Data Table, so each time any cell in the worksheet makes a calculation, the Data Table is updated. If the Data Table has many row
Image of page 95
Image of page 96

You've reached the end of your free preview.

Want to read all 547 pages?

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture