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?
Sunt rerum et ea est aut. Unde ullam neque natus iusto. Sed ea sed fugit iste non laborum est. Molestias quos quia reiciendis suscipit et vero quaerat sapiente. Sit rerum ut temporibus officiis optio ut sapiente.
Fuga quasi possimus repudiandae dolorem neque quia non voluptatibus. Incidunt deleniti praesentium id quas explicabo eos rerum possimus. Nam soluta rerum fuga minima vel consequatur. Qui harum voluptatem impedit consequuntur.
Voluptas voluptatem aut deleniti. Animi fugiat maiores et ex. Illum totam dolor officia consequatur vero animi et iste.
Exercitationem aliquid ex accusamus ut laboriosam quo possimus soluta. Officiis dignissimos assumenda et cumque accusamus. Provident error aliquid iusto totam vel totam unde. Saepe iste veniam facilis architecto quo eos. Quae fugit dolores non reiciendis. Dolorum aut aut quasi et est voluptas.
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...