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 Field)
  1. Quick Start
  2. Introduction
  3. Steps for Use
    1. Collect the files
    2. Language compatibility
    3. File name changes
    4. Re-establish links among the sheets
    5. Setup the demand scenarios
    6. Decide the production scales and pricing options you want to consider
    7. Estimate the probabilities of demand as a function of price
    8. Set your discount rate
    9. Run the scenarios
    10. Figure out what the results mean

COSTSKEL writeup

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
  1. Get the three necessary files
    1. DANAL5.XLM
    2. TREE_98.XLS
    3. COSTSKEL.XLS
  2. Set up the necessary language elements
  3. Set up the changes (if any) in file names in DANAL5.XLM
  4. Re-establish the links between TREE_98.XLS and your cost spreadsheet (probably COSTSKEL.XLS)
  5. Decide what your demand scenarios are going to be in TREE_98.XLS
  6. Decide which pricing and production options you want to consider
  7. Make your base estimate of the probabilities of the demand scenarios in TREE_98.XLS
  8. Set the discount rate you want to use in your cash flows in TREE_98.XLS
  9. 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 period macro).
  10. 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.
  11. Figure out what it all means for your decision problem.

Introduction


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:

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, shown below:

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 sheet:

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 at COSTSKEL:

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 menus:

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 of Price


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 noted below).

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 the results:

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 PM )
furd@mit.edu - (617) 253-2146