Financial Analysis and Modeling Using Excel and VBA

Chapter 8. Finding Iterative Solutions

For many financial problems, it is often convenient or necessary to find a solution by trial and error. What docs that mean? For example, we may want to know what sales growth rate will be necessary for a company to earn a certain earnings per share (EPS) in three years. Once we have built a model for the company with sales growth rate as one of its independent variables, we can change it in steps until the target EPS is achieved. (This assumes holding all other independent variables of the model constant.) Instead of looking for just the sales growth rate that will be necessary, we may also want to know what combination of certain independent variables, constrained in certain ways, could achieve the same target. Once again, we can find the answer by systematically varying the independent variables, that is, by trial and error. But this time it may take a lot of work.

The trial and error method is also called the iteration method, and there are many mathematical techniques (actually a whole branch of mathematics) to find iterative solutions to problems efficiently. Excel provides two tools—Goal Seek and Solver—for finding such iterative solutions. In addition, in certain circumstances you can find iterative solutions using a third Excel feature called circular reference.

In this chapter I will cover these three Excel features. Circular reference is the feature you are likely to use most often. Goal Seek is a handy tool, but not very powerful. ...