Defining Forecasts

Now that you have defined the assumption cells in the model, you are ready to define the forecast cells. Forecast cells contain formulas that refer to one or more assumption cells.

The president of Vision Research would like to know both the likelihood of achieving a profit on the product and the most likely profit, regardless of cost. These forecasts are displayed in the gross profit (cell C21) and net profit (cell C23) for the ClearView project.

You can define both the gross profit and net profit formulas as forecast cells, described in the following sections:

Gross Profit Forecast

  First, look at the contents of the cell for gross profit:

  1. Click cell C21.

    The cell contents are displayed in the formula bar near the top of the worksheet. The contents are C16*C19*C20. Crystal Ball uses this formula to calculate gross profit by multiplying Persons With Nearsightedness After One Year (C16) by Market Penetration (C19) by Profit Per Customer (C20).

    Now that you understand the gross profit formula, you are ready to define the forecast cell for gross profit.

    To define this forecast cell:

  2. Select Define, and then Define Forecast. Define Forecast button

    The Define Forecast dialog opens as shown in Figure 128, Define Forecast Dialog—Gross Profit If Approved. You can enter a name for the forecast. By default, the forecast cell label is displayed as the forecast name.

    Figure 128. Define Forecast Dialog—Gross Profit If Approved

    The Define Forecast dialog displays the forecast name that must be used.

    Use the forecast name that is displayed, rather than typing a new name.

  3. Since the spreadsheet model involves millions of dollars, type Millions in the Units text box.

  4. Click OK to return to the worksheet.

Net Profit Forecast

  Before defining the forecast cell formula for net profit, look at the contents of the cell for net profit:

  1. Click cell C23.

    The contents are displayed in the Microsoft Excel formula bar. The contents are IF(C11,C21-C7,-C4-C5).

    The formula translates as follows:

    If the FDA approves the drug (C11 is true), then calculate net profit by subtracting total costs (C7) from gross profit (C21). However, if the FDA does not approve the drug, (C11 is false), then calculate net profit by deducting both development costs (C4) and testing costs (C5) incurred to date.

    To define the forecast cell for net profit:

  2. Select Define, and then Define Forecast. Define Forecast button

    The Define Forecast dialog opens.

    Again, use the forecast name that is displayed in the Forecast Name text box and specify Millions in the Units text box.

  3. Click OK to return to the worksheet.

You have defined assumptions and forecast cells for the Vision Research spreadsheet, and are now ready to run a simulation.