Developing the Worksheet

You should build your spreadsheet model using principles of good design, since this makes understanding and modifying it easier.

The spreadsheet should include:

A descriptive title.
An input data area separate from the output and any working space. Place all input variables in their own cells where you can later define them as assumptions or decision variables.
A working space for all complex calculations, formulas, and data tables.
A separate output section that provides the model results.

Examine the Portfolio Allocation spreadsheet model (Figure 19, Portfolio Allocation worksheet) for an example.

Note that all assumptions are in rows 5 through 8. Rows 13 through 16 are reserved for decision variables, created by users during the OptQuest tutorials. Forecast cells reference these input variable cells in their calculations, not values directly. Therefore, you could easily change any values, and the forecast calculations would be automatically updated.

Other tips that improve the usefulness of your spreadsheet are:

Reference input data only with cell references or range names so that any changes are automatically reflected throughout the worksheet.
Use formats, such as currency or comma formats, appropriately.
Divide complex calculations into several cells to minimize the chance for error and enhance understanding.
Place comments next to formula cells for explanation, if needed.
Consult a reference such as those listed in References and Bibliography for further discussion of good spreadsheet design.