Decision Tree

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

Decision trees are a great tool for assessing potential outcomes and developing a strategic approach.  However, the problem with most decision trees is that they only lend themselves to a single set of assumptions.  In real life, if you ever present a decision tree to a client or your boss, you’ll spend most of the time debating the assumptions.  A small change in your assumptions can easily change your optimal strategy.  And because assumptions by themselves mean nothing, it’s important to view them within the context of the problem.

This is where Crystal Xcelsius can be really useful.  Because of the dynamic nature of the tool, it allows you to present a decision tree while maintaining flexibility in all of your assumptions.  Below is a decision tree sensitivity model built around the context of the pharmaceutical industry.  I’ve simplified it heavily, using only Phase II and III, and all of the numbers are made up.  You can perform sensitivity analysis on this scenario to see how changes in the assumptions affect the optimal approach.

Pharma Co. has a new drug called OBP-49.  It has already gone through Phase I testing and the results thus far have been promising.  Scientists at Pharma Co. believe that the drug may be effective in treating obesity and high blood pressure.  At the beginning of Phase II, Pharma Co. has two options:

  1. Continue investing in the drug at a cost of $5 million dollars
  2. Sell the rights to produce the drug for $200 million dollars

If Pharma Co. continues investing in the drug, Phase II testing is expected to take 2 years.  There are three possible outcomes:

  1. OBP-49 is effective in treating obesity and high blood pressue – 40%
  2. OBP-49 is ineffective – 30%
  3. OBP-49 is effective in treating only obesity – 30%

For the sake of simplicity, we’ll assume that each scenario has a maximum probability of 50%.

Assuming that OBP-49 is effective in treating both obesity and high blood pressure, Pharma Co. will have two options:

  1. Continue investing in the drug at a cost of $40 million dollars
  2. Sell the rights to produce the drug for $380 million dollars

If Pharma Co. invests in the drug, Phase III testing is expected to take 5 years.  The drug has a 60% chance of passing Phase III testing and receiving FDA approval.  If approved, the drug would be worth $900 million dollars at the end of the phase.

Assuming that OBP-49 is effective in treating only high blood pressure, Pharma Co. will have two options:

  1. Continue investing in the drug at a cost of $30 million dollars
  2. Sell the rights to produce the drug for $250 million dollars

In this scenario, the drug has a 70% chance of passing Phase III testing and receiving FDA approval.  If approved, the drug would be worth $500 million dollars at the end of the phase.

Pharma Co. uses a weighted average cost of capital of 5.0%.

{ 0 comments… add one now }

Leave a Comment


Related pages


xcel vlookupgoldratt the goal movievlookup for dummies excel 2010formula for countifhow to fix ref in excelvba excel graphcreating an if statement in excelhow to do goal seek in excel 2010how to learn vlookup in excel 2010if vlookup exampleeasy excel shortcutsmerge duplicate rows in excelvba offset selectionhow to draw bar graph in excelexcel random selection from listms excel row functionoffset excel exampleexcel hlookup functionvba excel averagehow to filter duplicates in excel3d stacked column chart excelwhere to find tick in excelstandard curve in excelvlookup microsoftmatt excellconvert excel files to pdfhow to lock a reference cell in excelvlookup microsoft excelformulas in excel definitionwhy do we use vlookuphow to create excel macrosremove duplicates in a listvlookup formulacount duplicate values in excelpaste special excel shortcutwhat is a histogram chartexcel eliminate duplicatesthe vlookup functionsimple if statement excelgaussian fit excelmultiple if formulas in one cellcumbersome tabhow to highlight a column in excelhlookup dan vlookuptask template excelexcel tutorial pivot tablesexcel formula with example in excel sheetexel vlookuphow to eliminate duplicates in excel 2010countif functions in excelhighlight duplicate values in excelhow to make a bar graph in excel machow to run sensitivity analysis in excelsymbol for greater than or equal to in excelexcel substitute multipleexcel lookup multiple columnsplot bell curve excelvba excel remove duplicateswebmail.kinross.comv look up tutorialcount columns excelzs associates careersexcel vlookup worksheetexcel function isnacreate a macro in excelexcel repeat commandfree trial of microsoft access 2013wingdings 2 tickvlookup example dataexcel troubleshoot
\n