Spreadsheet Models for Managers

Course Information and Syllabus

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.

Here’s a guide to what’s in this syllabus. If you can’t find what you’re looking for here, check the list of Frequently Asked Questions (FAQ).

• What you’ll learn
• Course calendar
• Aims and structure of this course.
• Comments from attendees
• Course requirements
• Course load
• Text and readings
• Syllabus

Instructor
Rick Brenner Chaco Canyon Consulting 700 Huron Avenue, Suite 19C Cambridge, MA 02138 Voice: (617) 491-6289 Fax: (617) 395-2628 Email: rbrenner@ChacoCanyon.com Download vCard

  More about the instructional staff

What you’ll learn

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.

Class comments

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.

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.” We recommend that you work at a pace of about one session per week.

Aims and structure

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.

This course has no exams.

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

Course requirements

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.

Required software

You’ll need access to a variety of software in addition to Microsoft Excel. For Windows, Excel from Office 2007 or later; for the Mac, Office 2011. Since all homework assignments and course notes are provided in electronic form, you’ll also need access to a Web browser, either Google Chrome 17.0 or later, Mozilla Firefox (version 7.0 or later), or Internet Explorer (version 7.0 or later), or Safari (version 4.0 or later), for readings and homework solutions. You’ll want Microsoft PowerPoint from Office 2007 or later for session notes. You’ll need Microsoft Word from Office 2007 or later to write some of the documentation that accompanies your course project.

Course load

On average, you’ll spend about four to seven hours in preparation for each session. Most of that time is spent working on homework assignments and preparing a course project. The project consists of an actual model of a business or 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.

Text and readings

No text is required. All course materials, including session notes, examples, homework assignments, and supplementary material are provided in electronic form. 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 as a ZIP archive. You can purchase it on line.

Just before each session, after you submit your homework, you’ll receive worked homework solutions in electronic form.

Syllabus

 

Session 1: Introduction to Spreadsheet Models:
Running sums, running differences, named parameters, references

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. Homework Assignment 1 due. 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 — providing price breaks for orders that exceed certain thresholds — produces quantization effects in expense or revenue models. Homework Assignment 2 due. 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. Homework Assignment 3 due. 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. Homework Assignment 4 due. 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. Homework Assignment 5 due. Project Proposal is due. 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. Homework Assignment 6 due. 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. Homework Assignment 7 due. 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. Homework Assignment 8 due. Project Midpoint Status Report and Excel Capability Request are due. 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. Homework Assignment 9 due. 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. Homework Assignment 10 due. Session Materials

Session 12: Service Systems:
Single-server queues

A service system is a system in which customers present themselves for service, are then serviced, and then 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. Homework Assignment 11 due. 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. Homework Assignment 12 due. 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. Homework Assignment 13 due. Session Materials

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