Maximum possible allocation given constraints

eddyble

New Member
Joined
Apr 30, 2018
Messages
4
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;

Mkt ValWeightBMKCapacityDeployNew Weight
1010%12%15x1y1
2020%25%15x2y2
2020%18%100x3y3
1010%5%50x4y4
4040%40%0x5y5
100 (total)180 (total)X (Total)

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

i_nth

New Member
Joined
Mar 28, 2019
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 30, 2018
Messages
4
Holy **** had no idea there was this add-in.

Saved me a huge headache!

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,109,003
Messages
5,526,221
Members
409,688
Latest member
Mc Junior

This Week's Hot Topics

Top