solution for Automation Project" and "Fosbuvir Project"

Crystal Ball Data Workbook Variables Name: Value: Last Var Column Worksheet Data Last Data Column Used 3 Sheet Ref Err:509 Err:509 Sheet Guid 9bbac9de-130f-4cff-8cb1-1f2a360a2a8b f12410cb-8654-4745-8b8f-f89e0f861aa4 Deleted sheet count Last row used 28 31 Data blocks CB_Block_0 CB_Block_7.0.0.0:1 Decisioneering:7.0.0.0 Decisioneering:7.0.0.0 CB_Block_0 Decisioneering:7.0.0.0 0 Err:509 2a62b937-bf2c-4a11-bfb5-be42818d1d60 31 CB_Block_7.0.0.0:1 Decisioneering:7.0.0.0 CB_Block_0 Decisioneering:7.0.0.0 #NAME? Automation Project Fosbeck's Automation Project Solution Legend Value given in problem Given Investment cost (today) Project life Annual labor costs savings Annual waste disposal cost savings Annual Byproduct sales Required rate of return Tax rate MACRS Schedule year 1 33% year 2 45% year 3 15% year 4 7% ($400.00) $5.00 $50.00 $10.00 $200,000.00 $0.12 35% Formula/Calculation/Analysis required Assumptions, Qualitative analysis or Short answer required Goal Seek, Scenario or Data Table cell Crystal Ball Input Crystal Ball Output Solution Basic Analysis (All numbers in $ Mil) Cash flow estimation Investment Annual labor costs savings Annual waste disposal cost savings Annual Byproduct sales EBITDA Less: Depreciation Additional EBIT Less: Taxes NOPAT Plus: Depreciation FCF NPV IRR Analysis Year 0 ($400.00) ($400.00) 1 2 3 4 5 $50.00 $50.00 $50.00 $50.00 $50.00 $10.00 $10.00 $10.00 $10.00 $10.00 $200,000.00 $200,000.00 ### ### ### $200,060.00 $200,060.00 ### ### ### ($132.00) ($180.00) ($60.00) ($28.00) ($400.00) $199,928.00 $199,880.00 ### ### ### $69,974.80 $69,958.00 $70,000.00 $70,011.20 $70,021.00 $129,953.20 $129,922.00 ### ### ### $132.00 $140.00 $60.00 $28.00 ### ### ### ### ### $468,442.25 32521.28% Scenario Analysis Sales and NPV numbers are in $ Mil Scenario probability Base Byproduct sales decrease by 10% Byproduct sales decrease by 30% Byproduct sales decrease by 50% Byproduct Sales NPV IRR Expected NPV Breakeven Breakeven byproduct sales The terminal period growth rates were estimated such that the intrinsic valuation of the firm's equity would equal the current market capitalization of the firm using the "Goal Seek" function. Page 32 CoGS ratio growth SGA CapEx Revenue1 Project Life Tax rate R&D probability of approval probability of obsolescence WACC 15% 50% $2.00 $2.00 $10.00 10 years 38% $0.60 10% 5% 12% Year Probability of Success Revenue Cost SGA R&D Depreciation (unconditional) EBIT Taxes Net Income 2017 2018 2019 0.1 $10.00 $1.50 $2.00 2020 0.95 $10.00 $1.50 $2.00 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.60 OCF CapEx FCF $2.00 NPV IRR Real Option Modification CapEx 1 CapEx 2 Depreciation $0.80 10 years $1.20 8 years Two-stage investment alternative can be evaluated either using Crystal Ball, or by simply calculating the N Using Crystal Ball Year Probability of Success Revenue Cost SGA R&D Depreciation EBIT 2017 2018 2019 2020 Taxes Net Income OCF CapEx FCF NPV IRR Evaluating two outcomes Separately If approved Year Probability of Success Revenue Cost SGA R&D Depreciation EBIT Taxes Net Income 2017 2018 2019 2020 2017 2018 2019 2020 OCF CapEx FCF NPV IRR If fails Year Probability of Success Revenue Cost SGA R&D Depreciation EBIT Taxes Net Income OCF CapEx FCF NPV IRR Expected NPV 2021 0.95 $10.00 $1.50 $2.00 2022 0.95 $10.00 $1.50 $2.00 2023 0.95 $10.00 $1.50 $2.00 2024 0.95 $10.00 $1.50 $2.00 2025 0.95 $10.00 $1.50 $2.00 2026 0.95 $10.00 $1.50 $2.00 2027 0.95 $10.00 $1.50 $2.00 2028 0.95 $10.00 $1.50 $2.00 2029 0.95 $10.00 $1.50 $2.00 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 $0.20 $6.30 $2.39 $3.91 simply calculating the NPV for two different outcomes and then finding the expected value 2021 0.1 2022 0.95 2023 0.95 2024 0.95 2025 0.95 2026 0.95 2027 0.95 2028 0.95 2029 0.95 2021 2022 2023 2024 2025 2026 2027 2028 2029 2021 2022 2023 2024 2025 2026 2027 2028 2029 Solution Legend Value given in problem Formula/Calculation/Analysis required Assumptions, Qualitative analysis or Short answer required Goal Seek, Scenario or Data Table cell Crystal Ball Input Crystal Ball Output CoGS ratio 15% growth 50% SGA Fosbeck $2.00 SGA Pharmaset $3.50 Pharmaset PPE $3.00 Revenue1 $10.00 Project Life 10 years Tax rate 38% R&D $0.60 Pharmaset probability of ap 40% probability of obsolescence 5% WACC 12% Year 2017 2018 Probability of Success 0.4 Revenue Cost SGA if acquired Depreciation (unconditional) EBIT Taxes Net Income OCF FCF Value if acquired 2019 0.95 2020 0.95 2021 0.95 2022 0.95 2023 0.95 2024 0.95 2025 0.95 2026 0.95 2027 0.95 2028 0.95 Solution Legend Value given in problem Formula/Calculation/Analysis required Assumptions, Qualitative analysis or Short answer required Goal Seek, Scenario or Data Table cell Crystal Ball Input Crystal Ball Output CoGS ratio growth SGA Fosbeck SGA Pharmaset Pharmaset PPE Revenue1 Project Life Tax rate R&D Pharmaset probability of a probability of obsolescence WACC Menlo Venture Investment Valuation multiple Year Revenue Cost SGA if acquired Depreciation (unconditional) EBIT Taxes NOPAT Terminal Value Pharmaset Book Value 15% 50% $2.00 $3.50 $3.00 $10.00 10 years 38% $0.60 40% 5% 12% $5.00 8 x EBIT Pharmaset If FDA Approved 2017 2018 2019 2020 2021 All Equity Case Menlo Ventures Share Dividends 1-4 Dividends 5-8 20% 0% 20% Fosbeck Incremental Cash Flow (After-Tax) 2017 2018 2019 2020 2021 If Successful If Fails Expected NPV IRR Convertible Debt Menlo Ventures Share 15% Coupon Rate 10% Fosbeck Incremental Cash Flow (After-Tax) 2017 2018 2019 2020 2021 If Successful If Fails Expected NPV IRR Redeemable Preferred Menlo Ventures Share Dividend Rate Warrants Price If Successful If Fails Expected NPV IRR 15% 7.5% $0.15 Fosbeck Incremental Cash Flow (After-Tax) 2017 2018 2019 2020 2021 Solution Legend Value given in problem Formula/Calculation/Analysis required Assumptions, Qualitative analysis or Short answer required Goal Seek, Scenario or Data Table cell Crystal Ball Input Crystal Ball Output 2022 2023 2024 2025 2022 2023 2024 2025 -Tax) -Tax) 2022 2023 2024 2025 2022 2023 2024 2025 -Tax) wer required