Need help with the attached excel spreadsheet. Sheet 1 is problem, Sheet 2 is template I need to use.
Pharmgen Corporation has developed a new medication for the treatment of high blood pressure. Tests on laboratory animals have been promising, and the firm is ready to embark on human trials in order to gain approval from the Food & Drug Administration (FDA). The firm estimates that it will cost an additional $4million to do the required testing. Although the firm has this amount of money available, if the product turns out to be unsuccessful, the company will be virtually wiped out.
If the tests prove successful, the firm believes that the amount it will earn on the drug will be a function of the number of competing drugs of this type that also gain approval. If no other drugs gain approval, the firm estimates it will earn $50 million in present worth profits on the drug. For every other drug that gains approval, Pharmgen believes the expected present worth profit will drop by $10 million. Based on industry publications, four other firms are working on similar drugs. Pharmgen's management estimates that the probability that its drug will gain FDA approval is 40% but that each of its four competitors' drugs only has a 20% chance of FDA approval.
Wyler Laboratories, a larger drug firm, has approached Pharmgen about acquiring the rights to the drug. Wyler is willing to pay Pharmgen $5 million for a half interest in the drug (and split any costs or profits from the drug with Pharmgen) or $8 million for the full rights to the drug. Pharmgen's management has used the indifference approach to determine the utility values shown below.
Prepare a business report to Dr. Joseph Wolf, president of Pharmgen, detailing your recommendation regarding the development of this drug.
Worth Profit or
Loss (in millions) 24 3 8 10 15 20 25 30 40 50
Utility 0 .30 .40 .45 .50 .55 .65 .75 .85 1.0