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.