Marketing Models

1

• Marketing Models

2
Example 12.12

• Marketing Models

3
Background Information

• DoItQuick is software company that sells programs
  to individuals for keeping track of home
  finances, home inventory, and other common tasks.
• The company has done extensive research into its
  costs and revenues, and it has discovered that
  new customers are much less profitable on an
  annual basis than long-standing customers. There
  are several reasons for this.

4
Background Information -- continued

• Long-standing customers tend to require less in
  overhead costs, they tend to order more
  merchandise annually, and they help DoItQuick
  make money by referring new customers to the
  companys products.
• The company estimates that a customer who has
  been loyal for n years that is has bought from
  the company for n consecutive years contributes
  a normally distributed random amount of profit in
  the nth year that has mean and standard deviation
  as listed on the next slide.

5
Background Information -- continued
6
Background Information -- continued

• DoItQuick is interested in seeing how much profit
  a typical customer is worth over his or her years
  with the company.
• This depends on the probability of retention. To
  model retention, let r(n) be the probability that
  a customer who has purchased for n consecutive
  years does not purchase the next year.
• If this occurs, we assume that the customer
  switches loyalty and never purchases from
  DoItQuick again.

7
Background Information -- continued

• A consultant has suggested to DoItQuick that a
  reasonable model of customer retention is to let
  r(1) 1-p for some p between 0 and 1, and to use
  the equation r (n) qr(n-1) for ngt2, where q is
  a positive constant.
• What does this model mean, and how can it and the
  data be used to simulate the nest personal value
  (NPV) of profit over a 20-year period from a
  typical customer who has made his or her first
  purchase from DoItQuick this year? Assume an
  interest rate of 10 for discounting.

8
Solution

• The solution is broken into several parts.
• First we will explain the consultants retention
  model.
• Then we will fit curves to the profit data.
• Finally, we will develop the simulation model and
  run it with _at_Risk.

9
Explaining the Retention Model

• The consultants retention model makes sense.
• First, p represents the probability that a
  customer who purchases this year for the first
  time will purchase again next year.
• Then q is the fraction by which the probability
  of not remaining loyal changes year by year.
• The company wants the r(n) values, the
  probabilities of losing customers, to be small,
  so it wants p to be large and q to be small. We
  will test several pairs of p and q when we run
  the simulation to see how these parameters
  affect the NPV of profit.

10
Finding the Data

• We first use the ideas from Chapter 2 to fit
  equations to the means and standard deviations.
• For each, we draw a scatterplot versus year, then
  superimpose an appropriate trendline with Excels
  Chart/Add Trendline menu item.
• As shown in the figure, a logarithmic fit of the
  means looks good, whereas a linear fit of the
  standard deviation seems appropriate.
• Therefore, in the simulation model we will
  estimate the mean and standard deviation of
  profit from a customer in her nth year with the
  company as 23.285 64.941ln(n) and 5.5515
  1.3505n, respectively.

11
LOYALTY.XLS

• The simulation model appears on the next slide.
• This file contains the model.

12
The Simulation Model
13
Developing the Spreadsheet Model

• The model can be developed with the following
  steps.
• Inputs. Enter the inputs in the shaded cells.
  These include the parameter of the fitted
  equations for mean and standard deviation, the
  discount rate, and selected values of the
  retention parameters p and q.
• Simulation index. We will use RISKSIMTABLE to run
  the simulation 12 times, once for each
  combination of p and q. To set up the model to do
  this, enter the formula RISKSIMTABLE(SimIndexes)
  in cell B11. Then obtain the corresponding values
  of p and q in cells B13 and B15 with the formulas
  VLOOKUP(SimIndex,LookupTable,2) and
  VLOOKUP(SimIndex,LookupTable,3)

14
Developing the Spreadsheet Model -- continued

• Profits. We want to simulate profits from a
  customer for as long as the customer remains
  loyal to the company. To do so, first calculate
  the appropriate means and standard deviations in
  columns B and C of the simulation section with
  the formulas InterceptMeanSlopeMeanLN(A21) and
  InterceptStdevSlopeStdevA21 in cells B21 and
  C21, and copy them down for all 20 years. Then
  generate the actual profits from this customer in
  column D as long as the customer remains loyal.
  Start by generating the first-year profit in cell
  D21 with the formula RISKNORMAL(B21,C21) Then
  for succeeding years, enter the formula
  IF(OR(F21Yes,D21),,RISKNORMAL(B22,C22))
  in cell D22 and copy it down. The OR condition
  checks whether the customer has discontinued
  buying from DoItQuick. If so, a blank is entered.
  Otherwise, a normally distributed profit is
  generated.

