Scenario Analysis

[Dwight]

Once again I’ve bumped up against my intellectual ceiling. If someone could give me a boost I’d be grateful. Here’s the situation:

I’m tracking portfolios and need a way to do scenario analysis. The company buys units in large blocks and then allocates them to individual portfolios based on need. There’s a transaction table and a price table. I manipulate these to get the current weight of each position relative to the overall portfolio value. This is displayed in a datasheet form as follows:

Portfolio ID Beginning Position Weight
Example1 100 5%
Example2 200 7%

If the company needs to allocate 5,000 units it would be good if they could begin breaking up the block by distributing them among the portfolios while simultaneously seeing the impact.

Could I somehow add a “New Position” and “New Weight” field that would react to user inputs as well as draw down the block’s size? The user could add to the New Position and see the New Weight field react.

I don’t know where to store the New Position values because there is no associated table. I thought maybe a Form/Subform would work but I am lost.

Obviously, this would be easy in Excel but all my data is in Access so I would like to do some one-stop shopping. Please just point me in the right direction. I’ll do the legwork.

Cheers,

Dwight

 

[Fizzio]

I'd like to help so forgive my ignorance but I cannot quite get my head around what you are trying to do can you explain in kiddies language

 

[Dwight]

I will try my best. Thanks for the interest. Kiddie language for a Brit:

Let’s say Portfolio 1 is worth 15,000 GBP in total. It owns 500 Harry Potter shares worth 2 GBP each. This makes the Harry worth 6.67% of the overall, i.e. (500*2)/15,000

Portfolio 2 is worth 20,000 GBP and also has 500 Harry Potter shares giving it a 5% weight (500*2/)/20,000.

This is historical data and I can calculate this all this. But here is where I’m stuck:

The manager now wants each portfolio to have about 10% of the overall portfolio in Harry Potter. They have a big block (maybe 10,000 new units) to spread out over all the portfolios. So I am looking for a way that they could play with the number of shares and see the effects.

For example, if they added 100 to Portfolio 1 (already has 500) the new weight would be (600*2)/15,000 or 8%. That is not enough so they would add some more. But sometimes the block they have is not big enough to get all the accounts up to 10%. Thus they would have to dole out some shares to Portfolio 2 because its level is way down at 5%.

It’s like hungry kids at a birthday party. You don’t have enough cake for all of them so you kind of spread it around as best as possible with what you have. You give the fat kids a little more to shut ‘em up and you skimp on the skinny girls.

My form is in datasheet view so it looks like a spreadsheet. It has current position, price, portfolio value, and position weight. I would like another column where the user could add the 100 shares to Portfolio 1 and see the weight go up to 8%. (it would also be good if they could see the 10,000 block go to 9,900). Then maybe later they could come back and add more shares if they were available after all the portfolios got tended to. They may have to subtract some of the new shares if they were too aggressive at first as well. They would play with all the portfolios until the 10,000 was down to zero and the accounts were all at 10% or as close as possible.

I don’t know if this is any clearer. I’m certainly confused. If you like I will put together a sample database. I try to ask the board my really hard problems and solve the ones I know I can get (no matter the time) on my own so if you could help me here I would really appreciate it.

 

[Fizzio]

I'm starting to follow a little bit (I'ts not just being a Brit - just being a Geordie - ask any of the other Brits, they'll agree!)

The biggest problem is how to divvy out the shares, especially as not everyone will be getting the same.

The two ways that immediately spring to mind.
One is to create a complex algorithm to do it for you but I have misplaced my PhD certificate in Advanced Mathematics so I'm no help there!
The second option, I agree, is a form subform arrangement. How you do this will depend on if all portfolios can have a few Harry Potter shares or only those who currently have them.

I would filter all the portfolios you need to 'tinker' with and create a form's recordset with them. The calculation to calculate the weighting is fairly simple and so the form controls could be as follows

Header:
ShareBlockName;BlockTotalToDivvyOut

Detail:
PortfolioName; CurrentValue; CurrentNoOfSharesFromBlock; CurrentWeighting; AllocatedShares (The value you enter) ; ProspectedWeighting (calculated as you currently do)

Footer:
BlockSharesLeft. (BlockTotalToDivvyOut - Sum(AllocatedShares))

You can easily monitor the running total by simple BeforeUpdate code. (or even validation rules on the running total control)

This will give you your prospected values and you would then probably need to use VBA to update the records.

any help?

 

[Dwight]

That is loads of help. Like I said I just want to be pointed in the right direction. Let me chew on this for awhile.

I gotta learn that VB. Seems to come up a lot as the answer to my problems.

Thanks again,