Spreadsheet Models for Managers

This reading is especially relevant for Session 5What's a Model? What's a Tool?

The difference between models and tools is a frequent source of confusion. Another source of confusion is the difference between static models and dynamic models. Briefly, models are tools, but not all tools are models. And we’re most interested in models that predict temporal behavior.

When you plan your course project, you come squarely up against the question, “What’s the difference between a model and a tool?” Getting it right can mean the difference between an accepted course project proposal and going back to the drawing board.

• Tools
• Models
• Business models
• Static models
• Dynamic models

Tools

The simplest concept is the tool. A tool is something you use to get a job done. It’s a means to an end. For example, in this course, Excel is a tool that we’re using to study business modeling. A second example: we use the Internet as a tool to distribute course materials, and to submit homework and course projects. Both are tools.

Excel and the Internet are large systems that we use as tools. But tools can be smaller than that, and they can even be components of other tools. In Excel 2010 and 2013, the command File>Open…, or in Excel 2007 the command Microsoft Office>Open, are tools as well. We use them to gain access to files on our computers, or on local networks. Your telephone is also a tool for this course — you might use it to talk to one of your teammates.

But it’s also possible to construct tools in the form of worksheets, which look like what we call models, but aren’t. And this is what can become confusing.

Models

A model is a simplified representation of a system, construct, device, or idea. For example, in the lobby of a high-rise building, you can sometimes find an architect’s model of the building as envisioned before construction. These models show the shape and design of the building, rather faithfully, and they’re geometrically correct. But they’re often incorrect in color, or density, or texture, or lighting. They focus on the shape of the structure.

Some models aren’t even faithful representations of shape. In the T-stations around Boston — and inside every subway car — are maps of the system or the line you’re riding, showing all the stops and their names. The maps are often incorrect in the detailed shapes of the lines, but they’re correct in their depiction of the connectivity and order of stops.

Models are useful because they’re selectively faithful to the things they model. A good model ignores or simplifies away the details that are unimportant for the purposes it serves. An architect’s model might be a 1/16 scale model of a building, but only visually — it often weighs a lot less than 1/16 of the weight of the building. It might not depict the piles that form the foundation of the building. It might not depict the hidden wires or structural steel that are invisible parts of the building. These elements are unimportant when the model is used to convey the visual impact of the building.

Good models focus on what’s important for their users. The purpose of the architect’s model is to communicate the visual characteristics of the building. Weight and hidden elements are irrelevant. The purpose of the map on the subway car is to help us determine when to get off the train. The geometric layout of twists and turns of the route is unimportant.

Business models

In business, we make decisions. We buy and sell companies, we design facilities, we hire staff, we train them, and we buy equipment. Knowing when and how much — and knowing it well — are critical success factors. We use models of our businesses, and models of the processes within them, to make these decisions. The models we use are simplified representations of our businesses. They emphasize the aspects of the businesses about which we’re making decisions.

For example, if we’re in furniture sales, when we model the inventory of the furniture store, we might be concerned with the items in inventory, and less concerned with the racks that hold the sofas in the warehouse. The number of racks might not even appear in the model. Our concern is when to order more sofas and how many to order.

On the other hand, if we’re responsible for managing the furniture store’s warehouse, we might be very concerned about the rack space in the warehouse. It might, in fact, be the central focus of our modeling effort.

Deciding what to include in a model, and what to ignore, is a critical design step in any modeling effort, because most models are actually decision support tools. You want to include elements that help you make your decision, and eliminate elements that are irrelevant to the decision. And this is why the distinction between models and tools is tricky — a model is a decision support tool. Models are tools. The problem is that not all tools are models. The situation is shown in the figure at the right.

In this course, our interest in models is even more specific than this. We’re studying models that represent dynamic business systems — systems that exhibit complex behavior. Models that do not exhibit complex behavior — so-called static models — are also useful, but they aren’t our focus.

Static models

As an example of a static model, consider a fast-food market predictor. Its purpose is to predict how many customers a new store can expect to receive daily. It uses information about the immediate vicinity of the store to compute the customer count:

Number of residents by age bracket and sex Square feet of office space Number of fast-food stores

Number of restaurants Location of freeway exits Number of on-street parking spaces Number of off-street parking spaces

To use the model, you fill in the numbers, and it calculates for you the number of customers you can expect for each day of the week. This is a static model, and it isn’t what we’re studying in this course.

Dynamic models

Dynamic models, as we’ve said, represent system behavior. That is, they show system attributes as they evolve over time, often in response to some outside forces. For example, consider a different kind of fast-food market predictor. In this model, we take into account all the factors described above in the static market predictor, but we also include factors that change with time. For example, we could model the public’s knowledge of the presence of the new store. At first, the public is completely ignorant of our plans. But as we advertise, and as construction begins, the public has increasing awareness of our presence. We plan promotions, mailings, give-aways, and radio advertising. All of these efforts pay off in terms of customer count, but some take longer to take hold, and some have immediate effects.

In a dynamic model, we represent these different effects, and we use the experience of other franchisees to predict their impact on customer count. We know, for example, that radio ads slotted at 11 am, just before lunch, have greater impact on that day’s customer traffic than do afternoon spots. They also cost more. So we use our model to help us make decisions about how much to spend and on what kind of advertising. Our model shows us not only how radio ads on one day can affect customer traffic two, three, and four days into the future, but also how they interact with our direct mail campaign.

Now that’s a very sophisticated model, but its sophistication isn’t what makes it a dynamic model. Rather, its ability to predict behavior — the response of model attributes over time — is what makes it dynamic.

In practice, static models sometimes look very much like dynamic models. You can distinguish them easily by examining the computation of system attributes for a given time period. In static models, all system attributes for any given time period can be computed without reference to any other time period. Thus, for example, in a static model over a period of twelve months, every value of every intermediate stream or output stream for month 10 can be computed by examining only input parameters and the values of streams in month 10. In dynamic models, to compute the values of intermediate streams or output streams, there is at least some dependence on prior time periods or subsequent time periods.

This course

This course is about dynamic models. We’re hoping that you’ll come to recognize their value as decision support tools, and that you’ll become adept at building simplified dynamic models quickly. That’s why we require that you build a dynamic model — not a tool, not a static model — for your course project.

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