Seeking a Better Goal Seek

Goal seek in the context of financial modeling is a simple analysis to answer a question of the form.

What value should X be to make Y equal to V?

Both X and Y are variables of the financial model and V is some constant. Break even analysis is a simple example of a goal seek problem and has the form:

What value should X be to make the net income equal to zero?

Believe it or not, Goal Seek can be realized as the solution to a circular reference. But don't try this in Excel; it doesn't work because of Excel's flawed iterative calculations.

X can be a variety of things such as number of units sold or number of days into the fiscal year. To illustrate, consider the following simple model of a business that purchases and resells chairs. The RevFormula and VCFormula calculate revenue and the variable costs given the number of units sold. These formulas might be as simple as the product of units sold and price per unit, or they may be more complex taking into account discounts for bulk orders.

Revenue = RevFormula(UnitsSold)
VariableCosts = VCFormula(UnitsSold)
NetIncome = Revenue - VariableCosts - FixedCosts

UnitsSold 8
Revenue 3,848
Variable Costs 2,792
Fixed Costs 1,000
Net Income 56

Although Excel and other spreadsheets provide goal seek in various forms, they often have flaws. One flaw, as in Excel, is that goal seek cannot easily be part of the model. There are many posts that seek help for ways to "automate" goal seek. Responses to these posts are often confusing, but there seems to be a general consensus that using VBA will allow goal seek to be recalculated every time.

Other posts suggest that goal seek can be solved using iterative calculations without resorting to VBA. To do this is relatively simple. Create a formula for UnitsSold as a circular reference that includes NetIncome as follows:

UnitsSold = UnitsSold + NetIncome

At first glance, this may look like magic. However, the only solution to this equation is the number of units sold that results in zero net income. And it works for most iterative methods except the fixed point method. If you were to put the above formulas into Excel it would not find a solution because Excel only uses the fixed point method. With Whitebirch models, if you choose any method other than the fixed point method, a solution is found. Because the fixed point method fails to work in these kinds of cases, using circular references to solve goal seek in Excel is not an option.

Goal seeks are solved in Whitebirch models by using iterative calculation, and the example above produces the following result:

UnitsSold 7.58
Revenue 3,644
Variable Costs 2,644
Fixed Costs 1,000
Net Income 0

The break even point is 7.58 units. As a theoretical value this makes sense, but if a fraction of a chair can't be sold, then you might be tempted to round this value. But if you simply added rounding to the units sold formula, then goal seek will not find a solution because there is no integral number of units sold that produces a net income of zero. The precision of the solution can be relaxed to allow values close to zero, but it may in general be unclear what precision should be specified. To solve this problem, Whitebirch allows the option of specifying the closest rounded value to the solution as an alternative to specifying a precision. This makes it very easy to adapt goal seek to creating an entire scenario based on practical values for units sold. Whitebirch also allows other options such as upper and lower bounds so that you can say you don't want to consider selling a negative number of units.