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


excel comment shortcutexcel formula columnexcel shortcut paste specialsample excel inventory spreadsheetsvlookup excel different sheetvba excel sampledelete all duplicatesvlookup and hlookup tutorialh lookup excelvlookup and hlookup combined2 stacked bar charts side by sidelookup function on excelhow to label graphs in excelblank function excelucla gpa calculatorhow do you do a tick on excelvlookup index match excelexcel countifs formulahow to add tick mark in excellabel excel definitionvlookup excelwhat is the average gpa for uclaexcel task tracking templatehow to filter duplicate rows in excelvba find value in rangevba offset functionprobability histogram generatorvertical lookup excel 2007excel symbol for not equal tovlookup ms excelhow to create a bell curve in excel 2010where to find tick in excelthe goal goldratt moviewhat is excel vlookupsites with most trafficcountif examplesexcel shortcut for paste specialdeloitte consulting principal salaryhow to construct a normal probability plot in excelhow to create a stacked bar chart in excel 2010shortcuts for excelchecking duplicates in excelnested formulas excelhow to learn microsoft excel formulascheapest microsoft office home and student 2013how to create a stacked bar chart in excel 2007vlookup explained simplyhow to delete rows and columns in excelhow to change axis values in exceltick sign in excelexcel formula for whole columnlookup formulaexcel formula with multiple conditionsexcel concatenate arrayexcel remove non duplicatesexamples of hlookupadobe reader 11 free download cnetvlookup example in excelhow to write a formula in exceltask planner template excelmicrosoft office free trial 60 daysdownloadable excelvlookup with indirectcpk formula for excelparenthesis excelexplain vlookup in excel with examplebest excel bookquestions to ask in mba interviewfind duplicate rows in excelharvard case study formatstacked bar pivot chartexcel formula to remove duplicateshlook up
\n