Mutually Exclusive Cells

Skudwincell

New Member
Joined
Mar 5, 2015
Messages
2
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(y) somehow?

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

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
5,111
Office Version
  1. 365
Platform
  1. Windows
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?
 
Upvote 0

Skudwincell

New Member
Joined
Mar 5, 2015
Messages
2
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?

oaksj2ygn
 
Upvote 0

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
5,111
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Scenario:123etc …
2Yr 1 Hi$120$130$150
3Yr 1 Lo$100$80$40
4Yr 2 Hi$125$130$150
5Yr 2 Lo$105$70$75
6…..
7Yr 10 Hi$130$200$150
8Yr 10 Lo$100$100$80
9
10
11Expected$112$110$106
12price Yr 1

<tbody>
</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.
 
Upvote 0

Forum statistics

Threads
1,186,662
Messages
5,959,062
Members
438,396
Latest member
Louise Rodway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top