Decision trees and expected value of perfect information (EVPI) calculations with MS Excel

Here is a collection of Excel spreadsheets I have been using while studying decision trees and value of information concepts.

I'm publishing these since there seems to be a gap between available theoretical descriptions and tutorials demonstrating how to actually calculate these using popular tools.

If you are looking for a good introduction into the concepts of decision trees and EVPI I'd recommend Decision Tree Primer by Craig W. Kirkwood. Especially the examples and exerices are quite clear and easy to follow.

However, if you try to calculate them by hand you will find out that it's very time consuming and it's easy to make mistakes — even with Excel it's not an issue of complex calculations, but getting right fields referenced and copied. So for actual calculations I strongly recommend a free Decision Tree add-on for Excel. They allow you to easily build graphical decision and probability trees, include risk aversion and probability flipping that are needed to solve some exercises in the primer.

If you have problems with understanding anything, just go and search on YouTube — for example there's a very nice series from Gator Tutoring. In short, clean films they explain concepts like certainty equivalent, expected utility and many others. You might also have a look at Wikipedia: risk aversion, exponential utility.

Note that the exponential utility function in Kirkwood's primer is written as:

And he's using risk tolerance expressed in dollars as parameter. The function in Excel add-on is defined in slightly different way and expects a fractional parameter :

The conversion is simple:

1. put the dollar value in some field in the spreadsheet (e.g. A1)
2. define a field with label gamma with formula =1/A1

Another useful thing to remember when working with the Excel add-on is that if you are trying to minimise a cost instead of maximising income, you just need to define the financial figures as negative numbers.