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


bruinbushow do i do a vlookup in excelformula excel sheetexcel make line graphvlookup for namesiferror in vlookuphow to generate random numbers in excel without duplicatescalendar planner template excelucla bruin loginexcel random samplingcheap microsoft licensesconvert this number to scientific notation 1000000bins in excelexcel symbol for not equal toclustered stacked column chart in excel 2010insert tick in wordexcel insert symbolwhat vlookup in excelbell curve xlstranspose function excel 2010windings check markucla parking structure 32excel graph projectionhow do i do a vlookup in exceldownload free trial microsoft word 2010ucla anderson academic calendarhow to conditional format in excel 2010vlookup for multiple criteriadefine vlookupif function excel 2010 multiple conditionshow to create a distribution graph in excelexcel generate histogramexcel vlookup compare two columnswhat is hlookup in excel with exampleexcel max formulafree trial microsoft office 2007 download full versioncurve graph generatorhow to highlight a column in excelexcel random samplingcombine hlookup and vlookuppiratebay excelexcel vlookup pdfexcel 2007 random number generatorexcel task scheduler templatems excel labelsexcel approximate matchgauss curve exceldifferent versions of ms excelhow to shade in excelhow to write a conditional statement in excelexcel trend graphhow to combine names in exceltick in box symboliferror excel functionsample formulas in excelhow to identify duplicate entries in excelstacked column chartexcel formulas usingfuzzy lookup excelerror bars excel 2010excel counifdecimal placementswingdings 2 font downloadstacked line chart excelexcel formulas and examplesexcel index match multiple matchesrandom number generator excel normal distributionexcel vba create new worksheethow to merge duplicate rows in excelms excel random number generatorexcel combine duplicate rowscolumn formula in excelvlookup microsoft
\n