Question

3) Suppose you wanted to simulate possible profit outcomes, and you know that revenues have a 30% likelihood of being $2 million, 12% likelihood of being $3.5 million, 48% likelihood of being $4 million, and 10% likelihood of being $4.5 million.

Furthermore, Costs have a 30% probability of being $3.25 million and a 70% probability of being $3.5 million.

Using Excel, simulate 100 instances of profit. Calculate average, minimum, and maximum profits.

Is 100 instances sufficient? Why or why not?

Answer 1

Revenue LIkelihood is let say x then

x	P(x)
2	0.3
3.5	0.12
4	0.48
4.5	0.1
Sum	1

Cost is let say y then probability distribution

y	P(y)
3.25	0.3
3.5	0.7
Sum	1

Profit P = X - Y

so we will simuate it 100 times and following is the result. Screenshow of excel image in given below

We will use Data analysis -> Random Number Generationmethod to solve the problem.

Revenue	Cost	Profit
3.5	3.5	0
2	3.5	-1.5
4	3.5	0.5
4.5	3.25	1.25
3.5	3.5	0
2	3.25	-1.25
2	3.5	-1.5
2	3.5	-1.5
4	3.25	0.75
2	3.25	-1.25
4	3.25	0.75
4	3.5	0.5
4	3.5	0.5
2	3.25	-1.25
2	3.5	-1.5
2	3.5	-1.5
4	3.5	0.5
4	3.25	0.75
4	3.5	0.5
4	3.25	0.75
4	3.5	0.5
4	3.5	0.5
4	3.5	0.5
4	3.5	0.5
3.5	3.25	0.25
4	3.5	0.5
2	3.25	-1.25
4	3.5	0.5
2	3.25	-1.25
4	3.5	0.5
2	3.5	-1.5
4	3.5	0.5
4	3.5	0.5
4.5	3.5	1
4	3.25	0.75
4	3.5	0.5
2	3.5	-1.5
4	3.5	0.5
4	3.25	0.75
4	3.5	0.5
4	3.5	0.5
4	3.5	0.5
4	3.5	0.5
2	3.5	-1.5
2	3.5	-1.5
2	3.5	-1.5
2	3.5	-1.5
2	3.5	-1.5
4	3.5	0.5
2	3.25	-1.25
4	3.5	0.5
4	3.25	0.75
4	3.5	0.5
4	3.5	0.5
2	3.25	-1.25
4	3.5	0.5
3.5	3.5	0
2	3.25	-1.25
2	3.25	-1.25
4.5	3.5	1
2	3.25	-1.25
4.5	3.25	1.25
4	3.5	0.5
4	3.25	0.75
4	3.25	0.75
4	3.5	0.5
3.5	3.5	0
4	3.5	0.5
2	3.5	-1.5
4	3.5	0.5
4	3.5	0.5
4.5	3.5	1
2	3.5	-1.5
3.5	3.5	0
3.5	3.25	0.25
4	3.5	0.5
4	3.25	0.75
4	3.25	0.75
3.5	3.25	0.25
2	3.25	-1.25
2	3.5	-1.5
4	3.25	0.75
4.5	3.5	1
4	3.5	0.5
2	3.5	-1.5
4	3.5	0.5
2	3.25	-1.25
2	3.5	-1.5
4	3.5	0.5
4.5	3.5	1
4	3.5	0.5
4	3.5	0.5
3.5	3.25	0.25
2	3.5	-1.5
2	3.5	-1.5
4.5	3.5	1
3.5	3.25	0.25
4	3.5	0.5
4	3.5	0.5
2	3.5	-1.5

Average Profit = -0.0725 million dollars

Minimum Profit = -1.5 milllion dollars

Maximum Profit =  1.25 million dollars

Here the sample size of 100 simulation is insufficient as we get the averaeg profit negative but as we calculate expected profit by using probability distribution, we get a position result.

so it should be higher.