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 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:

• I took your class at the Harvard Extension School back in 2006. First, thank you. I've since started and built an Internet retail business to a multimillion dollar level, using the tools I learned in your class to create the financial modeling and cash flow management tools that I use to this day. The skills I learned in Spreadsheet Models for Managers have been invaluable!
• 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.
• 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.

Begin by downloading the files you want:

After downloading, open each file and it will populate your iCal calendar for the entire semester. 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.

Since nearly all homework must be done using Microsoft Excel, you'll need access to the software.

This is a hands-on course. It's very helpful to have a computer available to use as you follow along during each session, because then you'll be able to see things for yourself and try them out as we go along. It also helps to have with you hardcopies of demonstration worksheets, homework solutions and session notes, which we provide to you in electronic form.

Although there is no textbook for this course, we provide extensive supporting materials at the course Web site. All class notes, examples, readings, homework, and homework solutions are available over the Internet, making it very convenient for you to pick up course materials and updates at any time. by email, and after grading, we return it to you by email.

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

General familiarity with the use of modern personal computers 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. 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 2003 or later; for the Mac, Office 2004 or Office 2011. Since all homework assignments and course notes are provided in electronic form, you'll also need access to a Web browser, either Firefox (version 3.5 or later), or Internet Explorer (version 7 or later), or Safari version 4 or later, for readings and homework solutions. You'll want Microsoft PowerPoint from Office 2003 or later for session notes. You'll also need an email account that permits attachments. Note: some 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 four to seven hours in preparation for each session. Most of that time is spent woking on homework assignments.

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.

At each session, after you submit your homework, you'll receive worked homework solutions in electronic form.

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:
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 for your course projects. We also examine some of the methods for collaborating and exchanging Excel workbooks. 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