PLZ SHOW THIS IN EXCEL AND PROVIDE DETAILED ANSWER

USE EXCEL TREE PLAN TO CONSTRUCT BOTH DECISION TREES

Please perform an analysis of the problem facing the Jean-Jacques Berthoud Pharmaceutical Company (Berthoud), and prepare a report that summarizes your findings and recommendations. Include the following items in your report:

1. A (simple) decision tree that shows the logical sequence of the decision problem given the EMRC research information is not available.

2. A recommendation regarding what Berthoud should do if the EMRC information is not available.

Berthoud has a very strong research branch the Lavoisier Medical Research Institute or Lavoisier Institut de Recherche Mdicale (LIRM), where renowned scientists, including one Nobel Prize winner, work side by side with young and enthusiastic recent university graduates. The scientists from the LIRM came up with two revolutionary ideas responding to the challenges of immunotherapy. They started developing a medication (let us call it drug A as the true chemical name is very long) that is supposed to be at least as efficient as existing types of immunotherapeutic medications, but much less expensive and therefore available to most patients. Meanwhile, another group at the LIRM are working on drug B (let us call it this), which will be expensive still, but is based on new principles and is expected to be extremely efficient with minimum side effects. Both research directions need a lot of funding, but they could generate a huge profit for Berthoud. Drug B would potentially become more profitable for the company than drug A, but drug A research has a higher likelihood of being completed within 1-2 years. At this point, the company has some financial difficulties and cannot fully support these LIRM projects. Therefore, the company's research teams are going to apply to the famous Hirschmann Scientific Foundation, known to be very generous when it comes to anticancer research. According to the rules, however, the foundation will consider only one project, so the company is facing a dilemma: to go ahead with project A or project B if they want to apply for funding. Each project has an estimated cost of $60 million, and the foundation can cover half of it. The rest will be invested by the company. At this point the management think that they have an 80% chance to get support from Hirschmann Scientific Foundation if they submit the project A proposal and a 70% chance to get support from Hirschmann Scientific Foundation if they submit the project B proposal. If the company's proposal is rejected or not submitted for whatever reason, then Berthoud will continue to produce existing immunotherapeutic medications instead of developing new ones. Not necessarily, every promising idea ends up in success. If funding is received for drug A, then, realistically speaking, the detailed forecast would look like this: Outcomes for drug A research Great success Moderate success Some success No success Probability 0.20 0.10 0.30 0.40 Profit/Loss ($ millions) 500 200 50 100 If funding is received for drug B, then the detailed forecast would not be readily available as the research will be based on the methods that have never been used before. Then the forecast would look like this: Outcomes for drug B research Success Failure Probability 0.25 0.75 Profit/Loss ($ millions) 1000 - 250 If the company is denied financial support or prefers not to apply, then it will continue to produce the existing types of medications with a guaranteed profit of $50 million. Profits/losses listed above do not incorporate the supposed $30 million investment by the company in the research. The cost of preparing the proposal for Hirschmann Scientific Foundation is $1 million. However, some in the management think that the development and production of drug B entails too much risk and uncertainty, and it would be better to focus on drug A only. The company is considering hiring the Edinburgh Medical Research Company (EMRC) from UK to estimate their chances with the development and financial success of drug B. EMRC experts will provide a favourable report (success is the most likely outcome of the drug B research) or an unfavourable report (the research for drug B will most likely end up in failure). Success means that the medication with expected properties is created and approved by Health Canada. It is known that there is a 90% chance that EMRC provides a favourable report given the positive outcome. There is also an 80% chance that EMRC provides an unfavourable repot given the negative outcome. EMRC specialists need six months to complete their analysis and request $10 million as they have to use expensive equipment and hire additional staff. Of course, the company would like to get a report from EMRC before they make a decision and submit their proposal to Hirschmann Scientific Foundation. The company needs a qualified second opinion, as at the moment opinions among management itself are sharply divided and votes are split