A medical device company is allocating next year's budget among its divisions. As a result, the R&D Division needs to determine which R&D projects to fund. Each project requires various software and hardware and consulting expenses, along with internal human resources. A budget allocation of $1,300,000 has been approved, and 35 engineers are available to work on the projects. The R&D group has determined that at most one of projects 5 and 6 should be pursued, and that if project 2 is chosen, then project 5 must also be chosen. The accompanying model was developed to select the best projects within the budget. Analyze the sensitivity of the optimal solution for the medical device company as the budget and available engineers are varied. Write a short summary memo to the manager in which you outline the results and make recommendations Click the icon to view the project information and the model Identify the optimal solution for each coll in the table below. Engineers Budget 30 35 $1,000,000 $ $ $ $1,300,000 $ s $ si $1,600,000 S si 40 45 s $ E F 0 B Internal 1 Additional Project Net Present Value (NPV) Chosen Engineers Costs 2 1 $650,000 8 $225,000 3 2 $400,000 12 $180,000 4 3 $340,000 8 $130,000 5 4 $725,000 10 $200,000 6 5 $580,000 4 $400,000 7 6 $500,000 9 $196,000 8 7 $550,000 7 $70,000 9 10 Cost =SUMPRODUCT(D2:08,F2:F8) 1,300,000 11 Workforce =SUMPRODUCT(C2:C8,F2:F8) = 35 At most one 5 =F6+ F7 1 and 6 13 if 2, then 5 =F6 =F3 14 Total NPV =SUMPRODUCT(B2:38,F2:F8) Note that the values in F2:F8 are binary variables, where each value will be 1 if the corresponding project is selected. OOOOOOO M A A A 12