Infocomp Systems Lab is a research and development (R&D) company that develops computer systems and software primarily for the medical industry. The lab has proposals from its own researchers for eight new projects. Each of the proposed research projects requires limited resources, and it is not possible to undertake all of them. The following table shows the developmental budget, the number of researchers, and the expected annual sales from each project if successfully developed and implemented:
|
Project
|
Developmental Budget ($1,000,000s)
|
Number of Research Personnel
|
Expected Annual
Sales ($1,000,0005)
|
|
1
|
$0.675
|
6
|
$0.82
|
|
2
|
1.050
|
5
|
1.75
|
|
3
|
0.725
|
7
|
1.60
|
|
4
|
0.430
|
8
|
l.90
|
|
5
|
l.240
|
10
|
0.93
|
|
6
|
0.890
|
6
|
1.70
|
|
7
|
l.620
|
7
|
1.30
|
|
8
|
l.200
|
6
|
l.80
|
(1) The company would like to remain within a total developmental budget of $5,000,000.
(2) The number of available research personnel is 27, and Infocomp would like to avoid obtaining extra researchers.
(3) The company would like the expected future annual sales from the implemented projects to be at least $6,500,000.
(4) Projects 1,3,4, and 6 are considered offensive in that they represent new product initiatives, while projects 2, 5, 7, and 8 are existing product upgrades and thus defensive in nature. The lab would like to select at least two projects from each group.
(5) Projects 2, 3, 5, 6, and 7 are considered the most risky of the projects, and the company would prefer not to select any more than three of these projects.
(6) The lab's owner has indicated that she would like to see projects 5 and 6 initiated if doing so would not interfere with the achievement of any of the more important goals determined by the lab's top management.
a. Formulate a goal programming model to determine which projects Infocomp Systems Lab should select to best achieve its goals.
b. Solve this model by using MS Excel. (Note that the solution requires 0-1 integer values for the variables in the model.).
Please be detailed, it would be great if you could show screenshots of excel