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.