Please solve this using excel? (tree plan simple and revised) and show the steps

In today's world, there is a growing need for new and innovative drugs, vaccines, biologicals, diagnostics and gene therapeutics, particularly now that people are getting older and more and more microorganisms are becoming resistant to drugs. Jean-Jacques Berthoud Pharmaceutical Company (Berthoud Compagnie Pharmaceutique or Berthoud for short) began operations in a small lab McGill University in 1998, primarily focusing on meeting the chemical needs of local researchers. The company became known for its strong chemistry expertise and expanded both its operational lab footprint as well as its customer base. By 2010, Berthoud had outgrown its operational space and moved to its current 100,000 square foot facility in Montreal. The company provides drug discovery, development and manufacturing services to the pharmaceutical and biotechnology industries in Canada. The company is currently about to make an important strategic decision. It is becoming increasingly clear that in addition to surgery, chemotherapy, and radiation, cancer immunotherapy has grown into a new and very promising method of cancer treatment. Unlike other methods, immunotherapy focuses on using your body's own immune system to fight cancer. One factor is becoming more and more important over the last months. Recent statistics showed that the morbidity and mortality of COVID-19 is higher in elderly and male patients. Specifically in cancer patients, those who are elderly and/or who have lung cancer, hematologic malignancy, advanced-stage disease, active solid tumor, or a diagnosis of solid tumor in the past 5 years, or who had oncological surgery performed in the last 30 days, are at highest risk of an unfavorable outcome. However, statistical analysis also showed that namely in such cases immunotherapeutic drugs proved extremely efficient and can be literally life-saving. There are challenges, however. First, most of the immunotherapeutic drugs are very new and their side effects are not yet properly studied. Secondly, these medications are usually very expensive and so far are mostly not covered by OHIP and similar insurance plans. 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. 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. 3. A decision strategy that Berthoud should follow if the research is conducted based on the posterior probabilities and a revised decision tree. 4. A recommendation as to whether Berthoud should employ EMRC, along with the detailed decision policy, the value of the information provided by the research firm and the efficiency of this information. Use Excel TreePlan to construct both decision trees. Include the details of your analysis as well as the TreePlan output as an appendix to your report