Financial Modeling with Crystal Ball and Excel, + Website, 2nd Edition

CHAPTER 8

Modeling Financial Statements

Perhaps the most widely used financial models are the pro forma income statement and balance sheet. Most companies use deterministic versions of these models for planning, and they are a natural place to start when constructing a Crystal Ball model. Especially when decision makers are first exposed to risk analysis with Crystal Ball, it is best to make your stochastic models resemble as much as possible the deterministic models to which decision makers in your company are already accustomed.

In this chapter, we start with deterministic pro forma statements from Chapter 6 of Sengupta (2004), which is an excellent source for more information on constructing deterministic financial models in Excel. Thus we will focus on using Crystal Ball with an existing deterministic model, just as you might do on the job. We walk through use of the basic tools to get you started. As you gain experience, you might be tempted to make your models far more complex than those presented here. However, do not add complexity just for its own sake. It is far better to start with a simple model, and add complexity only if necessary to help make a sound decision.

8.1 DETERMINISTIC MODEL

Figure 8.1 shows the historical income statement for 1999 to 2002, and Figure 8.2 shows the balance sheets for 1999 to 2002 for the Vitex Corp. example from Chapter 6 of Sengupta (2004). Our job is to project these measures forward using historical data and input from management about ...