0
$\begingroup$

I am trying to run a monte carlo simulation and I just wanted to make sure that I set it up properly.

A salesman visits 100 different homes. Someone answers the door 80% of the time. Of that percentage, 65% of the time it's a female. 30% of females make a purchase and the value of their purchase is normally distributed with a mean of 22 dollars and standard deviation of 5. 20% of males make a purchase and the value of their purchase is normally distributed with a mean of 28 dollars and standard deviation of 3.

This is how I set up my excel sheet: Excel Screenshot

Have I set up the problem correctly? I just want to make sure because the software that I'm using has limitations and I can only run it with a certain amount of uncertain variables, of which I have too many right now.

$\endgroup$
  • $\begingroup$ What is it you are ultimately trying to do? What will you do w/ the 'simulation'? Note that just asking for Excel help is off topic here. $\endgroup$ – gung - Reinstate Monica Jul 18 '18 at 19:56
2
$\begingroup$

This hasn't been set up properly. Assuming that the code that you put in the visit 1 row is the same for all the other rows, you can wind up with the following incorrect situation:

No one answers, but both a male and a female answer the door.

This, according to your model, can occur with probability 0.039, or about 4%.

What you should do is have the cases depend on the cells next to them; for example, there should be a single gender column (not two) where 0 signifies male, and 1 signifies female (for instance). In the case where no one answers the door, you can set it as 2 to represent "no answer". The following line

IF(B2=1, PsiBernoulli(0.65), 2)

would set the gender column, and would also take into account whether or not someone answered the door at all.

Hopefully you see how to proceed with the rest of the columns.

$\endgroup$

Not the answer you're looking for? Browse other questions tagged or ask your own question.