# 80 panel b project analysis for current trial in

• Lab Report
• 547
• 100% (5) 5 out of 5 people found this document helpful

This preview shows page 77 - 79 out of 547 pages.

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 9,649 10,956 12,440 Sales price per unit \$1.36 \$1.41 \$1.47 Variable cost per unit (excl. depr.) \$1.17 \$1.21 \$1.25 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 \$13,125 \$15,499 \$18,302 Variable costs = Units × Cost/unit \$11,333 \$13,255 \$15,501 Nonvariable costs (excluding depreciation) \$2,102 \$2,166 \$2,231 Depreciation \$2,697 \$3,597 \$1,199 Earnings before interest and taxes (EBIT) −\$3,008 −\$3,518 −\$628 Taxes on operating profit (40% rate) −\$1,288 −\$1,507 −\$269 Net operating profit after taxes −\$1,720 −\$2,012 −\$359 Add back depreciation \$2,697 \$3,597 \$1,199 Equipment purchases −\$8,093 Profit from salvage value Cash flow due to tax on salvage value (40% rate) Cash flow due to change in WC −\$1,969 −\$356 −\$421 −\$497 Opportunity cost, after taxes \$0 \$0 \$0 \$0 After-tax cannibalization or complementary effect \$0 \$0 \$0 Project net cash flows: Time Line −\$10,061 \$621 \$1,165 \$343 Project Evaluation Measures NPV -\$5,174 IRR -11.43% MIRR -8.17% Profitability index 0.49 Payback #VALUE! Discounted payback #VALUE! Calculations for Payback Year: 0 1 2 3 Cumulative cash flows for payback ### ### ### ### Discounted cash flows for disc. payback ### ### ### ### Cumulative discounted cash flows ### ### ### ### sold in Year 1 and annual change in units sold in later years: r = NOWC t = 15%(Revenues t+1 )
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 144 and "check" the box by clicking it with your cursor. Figure 11-8 Summary of Simulation Results (Thousands of Dollars) Number of Trials 100 Input Variables Average \$8,093 9,649 14% \$1.50 \$1.07 \$2,128 Standard deviation \$0 0 6% \$0.19 \$0.08 \$153 Maximum \$8,093 9,649 28% \$1.96 \$1.24 \$2,638 Minimum \$8,093 9,649 −1% \$0.95 \$0.90 \$1,710 Correlation with unit sales −0% NPV We use a Data Table to perform the simulation (the Data Table is below shaded in lavender). When the Data Table is updated new random variables for each of the inputs we allow to change in Figure 11-7 above, run the analysis in Panel B above, and t NPV for each trial. (We also save the input variables for each trial so that we can verify that they are behaving as we expect.) first column of the Data Table (the variable to be changed in each row) to numbers from 1-100. We don't really use these num anywhere in the analysis, but if we tell the Data Table to treat these as the Column inputs, Excel will recalculate all items in th including the random inputs and the resulting NPV. In other words, we "trick" Excel into doing a simulation. We tell Excel to i the Column inputs in the Data Table into the cell immediately below this box. This cell isn't linked to anything else, but each t updates a row of the Data Table, all the random values will be updated.