Here Is a Basic Spreadsheet Model. It Is Complicated, So Let S Examine It One Section at a Time

Solution to Orange Juice Market Share[1]

Suppose that each week every family in the United States buys a gallon of orange juice from company A, B, or C. Let PA denote the probability that a gallon produced by company A is of unsatisfactory quality, and define PB and PC similarly for companies B and C. If the last gallon of juice purchased by a family is satisfactory, then the next week they will purchase a gallon of juice from the same company. If the last gallon of juice purchased by a family is not satisfactory, then the family will purchase a gallon from a competitor. Consider a week in which A families have purchased juice A, B families have purchased juice B, and C families have purchased juice C. Assume that families that switch brands during a period are allocated to the remaining brands in a manner that is proportional to the current market shares of the other brands. Thus, if a customer switches from brand A, there is probability B/(B + C) that he will switch to brand B and probability C/(B + C) that he will switch to brand C. Suppose that 1,000,000 gallons of orange juice are purchased each week.

Here is a basic spreadsheet model. It is complicated, so let’s examine it one section at a time.

In order to keep the scale of the model within the capabilities of Crystal Ball, we have reduced the number of users to 600.

B4:D4 contain the probabilities that a given unit of the respective companies’ products will be unsatisfactory. These will be used with Crystal Ball to generate “bad” orange juice using the binomial distribution.

B7:D7 contain the initial distribution of customers across the three brands. Every week, the number of customers for each brand is calculated using this formula:

For brand A in week 2, this formula is calculated with:

For brand B in week 2, this formula is calculated with:

Brand C will have all of the total customers from the previous week minus the Brands A and B current customers, so

=(SUM($B$7:$D$7))-(SUM(B8:C8))

Now recall that a binomial random variable X is an integer between 0 and n, viewed as the number of “successes” out of n “trials”. The binomial distribution assumes that there is a probability p of a success on any one trial, and that all trials are independent of each other. In this case, X is the number of gallons that are “bad”, n is the total number of gallons purchased of a particular brand, and p is the probability that any one gallon is “bad”.

In the first week, each brand has 200 customers, so n will be 200 for all three brands, in the second week, n is 224 for Brand A, 194 for Brand B, and 182 for Brand C. (These will change during the course of the simulation.)

We have used columns E, F, and G to truncate these random n values so that they are always at least 1. This will not make any difference most of the time, but occasionally one brand’s customer base will go to zero in a long simulation run, causing an error with Crystal Ball. (Crystal Ball doesn’t know how to generate a binomial variable when n is zero.)

So we need to set up binomial random variables, where the n for each brand is given in column E, F, or G and the p is given in B4, C4, or D4, depending on which brand.

Here is the assumption definition for H7:

Note that we have used dollar signs in the cell references, so this can be copied down to the rest of the assumption cells in column H.

Our model now includes random numbers of “bad” gallons of orange juice for each brand every week. Using these numbers of bad gallons, our model imitates the numbers of customers who abandon each brand each week. Now we need to model the switching behavior of those customers. For example, we have modeled the departure of 18 customers from Brand A in the first week, and another 25 from Brand A in the second week, but we haven’t yet modeled where those customers will go (Brand B or Brand C) to get their orange juice in the following week.

We can use the binomial distribution again here. The number of people who switch from Brand A to Brand B in any given week will be a binomial random variable, with n equal to the total number of people who abandon Brand A in that week, and p equal to the proportion of the non-Brand A market held by Brand B in that week, or B/(B + C). (Recall that the problem asks us to “Assume that families that switch brands during a period are allocated to the remaining brands in a manner that is proportional to the current market shares of the other brands.”)

We’ll set up the ns for these binomial random variables in columns K, L, and M, using MAX functions as before to make sure that they never go below 1. In column N we calculate the proportion of non-A customers who buy B in the current week, once again using a MAX function to make sure this is never zero.

In cell O7 we generate the random number of A customers who switch to Brand B in the first week. Note the use of the “dynamic” radio button, which allows the n and p to change from trial to trial.

The number of A customers who switch from A to Brand C is calculated in column P; it is simply the difference between K7 and O7.

We model the switching behavior of former B customers in columns Q, R, and S, and former C customers in columns T, U, and V.

Finally, the various numbers of switchers are taken into account for the start of the next week in columns B, C, and D.

All of the binomial assumptions (columns H, I, J, O, R, and U) get copied down through row 58, so we can model a 52-week year.

1. After a year, what will the market share for each firm be? Assume PA = 0.10, PB = 0.15, and Pc = 0.20. (Hint: You will need to use the binomial distribution to see how many people switch from A and then use the binomial distribution function again to see how many switch from A to B and from A to C.)

Using the Crystal Ball output, we can estimate that Brand A will have about 56% of the market, Brand B about 33%, and Brand C about 12%.

1. Suppose a 1% increase in market share is worth $10,000 per week to company A. Company A believes that for a cost of $1 million per year it can cut the percentage of unsatisfactory juice cartons in half. Is this worthwhile? (Use the same values of PA, PB, and PC as in part a.)

There are a number of ways to approach this kind of issue. One elegant way is to run two simulations simultaneously, in which the only difference is (in this case) the different value for PA. We’ll run the same model as before, but add to it, in parallel, a second model in which PA = 0.05 instead of 0.10.

The old model is in a worksheet called Part (a) and the new model is in a worksheet called Part (b). We’ll add a new forecast cell in the new model (cell K4), which will be the difference between the two ending markets shares for Brand A:

The summary statistics for this new forecast cell ought to give us a good idea as to how Brand A’s share would change if they could reduce their probability of a bad gallon.

After we run the new combined simulation, we get these results:

A 95% confidence interval for the increase in market share is given by:

Let’s take the most pessimistic end of this confidence interval and assume that Brand A will get an 18.56% increase in market share for its $1 million investment. This translates to $185,600 per week, or about $9.6 million per year.

B60.23501Prof. Juran

[1] Based on 12-38 (p. 686) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Based on Babich (1992). Solution by David Juran, 2002.