Making useful models of businesses and business processes is partly art and partly science. We hope you'll learn a little bit of both. Art is best learned by seeing examples, and we'll provide you with lots of examples — some in demonstrations and some in homework. The science of spreadsheet models is fairly simple, too. You build models in ways that make them easy to understand and easy to change.

Most people who use spreadsheets learn how to use them from books or from on line help. And although those sources are good for what they are, the art and science of spreadsheet modeling are difficult to convey on the printed page or on screen. In this course, in class and in the labs, and especially in your teams, you'll see things from a new perspective. You'll have a chance to ask questions and to answer them, and you'll be amazed at how much simpler and how much more elegant your approach to spreadsheets will become.

And many of you will have the experience of amazing your colleagues at work when they see the powerful techniques you'll be using.

Here's what past enrollees at Harvard Extension School have said:

• It was a pleasure for us to be in your course and it really added value to our knowledge.
• This course was really good and probably the best course I ever took at Harvard. Congratulations to you!
• I truly enjoyed your course. Best wishes for this and the coming semesters.
• Thank you for a great class. I learned a lot, and had fun at the same time! It's too bad the class has to end as I was enjoying each new lesson. I can see many uses for these tools, not only in accounting, and management roles, but also in engineering (test programs, quality databases, etc.). I'm glad I took the class.
• (Received in January, 2004): I don't know if you remember me or not, but I was in your class in the fall of 2001. Well, I have to tell you what a fabulous class it was!! I have been using many of the techniques I learned in that class and have found it helps to get ahead and impress the right people by having advanced knowledge in the subject of spreadsheet models. In fact, since graduating from the Extension School, I have landed a pretty high-level job at <name withheld>, and was fully recruited and relocated with plenty of perks. So, just in case you were wondering, companies really do like to see the Extension School on a resume and will make every bit of it pay off.
• Thanks for a great semester!

This course is structured around a series of sessions. To study the material presented, and to work the problems, should take about five to seven hours per session. There's some variation, of course, and the earlier sessions are more "meaty." The intention is that once you've decided on a course project (about session 5), more of your time will be devoted to the project, and less to the problem sets. We recommend that you work at a pace of about one session per week.

This course introduces practical approaches to business modeling, business planning, and forecasting, using a variety of mathematical models easily implemented using commercial spreadsheet software. Simple models of cost and revenue sources, cash flow, plant and equipment requirements, and employee costs and productivity provide distinct advantages to the planner and decision-maker. By avoiding the (sometimes overwhelming) details of an accounting system or of a highly sophisticated modeling paradigm, a simple model provides insight into the dynamical behavior of the business process, providing the manager an opportunity to develop an intuitive grasp of the issues and trade-offs. The operational models are intended to meet the needs of retail, wholesale, service, publishing, or software concerns of a range of sizes from startup to global enterprise.

Mastery of the content of this course gives you a real edge in business planning. You'll learn how to make realistic projections that are based on reasonable representations of a business or business processes. And you'll learn techniques that are sophisticated, yet simple to implement in a real business context.

We provide you with an assortment of modeling techniques to use in developing a term project of your own design. Using Microsoft Excel for Macintosh or Windows, you'll develop an actual model of a business or business process of your choice. You'll use this model to study the response of some business process parameters to specified conditions. Nearly all homework must be done using Microsoft Excel, so you'll need access to the software. Laboratory accounts will be provided, but you need not work in the Laboratory.

Because the typical work environment in most companies involves working in groups, students are encouraged to work in teams or small groups, both for weekly homework and especially for the course project. Teams aren't required, but they're encouraged. By working with a team on a spreadsheet project, students gain an appreciation for some of the issues that arise in such a cooperative effort, and become better managers through that understanding.

General familiarity with the use of modern personal computers, including graphical user interfaces such as the Macintosh user interface or Microsoft Windows, is required. Knowledge of the fundamentals of spreadsheet manipulations is required. Although we assume that you're familiar with, though not necessarily expert in, the use of Microsoft Excel, students who are familiar with some other spreadsheet product should be able to adapt what they know to this course, with instructor's approval. Knowledge of spreadsheet macro languages isn't assumed, but you'll learn to write a few simple macros in this course. Most students who know how to use Excel, and even some experts, will learn techniques that aren't well known, but which are extremely useful, not only for modeling, but in numerous other applications as well.

You'll need access to a variety of software in addition to Microsoft Excel. For Windows, Excel from Office 2000 or later; For the Mac, Excel 2001 or 2004. Since all homework assignments and course notes are provided in electronic form, you'll also need access to a Web browser — either Netscape or Internet Explorer — version 5 or later (for readings and homework solutions) and to Microsoft PowerPoint from Office 2000 or later (for session notes). You'll need Microsoft Word from Office 2000 or later to write some of the documentation that accompanies your course project, and you'll also need an email account that permits attachments. If you don't have one, one will be provided. Note: many free email services don't permit attachments, or don't permit attachment sizes that we'll need. We recommend that you find an email service that permits attachments of at least 600 kbytes.

On average, you'll spend about five to seven hours in preparation for each session. Most of that time is spent performing homework assignments and preparing a course project. The project will include an actual model of a business process and documentation for it.

This isn't a reading course. There is no textbook. You'll spend most of your time learning about business models and making Excel do things you never dreamed it could do.

