Please needs to be done all in excel

Tazer, a pharmaceutical manufacturing company, entered the pharmaceutical market 15 years ago with the introduction of six new drugs. Five of the six drugs were simply permutations of existing drugs and therefore did not sell very heavily. The sixth drug, however, addressed hypertension and was a huge success. Since Tazer had a patent on the hypertension drug, it experienced no competition, and profits from the hypertension drug alone kept Tazer in business. Pharmaceutical patents remain in force for 20 years, so this one has five more years before it expires.

During the past 15 years, Tazer continued a moderate amount of research and development, but it never stumbled upon a drug as successful as the hypertension drug. One reason is that the company never had the motivation to invest heavily in innovative research and development. The company was riding the profit wave generated by its hypertension drug and did not feel the need to commit significant resources to finding new drug breakthroughs.

Now Tazer is beginning to fear the pressure of competition. Tazer knows that once the patent expires in five years, generic drug manufacturing companies will swarm into the market like vultures. Historical trends show that generic drugs decrease sales of branded drugs by 75 percent.

Tazer is therefore looking to invest significant amounts of money in research and development this year to begin the search for a new breakthrough drug that will offer the company the same success as the hypertension drug. Tazer believes that if the company begins extensive research and development now, the probability of finding a successful drug shortly after the expiration of the hypertension patent will be high.

As head of research and development at Tazer, you are responsible for choosing potential projects and assigning project directors to lead each of the projects. After researching the needs of the market, analyzing the shortcomings of current drugs, and interviewing numerous scientists concerning the promising areas of medical research, you have decided that your department will pursue five separate projects, which are listed below:

Project Up: Develop a more effective antidepressant that does not cause serious mood swings.

Project Stable: Develop a drug that addresses manic-depression.

Project Choice: Develop a less intrusive birth control method for women.

Project Hope: Develop a vaccine to prevent HIV infection.

Project Release: Develop a more effective drug to lower blood pressure.

For each of the five projects, you are only able to specify the medical ailment the research should address since you do not know what compounds will exist and be effective without research.

You also have five senior scientists to lead the five projects. You know that scientists are very temperamental people and will only work well if they are challenged and motivated by the project. To ensure that the senior scientists are assigned to projects they find motivating, you have established a bidding system for the projects. You have given each of the five scientists 1,000 bid points. They assign bids to each project, giving a higher number of bid points to projects they most prefer to lead.

f. You decide that Project Hope and Project Release are too complex to be led by only one scientist. Therefore, each of these projects will be assigned two scientists as project leaders. You decide to hire two more scientists in order to staff all projects: Dr. Arriaga and Dr. Santos. Because of religious reasons, neither of them wants to lead Project Choice and so they assign 0 bid point to this project. The following table lists all projects, scientist, and their bids