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 3 and 5 should be pursued, and that if project 2 is chosen, then project 3 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. 5 Click the icon to view the project information and the model. Identify the optimal solution for each cell in the table below. R&D Project Data and Model Engineers 45 30 $ 40 $ $ Budget $700,000 $1,000,000 $1,300,000 35 $ $ $ A B E F $ $ $ $ 1 $ $ Chosen Project 1 2 3 4 5 6 7 Net Present Value (NPV) $650,000 $400,000 $480,000 $550,000 $600,000 $725,000 $340,000 2 3 4 5 6 7 8 9 10 11 Internal Engineers 8 12 4 7 9 10 8 D Additional Costs $225,000 $180,000 $400,000 $70,000 $196,000 $200,000 $130,000 0 0 0 0 0 0 0 =SUMPRODUCT(D2:08,F2:F8) =SUMPRODUCT(C2:C8,F2:F8) A4 1,300,000 35 12 Cost Workforce At most one 3 and 5 If 2, then 3 Total NPV =F4 + F6 1 > =F3 13 14 =F4 =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. Print Done Enter your answer in the edit fields and then click Check Answer. 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 3 and 5 should be pursued, and that if project 2 is chosen, then project 3 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. 5 Click the icon to view the project information and the model. Identify the optimal solution for each cell in the table below. R&D Project Data and Model Engineers 45 30 $ 40 $ $ Budget $700,000 $1,000,000 $1,300,000 35 $ $ $ A B E F $ $ $ $ 1 $ $ Chosen Project 1 2 3 4 5 6 7 Net Present Value (NPV) $650,000 $400,000 $480,000 $550,000 $600,000 $725,000 $340,000 2 3 4 5 6 7 8 9 10 11 Internal Engineers 8 12 4 7 9 10 8 D Additional Costs $225,000 $180,000 $400,000 $70,000 $196,000 $200,000 $130,000 0 0 0 0 0 0 0 =SUMPRODUCT(D2:08,F2:F8) =SUMPRODUCT(C2:C8,F2:F8) A4 1,300,000 35 12 Cost Workforce At most one 3 and 5 If 2, then 3 Total NPV =F4 + F6 1 > =F3 13 14 =F4 =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. Print Done Enter your answer in the edit fields and then click Check