15
Developing the Spreadsheet Model -- continued

• Probabilities of quitting. Calculate the
  probabilities of quitting in column E from the
  retention model. To do so, enter the formula
  1-PrKeepBuying1 in cell E21. Then for succeeding
  years, enter the formula IF(OR(F21Yes,D21),
  ,RetFactorE21) in cell E22 and copy it down.
• Quits? We keep track of the customers status in
  column F. First, enter the formula IF(RAND( ) lt
  E21,Yes,No) in cell F21. Then enter the
  formula IF(OR(F21Yes,D21),,IF(RAND(
  )ltE22, Yes, No)) in cell F22 and copy it
  down. This logic will produce several values of
  No, followed by a single Yes and then blanks.

16
Developing the Spreadsheet Model -- continued

• Output cells. We will keep track of the NPV of
  profit and the number of years remaining loyal
  for this customer as _at_Risk outputs. Calculate
  these in cells B43 and B44 with the formulas
  RISKOUTPUT( ) NPV(DiscRate,Profits) and
  RISKOUTPUT( )COUNT(Profits). Note that the
  COUNT function counts nonblank cells only.

17
_at_Risk Results

• We set the number of iterations to 1000 and the
  number of simulations to 12.
• Selected summary results appear on the next
  slide.
• For a change, we copied and pasted the _at_Risk
  results to the spreadsheet so that we could
  easily see how they vary with p and q.
• The bar charts of the means clearly show how
  large values of p and small values of q are best
  for the company.

18
_at_Risk Results
19
_at_Risk Results -- continued

• By increasing the probability of keeping
  customers loyal, the company can make a big
  improvement in its bottom line.

20
Example 12.13

• Consumer Preference Models with Correlated Values

21
Background Information

• There are currently two brands of brownies on the
  market.
• The Bisquake Company plans to enter the brownie
  market with one of two new brands.
• Each of these existing brands and potential new
  brands is characterized by three attributes
  sweetness (measured on a 1 to 10 scale),
  chewiness (measured on a 1 to 10 scale), and
  price per box.
• These attributes are shown in this table.

22
Background Information -- continued

• Each customer is assumed to choose one of these
  brands over the other on the basis of a weighted
  combination of the three attributes.
• That is, each customer is assumed to calculate a
  score for each brand as Score ws(Sweetness)
  wx(Chewiness) wp(Price) where the ws are
  weights.

23
Background Information -- continued

• Each customers weights are different, depending
  on how important sweetness, chewiness, and price
  are to the customer. However, we might expect
  these weights to be correlated.
• For example, if a customer attaches a lot of
  importance to sweetness, she might also attach a
  large weight to chewiness thus they would be
  positively correlated.
• We assume that the population of customers assign
  normally distributed weights with the means and
  standard deviations shown in this table.

24
Background Information -- continued

• We will also assume that the correlations between
  these weights are as given in the table below.

25
Background Information -- continued

• Note that all correlations are positive, which
  implies that if a customer puts a large weight on
  one attribute, he will tend to put a large weight
  on the other two attributes.
• Bisquake wants to use simulation to identify the
  new brand (from the two possibilities) that is
  likely to obtain the larger market share.

26
Simulation

• A single iteration of this simulation will
  simulate the behavior of a single customer. That
  is, it will generate this customers weights,
  find the customers scores for each of these
  brands, and see whether the customer prefers new
  brand 1 or new brand 2 to the existing brands.

27
BROWNIE.XLS

• This file provides the setup to develop the model
  seen on the next slide.

28
The Simulation Model
29
Developing the Spreadsheet Model

• Inputs. Enter the inputs in the shaded ranges.
  These include the given means, standard
  deviations, and correlations for customers
  scores on the attributes. They also include the
  actual attributes of the two existing and two new
  brands.
• Simulated weights. _at_Risks method of generating
  correlated random numbers is not very intuitive,
  but it is quite easy once you see how it works.
  We want the weights in the SimWeights range to be
  normally distributed with the means and standard
  deviations in the range B6D7, but we also want
  them to be correlated. To accomplish this,
  generate the first weight (for sweetness) in cell
  B24 with the formula RISKNORMAL(B6,B7,RISKCORRMAT
  (CorrMatrix,B23)) Then copy this to the range
  C24D24 to generate the weights for the other two
  attributes.

30
Developing the Spreadsheet Model -- continued

• It simply instructs _at_Risk to generate a normal
  random number but to correlate it with other
  potential random numbers, using the correlations
  in the first column of the CorrMatrix range. (It
  uses the first column because the second argument
  of RISKCORRMAT is 1.) For chewiness, this second
  argument is 2, and for price it is 3. This second
  argument essentially designates the position in
  the correlation matrix for the particular random
  value.