No text is required. All course materials, including session notes, examples, homework assignments, and supplementary material are provided here. Together with the documentation for Microsoft Excel (the online version is good enough), these materials are all you need — you should not need any additional text.All course materials, including homework solutions, are available in electronic form on a CD. You can purchase it on line.

Session 1: Introduction to Spreadsheet Models:
Running sums, differences and parameters

We begin our exploration of spreadsheet models and techniques for building them. What is a spreadsheet model? What makes for a good one? We explore running sums, running differences, parameters, and names. We'll learn about the value of using names. Finally we look at those pesky dollar signs, and answer every question you always wanted to know about absolute and relative references. Session Materials

Session 2: Analysis and Synthesis:
Array arithmetic and matrix products

Analysis is a technique in which we decompose a problem into parts, and then deal with the parts. In synthesis, we reassemble the parts to obtain the solution to the original problem. For many problems, only this approach makes them tractable. Session Materials

Session 3: Cushioning, Crowding and Quantization:
Worksheet functions and circular references

Since modeling is inherently inexact, we sometimes build into our models a safety margin. We call this cushioning. The contrary practice of planning to overtax resources is called crowding. In business modeling, there are occasions when both cushioning and crowding are valuable techniques. Quantization is the effect we observe in models when some of the model parameters are restricted to lie in bands, or when they must adhere to specific values. For example, the practice of "staircasing" price schedules produces quantization effects in expense or revenue models. Session Materials

Session 4: Temporal Response:
Array access, reference operators, and convolution

The focus of this session is the use of arrays to model temporal response of systems. We'll show how to do this using both a high-level method, called convolution, and a more "nitty-gritty" approach using arrays. Session Materials

Session 5: Course Project Proposals:
Making a business case for your modeling effort

To prepare for this session, you'll form teams (at your option — you can work alone if you prefer) to prepare to write your course project proposals. During this session, we'll discuss what's required, and then in your teams, you'll create a rough outline of your proposal. Session Materials

Session 6: Graphics:
Communications and intuition

Graphics — both as representations of numerical data, and as elements of screen and page display — offer two advantages. They enhance communications, and they develop intuition. Session Materials

Session 7: Managing Spreadsheet Modeling Projects:
Spreadsheet inspections

As a manager, you're responsible for the processes people use for developing spreadsheet projects. One practice stands out as a way of making your team, group or department more effective — structured technical reviews. In this session, we'll study the practice of technical reviews, and learn why they are so helpful to the organization, as it tries to become more sophisticated about business modeling. Session Materials

Session 8: Financial Models:
Capital transactions

Having a fundamental understanding of accounting is a great help in modeling businesses, especially in the context of writing a business plan. In this session, we'll present a high-level view of the modeling issues associated with modeling entire businesses. Session Materials

Session 9: Capital Leases I:
Present and future value

To model capital leases, we must understand the interplay between the lease term, the interest rate, and the number and frequency of payments. Excel offers three worksheet functions that implement the underlying formulas that express these connections. In this session, we'll thoroughly explore the use of these facilities, not only in the straightforward situations that one usually sees covered, but also in the more complex arrangements that arise when payments or interest rates are non-uniform. Sadly, these situations are more common than we often suppose, so it pays to know how to handle them. Session Materials

Session 10: Capital Leases II:
Modeling and the Lease Characteristic Array

In this final session of the series on capital leases, we explain how to model the effects of a stream of lease events that occur under the terms of a single lease agreement. Since each event triggers its own set of cash transactions, interest payments, principal payments, and depreciation, tracking the effects of such a stream on the three financial statements can be nightmarishly complex. Session Materials

Session 11: Inventory Modeling:
Economic order quantity and quantity discounts

In businesses in which inventory is a significant cost factor, modelers must understand the issues related to inventory and inventory maintenance. In this session, we'll look at a simple model for inventory replenishment, the Economic Order Quantity. Session Materials

Session 12: Service Systems:
Single-server queues

A service system is a system in which customers present themselves, are serviced, and depart. For example, an airline ticket counter is a service system. Service systems are very common in business, and knowing how to model a service system's capacity is an important skill for a modeler. Session Materials

Session 13: Using Macros I:
Scalar function macros in Visual Basic for Applications

Macros, usually seen as a technical element of any spreadsheet model, are also an important tool of the manager. By encapsulating commonly used techniques in a macro, the manager assures that every time that technique is used, it's used in exactly the same way. This is the most valuable attribute of any macro — it increases the reliability of the models that use it. Session Materials

Session 14: Using Macros II:
Array function macros in Visual Basic for Applications

Function macros, as we've seen, are macros that return values. They correspond to Excel's family of worksheet functions, and provide a way for you to add capability transparently to the user. In the previous session, we looked at function macros that return a single value — scalar function macros. In this session, we'll examine array function macros — macros that return a rectangular array of values. Convolve is an example of such a macro. Session Materials

Session 15: Spreadsheet Tools for Managers:
Tools for the group or the enterprise

Whether you manage the group or the enterprise, you probably use spreadsheets not only for models, but also as tools. Spreadsheets are used for everything from the annual budget exercise, to generating weekly sales reports. Although the spreadsheet is almost always helpful in any of these roles, naive design and even more naive use inevitably leads to problems. Session Materials