Using the Excel Template (Attached screenshots of the template) and the information provided in the Project 1 description Below, construct an Excel-based model and determine the base-case NPVs for each alternative.

The following information was obtained from subject matter experts for this Go/NoGo decision.

1. Time horizon of analysis is 2020 - 2025. The discount rate is 9%.
2. Market Size is a compound annual growth rate (CAGR) model (see figure below), beginning in 2020 and ending in 2025.
   1. Initial Market Size in the year 2020 10-50-90 values are 2, 3, and 4 million units.
   2. CAGR for Market Size 10-50-90 values are -5%, 10%, and 20%.
3. Market Share 10-50-90 values are 25%, 50%, and 75%, for the Go strategy in the years 2020-2025.
4. Units Sold = Market Size Market Share, for the Go strategy.
5. Pricing for the Go strategy is a linear growth model (see figure below), beginning in 2020 and ending in 2025.
   1. Initial price in year 2020 is $20 per unit.
   2. Final Price 10-50-90 values in year 2025 are $20 per unit, $25 per unit, and $30 per unit.
6. Costs for the Go strategy 10-50-90 values are $30 million per year, $40 million per year, and $50 million per year in the years 2020-2025.
7. Revenue = Pricing Units Sold, for the Go strategy.
8. Cash Flow = Revenue - Costs.

Linear growth model: y = y1 + (y2 - y1) / (x2 - x1) * (Year - x1) CAGR model: y = y1 (1 + CAGR)^(Year - x1)

Show transcribed data

Expert Answer

Go 2020 2021 2022 2023 2024 2025 Market Size Market Share Units Sold Price FINANCIAL RESULTS ($ million) 2020 2021 2022 2023 2024 2025 Year Revenue Costs Cash Flow Net Present Value NPVINPUTS Description Units Variable Name In Use Name of Switch Index Alternative 1 Alternative 2 Strategic option Strategy Go Strategy.SW 2 NoGo Go Base-Case Values Description Units Variable Name In Use Name of Switch Index 10th percentile 50th percentile 90th percentile Initial Market Size in 2020 million units InitialMarketSize 3 InitialMarketSizeSW 2 3 10-50-90 values: 2, 3, 4 Compound Annual Growth Rate for market size CAGR 10% CAGRSW -5% 10% 20% 10-50-90 values: -5%, 10%, 20% Market Share MarketShare 50% MarketShareSW 25% 50% 75% 10-50-90 values: 25%, 50%, 75% Initial Price in 2020 S per unit InitialPrice 20 InitialPriceSW NNNNNNN 20 20 20 constant Final Price in 2025 S per unit FinalPrice 25 FinalPriceSW 20 25 30 10-50-90 values: 20, 25, 30 Costs $ million per year Costs 40 Costs SW 30 40 50 10-50-90 values: 30, 40, 50 Discount Rate Discount 9% DiscountSW 9% 9% 9% constant Net Present Value (S million) 0.00