• Scores for brands. Calculate this customers
  scores for the four brands in the range B27B30
  by entering the formula SUMPRODUCT(SimWeights,B17
  D17) in cell B27 and copying it to the range
  B28B30. This formula weights the attributes of
  each brand with the customers weights.

31
Developing the Spreadsheet Model -- continued

• Is either new brand chosen? One of the new brands
  will be chosen if its score is larger than the
  larger score of the two existing brands.
  Therefore, enter the formula RISKOUTPUT(
  )IF(B29gtMAX(ExBrScores),1,0) in cell B33 to
  check whether new brand 1 is preferred to the
  exiting brands. Then copy it to cell B34 to do
  the same for new brand 2.
• Summarize output cells. The _at_Risk output cells,
  B33 and B34, contain 1 or 0 depending on whether
  either new brand is preferred to existing brands.
  We want to determine the fraction of the time
  these will be 1. To do so, we can run the
  simulation for many iterations and calculate the
  means of the output cells. This is because the
  average of a sequence of 01 and 1s is the
  fraction that are 1s. We can calculate these
  fractions directly in the spreadsheet by entering
  the formula RISKMEAN(B33) in cell B37 and
  copying it to the cell B38.

32
Using _at_Risk

• We set the number of iterations to 1000 and the
  number of simulations to 1.
• After running _at_Risk, we see from cells B37 and
  B38 that new brand 1 preferred to existing brands
  in 64.7 of the iterations, and new brand 2 is
  preferred to existing brands in 76.6 of the
  iterations.
• Based on this information, new brand 2 appears to
  be the more promising brand for BisQuake to
  market.

33
_at_Risk Results

• How do these results depend on the correlation
  structure we assumed earlier?
• We note that because price weights are negative,
  the positive correlation between the sweetness
  (or chewiness) and price is less intuitive. It
  says that large weights on sweetness tend to go
  with large weights on price. But because price
  weights are negative, a larger weight on price
  means a less negative weight for price for
  example 5 is larger than 8. So the positive
  correlation between sweetness and price really
  means that if a customer puts a lot of weight on
  sweetness, he cares less about price.

34
_at_Risk Results

• For the sake of argument, suppose you think that
  the weights a customer assigns to the three
  attributes are probabilistically independent.
• Then we should change the correlations in all
  cells of the correlations matrix to 0 and rerun
  the simulation.
• When we did this, the values in cells B37 and B28
  changed to 69.8 and 82.2.
• This is not a dramatic change, but it does show
  that correlations can make a difference.

35
Example 12.14

• A Market Share Model

36
Background Information

• Sweetness and IceT are the two dominant companies
  in the bottled iced tea market.
• Each currently possess 49 of the total iced tea
  market, with three smaller companies splitting
  the remaining 2.
• At the beginning of any year, a random number of
  new small companies enter the iced tea market.
• The actual number of new entries is assured to be
  Poisson distributed with mean 1.

37
Background Information -- continued

• After the new entries enter the market, there is
  a random shift in market share among all
  competitors.
• Essentially, all competitors lose a random
  percentage of their market share to other
  competitors.
• We will assume that each of these percentages is
  triangularly distributed with the parameters
  given in the table on the next slide.
• Therefore, the more small companies there are in
  the market, the more of its market share
  Sweetness will tend to lose to them.

38
Background Information -- continued
39
Background Information -- continued

• At the end of each year, each of the small
  companies has a 50 chance of exiting the ice tea
  market.
• Each small company that exits will lose its
  market share to Sweetness or IceT.
• The percentage of this marketshare that goes to
  Sweetness is triangularly distributed with
  parameters 40, 50, and 60 the rest goes to
  IceT.
• The dominant companies, Sweetness and IceT, want
  to use simulation to see how their market share
  is likely to change over the next 10 years.

40
Solution

• At the beginning of the year we observe the
  market shares of Sweetness, IceT, and the small
  companies (combined).
• Next, we simulate the number of new entrants.
  Then we simulate the shifts in market share
  during the year.
• Next, we simulate the number of small companies
  that exit at the end of the year, and we simulate
  the market shares that go to Sweetness and IceT.
• Finally, we tally the total market share at the
  end of the year for all competitors.

41
ICETEA.XLS

• This file provides the setup to develop the model
  seen on the next two slides.

42
The Simulation Model
43
The Simulation Model
44
Developing the Model

• The model can be formed with the following steps
• Inputs. Enter the inputs shown in shaded ranges.
• Beginning market shares. For year 1 the beginning
  market shares are inputs. For example, find the
  beginning market share for Sweetness in cell B35
  with the formula B5. For every other year, the
  beginning market shares are the ending market
  shares from the previous year. For example, find
  the beginning market share for Sweetness in year
  2 by entering the formula B66 in cell C35. Then
  copy this to the range C35K37 for all the
  competitors over the remaining years.

