Then re-do loop with new start, finish and incrementįor changing_cell = start To finish Step incrementĪnswer = changing_cell + second_value ' this is calculation to get goal seek. So here are the steps:įunction basic_example(target, second_value) As Variant The objective of this exercise and the subsequent subjects is to try and make the process easier. This all sounds simple, but it can be a pain as I try to explain below. When the target value is reached, you stop the loop. When making this loop, you work around the loop with a step increment. The basic idea of a dynamic goal seek is to make a FOR and NEXT loop where the loop changes the changing cell value. the DSCR in project finance or the P/E ratio in cost of capital analysis) and you define the cell that changes to reach the target (in excel this must be an input).
Introduction to Dynamic Goal SeekĪs with any goal seek function, you input a target amount for a particular cell that has a formula (e.g. In addition, the when you are backing out the cost of capital and the terminal value is a function of the cost of capital itself this technique can be very useful. For corporate finance, the dynamic goal seek could be useful in deriving the equity capital injection to reach a target capital structure so that you do not have to run the solver. If you later want to operate another goal seek that derives prices or capital expenditures this can be very useful. A dynamic goal seek can be useful in project finance models when you have a an equal installment or a level repayment. The similarity is that you need to re-program equations in excel and that you must do this with a user-defined function that has so kind of iteration loop.
The process of making a dynamic goal seek is similar to resolving a circular reference with a user-defined function. But sometimes you may want to have the goal seek work on a dynamic basis, meaning that you do not want to go to the data menu and then hit the what if analysis and enter the target cell etc. A dynamic goal seek function is where you make a function that automatically changes to an output variable that comes from a goal seek when you change one of the inputs. The goal seek is a wonderful tool in excel.
0 kommentar(er)
