Introduction

The goal seek function is a very important tool in excel (it was also there in lotus). In large models the last thing you often do is to use a goal seek to make a bid on the model. I have wanted to make a goal seek function where you can make something like = SUM(…), but with =GOAL_SEEK(…). When you make a function, it can operate much faster than the classic method in excel and it can be used together with resolving a circular reference to make things speed up dramatically. I had tried this before AI but it was necessary to re-write the formulas in excel that go from the input that drives the goal seek (for example, the price), all the way to the end (for example, NPV). This was a complete pain before AI because you would have to write the formulas on a manual basis (for example, Revenues(period) = Price x Quantity(Period) and all of the other formulas down to NPV).

Bisectional Method and Newton Method

.

.

.

.

File without Macros and Old Method for Goal Seek

The two files below are designed to allow you to begin the process without any functions. The file has some inputs for price, operating costs, capital expenditues and financing. We have made this relatively simple with a single construction period and no taxes so there are no circular references. There is debt with either sizing from debt to capital or DSCR to make the goal seek process a little more complicated. Finally equity cash flow is computed and measured at a target equity IRR. A file where the equity IRR is computed from the price and different scenarios are computed is attached to the button below. If you want to practice all of the excercises in the videos, you can use this file. Other files illustrate subsequent steps in the process including translating the model equations to VBA using AI and adjusting the VBA with templates for using goal seek functions.

.

Goal Seek Macro from Bi-Sectional and Newton

.

.