45
Developing the Model -- continued

• Entries to the market. In year 1 find the number
  of small companies before entries, the number of
  new entries, and the number of small companies
  after entries by entering the formulas B9,
  RISKPOISSON(MeanEntries), and SUM(B40B41) in
  cells B40, B41, and B42, respectively. Note that
  the RISKPOISSON function, which takes a single
  argument, generates the number of new entrants in
  a single year. For year 2 the number of small
  companies before entries is the remaining number
  from year 1. Therefore, enter the formula B58 in
  cell C40. Then copy the formulas in cells C40,
  B41 and B42 across the rows.

46
Developing the Model -- continued

• Market shares lost during the year. Generate the
  percentage of its market share Sweetness loses to
  IceT and to the small companies (combined) in
  year 1 by entering the formulas
  RISKTRIANG(B24,C24,D24)B35 and
  RISKTRIANG(B25,C25,D25)B35B42 in cells
  B46 and B47 and then copy these across rows 46
  and 47. Next, enter similar formulas in rows 49,
  50, 52 and 53 for market share lost by IceT and
  the small companies. For example, the formula in
  cell B53 is RISKTRIANG(B31,C31,D31)B37.

47
Developing the Model -- continued

• Exiters. Rows 56-59 contain information about
  small companies before and after exiting. To
  calculate this information, enter the formulas
  SUM(B37, B47,B50)-SUM(B52B53),
  IF(B42gt0,RISKBINOMIAL(B42,B13),0), B42-B57,
  and IF(B42gt0,(B57/B42)B56,0) in cells B56, B57,
  B58, B59. The copy these across rows 56-59. The
  formula in B56 simply tallies the market shares
  lost and gained for the small companies before
  exiting takes place. The formula in B57 uses the
  RISKBINOMIAL function to generate the number of
  small companies and the probability that any
  company exits. Finally, the formula in B59 finds
  the amount of market share possessed by the
  exiting companies under assumption that all small
  companies have an equal market share.

48
Developing the Model -- continued

• Market share gained by exiters. The assumption of
  the model is that the market share of the exiters
  in row 63 is split randomly between Sweetness and
  IceT. To generate the split, enter the formula
  RISKTRIANG(B20,C20,D20)B59 and B59-B62
  in cells B62 and B63. Then copy these across rows
  62 and 63.
• Year-end market shares. Calculate the year-end
  market shares of Sweetness, IceT, and the small
  companies (combined) by entering the formulas
  SUM(B35,B49,B52,B62)-SUM(B46B47),
  SUM(B36,B46,B53,B63)-SUM(B49B50), and B56-B59
  in cells B66, B67, and B68. Then copy these
  across rows 66-68. If you like, you can check
  that the year-end market shares sum to 100 for
  each year, as they should.

49
Developing the Model -- continued

• _at_Risk outputs. We have not yet designated any
  cells as _at_Risk output cells. There are at least
  two possibilities. If we are interested in only
  the final market shares after 10 years, we should
  designate cells K66, K67, and K68 as output
  cells.
• Alternatively, if we want to see how market
  shares move through time, we can specify whole
  ranges as output ranges. When you do this, the
  formulas change slightly. For example, the
  formula in cell B66 becomes RISKOUTPUT(,Sweetnes
  s,1)SUM(B35,B49,B52,B62)-SUM(B46B47) to
  indicate that this is the first cell in the
  Sweetness output range.

50
_at_Risk Results

• We set the number of iterations to 1000 and the
  number of simulations to 1.
• After running _at_Risk, we obtain histograms of
  market share after 10 years. The histograms can
  be seen on the next two slides.
• We see that the final IceT market share is
  essentially symmetric around its original value
  of 49, although there is considerable
  variability.
• In contrast, the final market share for the small
  companies has a good chance of being 0, although
  there is a small probability that it could be
  considerably larger up to 8, say.

51
_at_Risk Results
52
_at_Risk Results
53
_at_Risk Results -- continued

• Assuming that we designate whole rows as output
  ranges, such as row 66 for Sweetness, we can
  obtain a summary chart of the companys market
  share though time as shown on the next slide.
• This chart shows that the mean market share for
  Sweetness remains approximately constant through
  time.
• However, as we stand at the beginning of year 1
  and try to predict the future, there is more
  uncertainty the farther out we look.
• This is a general rule. It is almost always
  harder to make long-range forecast than
  short-range forecasts!

54
_at_Risk Results