Marketing Modeling: Pricing Tied Products

Have you ever wondered why the world cup album is so cheap or zero price? The pricing model used here is pretty close to the model of razor-blades, printer-cartridges, console-games, cinema-foods, etc. In this model the main profits will come from the tied products. In this example the profits will come from the stickers. 

The idea behind this pricing strategy is to engage as much as possible album buyers to increase the chance of future sticker sales. 

Using Excel modeling and solver GRG, I was able to optimize the pricing strategy from the album and its stickers sales.

Doing a quick market sizing and assuming some elasticity I built a demand curve for the album. Running Excel solver to find the optimal price of the album I got that the best price is zero. Using this price I got negative profits from album but very large positive profits from sales of sticker package. Here in the picture you can find the assumptions and the solutions.

World Cup Album Pricing Strategy

Here is the link of the Excel file.

If you found this helpful, please share.

Excel Modeling: Voronoi Diagram in Excel

Voronoi is a mathematical tool that helps us show easily influential areas of a company o institutions. In business it is a powerful tool to see what is your potential market graphically, and create population clusters based on locations.

As an example of clusters, I divided the Peruvian country by departments using a voronoi diagram. Each area represents the minimum distance to its capital, assuming no natural barriers. If you see Loreto, almost half of it is closer to San Martin and Ucayali. There are many useful policy decisions that regional policymakers can make using this analysis.

Peru: Voronoi Diagram

Many chain retailers are using this analysis to have a grasp of the competitors and their potential markets before opening a new store.

Usually to do this analysis you have to use advanced software like R, Matlab or ArcGis among others.

Using only Excel formulas, I was able to replicate the calculations of influential areas for a popular supermarket in Peru and what would be its "market share" if there were not competition.

To build this model I needed only the geolocation of the supermarket, and the border limits to do the analysis.

The general idea that I used to build the Excel function (no VBA) was:

m, n number of pixels to draw
x1,x2,...xk: Latitude
y1,y2,...yk: Longitude

for i = 1 : m
y = ( m - i ) / ( m - 1 ) ;
   for j = 1 : n
   x = ( j - 1 ) / ( n - 1 ) ;
      for kk = 1 : k
      d = min(norm ([x1,y2] - [ x , y ] ));
      end
      a(i,j)=closest k store
   end
end

The Excel formulas that I used were a combination of arrays, Index, Match, and Min.

Plaza Vea: Voronoi Diagram

The  Excel file is here. 

If you found this helpful, please share.