August 4, 2009
Making Decisions and Looking Up
ne reason Excel is so useful is that it can do an enormous number of complex calculations accurately and fast. But if it could do only straightforward calculations, then it would not be half as useful as it is. A signiﬁcant part of its usefulness or power comes from the fact that in Excel you can write formulas to make various kinds of decisions equally quickly and accurately.
What do we mean by making decisions? Making decisions means choosing which path or action to take out of several possibilities depending on the results of one or more conditions or tests. If you think about it you will realize that this is very similar to what we mean by making decisions and how we make decisions in real life as well, even though we generally do not explicitly think about what conditions or tests we are considering to decide what action we would take.
In ﬁnancial models, you will often need to use Excel’s decision-making abilities. Although most Excel users know how to set up simple decision making in Excel, they cannot create complex decision structures, and they think that the problem is that they do not know Excel well enough or do not know all the necessary features. But most of the time the real problem is that to set up a decision-making structure you have to ﬁrst lay out in clear and precise steps exactly how a decision is to be made. Since we do not make decisions in such clear and precise steps, it takes some conscious practice to learn and document the steps of a decision. Once you have thought through and documented exactly how a decision is to be made, translating it into Excel formulas is not that difﬁcult. In this chapter, I will introduce Excel’s decision-making tools. But more important, by using several examples I will show you how can break down a decision into steps and then translate it into Excel’s language. Learning to create decision structures is very important for ﬁnancial modeling. You should devote the necessary time and effort to learn it well and then practice writing decision making formulas at every opportunity you get.
P1: OTA/XYZ c05 P2: ABC
August 4, 2009
EXCEL FOR FINANCIAL ANALYSIS AND MODELING
I will also cover two other groups of Excel’s features that act very much like decision making. The ﬁrst is looking up the value for something from a list or table depending on certain speciﬁed conditions or tests. This is a very powerful use of Excel. The second is formatting one or more cells differently depending on their content. This capability, called conditional formatting, is very useful to make your data or model outputs more clear and attractive. The conditional formatting feature has been signiﬁcantly enhanced in Excel 2007. You will be able to use conditional formatting with its full power only if you are using Excel 2007.
Excel includes two logical values, TRUE and FALSE, which you can use in formulas or which can be the results of certain computations. (How and why you use them will become clear a little later.) For Excel to recognize these as logical values and not confuse them with text, you have to write them in your formulas in all capitals, as shown, and without the quotation marks normally used for texts.
Excel also provides two functions, TRUE() and FALSE()—they do not take any arguments—which return the values TRUE and FALSE. These functions are provided only for compatibility with other spreadsheet programs. You will not need to use them because entering TRUE or FALSE anywhere is the same as entering the equivalent function.
Excel offers several logical functions that you can use to give your formulas simple to fairly complex decision-making abilities. The IF function is the workhorse of