Please see the attached decision analysis case study problem and the Excel spreadsheet.
RocketCase Decision Analysis Case Study You have been hired as a consultant for RocketCase, a company specializing in high-tech cell phone cases. RocketCase has developed a new iPhone case and is ready to bring it to market. RocketCase’s objective is to maximize net present value. RocketCase’s existing plant has the ability to produce 4 million iPhone cases per year. If demand is high, the production output of RocketCase‘s current factory will not be sufficient. RocketCase is considering whether to expand production capacity, but if demand is low the investment might not pay off. They are considering four options: 1. Continue with the existing factory (the “status quo” alternative). 2. Build and pay for the factory themselves (the do-it-yourself or “DITY” alternative). 3. Form a profit-sharing partnership with a larger, more experienced company, Case World 4. Form a royalty partnership with Case World. Profit-sharing partnership option: Case World will pay the entire cost of the new plant in return for a 50% share of net profit (revenues–costs). All production will take place at the new factory and RocketCase will pay for cost of goods sold (COGs). Marketing will be done by Case World. Royalty partnership option: RocketCase will pay royalties of 10% of revenues to Case World. All production will take place at the new plant and RocketCase will pay for the new plant and COGs. Marketing will be done by Case World. The following influence diagram illustrates the interrelationships among the variables of RocketCase decision. Low, Base and High values were obtained from subject matter experts for the uncertain variables in the model. The following assumptions were gathered from RocketCase: 1. Since the innovation cycle in this industry is so rapid, the cash flow evaluation time period is 2020-2025. 2. If the new facility is constructed, it must be paid for in full at the time of completion. Assume the new facility would be completed in 2020. 3. The production capacity of the new plant will be able to meet demand fully for certain. 4. Assume the only cash flow in 2020 is the cost of the new plant. Assume operations and the remaining cash flows start in 2021. This includes unit demand, units sold, revenue, costs, etc. 5. The cost of a new plant is uncertain. COGs is uncertain regardless of the option. 6. The number of units sold is determined from the minimum of demand and capacity. 7. Unit demand is determined from the initial demand in 2021 and the compound annual growth rate (CAGR) of demand. Initial Demand and CAGR are uncertain. Unit Demand = InitialDemand (1 + CAGR)^(Year – 2021), where Year = 2021 – 2025 8. If Case World markets the product, RocketCase management estimates that demand will be at least 40% greater than the RocketCase forecast, due to Case World’s superior marketing department. The impact on unit demand of Case World’s market muscle is uncertain. 9. RocketCase management has no control of what the unit price will be, i.e., unit price is uncertain. 10. RocketCase marketing costs are budgeted to be $5 million per year regardless whether they have an alliance with Case World. Therefore, exclude it from the evaluation. 11. Assume a 9% discount rate and a 21% tax rate. No tax credit. Neglect the depreciation of the new plant. 12. Financial equations: · Revenue = Units Sold Price · Cost = Units Sold COGs · Net Profit = Revenues – Costs · EBIT = Net Profit – Royalty – Profit Sharing · Earnings = EBIT – Tax Cash Flow = Earnings – Plant Cost QUESTION 1: Identify the following Decision Alternatives: Value Proposition: Uncertainties: QUESTION 2: Use Case Study Excel Template to construct an Excel model of RocketCase and determine the base-case NPVs of each alternative. Alternative NPV ($ million) Question 3: Link your Excel model to DPL and construct a tornado diagram for each alternative to determine which uncertainties are most sensitive. · Use the Low-Base-High values obtained to generate your tornado diagrams. · Title each tornado with the name of alternative and include the base-case NPV in parenthesis in the title, e.g., Status Quo (xx). · Use the same x-axis for NPV ($ million) for each tornado. · Attach all tornado diagrams below. Question 4: Get probability assessments from the subject matter expert on the key uncertainties from the following information: Initial Demand Compound Annual Growth Rate (CAGR) Price · Use the extended Swanson-Megill (ESM) to approximate the probability assessment obtained for the top-3 most-sensitive uncertainties. · The resulting 10-50-90 values from the ESM of the key uncertainties will replace the low, most-likely, and high values in your Excel model. · The 10-50-90 values of the top-3 most-sensitive uncertainties will be the only uncertainties used in the probabilistic evaluation (expected NPV and cumulative distribution function). · List the top-3 most-sensitive uncertainties and their 10-50-90 values included in your probabilistic model. Question 5: Determine the expected NPV ($ million) for each strategic alternative of RocketCase decision and indicate the optimal alternative. Express expected NPVs to the nearest million. Question 6: Construct a cumulative distribution function (CDF) for all of the alternatives (all alternatives should be a single CDF from DPL). Question 7: What is you recommendation and why? What insights (e.g., EVPI, chance of losing money) can you provide to RocketCase to give them confidence in making this decision? Net Present Value Unit Demand Units Sold Revenues Costs Discount Rate Net Profit Capacity Cash Flow Earnings EBIT Tax Royalty Payment Profit Sharing Payment Tax Rate Royalty Rate Profit Sharing Rate Existing Plant Capacity National Marketing Muscle Initial Demand Demand CAGR Unit Price COGs Plant Cost Build a new plant? Input INPUTS DescriptionUnitsVariable NameIn UseName of SwitchIndex Strategic OptionStrategyStrategySW2 DescriptionUnitsVariable NameIn UseName of SwitchIndexLowBaseHigh Market Initial Demand in 2021million units per yearInitialDemand0.0InitialDemandSW2 Demand Growth% per yearCAGR0%CAGRSW2 Price$ per unitPrice0.0PriceSW2 Case World Marketing Impact% per yearCWMktg0%CWMktgSW2 Costs Production Cost$ per unitCOGS0COGSSW2 Plant Cost$ millionPlantCost0PlantCostSW2 Deal Structure Terms Profit-Sharing Partnership% per yearProfitShare0%ProfitShareSW2 Royalty Partnership% per yearRoyalty0%RoyaltySW2 Global Variables Discount Rate%Discount9%DiscountSW29%9%9% Tax Rate%Tax21%TaxSW221%21%21% Existing Plant Capacitymillion unitsExistingPlantCap0ExistingPlantCapSW2 &F &A&D Output Strategy: CALCULATIONS 202020212022202320242025Year Unit Demand (million) Unit Demand Increase from Case World (million) Unit Demand Total (million) Units Sold (million) (production capacity limited) FINANCIAL RESULTS ($million) 202020212022202320242025 Revenue Cost Net Profit Royalty Profit Sharing EBIT Tax Earnings Plant Cost Cash Flow Net Present Value (NPV, $million)NPV &F &A&D