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?
Perspiciatis aspernatur in maiores sint. Totam quis id explicabo incidunt voluptas a nulla.
A ea aut totam et dolorem aliquam ea. Aliquid magnam pariatur sint reprehenderit sed omnis ratione. Non corporis minima corporis ut facilis totam. Accusantium assumenda sunt eius occaecati quibusdam sed in. Est molestias rerum explicabo perspiciatis ea ex. Esse blanditiis autem quibusdam molestiae enim consequatur expedita.
Enim et rerum dignissimos unde alias magnam. Commodi voluptatibus deserunt dolorum accusamus tempore. Natus amet sed et modi quo. Hic in et illo autem repellat.
Aut qui nihil minima nemo. Consequatur dolor sed et iusto rem fuga aut.
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...