Decision Tree Spreadsheets for Strategic Planning Exercise
(TREE_98.XLS and DANAL5.XLM files modified to correct errors reported as of May
20, 1998 - thanks Jackie and Joel!)
(Further modifications per R. de Neufville notes made to TREE_98.XLS June 10, 1998)
Table Of Contents
(Last updated/edited on Nov 08 1998 at 02:06:12 PM by Frank
Steps for Use
Collect the files
File name changes
Re-establish links among the sheets
Setup the demand scenarios
Decide the production scales and pricing options you want to consider
Estimate the probabilities of demand as a function of price
Set your discount rate
Run the scenarios
Figure out what the results mean
Data Table Analysis Writeup - large PDF file
(You may also want the latest TREEPLAN - you can get it off the WWW here.
A local copy is here.)
The MIT TPP21 (Dynamic Strategic Planning) course page is here.
(The local static mirror is here.)
Quick Start - The Basic Checklist For Action
Get the three necessary files
Set up the necessary language elements
Find out what you use for Row and Column (Tools | Options | General Tab)
Change the cells in DANAL5.XLM and TREE_98.XLS
Set up the changes (if any) in file names in DANAL5.XLM
Re-establish the links between TREE_98.XLS and your cost spreadsheet (probably COSTSKEL.XLS)
Figure out the row and column addresses of the cells to be linked
Put the necessary values into the correct DANAL5.XLM cells
Run the update links macro
Decide what your demand scenarios are going to be in TREE_98.XLS
Decide which pricing and production options you want to consider
Make your base estimate of the probabilities of the demand scenarios in TREE_98.XLS
Set the discount rate you want to use in your cash flows in TREE_98.XLS
Run the macros for the first and second periods. Remember that the second period
macro has to be run three times, because the size of the second period demands depends
upon whether a Low, Medium or High level of demand took place in the first period
(a setting in the TREE_98.XLS sheet has to be changed for each run of the second
Review the results of the analysis, knowing that changes in the demand scenario
probabilities will be correctly reflected in the results, but all other changes
in the estimated parameters requires re-running the period one and two macros.
Figure out what it all means for your decision problem.
The central exercise of the strategic planning course is the use of a two stage
decision tree, in conjunction with a cost model and a series of market predictions,
to develop a strategic plan. A key educational objective has been the evolution
of the exercise away from a very specific combination of product, market, and scenario
tools (specifically, structural ceramic components for the automobile) toward a
general set of analytical tools that can be employed by the students to analyze
problems of their own choosing. The current set of Excel spreadsheet tools moves
us closer toward this goal.
These tools are the following spreadsheets:
TREE_98.XLS - This Excel workbook includes a complete
two stage decision tree for the following problem:
Confronted with an uncertain market demand, the use is faced with deciding the
size of the plant to be constructed and the price that should be charged for the
product. After a five year period of coping with the consequences of these initial
decisions, there is an opportunity to change both the size of the production facility
and the pricing of the product. What is the appropriate strategy?
The TREE_98.XLS contains the entire decision tree, broken into component branches
and spread across a total of 19 individual spreadsheets. The "top" 20th
spreadsheet includes the user's estimates of market demand and the production &
pricing scenarios that are to be analyzed.
COSTSKEL.XLS - This EXCEL spreadsheet is a cost modeling
template. It essentially gives the user one of four possible methods for estimating
the technical relationships between the scale of production and the costs of operation.
It's use is described here.
DANAL5.XLM - This macro spreadsheet includes the necessary
pre-defined commands for linking TREE_98 and COSTSKEL together, and using the two
to construct profit scenarios for the decision trees.
Step 1: Collect the files
Download them by clicking on the following links:
After they have been downloaded, you can open the three of them simultaneously in
Excel. Please note that the TREE_98.XLS sheet will offer to re-establish links with
other sheets after loading. Unless you have done the necessary linking (described
in the following sections), you should NOT re-establish links the first time opening
the TREE_98.XLS file.
Step 2: Language Compatability
Excel macro commands largely rely upon a Row-Column notation that, unfortunately,
is language dependent. For example, in the english language versions of Excel, the
cell A1 is given the name R1C1 (for 'row 1, column 1'). Thus, the macros have to
be rewritten to match the values used in other languages. To find out what those
values are, go to the Tools... Options menu choices in the Excel main menu and then
choose the 'General' tab. You will get the following dialog box:
Note the choices for reference style. You will want to remember these letters, because
you need them to setup the macros to work correctly.
There are two places requiring these values. The first is in the TREE_98.XLS sheet,
Cells B35 and B36 must contain the language specific values that you found in the
General page of the tabbed Options dialog. This page also depicts the cells that
form the links into and from the COSTSKEL.XLS sheet.
The other place requiring language specific requirements is in the DANAL5.XLM macros
Again, there are Row/Column cells to be designated. Note further that the Row and
Column designations in the LINK table correspond to the position of the Plant Capacity,
Annual Production, and Piece Cost cells in TREE_98.XLS shown above.
Step 3: File Name Changes (if any)
The TREE_98.XLS sheet, by itself, cannot do anything particularly useful; it requires
a cost 'engine' that will compute the cost of producing some number of units in
a production facility of a specified size. The size of the facility and the number
of units produced are automatically generated by the scenarios in the TREE_98 file,
but it requires that there be a file that can calculate a per unit cost of production
given those two inputs. The COSTSKEL.XLS sheet is provided to allow you to create
your own cost model, according to a variety of possible modeling approaches. The
use of COSTSKEL is discussed elsewhere; here, we merely show how to create the links
between it and TREE_98. First, you must tell the macro sheet DANAL5.XLM the names
of the files and the location of the relevant calculations. First, the names of
the files are specified here:
Note that the names do not include paths; just the basic name of the sheet file.
You may change the names in these cells, but make sure that the filenames you want
to use actually match!
Step 4: Re-establish Links Among the Sheets
As shown above, you also want to make sure that the scenario plant sizes and annual
production forecasts are correctly supplied to the cost model, and that the cost
model result is correctly "aimed" at the cell that TREE_98 expects to
find it in. The first thing is to make sure where these places are! So, let's look
Here, the size of the plant is specified in cell B5 (or Row 5; Column 2) and the
annual production is specified in cell B6 (or Row 6; Column 2). These are the values
that the TREE_98 spreadsheet will generate based upon our assumptions and options,
so these values will actually be set by TREE_98 when we get things all set-up properly.
Similarly, TREE_98 will need to get the cost that is calculated by this model, so
we'll also have to tell it where to find the calculated cost. That can be figured
out by looking here:
So, the piece cost is calculated in cell B38 (or Row 38; Column 2). Now, if the
idea is to link the cost model with the TREE_98 model, we need to know which cells
in TREE_98 correspond to these three cells, so let's look here:
So, C26 (row 26; col 3) is where TREE_98 is going to set plant size, and C27 (row
27; col3) is where it will set annual production; and it will expect to find a calculated
cost in C31 (row 31; col 3). So now, we just have to tell DANAL5.XLM where and what
the links are. That specification is done here:
Once these values are set, we have to run two macros: one to update all the cell
references in the DANAL5.XLM sheet (including the file names and the international
references) and one to actually link the two sheets together. These macros can be
found in the dialog box that appears when you select Tools | Macros in the Excel
First, run the CellNameUpdate macro. Second, run the Link_Update macro. Now the
TREE_98 spreadsheet and the specified cost spreadsheet are linked together.
Step 5: Setup The Demand Scenarios:
The TREE_98 spreadsheet gives you two periods of five years each, over each of which
you can assign three potential demand growth/share growth scenarios. You supply
these forecasts in the following tables:
For the first five year period, you state the size of the total market that exists,
and then predict your possible rates of share growth. Note that you cannot specify
absolute share numbers except in the first year. Thereafter, it is the rate of growth
that will define the absolute share.
In the second period, the basic format of the table remains the same. However, the
size of the forecat share is dependent upon which growth scenario (High, Medium,
or Low) took place in the first period. Because of this dependence, this table requires
an additional input; the demand scenario assumed in the preceding five year period.
Step 6: Decide the Production Scale and Pricing Options You
Want to Consider
You have three possible scales of production to consider in the first period, and
one additional scale in the second period. You also have two prices that can be
considered for each of the scales of operation.
Step 7: Estimate the Probabilities of Demand As A Function
Finally, you can estimate the probabilities of demand in the following table:
There are two important caveats to consider. First, the second period probabilities
are likely to be related to the first period probabilities and the actual demand
experienced. The spreadsheet does not impose any requirement: the type of relationship
is up to you. Second, the probabilities, in conjunction with the prices, yield an
assumed price elasticity of demand. The model estimates an average value, based
on the inputs you supply - it is important that you can defend the resulting values,
since the spreadsheet does not impose any structure on your demand estimates.
Also, you can revisit the probabilities fairly easily. The final results are automatically
updated as you change these probabilities; you do not have to re-run the scenarios
(Step 9) each time you change these probabilities (unlike the discount rate, as
Step 8: Set your discount rate
The cash flows can be discounted, although only one global discount rate can be
specified. The discount rate can be set here:
Note that you cannot simply change the discount rate to find out what changes there
may be in the final output of the decision trees. You must perform
Step 9 after changing the discount rate to make sure that all cash
flows are correctly discounted using the new rate.
Step 9: Run the Scenarios
The total decision tree for the problem is stored on separate pages of the TREE_98.XLS
sheet. The Period One root tree is on the Period One sheet. The remaining sheets
(18 in total!) are named according to the following scheme:
Period 2; <<scale of production in period 1>>,
<<price in period 1>>, <<demand experienced in period 1>>
Thus, a sheet named "Period 2; Med Scale, P2, LoD" is the subtree off
of the end of the Period 1 tree branch corresponding to medium (or scale #2) production
& price #2 choices and when Low Demand is experienced. The Do Nothing option
is maintained, but (according to this setup) also closes the user off from making
any choices in the second period. The tree reads its outcomes from a table that
you must construct, using macros built into DANAL5.XLM. First, you should run the
Period One macro, selecting it from the macro list.
After running PeriodOne, you must run PeriodTwo three times, once for each possible
value of the first period demand. (Remember this?:
Once you've done this, the data for the tree has been assembled and you can read
Step 10: Figure out what this means
As you should know by now, a single point estimate is practically useless. The real
test is the robustness of this result, given the structure of the uncertainties
that you know are embedded in this result.
Frank R. Field, III (last updated/edited on Nov 08 1998 at 02:06:12
firstname.lastname@example.org - (617) 253-2146