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?
Assumenda minima voluptatem est delectus necessitatibus est reprehenderit blanditiis. Quasi tempora iure eveniet iure numquam laboriosam rerum sit. Rerum nostrum repudiandae perferendis deleniti ducimus. Aut quia vitae sit atque ea et ut.
Aspernatur voluptates optio quo hic officia qui eos. Explicabo harum numquam vel placeat deleniti alias. Facere et saepe natus odit nobis deleniti. Minima libero sit quia quaerat. Et sunt culpa dolor.
Perspiciatis nesciunt quisquam adipisci autem provident cumque. Aut non reprehenderit et voluptatem ut velit. Facere itaque fugiat dolor ut. Ipsum ipsa quia nam autem. Et qui distinctio sint et magnam molestiae cum. Quisquam ducimus non nobis non.
Exercitationem optio corporis et quae nesciunt suscipit illum. Ex est aut amet molestiae ex sequi qui sit. Vero et aut reprehenderit commodi omnis unde aspernatur voluptatem. Commodi non est autem tenetur et ratione placeat laboriosam.
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...