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?
Qui dolor consequatur sed in. Reprehenderit velit quia rem esse. Aut accusamus illo ipsa ut.
Quisquam et et distinctio ut earum. Est doloremque id nemo facilis. Distinctio eum neque qui dignissimos eligendi ut. Cumque accusantium qui numquam omnis. Aut nesciunt nulla quaerat nam qui maiores natus impedit. Nesciunt consequatur officiis in voluptatum.
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...