. Allied products has six R&D projects that are potential candidates for selecti
ID: 3174111 • Letter: #
Question
. Allied products has six R&D projects that are potential candidates for selection during the upcoming fiscal year. The table below provides the expected net present value and capital requirements over the next five yeas for each project. The table also indicates the planned budget expeditures for the entire R&D program during each of the next five years. Which projects should be selected?
Capital required (thousands)
Project NPV (thousand) Y1 Y2 Y3 Y4 Y5
1 140 90 25 22 18 10
2 260 95 50 10 10 20
3 88 58 17 14 12 12
4 176 115 25 25 10 0
5 192 48 20 12 30 40
---------------------------------------------------------------------------------------------------------------------------------------------------------
R&D Budget 180 60 70 40 35
==============================================================================================
(Please include definitions of all variables and constraints, a mathematical formulation, excel firmulation, all Solver (screenshots) entry, and a corret answer.)
Explanation / Answer
What is an Objective function?
An objective function can be the result of an attempt to express a business goal in mathematical terms for use in decision analysis, operations research or optimization studies. It is a function that is desired to maximize or minimize.
Lets start by declaring the variables in order to solve the problem.
Let A,B,C,D and E be binary variables for project 1,2,3,4 and 5 respectively.
Objective function = $140000A + 260,000B + 88,000C + 176,000D + 192,000E
As per the constraints ,
For year 1 ,
260,000B + 88,000C + 176,000D + 192,000E < = 180,000
For year 2,
260,000B + 88,000C + 176,000D + 192,000E < = 60,000
For year 3,
260,000B + 88,000C + 176,000D + 192,000E < = 70,000
For year 4,
260,000B + 88,000C + 176,000D + 192,000E < = 40,000
For year 5,
260,000B + 88,000C + 176,000D + 192,000E < = 35,000
As displayed in below mentioned screenshot, prepare an excel sheet. We have used the SUMPRODUCT() function to calculate the total amount needed.
Now , use solver and add parameters as follows:
Click on Solve button and you will get the below result:
Thus , Allied Products can select C and D and the total NPV value will be 163,000.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.