Hi all,
I'm looking to make an equity waterfall model that I've built be able to react dynamically to changes in the order/percentage of funding. The model has common & pref equity in addition to a construction loan. Ultimately I want to be able to set the order in which funding occurs and have the model dynamically change based on that information. I'd also like to allow for the possibility of splitting the funding and having a certain % of each source fund at the same time (ex. common equity and construction loan are both the first source with a 50-50% split). I'm running into some issues with circular referencing - any ideas on how to best get around that?
Set up Options (i.e. 1, 2, 3, 4, etc.. to be the different scenarios you are referring to). Then you can create a List on your dashboard to select the option you want to evaluate from your dropdown. You could use the choose function within your various calc cells to do X if option 1 is selected vs. Y if option 2 is selected...probably easier to create one list of options for your equity funding and another for your debt funding. Do this help at all? Another way I've seen stuff done is through a scenario table (where your inputs are manipulated via the table, and then you index match the table to calculate the cells in your funding order with equity waterfall to follow).
I've been looking to build out the model with a Choose function as you mentioned, which has been working for the most part. The problem that I'm having occurs mainly when two sources are being deployed at the same time. This is how my formula is structured right now for if Source 1 Funding is in position 1 (deployed first):
Source 1 Funding = MIN(-(SUM(Construction Period Expenses)+Source 2 Funding(Source 2 Position =1)+Source 3 Funding(Source 3 Position =1)),Total Potential Source 1 Funding -SUM(Previous Source 1 Funding))*Source 1 % Split
The issue that I'm running into is that, for example, when Source 2 Funding is also in position one, there is a circular reference created so that Source 1 Funding is trying to subtract Source 2 Funding from the total expenses to determine the remaining amount to deploy, and vice versa for Source 2 Funding. This is causing it to throw off some wonky numbers that don't make sense or represent the actual split I'm trying to find (for simplicity say 50-50). Any clue how to work around this?
Consequatur eos aliquam explicabo dolor. Sit molestiae quaerat cumque velit repudiandae praesentium. Ab voluptas quidem rem officia sunt voluptatibus similique. Animi aut quod dolorum cum adipisci in. Ad voluptas repellendus eum quos deserunt ipsum vitae officiis.
Aut eligendi impedit ducimus deleniti. Rerum consequuntur et qui neque facere aut ea.
Voluptates omnis non omnis officiis dolores optio inventore consectetur. Est eum est deserunt sint possimus.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...