70.2K

Verified Solution

Link Copied!

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 1 and 3 should be pursued, and that if project 2 is chosen, then project 1 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.

	A	B	C	D	E	F
1	Project	Net Present Value (NPV)	Internal Engineers	Additional Costs		Chosen
2	1	$580,000	4	$400,000		0
3	2	$400,000	12	$180,000		0
4	3	$500,000	9	$196,000		0
5	4	$550,000	7	$70,000		0
6	5	$340,000	8	$130,000		0
7	6	$725,000	10	$200,000		0
8	7	$650,000	8	$225,000		0
9
10	Cost	=SUMPRODUCT(D2:D8,F2:F8)	<=	1,300,000
11	Workforce	=SUMPRODUCT(C2:C8,F2:F8)	<=	35
12	At most one 1 and 3	=F2+F4	<=	1
13	If 2, then 1	=F2	>=	=F3
14	Total NPV	=SUMPRODUCT(B2:B8,F2:F8)

Note that the values in F2:F8 are binary variables, where each value will be 1 if the corresponding project is selected

Identify the optimal solution for each cell in the table below.

	Engineers
Budget	30	35	40	45
$1,000,000
$1,300,000
$1,600,000

FILL IN THE BLANKS/CHOOSE

For combinations with the smallest optimal solution possible, the best option is a budget of $____ AND ___ ENGINEERS.

To achieve a higher return, it (1. IS ALWAYS NECESSARY TO DECREASE BOTH THE AVAILABLE WORKFORCE AND THE BUDGET. 2. IS NECESSARY TO INCREASE AT LEAST ONE OF THE AVAILABLE WORKFORCE OR THE BUDGET. 3. IS ALWAYS NECESSARY TO INCREASE BOTH THE AVAILABLE WORKFORCE AND THE BUDGET. 4. IS NECESSARY TO DECREASE AT LEAST ONE OF THE AVAILABLE WORKFORCE OR THE BUDGET. )

Increasing the budget while holding the number of engineers constant will (1. ALWAYS 2. NOT ALWAYS) increase the return, while increasing the number of available engineers while holding the budget constant will (1. ALWAYS 2. NOT ALWAYS) increase the return.

For all given values of (1. # OF ENGINEERS. 2. BUDGET) in the table, increasing the (1. # OF ENGINEERS. 2. BUDGET)does not always increase the return.

The optimal solution for a budget of $1,300,000 and 35 available engineers is a return of $___

For this particular level of return and for the budgets and numbers of engineers considered, the best overall combination is to use a budget of $____

Answer & Explanation Solved by verified expert

See Answer