Financial Analysis: A Controller’s Guide, Second Edition

Chapter 17. “What If” Analysis with an Electronic Spreadsheet

A common question posed to the controller begins with “what if ?” It may be “what if the interest rate on our adjustable-rate bonds increases—what will we pay?” or “what if both the interest rate and payment dates change on our bonds—how will that impact us? The controller typically models an answer based on a few discrete points in the equation, and does so by manually coding a formula with an electronic spreadsheet. This approach yields a few possible answers from among a broad range of all answers, is subject to error, and takes a considerable amount of time to create. Fortunately, some electronic spreadsheet tools can improve upon this task. In this chapter, we will give examples of how to handle “what if” analysis with both single and multiple variables, using Excel as the example spreadsheet.

“What If” Analysis with Single Variables

When the controller must determine the answer to a formula where one element varies, the simplistic approach is to create a table containing all possible expected values of the variable, and calculate the answer for each one. Though workable, this approach is slow. Instead, consider using the table fill function of Excel to more rapidly develop an answer. An example is shown in Exhibits 17.1 through 17.3. In Exhibit 17.1, we have set up a formula to determine the present value of a series of payments of $1,500 per payment, extending over eight periods. However, the interest rate could ...