In this excel tutorial I’m going to be talking to you about joint probability and how it can play into your economic analysis. I do want you to know that this is a second in a series of three tutorials. You need to watch the earned value tutorial before watching this one. And then there’s a follow on after this one with that will deal with standard deviation. So, this is number 2 out of a series of 3. First thing I need to let you know is joint probability. If two things are not independent then the probability of A and B is simply the probability of A times the probability of B. And the number of outcomes in a joint distribution is the product of the number of outcomes in each variable’s distribution.
So, with those words in mind let’s take a look at how we can use joint probability in our economic analysis. Since it’s a continuation of our earned value, we have the same annual benefits with the same probabilities, the same two lives with their same probabilities. Our initial cost and interest has stayed the same. In order to do a calculation with joint probability what you need to do is figure out all the ways they can go together. So, our benefits can be here, they can either be with 9 years or they can be with 12 years. So, I get 6 total. I have 3 benefits times 2 possible lives and I get 6 possible scenarios here. Now, remember, joint probability, if two things are not independent then the joint probability is A times B. The probabilities here for our annual benefit and our probability are not independent.
So what we simply need to do is multiply them together. So that would equal C11 times E11. I did not lock the cells so I can grab the corner and it will increment all those joint probabilities for me. Now, the next thing I need to do is figure out what is the present worth of this particular scenario. In this case, I want you to take a look at how I’ve set this up. I’m highlighting so you can see. We’re gonna do the present value. Our rate is in B3, that never changes so I’ve locked that cell. Our year is in column D11. Notice I did not lock that at all because I want that to increment. Our payment is in negative B11. Notice again that I did not increment it. Then I’ve closed that off and then I have to add on the $20,000 that’s already sitting at year zero. And I’ve locked that because that always stays there. And I simply hit return. And what I can do is grab the corner and it will appropriately calculate the present worth of each of these.
Now what I can do is figure out the present worth times the joint probability. Which simply is equals the joint probability times the present worth. And then I can grab the corner and pull them down and I can see that for each one. Now what I can do is actually figure out the expected value of the present worth based on this information. This would simply equals, sum of these values and I would get $19,716. What this is telling you is that, that $19,716 is…value is a more accurate value than the approximation that we did in the previous tutorial. So, it gives you a more accurate answer by giving you the possible different possible combinations. Now notice here that you have, this one you have almost a 33% of having $5,000. So, you can also relate those this way. But what this is doing, like I said before, is giving you a more accurate value for your present worth than the previous tutorial that we did which was a more of an approximation. In this excel tutorial I’ve shown you how to apply joint probability to your economic analysis.