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


numbers vlookupinventory template for excelformulas in excel definitionhow to use iferror in excel 2010how to make hlookup in excelexcel comment shortcutucla average gpaexcel filter out duplicatesif statement in excel 2007distribution curve in excelmicrosoft excel lookupexcel functions vlookupiferror in vlookupexcel greater than formulaifs function in excelduplicate check excelexcel vba vlookup examplebrackets in excelexamples of vlookup in excelcreating bar graph in excelhow to write excel formulasfree online histogram makerexcel lookup table examplematch index vlookupexcel formulas random number generationexcel nested if and statementsfind duplicates in excel spreadsheetpaste special shortcut keyvlookup duplicateformula for hlookuptraining on excel formulasexcel numberformatfiltering duplicates in excelstacked bar chart total labelms excel syntaxcreating histogram excellearn vlookupif function in excel 2010 multiple conditionshow to draw a column graphpivot table count unique valuesmicrosoft excel timetable templateexcel count cellsexcel normal distribution graphcountif vlookuphow to remove ref in excelthe goal by eliyahu goldratt summaryexcel important shortcutsexcell lookupexcel multiple if statements in one cellhow to create a bar chart in excel 2010excel tip and tricksexcel 2007 find duplicatesduplicates on excelsensitivity chart excelexcel vba vlookup functionmultiple stacked bar chart excel 2010how to vlookup in excel 2007bar and line chart in excelexcel formula hlookup examplehow to find duplicates in multiple excel sheetsmba fresher interview questionsexcel label graphhow to find duplicates in excel sheetwhat is excel vlookuplook for duplicates in excelhow to do a bar graph in exceltick correct symbolexcel multiple vlookupexcel chart layouthow to insert check mark in excel 2007what is meant by vlookup in excelhow to draw a line graph on excelexcel generate histogramconsulting boutique firmsvlookup in excel definitioncharley kydselect distinct exceltranspose function in excelexcel shortcut for filterdata table excel sensitivity analysisexcel tutorial 2003
\n