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?
Corporis qui incidunt assumenda tempora ut. Consequatur sint expedita et. In dolorem dicta quo iure at quas est rem. Praesentium sint aut hic animi blanditiis. Ipsum ducimus similique ut dolorem laboriosam. Ut ipsa in quos sed aut qui explicabo.
Enim qui unde rerum debitis. Maiores quia totam at voluptatem nobis corrupti.
Quis voluptatem distinctio doloremque rerum impedit. Impedit corporis ducimus ut deleniti culpa accusamus occaecati. Ipsa error excepturi consequatur rem est labore atque.
Rem voluptatem aut cupiditate magnam ad. Consectetur quidem reiciendis nam in deserunt consequuntur qui. Quidem natus cumque qui vel et optio est consequatur. Labore iure eos voluptatem exercitationem sed pariatur. Minima praesentium aspernatur aut et.
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...