Under "decision analysis" we encounter four sub-issues: (1) descriptive decision trees; (2) adding testing to reduce uncertainty and valuing information; (3) adding risk; (4) imperfect tests and "tree flipping." In this lab we'll see how to employ Excel in the service of #2 on the way to including #4.

Our model will be built around the installing generator in a hydroelectric damn example found in Stokey and Zeckhauser.

We will build a model that looks like this:


This model has a number of features we have already talked about

  • It has a spinner that lets us try out different values for the parameter "costs of doing the test"
  • It has a spinner that lets us try change the probability of failure parameter
  • It has a spinner that lets us try different values of the payoff parameters (how much electricity each scenario will produce)

and a few we have not

  • It has spinners that let us introduce "imperfect tests" - will talk about this later.

Excel Skills

For this lab you will need to be able to do the following

Excel drawing and formatting

  1. Cell borders and fill
  2. Change column width
  3. Changing font format, alignment (horizontal and vertical)
  4. Merging cells 2007
  5. Drawing objects Youtube

Excel Formulas 2007

  1. use formula to calculate an expected value
  2. "if-then-else" formulas
  3. use formula and text() function to create complex text strings

Adding Controls to Worksheets

  1. how to access the developer tab
  2. how to create a spin button
  3. how to link a spin button to a cell
  4. how to create a "divider" for a spin-button-controlled cell

Lab Instructions

We'll build on the example described on pages 206ff. A damn project will use either a standard generator or a new, more efficient one. Costs are identical. A conventional generator produces $5 million of electricity. A new generator might produce $8 million of electricity per year (probability of this is 0.7), but it could also turn out not to work well and produce only $3 million (0.3 probability). We could run a (completely accurate) test on the new type at a cost of $0.5 million. What should we do?

After solving the specific problem, evolve the solution into a more general model that permits us to vary probabilities and costs.

Getting Started ON PAPER.

Sketch the decision tree ON PAPER. After you are done, check it versus the tree in figure 12-7.
  • Let's reproduce the picture in Excel.
    • Start by making the field of cells we will use to build the simulation square and not so small.
      • Select rows 1-50, right click, and set row height to 20.
      • Select columns C-V, right click, and set column width to 4.
    • Since our right-most columns will contain text and outcome values and such, let's set them to a little wider. Same for A and B
    • We'll put some PARAMETERS in the upper left of the model
      • In A2 type "New Gen Works Well," A4 "New Gen Fails," A6 "Old Gen," A8 "Cost of Test," and A10 "Prob New Fail." Align these left and adjust column width so they fit nicely. Now give cells C2, C4, C6, C8 a light blue fill - this will be our symbol for "parameter value"
      • Highlight each of these in turn and INSERT>NAME>DEFINE NAME and give them names like "NEW_GEN_WORKS" and "NEW_GEN_FAILS" (that is, the same as the label but in caps and with underscores instead of blanks).
      • Type in the appropriate values in these cells.
      • Select column W, right click and set column width to 14. Select columns X, Y, and Z and set width to 7.
      • In cell W3 type "Outcome." In cells X3, Y3, and Z3 type Payoff, Cost, and Net.
      • In cells W4, W7, W9, and so on, as shown below, where we would type the VALUES from your sketched tree, we will instead, under "Payoff," "Cost," and "Net" type formulas that point to the parameters we just created.
  • Add formulas for the probabilities and calculations of EV.
  • Draw the first few lines of the tree
    • If you are not familiar with how cell borders work in Excel, consult Excel Cell Borders or Excel's help function
    • Select cells U4:W4 and give them a "Bottom Border." Then repeat this for U7:W7 and so on under each of the outcomes.
    • Select cell T8 and click on the border drop-down and more borders and insert a diagonal border across the cell. Then do the same in T9, except use the other diagonal.
    • Select O4:T4 and insert bottom border. Similarly, for O8:S8, O12:T12, etc.
    • Next, go to N5 and note that this time we want the diagonal to span two rows. Select the range N5:N6 and merge these cells (Merge and Center on the Alignment tab). After the cells are merged, insert diagonal borders. You should end up with something like this:
  • Draw a small square for the choice nodes (remember shift key will make object same height and width). Add text and set it to the right color, size, font. Set color, border, etc. to something visually appealing and set this as the default shape properties. Then create a small circle for the chance nodes. Copy and paste enough of these for the tree diagram. Label each one by adding the appropriate text and position each one correctly.
  • Next, we want to insert the probabilities we know on the branches coming out of each chance node. These are shown in grey below. Note that in some cases we've horizontally joined a few adjacent cells. This is mostly cosmetic. The important thing is just to be consistent.
  • The next refinement is to add calculations for the values of nodes. At chance nodes we calculate the expected value (what the book calls EMV). At choice modes we take the value of the branch with the top value. We can implement the former with a simple formula to represent the following
\begin{align} EMV=\sum _{ i=1 }^{ 2 }{ { P }_{ i }\times { V }_{ i }={ P }_{ 1 }\times { V }_{ 1 }+{ P }_{ 2 }\times { V }_{ 2 } } \end{align}



* For choice nodes, we can implement the calculation with an if formula

=if (branchBvalue > branchAvalue, branchBvalue, branchAvalue)

* NOTE that you get a more attractive picture if you merge two cells near the node as shown in pink above.

Adding Text Messages for Results

In the cells adjacent to the values of choice nodes, we will add a formula that produces a context dependent text message.

The formula will produce a text string that depends on the values calculated. The basic form would be this:

="And so… "&if(topbranch>lowbranch,"do top action","do lower action.")

Merge a few cells together and center vertically and then put formula in for each choice node.

Adding Parameters

Over to the side, enter the names of parameters (cost of test, probability failure, good new output, bad new output, old output) and put in the initial values for all of these.
Define names for these values.
Replace references to these numbers in the model with references to these cells.
Create spinners to control them. In a few cases you will need to create a decimal divided spinner.

Try out the model

Vary the cost of the test and notice when the decision changes. Does this agree with your calculation?
Return to the original value and vary the likelihood of failure. When does the decision change?
Come up with a strategy for systematically varying these two parameters to see relation to decision. Can you think of how you might plot them?

You can DOWNLOAD finished version in Excel for comparison.


Consider this example from pp 206-7 of Stokey and Zeckhauser's A PRIMER FOR POLICY ANALYSIS