Keep sheet names short — fewer than ten characters if you can. There are two reasons for this. First, the sheet tab region at the bottom of the screen can display only a few tabs at a time. If the sheet names are short, then you can see more sheets without scrolling. This makes your model easier to use. Second, if you use local parameter names, then on sheets other than the home sheet of the parameter name, the sheet name appears in any formulas that use the parameter name. Long sheet names make such formulas more difficult to read, which, in turn, makes the model more difficult to maintain. Thus, long sheet names are a liability both for ease of use and for maintenance.

When local names appear in formulas, the workbook and sheet name prefixes must be wrapped in single quotes if the sheet name starts with a number, or if the workbook or sheet name contains any character other than numbers, underscore, period or letters, or if the sheet name is the same as any legal cell reference. These single quotes are annoying to type and to look at. To avoid their appearance, restrict sheet and book names to use only numbers, letters, periods and underscores, and start every sheet name with something other than a number. Avoid names that coincide with legal cell references.

Most models provide their users some freedom to influence their behavior, usually by supplying data. For example, your model might be designed to explore cash flow as a function of the timing of certain product introductions. The timing might be user-supplied data.

Wherever possible, segregate user-supplied data. Put all the cells that users can modify on one worksheet. Give that worksheet a suggestive name, such as "Input." Position this sheet near the front of the workbook, where it's easy to find. And make certain that there are no other parameters on that worksheet. This structure makes it easier for your users to find the cells and ranges that they have permission to change. At the same time, it protects the rest of your model from dangerous fiddling by naïve users.

Use color to provide visual cues to the reader of your models. For example, in the homework and demonstrations for this course, cells that have special significance are tinted. Yellow means data — homework problem parameters, rather than formulas. Green means definitions of names. Blue cells show formulas for cells in the indicated columns of the same row. Gray is background. White cells, for the most part, are formulas or data that the user is not to disturb.

If you do use color, select pastels. Darker colors, such as red, obscure the contents of the cell when you print the worksheet on a black-and-white printer. This effect is especially problematic when you try to photocopy the printed pages. You can get around the problem by choosing white for the text color, but that's extra work (unless you define a style).

It's also likely that your model contains some streams or cells that you consider to be "results" or "output." Wherever possible, segregate these ranges on a single worksheet with a suggestive name, such as "Output." This makes it easier for users to find the results. Position this sheet near the front of the workbook, where it's easy to find.

It's possible that your model logically produces results on several different worksheets in the workbook. If so, construct a separate summary worksheet (called "Output," for example) that simply links to the places where the results are originally produced, and presents them in a convenient, compact way.

Simple, effective charts are an excellent way to present results to your users. In very little space, they can present complex relationships and trends far more effectively than a table of numbers can. Use them sparingly, but use them when they're appropriate. Embedded charts are preferable, because your users can see both data and chart on the same sheet.

Many models contain multiple categories of analogous structures. For example, if you're modeling an apparel company, you might have product lines for men's, women's, boy's and girls' clothing. These product lines might have similar structures, such as revenue, expenses, cost of goods, and so on.

When you construct names for these structures, you might be tempted to create names such as MensRevenue, WomensRevenue, BoysRevenue, GirlsRevenue. This approach is almost always a mistake. It's better to create a worksheet for each of the four categories, and place analogous structures on each sheet. Then you can create local names for each structure on its corresponding sheet: Mens!Revenue, Womens!Revenue, Boys!Revenue, Girls!Revenue.

One of the many advantages of this approach is that analogous derived structures on the four sheets will then have identical formulas. For example, the formula for NetIncome would be

This formula would then be identical on all four sheets. Such parallel structures are cheaper to maintain because they're easier to understand.

Placing related data on successive lines on the worksheet has advantages, especially if we name it cleverly. Excel has a number of capabilities that work well for contiguous ranges, but not at all (or incorrectly) for discontiguous (sometimes called compound or multiple) ranges. For example, even the most basic operation of copy and paste is unavailable for two rows separated by a third. To copy and paste this structure, you have to either do two copies and pastes, or copy and paste all three rows. Keeping related data in contiguous ranges gives you maximum access to Excel's capabilities.

Usually, your models predict the time evolution of business parameters in response to some initial conditions and assumptions. You always have the choice of setting things up so that increasing time moves in any one of four directions: up, down, left or right. Most people choose either down or right. Choose right. Excel has a variety of tools for maintaining spreadsheets, and they tend to work better if you set things up that way. Moreover, "increasing time to the right" is the conventional way to set things up, so you'll confuse people if you do it any other way.

A commonly used design for financial models entails interspersing in the same row of data both the annual sum of the four quarters that contribute to that sum. When this is done, a typical row might schematically look like this:

Although this might be useful as a presentation structure, it's extraordinarily difficult to maintain as a working structure, because the commands Edit>Fill (Excel 2003 and 2004), or Home>Fill>Right (Excel 2007) are fairly useless. If you must present results in this form, create the results from behind-the-scenes structures that are quarterly-oriented and annually-oriented.

"Rolling up" denotes the process of summing multiple contributions from different sources. For example, to compute the sales results for a company that divides its territory into regions, you would sum the sales results of each of the regions. So if you had streams of quarterly sales numbers, one for each region, the total sales would be the sum of the streams — you would "roll up" the streams.

But what happens if you have sales figures for several product lines? Let's say that you have three product lines and four regions. Total sales is thus the sum of 12 streams. If you're interested in total sales by region as well, you would compute that number for each region by summing over the product lines for that region. If you're interested in total sales by product line, you would sum over the four regions for each product line. As you can see, things can get complex.

One easy way to deal with this is to treat each region as its own company. Group the three product lines for that region, and compute a sales total stream for that region. Then compute the company total by forming a block that is the sum of all four of the regional blocks.

In this approach you avoid having to add individual streams together.