Spreadsheet Models for Managers

This reading is especially relevant for Session 5Structuring Excel Models

When you build a complex model, or even a simple model, you have to make design choices that affect two important properties of the model — its usability and its maintainability.

Usability, sometimes called “ease of use,” and Maintainability are often in direct conflict with each other. Consequently, even if you realize that models have these properties, it can be difficult to make design choices that produce models that are both easy to use and cheap to maintain. This page contains some guidelines and insights that help you make wise design choices. You might want to keep them in mind when you construct your course project.

• Use short sheet names
• Avoid workbook or sheet names that require single-quotes in references
• Segregate user-supplied data
• Use color
• Segregate results
• Use charts for presenting results
• Use identical local names in similar sheets
• Place related data in contiguous ranges
• Arrange time horizontally
• Make rows uniform
• Make worksheets uniform
• When rolling up multiple rows, roll up groups

Use short sheet names

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 each Excel workbook’s window can display only a few tabs at a time. If the sheet names are short, then you can see more sheets without scrolling the sheet tabs. 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.

Avoid workbook names or sheet names that require single-quotes in references

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 them, restrict sheet names and workbook 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.

Segregate user-supplied data

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 well-meaning but naïve users.

Use color

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. In our course materials, yellow means data rather than formulas — homework problem input parameters, or homework problem input streams, for example. 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 black-and-white printers. 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).

Segregate results

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 helps users 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.

Use charts for presenting results

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.

Use identical local names in similar sheets

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, boys’ 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.

Place related data in contiguous ranges

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 must 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.

Arrange time horizontally

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.

Make rows uniform

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

Q1

Q2

Q3

Q4

Y1

Q1

Q2

Q3

Q4

Y2

Although this might be useful as a presentation structure, it’s extraordinarily difficult to maintain as a working structure. The non-uniformity of the cells in the range makes it inconvenient to use the fill commands Edit>Fill>Right or Edit>Fill>Left (Excel 2011); Home>Edit>Fill>Right or Home>Edit>Fill>Left (Excel 2011); and Home>Editing>Fill>Right or Home>Editing>Fill>Left (Excel 2007, 2010, and 2013). And the non-uniformity also renders audit tools useless, because audit tools look for irregularities — inhomogeneities in row structures.

Make worksheets uniform

Typically, models of businesses or organizations include models of parts that are very similar. For instance, the departments of an organization have similar models, despite having very different functions. They all have employees, equipment, hiring schedules, and so on. Their cost profiles might differ greatly, but these differences can often be captured in the values of parameters or other inputs and input streams.

We can dramatically simplify the task of modeling a set of departments if we can design a worksheet that serves the needs of every department, and then make one copy of that worksheet for each department. Later, when we want to amend that worksheet we can group all similar worksheets, and with a single operation, edit all of them at once. (Try it!)

But there’s a problem with this approach.

We’d like to group all inputs on a single worksheet (or a few worksheets). When we do that, the names of similar parameters for different departments must be distinct. For instance, suppose we have an initial headcount for each department. If we define it on each department worksheet, then modifying initial headcounts requires chasing through all the department worksheets to locate the initial headcount and edit it. Ugh. Much better to put all the initial headcounts in a single place on an input worksheet. But if we do, we must define names like HRInitHdct, EngInitHdct, MktgInitHdct, and so on. And if we do that, then the formulas on each department worksheet have to be different, because they refer to parameters with different names. What to do?

There is a simple solution that enables us to collect all the input parameters on a single input worksheet, and yet still keep the structures of similar worksheets similar, if not identical.

The key is to define a parameter — call it InitHdct — on the department worksheet. Then let its formula link to the correct initial headcount parameter for that department. If we do this, then formulas on the department worksheets that use initial headcount are now all identical, because they can refer to InitHdct. The uniqueness of each department has been segregated to a single cell, InitHdct.

This simple example hardly justifies the technique. But if you have numerous parameters and numerous departments, this method can dramatically simplify the maintenance and design tasks.

When rolling up multiple rows, roll up groups

“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’s the sum of all four of the regional blocks.

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

Last Modified: Wednesday, 27-Apr-2016 04:15:26 EDT