Financial Analysis and Modeling Using Excel and VBA

Chapter 7. Answering What-If Questions

We create models primarily to project values of output (dependent) variables in order to make decisions and rake actions. Such projections are never perfect, in part because we can never specify the necessary input (independent) variables exactly; those are almost always estimates or assumptions. So we are rarely interested in the values of the dependent variables for just one set of independent variables. More often we want to know how the output values will change if certain input values turn out to be different or we choose to make them different (for example, we decide to make only half as much investment in new plant and equipment). In other words, a key reason we build financial models is to be able to answer what-if questions.

Excel includes two useful tools to answer the two types of what-if questions we generally ask. In the first type, we are interested in looking at the projected values of one or more dependent variables for a series of values of one or two independent variables. Excel's data table feature is designed to answer this type of question. In the second type, we want to see the values of a selected group of dependent variables for a set of values for a selected group of independent variables. A specific set of values for a selected group of independent variables is called a scenario. The Excel tool to answer what-if questions about scenarios is called the Scenario Manager.

Excel includes in its what-if group a third tool ...