Mutually Exclusive Cells

[Skudwincell]

Hey Guys,

I'm currently doing some uni analytics work and I'm trying to make a cell represent the chance of two other cells being mutually exclusive. Is there a way of including the probability of each cell occurring in the formula?

The example is

Year 1:
Low Price: x, 0.4
High Price: y, 0.6

Year 2:
Low Price: a, 0.4
High Price b, 0.6

and so on..

I want a cell to show the answer after assuming the cells are mutually exclusive.

I'm assuming the formula I need to use is P(x) + P somehow?

Any help would be greatly appreciated as this is due soon!

 

[StephenCrump]

Welcome to the Forum!

I'm not sure what you're looking for here. Do we interpret the question as saying that in year 1, something can have a low value, $x, with 40% probability, and a high value, $y, with 60% probability. If so, these possibilities are mutually exclusive, because it's either x or y, and can't be both.

Similarly, is it the same something in year 2 with 40% probability of low price, a, and 60% probability of high price, b?

What probability/probabilities do you need to determine?

Rather than being mutually exclusive, did you mean to say that the year 1 and year 2 results are independent?

 

[Skudwincell]

Welcome to the Forum!

I'm not sure what you're looking for here. Do we interpret the question as saying that in year 1, something can have a low value, $x, with 40% probability, and a high value, $y, with 60% probability. If so, these possibilities are mutually exclusive, because it's either x or y, and can't be both.

Similarly, is it the same something in year 2 with 40% probability of low price, a, and 60% probability of high price, b?

What probability/probabilities do you need to determine?

Rather than being mutually exclusive, did you mean to say that the year 1 and year 2 results are independent?

Hey Stephen,

Yes I think I mean independent. Basically I need to determine what price I pay for oil from years 1 to 15. The probability for x and y remains the same each year but x and y both change year from year. I know what I sell my oil at but I need to determine the margin on it each year.

After this I need to do basic analysis such as NPV to show which option is the best one to go for.

(I have multiple scenarios to carry out and then I have to implement changes into the model.)

Are you able to help at all?

 

[StephenCrump]

Are you able to help at all?

Happy to help with implementing in Excel, less inclined to do your assignment for you

If I'm interpreting this correctly, it sounds like you're testing multiple scenarios that might look something like this:

Excel 2010

	A	B	C	D	E
Yr 1 Hi
Yr 1 Lo
Yr 2 Hi
Yr 2 Lo
…..
Yr 10 Hi
Yr 10 Lo
Expected
price Yr 1

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Scenario:[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]etc …[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]$120[/TD]
[TD="align: right"]$130[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]$100[/TD]
[TD="align: right"]$80[/TD]
[TD="align: right"]$40[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]$125[/TD]
[TD="align: right"]$130[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]$105[/TD]
[TD="align: right"]$70[/TD]
[TD="align: right"]$75[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]$130[/TD]
[TD="align: right"]$200[/TD]
[TD="align: right"]$150[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$100[/TD]
[TD="align: right"]$100[/TD]
[TD="align: right"]$80[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]$112[/TD]
[TD="align: right"]$110[/TD]
[TD="align: right"]$106[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

1​

where B11: =0.4*B3+0.6*B2

After this I need to do basic analysis such as NPV to show which option is the best one to go for.

How do you define which scenario is the "best one"? Scenario 3 gives you the lowest expected price, and hence the highest expected margin, in year 1. But if you're locked into selling at $140, say, then you have a 60% chance of making a loss under scenario 3, whereas scenarios 1 and 2 will always deliver profits.

If you're only interested in maximising total expected margin, you can simply attached NPV factors to the expected margins year by year.

In the (I suspect more likely) event that you're interested in maximising the chance that the NPV of your margin is at least $X (where X might be 0, or some higher amount) you could use Monte Carlo simulation to take into account the variability of your net margin.