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.

Excel Modeling: Optimal Taxi Model

Imagine that you manage a taxi company and want to select the taxi that helps to minimize the time to get to the new client. This tool helps to make decisions quickly and increase profits for the company having the clients happy.

In this example I randomly put ten taxis in a squared area moving to a new destination. I randomly put a client in this place and get the best taxi to optimally serve the client based in the least distance. 

In real life the implementation should not be a problem. A GPS in every taxi unit and the client location (latitude and longitude) are the necessary tools to be able to use the model. 

Here is the link.

If you found this helpful, please share.

Financial Modeling: Excel Forecasting Trends

I created an Excel model to try different line trends and choose which of them is going to be the best fit for the model.

Many time series contain trends. A trend usually measure the time persistence of one variable (sales, price, etc) which is difficult to foresee that it will remain in the future.

A good practice to identify a trend is to insert a line graph, and try different trend options in Excel.
Among these options, the most common types are:

Function	Expression	Excel formula
Linear	y = bx+a	=LINEST(y,x's)
Logarithmic	y = bLn(x) + a	=LINEST(y, LN(x's))
Power	y = ax^b	=LINEST(LN(y), LN(x's))
Exponential base e	y = ae^(bx)	=LINEST(LN(y), x)
Polynomial Order 2	y = cx^2+bx+a	=LINEST(y,{x^2,x})
Polynomial Order 3	y = dx^3+cx^2+bx+a	=LINEST(y,{x^3,x^2,x})

Once we have identify the best fit for our data. We need to rebuild the trend based on the function type and its mathematical expression.

Why do we care to choose the right trend?

Trend sometimes has a huge impact in the variable behavior, and choosing the wrong one we can introduce errors in our model.

What is the advantage of using my Excel file attached?

There are two. First it gives us automatically the best R2 among all the trend options, and, second, check the future behavior that we are going to introduce in our model.

Here is the Excel file (link). If you found this helpful, please share.

Financial Modeling: Sensitivity Analysis in Excel

Following the analysis of the last posting, we know how to do a Regression Tornado.  Now doing a little math, we can change the standardized coefficients to dollar values and identify the monetary impact. For example, a standard deviation  increase in Unit Sales Y1 impacts the NPV by $ 6 million, and one sigma Growth Rate changes the NPV by one million.

Case study: We need to increase the NPV by $5 million. What do we need to do?

To achieve this goal we need to raise 21 million units in the Units Sales Y1 which is feasible given the 30 million units gap between the baseline and the best scenario in our model. On the other hand, to achieve the same goal using Growth Rate we need to boost growth sales by 3.5%. 

Here is the link with the Excel file. If you found this helpful, please share.

Financial Modeling: Regression Tornado Graph Without @Risk

Regression tornado graph below is a useful tool that shows the most important variables impacting a model. Now, this tool is only found in @Risk, but using standard Excel functions I replicated this analysis in this Blog.

What is the difference between a normal regression and a regression tornado? The coefficients of the regression tornado are standardized or adjusted to make them comparable and to easily extract conclusions from them. The standarized coefficients give us how many standard deviations the main output is going to change when the independent variable change by one standard deviation.

For example, in this model: NPV=f(Sales, Growt Rate, ...)

At first glance, Unit Sales Y1 has more impact on the NPV than the Growth Rate. A standard deviation  (sigma) increase in Unit Sales Y1 results in an increase of 0.8 standard deviation in the NPV, while one sigma increase in growth rate is only 0.2 increase in NPV.

Why is it important? It tells us that we need to start very strong in our first year sales because it will make a huge difference in the NPV outcome.

All my calculations are in this Excel file (link).
Here is a useful manual of how to use tornado regression charts in @Risk (link).
If you found this helpful, please share.