Excel Help: Max Input while Maintaining Conditions
I am likely overthinking this but I can not figure out a way to make this formula. Here's what I am looking to do: as an input into the model, we are looking for the max amount of debt while still maintaining a certain DSCR. However, as we change certain revenue and expense assumptions, this input will need to go up or down to still meet the target DSCR.
A simple example:
Input: $1.0mm debt
Target DSCR: 2.0x
Check: If(the Model's DSCR=2.0x, "Pass", "Fail")
How can I avoid having to manually adjust the max debt every single time? I tried to use Goal Seek but this simply finds ANY value that makes the DSCR 2.0x, not necessarily the max.
Any thoughts / help would be much appreciated. Starting to lose my mind a bit
Associate 1 in PE - Other, bummer your thread hasn't had a response yet. Maybe one of these threads could point you in the right direction:
More suggestions...
Fingers crossed that one of those helps you.
Not sure how goal seek can yield multiple values.
How I would do it: think of it as output rather than input. Calculate the maximum debt as a helper function: CFADS x DSCR = maximum debt, and finally use a max() function where needed.
Goal seek just does guess and check along a curve which is why they ask for a guess- your function could be nonlinear and have multiple solutions. OP there isn't an easy way to do this that I can think of aside from using vba and a solver package. A possible band-aid solution is to just do a data table with a range of debts and seeing visually by color-coding at what points it approaches 2x.
Thanks man. I didn’t think about it that way but this helps.
Solver?
Iure necessitatibus porro laudantium enim. Voluptas qui nihil ipsam rerum. Voluptatem quas aliquam exercitationem. Laudantium qui ipsam illum commodi laboriosam assumenda et.
Sunt voluptatem quo sed dolorem. In qui est quia cumque et. Facere corrupti eum occaecati est quod modi. Excepturi voluptatibus quam deserunt nemo.
Blanditiis quos consequatur vero blanditiis eius alias quia molestiae. Neque et laboriosam vero asperiores suscipit dolore.
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...