Part 2. MM4XL Tools > 1. Strategic Tools > Brand Switch > Technicalities

Brand Switch Analyst


For the technicians, who like to look into the black box, here is a description of the model behind Brand Switch Analyst. For a detailed reference read Theil and Rey and the bibliographic references listed in this Help file.

The Quadratic Programming Model

Let P be the matrix of transition probabilities (switch rates) to be estimated, with 0<= pij <= 1, Theil and Rey methodology is to minimize the sum of the squared residuals subject to the constraints set to the pij. In general, let D be any nxn symmetric and positive definite matrix. We can estimate transition probabilities solving the following quadratic problem:


Brand Switch Software Estimates Customer Loyalty and Retention from Sales Performance Data

Subject to:

Brand Switch Software Estimates Customer Loyalty and Retention from Sales Performance Data

Brand Switch Analyst solves the model iteratively, and it can also search for an optimal solution. It first transforms the input data in relative frequencies, then it sets the transition probabilities pij to unknowns and Solver minimizes the equation above.

The criticisms to this model are mainly concerned with the assumptions that:

  • the market stays the same over time, its size does not vary
  • all consumers are supposed to buy every time
  • it considers a fix quantity bought by each customer

We believe the simplification of the model can be seen as the cost of saving the money for a representative marketing research study. Brand Switch Analyst is a loyal reproduction of the method first introduced by Theil and Rey in the journal Management Science.


With the brand switch matrix available you can use Excel's built-in function MMULT() for:

  1. computing higher order Markov processes;
  2. projecting future market shares (refer to the Excel's help for the details).

MMULT returns the matrix product of two arrays. An array can be a vector of data placed on one single row or column range. Multiple arrays take the form of a matrix. Multiplying the (multiple vector) full matrix of switch rates times the (single row vector) last row of available sales values MMULT returns the projected sales values at time t+1, as when you print the forecast from the Brand Switch user interface.

Squaring the switch matrix produces the probability of retention, gain, and loss of share at time t+2 without re-computing the switch matrix. This is also called a second order Markov process. Back to forecasting, Okt03 (Oct.03) is the last available data and forecast at t+1 means November03. By squaring the switch matrix (second order Markov process) and multiplying it times the sales forecast t+1 you produce an estimate of market shares at time t+2 (Dec03). The matrix below corresponds to the square of the switch matrix of our Bakeries example. A third-order matrix is found multiplying the row vector times the second order matrix, and so on.

Brand Switch Software Estimates Customer Loyalty and Retention from Sales Performance Data

Known problems

When using Brand Switch Analyst there are two major technical limitations you should be aware of.

First, as already mentioned, the standard Solver version included in any copy of Excel allows the use of 200 unknown variables or less. Therefore, the maximum number of products that can be analyzed with the standard Solver is 14, as the estimation model uses 14x14 (196) unknown variables in order to be solved, which is the closest number to 200.

Remember! Start Solver once before using MM4XL, or you might get an error window back.

Second, if you get the error message below, do not panic. It is neither your fault nor Band Switch's. It is due to certain Excel versions that do not return resources to the system when producing large amounts of charts. The only way to get them back is restarting Windows! Microsoft claims to have fixed the problem with Excel 2000.

Brand Switch Software Estimates Customer Loyalty and Retention from Sales Performance Data

Use the Map One Product Only to escape from Excel eating up your system resources. This way Excel does not collapse.

Lifetime license:
MM4XL All Tools
Price: euro 238.00

Vote this tool
167 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