Monte Carlo Simulation Software: Management Process Risk Analysis

Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > 3. Examples > Example 3: Correlated variables

Risk Analyst

Example 3: Correlated variables

The workbook (Excel file) with the model described in this example is called Correlated-Shares.xls and can be found in the directory Examples at the location where MM4XL software was installed.

This example demonstrates how to use the mmCORREL function to estimate product acceptance of pharmaceutical drugs. The model can be enlarged and applied to other product categories too.

The model

A survey study run with 1000 physicians found that safety, efficacy and price of the drug have a strong impact on the prescribing behavior of the sample. Our marketing manager is now wondering whether these variables could help to build a model to find out whether to market one of two new products in development.

The following Contributing Factor Diagram (CFD) shows the model for the selection of the New Product that should be launched (rectangle on the left in the picture), if any. The Best Product (hexagon on the right) is found by selecting the highest Score as provided by the model.

Monte Carlo Simulation Software: Management Process Risk Analysis

Each run of the model simulates the prescribing behavior of one doctor, and the brand each doctor prefers is chosen according to the assumption that the best brand is the one producing the highest score. The score is obtained as the sum of weighted attribute values, that is:

Or, for instance, the score of the Existing Brand is:

Modeling assumptions

The modeler assigned objective values to efficacy and safety of the existing brand and to the new products in development, called New Brand A and New Brand B. In other industries less well regulated than the pharmaceutical one the modeler can still use survey data to evaluate attributes.

The 1000 doctors also assigned a score ranging 0-100 to the importance they attributed to safety, efficacy, and price of the existing brands. From the answers, the mean and standard deviation for each variable were found and the correlation coefficient was computed, as shown in the following tables.

Cells displaying in green host two kinds of Risk Analyst formulae. In row 21 there is the array function mmCORREL, which produces values used in row 28 by three mmNORMAL functions to estimate attribute weights correlated according to the correlation coefficients in the range C18:E20. Our sample said that safety and efficacy are both strongly correlated to the price of the drug. This fact may be interpreted as if the sample doctors believe that safe and effective drugs cost more, perhaps due to the cost of the primary research.

In G11:G13 of the table below there are SUMPRODUCT formulae that find scores by multiplying the values in row 21 with the attribute values in each of the rows 11:13. This means that the higher the score, the higher the value a doctor from the sample attributes to the brand.

Finally, in I12:I13 we use the following formula to identify the Best Product:

=mmOUTPUT()+IF(G12>G11,1,0)+mmNAME("New Brand A")

In row 29 the Dummy Weights are required only to show in the Sensitivity page of the Preview form the correlation between attributes. Indeed, they get very close to the desired level of correlation demanded by the modeler.

Simulation

This model was simulated 1000 times, although during the fine-tuning phase it was common practice to simulate only 100 runs in order to save time. The operational time, however, was a minor issue with this model, because it runs fast and even 1000 simulations take just seconds to produce.

The Short Report was used to evaluate the model. Basically, the core information we looked for was the number of times each New Brand produced better results (score) than the existing brand.

For the sake of accuracy, from the Sensitivity page of the Preview window one can see that the model produced correlation levels for the three attributes in accordance with the values of the table Correlations.

Interpretation

From range E10:E11 of sheet Short Report1 of file Correlated-Shares.xls we can see that in 71% of the cases the model found New Brand B to have a higher score than the Existing Brand, as opposed to only 44% of cases when New Brand A performed better than the existing one. According to these results, the modeler can assume that New Brand B could be preferred over the Existing Brand, and the project should be pursued further.

This model is rather simplistic but nevertheless it shows effectively how to use correlated variables in simulation models.

Go to top | Feedback

5 stars:
4 stars:
3 stars:
2 stars:
1 star:

Your vote
1 2 3 4 5
Email:
Gender:	M F
Age:
Position:
Department:
Comment: