Hope to be bailed out- I have a duzzy,
Despite the headache, believed to have ½ solved and looking for advice suggestion to scale my exiting production.
With a attached is a workbook designed to analyze investment exposure of Fund of Fund Investments.
In workbook termed Parent Weighted Allocations, have solved for one instant (Parent Fund 4271), where parameters allow to analyze the investment exposures based on three underlying child funds.
Thought that I was smart, ‘superboss’ is asking to analyze 15 other parent funds (only 7 shown here). Moreover wants to review daily !
Difficulty with my model is that the user needs to enter a specific weight in my child weight parameters ‘154_%Weight, … 442_%Weight. Done across, 15 Parent funds on daily basis would run me bazerkers !
To help automate production, have added the respective weights of my child funds to my data source. Are looking for advice though that would permit having the child weight parameters view the Parent weights table, so that they would not need to be keyed in.
Otherwise are looking for a magic calculation calculated field that would use logic that would join (multiply) respective Market Value % of Parent and Child.
If Parent fund 4271,
Where CI_IssuerName = '.15X CN' THEN 'MW_FOF' (*) ‘MARKET VALUE %’of NAFI PORT CODE ‘154’ +
IF CI_IssuerName =.442X CN 'MW_FOF' (*) ‘MARKET VALUE%’ for NAFI PORT CODE ‘442’ +
IF CI_IssuerName =.4045X CN 'MW_FOF'* ‘Market Value %’ for NAFI PORT CODE ‘AA472’
SUM = MAGIC !
If solving for my example 4271 could likely be on my way to complete calcuations for other permutations
In then end was hoping to use this calculation to produce the desired ouput shown in the excel sheet.
Knowing that this might involve a little magic, are happy to add clarify wherever possible. Much thanks in advance,