Part 2. MM4XL Tools > 1. Strategic Tools > Risk Analyst > 3. Examples > Example 1: Advertising Planning

Risk Analyst

Example 1: Advertising Planning

A hypothetical product manager is planning next years budget for coupons to redeem product samples placed in two magazines. The question is whether to purchase only medium A, only medium B, or both media. If any medium is purchased, the next question is whether to purchase only for half a year or for the whole year.

For this example see file 0-Advertising.xls.

The model

The following Contributing Factor Diagram (CFD) shows the model in a pictorial manner. The Media plan of choice will be the one with the lowest index, which measures the average cost per redeemed coupon and it is found using the size of the Redemption and the Cost of the medium.

 Monte Carlo Simulation Software: Management Process Risk Analysis

Modeling assumptions

This table shows the fix parameters of the model for each media.

 Monte Carlo Simulation Software: Management Process Risk Analysis

A coupon is redeemed when a customer returns it through an authorized store. From past experience the product manager could make estimates of the probability of redeeming given levels of redemption ranging from 1% to 6% (column B of the following picture). Media A, for instance, was assigned a probability of 5% to the possibility that only 1% of the Net Audience will redeem the coupon, 30% probability that 2% of the audience will redeem, and so on.

 Monte Carlo Simulation Software: Management Process Risk Analysis

The data in the table above was used to build the formula that returns the Index that measures the goodness of each of the three plan options, as shown in the table that follows. The formula in cell C28, for instance, is as follows and it is copied across the whole range C28:H81:

=$B28*C$12*(1-F28) / ($B28*mmDISCRETE($B$19:$B$24,C$19:C$24)*C$10) +mmLOCK()

The first part of the formula finds the cost of placing the coupon in one issue of medium A and the second part, after the sign divided, estimates the number of readers that will redeem their coupon. The formula uses mmLOCK to make the simulation engine run fast.

 Monte Carlo Simulation Software: Management Process Risk Analysis

The Discrete distribution is explained later in this help file. For now it is only necessary to know that it is designed to extract each of the values in the range B19:B24 according to the probabilities of occurrence in the range C19:C24, for instance, or in the range D19:D24, or in another range. Multiplying the returned value times the value in C10, we obtain an estimate of people redeeming from each issue. The formula in column I returns the column number of the best plan as from columns C:D. The best plan is the one with the lowest cost by redeemed coupon.

Finally, the last three rows of the following table host the Output variables of this model. That is, those values that we want to monitor in order to judge the outcome of the model. Row 13 says that 44% of the time Media B was the best option of the three in terms of Cost/Redemption. If the product manager was to buy a space in 54 issues, the media of choice should have been the media with the largest percentage in row 13. Whether to buy for the whole year or for one semester only can be determined from the data in row 14 and 15. In order to answer, the model must be run to produce the simulated values.

 Monte Carlo Simulation Software: Management Process Risk Analysis


The model was run 1000 times, and then the Short Report was printed, as shown in the following picture.

 Monte Carlo Simulation Software: Management Process Risk Analysis


From rows 10:12 of the table above we see that the best plan among the three is the one with Media B only, called Best 2, where best is the plan with the highest Mean value, that is the plan that during the 1000 simulations happened to return most often the lowest cost per redeemed contact. In 475, or 47.5%, of the simulated trials Media B had the lowest cost. Although, it must be said, a certain level of uncertainty is associated with the outcome, which can be seen in the broad range of values the variable can take, from 25.9% to 74.1%.

Concerning whether to buy for one semester or two, rows 13:15 of the table above refer to the Mean cost of each media over the first 27 weeks while rows 16:18 refer to the Mean of weeks 28 through 54. In this case the lowest value is better because we are referring to cost by redeemed coupon. Cell E15 suggests that for the short run, one semester only, the best option is to use both media A and B because together they return the lowest cost per redeemed coupon. On the other hand, going for the whole year it would be best, according to these results, to employ Media B only.

Although it is the preferable plan, Plan C shows a larger standard deviation than Plan B, which means that its simulations were more spread in value than those for Plan B. Therefore, in order to validate the results of this simulation, we computed confidence intervals as shown in range C21:F34 of sheet Short Report1 of the Excel file relating to this example. The figures in D31:F31 do not overlap with values in D33:F33, so we can conclude it could be really better to buy only Media B for the whole year.

Lifetime license:
MM4XL All Tools
Price: euro 238.00

Vote this tool
335 votes: vote vote vote vote vote
5 stars:
4 stars:
3 stars:
2 stars:
1 star:
We proudly serve
Your vote
vote1 vote2 vote3 vote4 vote5