Maximum possible allocation given constraints

[eddyble]

Hey Guys,

I'm not too sure how to approach this but i think it's a VBA problem and i'm having a lot of trouble figuring it out. Apologies in advance... it's a long one lol

TL;DR The problem is i'm trying to find the optimal allocations to result in the highest possible value given some constraints.

Here's the long version;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Mkt Val[/TD]
[TD]Weight[/TD]
[TD]BMK[/TD]
[TD]Capacity[/TD]
[TD]Deploy[/TD]
[TD]New Weight[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10%[/TD]
[TD]12%[/TD]
[TD]15[/TD]
[TD]x1[/TD]
[TD]y1[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]20%[/TD]
[TD]25%[/TD]
[TD]15[/TD]
[TD]x2[/TD]
[TD]y2[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]20%[/TD]
[TD]18%[/TD]
[TD]100[/TD]
[TD]x3[/TD]
[TD]y3[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10%[/TD]
[TD]5%[/TD]
[TD]50[/TD]
[TD]x4[/TD]
[TD]y4[/TD]
[/TR]
[TR]
[TD]40[/TD]
[TD]40%[/TD]
[TD]40%[/TD]
[TD]0[/TD]
[TD]x5[/TD]
[TD]y5[/TD]
[/TR]
[TR]
[TD]100 (total)[/TD]
[TD][/TD]
[TD][/TD]
[TD]180 (total)[/TD]
[TD]X (Total)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Mkt Val = Market Value of some asset
Weight = % weighting relative to sum of market value
BMK = Benchmark weight
Capacity = additional capacity that can be added to Mkt Val
Deploy = the amount of capacity that is deployed
New Weight = (MKT Val + Deploy)/(Total MKt Val + Total X) where X is the sum of X1, x2 etc.

Here are the constraints;

New Weight - Weight < +-2%
New Weight - BMK < +-5%

So far i've figured out the maximum X value is ~ 5.2631 which was driven by the constraints acting on the 40 Value (This value has no capacity to add and must be at least 38% according to constraint 1) using guessing and checking.

So the problem is that the New Weightings are based on how much you Deploy which impacts the New Weightings which are driven by the constraints.... so it's sort of very circular and besides guessing and checking to see if it breaches constraints i haven't figured out a way to automate this process and solve for Max X.

Hopefully someone has an idea because this is doing my head in

 

[i_nth]

This is a problem for Solver. You can construct the model in Solver almost exactly as you've described it.
You're right that the optimal solution is x = 5.2632. There are multiple optima that have that value, including allocating 2.6316 to the first two rows, or 1.7543 to each of the first three rows.

 

[eddyble]

Holy **** had no idea there was this add-in.

Saved me a huge headache!

Cheers