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.

Financial Modeling: Budget Analysis Problem

Have you ever tried to negotiate a budget? It is easier when your business units or departments ask for lower money but it is not a typical real situation. It is always useful to have some rules as a starting point and analytic tool to help us crunch the numbers.

What are the typical information that you have? 

- The request of money by each division

- Last year given and budget used by each division

- Weight of revenue or profits generated by each business unit

- Total budget to spend

I found an interesting budget problem in internet that was solved using a long VBA program, but here I was able to solve using only Excel formulas and solver evolutionary. Here is the challenge: 

"Suppose your company is in the middle of its annual process to plan its revenues and expenses. You are head of a division with 6 departments (A to F). Your department heads requested 2000, 1900, 2000, 2000, 600 and 2000 but you only got a budget of 9500 from your company.

Your departments' weighted contributions to your company's revenues are 30%, 20%, 15%, 15%, 10% and 10%. How do you distribute your budget?" (Link)

Here is the answer from the link.

I was able to replicate the solution of this problem using an Excel model. Although it seems an easy job, it is very complex, and it requires some rules to replicate the given VBA solution.

Some rules behind the solution:

• There is a positive relationship between % Revenue and the assigned budget. A more % revenue, more budget assigned 
• Equal money request and weighted revenue implies an equal assigned budget between the two divisions
• Equal weighted revenue and different money request implies possibly different budgets
• Numbers are integers and multiple of 25
• I wont give more money than requested

Excel implementation:

• Put the information of money request and weighted revenue
• Bottom assigned money is found between requested money and total budget weighted by % revenue
• The maximum is the top asked money of each division
• Objective is to maximize the budget of the more profitable business units under the constraints

Why Evolutionary:

Because there are many conditional cells the typical solver algorithms are not useful to get the optimal budget and the used of the genetic algorithm of evolutionary is perfect for this situation.

Evolutionary works really well with penalties. In this problem there are two:

1. Total budget is equal to the sum of each individual assigned money
2.  a) If the money request weighted by %revenue "Division i" is higher (lower) than the requested money weighted by %revenue of "Division j", and the assigned money is higher (lower) it is Ok
   b) If the requested money and %revenue of two divisions are equal
   The combination of these two conditional helps solver to find a really optimal solution. 

Budget Analysis

Here is the Excel link with the 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.