An engineering firm would like to increase its return on five potential new engineering and development projects, as shown in the accompanying project information and model, but the project manager knows that this will require more cash and/or personnel. Conduct a what-if analysis of the optimal solution as the cash limitation varies from $150,000 to $230,000 in increments of $20,000, and the personnel limitation varies from 12 to 16. Summarize the returns for these solutions in the form of a heat map. Identify the solutions that represent the lowest amount of resources required to achieve each distinct retum value. Write a short memo to the project manager explaining the best options and trade-offs that must be made to increase the returns. Click the icon to view the project information and the model. A Heat map b Choose the heat map that correctly summarizes the retums for each solution. $150,000 $170,000 $190,000 $210,000 $230,000 121 Click here to view heat map b. Click here to view heat map d. Click here to view heat map a. Click here to view heat map c. . 13 14 15 Identify the solutions that represent the lowest amount of resources required to achieve each distinct return value. Complete the table below. 16 Personnel Cash Requirement $ * Engineering Project Data and Model Print Done Lowest Return Second Lowest Retur Third Highest Return Second Highest Return Highest Return $ $ ODD $ A B D E F G 1 Project 1 Project 2 Project 3 Project 4 Projects Available Resources 2 Expected Return (NPV) $180,000 220,000 $150,000 $140,000 $200,000 3 Cash Requirements $80,000 $80.000 $29,000 $54,000 566,000 4 4 Personnel Requirements 5 3 2 5 3 5 6 Project Selection Decisions 0 0 0 0 0 7 Retum =SUMPRODUCT(B2:F2,B6F6) Cash Used SUMPRODUCT(B3:F3, B6:56) 9 Personnel Used =SUMPRODUCT(84:F4,B6:F6) Note that the values in B8:F6 are binary variables, where each value will be 1 if the corresponding project is selected. Enter your answer in the edit fields and then click Check Answer. nart An engineering firm would like to increase its return on five potential new engineering and development projects, as shown in the accompanying project information and model, but the project manager knows that this will require more cash and/or personnel. Conduct a what-if analysis of the optimal solution as the cash limitation varies from $150,000 to $230,000 in increments of $20,000, and the personnel limitation varies from 12 to 16. Summarize the returns for these solutions in the form of a heat map. Identify the solutions that represent the lowest amount of resources required to achieve each distinct retum value. Write a short memo to the project manager explaining the best options and trade-offs that must be made to increase the returns. Click the icon to view the project information and the model. A Heat map b Choose the heat map that correctly summarizes the retums for each solution. $150,000 $170,000 $190,000 $210,000 $230,000 121 Click here to view heat map b. Click here to view heat map d. Click here to view heat map a. Click here to view heat map c. . 13 14 15 Identify the solutions that represent the lowest amount of resources required to achieve each distinct return value. Complete the table below. 16 Personnel Cash Requirement $ * Engineering Project Data and Model Print Done Lowest Return Second Lowest Retur Third Highest Return Second Highest Return Highest Return $ $ ODD $ A B D E F G 1 Project 1 Project 2 Project 3 Project 4 Projects Available Resources 2 Expected Return (NPV) $180,000 220,000 $150,000 $140,000 $200,000 3 Cash Requirements $80,000 $80.000 $29,000 $54,000 566,000 4 4 Personnel Requirements 5 3 2 5 3 5 6 Project Selection Decisions 0 0 0 0 0 7 Retum =SUMPRODUCT(B2:F2,B6F6) Cash Used SUMPRODUCT(B3:F3, B6:56) 9 Personnel Used =SUMPRODUCT(84:F4,B6:F6) Note that the values in B8:F6 are binary variables, where each value will be 1 if the corresponding project is selected. Enter your answer in the edit fields and then click Check Answer. nart