Question

LARKIN CORPORATION Larkin Corporation conducted a test designed to evaluate the effectiveness of a new television advertisement for one of its household products. The particular television advertisement was shown in a test market for a two-week period. In a follow-up study, randomly selected individuals were contacted by telephone and asked a series of questions to determine whether they could recall the message in the television advertisement and how likely they were to purchase the product. The test market study provided the following probabilities: Individual could recall the message 0.40 Individual could not recall the message 0.60 Further, response to the question of how likely they were to purchase the product provided the following probabilities: IF Purchase Definitely No Uncertain Definitely Yes Could recall the message 0.30 0.30 0.40 Could not recall the message 0.50 0.40 0.10 For example, if an individual could not recall the message there is a 10% chance that they would definitely purchase the household product. Larkin Corporation management is interested in knowing what is the overall probability of an individual answering “definitely yes” to the likelihood of purchase question using simulation for 1,000 trials. Note: Please give the answer in the form of Simulation in Excel sheet. I used a decision tree and came up with an answer of 0.22 so 220 out of 1000 would definitely say yes to buy. Correct??? and how do I set up in Excel?

Answer 1

Let following be the events

• R= An individual can Recall the message
• NR= An individual can not Recall the message
• N=An individual would respond, Definitely No, to if Purchase
• U=An individual would respond, Uncertain, to if Purchase
• Y=An individual would respond, Definitely Yes, to if Purchase

We know the following probabilities

the overall probability of an individual answering “definitely yes” to the likelihood of purchase question is

The above is the theoretical probability.

Using the simulation we will get an approximation which will be close to 0.22, but not exactly 0.22. As we increase the number of simulations, the standard error of this estimate decreases and we get better estimates

To simulate if an individual can recall or not recall the message.

• Generate a random number from uniform distribution in the interval (0,1), using =RAND()
• If the random number is less than 0,4 then the individual could recall, else the individual could not recall in this trial of the simulation

Based on if the individual can recall or not recall, we do one of the following

If the individual can recall in this trial then do the following

To simulate if an individual purchases given that the individual can recall the message.

First get the cumulative probabilities and the random number intervals

	Recall=Yes
			Random number interval
Purchase	Conditional probability	Cumulative probability	From	To	Purchase
Definitely No	0.3	0.3	0	0.3	N
Uncertain	0.3	0.6	0.3	0.6	U
Definitely Yes	0.4	1	0.6	1	Y

• Generate a random number from uniform distribution in the interval (0,1), using =RAND()
• Check the random number interval in which the generated random number falls in and get the corresponding purchase decision.
  • For example if the random number generated is 0.45, then it falls in the interval 0.3 to 0.6 and hence the purchasing decision is Uncertain

If the individual can not recall in this trial then do the following

To simulate if an individual purchases given that the individual can not recall the message.

First get the cumulative probabilities and the random number intervals

	Recall=No
			Random number interval
Purchase	Conditional probability	Cumulative probability	From	To	Purchase
Definitely No	0.5	0.5	0	0.5	N
Uncertain	0.4	0.9	0.5	0.9	U
Definitely Yes	0.1	1	0.9	1	Y

• Generate a random number from uniform distribution in the interval (0,1), using =RAND()
• Check the random number interval in which the generated random number falls in and get the corresponding purchase decision.
  • For example if the random number generated is 0.45, then it falls in the interval 0.0 to 0.5 and hence the purchasing decision is Definitely No

Now prepare the following sheet

Copy the rows from A9 to D1008 to make 1000 trails. Paste the columns as values to avoid changes

The rest of the columns

get this

Now the overall probability of an individual answering “definitely yes” to the likelihood of purchase question is

Prepare the following

Get this

The estimated probability using the simulation is 0.211 and this is close enough to the theoretical probability of 0.22