Problem 1 (65 points) The problem uses the spreadsheet titled Diversified Healthcare Data found in Articles and Other Tools folder, within Modules on Canvas. Diversified Healthcare is a healthcare company that is comprised of 4 business units: Consumer Goods (CG), Diagnostics (DIA), Medical Devices (MD), and Prescription Drugs (Rx). Each business unit has 5 projects, so the company has a total of 20 projects at different stages of development across its portfolio. The challenge for management is to decide on which projects to continue investing in since it is constrained by the size of its budget, as well as the current number of engineering resources on board. We will look at the prioritization problem in different ways to illustrate how these different approaches can yield different results. There is a goal to have at least one project from each business unit.

a) Calculate the Aggregate Score for each project assuming you are ranking on Strategic Value, Top Line Sales, NPV, and Risk. Be sure to apply the weighting factor given in the spreadsheet. Because there is such a range of values among the criteria, it is helpful to normalize all the data, so I did that for you in the spreadsheet provided. Rank the projects based on their Aggregate Score using the normalized data. If management wanted to cap R&D resources at 450 and the budget at $625M, what are aggregate sales for that scenario? (10 points)

b) Create an Efficient Frontier curve, like Figure 12-1 in our text. You will need to calculate the Benefit-to-Cost (BCR) ratio for each project (NPV/Total R&D Cost). Can you identify any low value projects from your curve? Look where you curve begins to flatten out and draw your investment cut line. What are aggregate sales for this scenario and how many R&D resources and 2023 R&D budget are required? (15 points)

c) Use Excel Solver (or you can do it manually) to identify the optimal portfolio if only 450 resources will be available in 2023 with a budget that does not exceed $625M. Assume you are maximizing total NPV. Are all business units represented? What if instead you maximize 2023 sales, does it change and which projects get funded? (30 points)

d) Compare the results from the 3 approaches. What comments can you make? (10 points)