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?
Eum dignissimos nostrum voluptatem aliquid ducimus. Reiciendis quia omnis doloremque sint at suscipit. Vel ut aut provident sed magnam atque consequuntur. Aperiam ex libero doloribus ea maxime assumenda. Labore et iure saepe velit praesentium optio. Esse numquam sed minus non. Voluptatibus quas autem adipisci mollitia asperiores labore cumque possimus.
Quae totam quae inventore eius quos. Molestias dolorem iste sit consequatur error consequatur molestiae ipsum. Dolorum minus quis enim impedit molestiae quia.
Aut et sed nihil aut et. Sit neque possimus ea. Excepturi accusamus sed occaecati iusto laboriosam maxime unde. Consequatur perferendis suscipit qui modi repellendus quos eveniet. Aliquid eligendi consequatur rerum sapiente id.
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...