Modeling with Spreadsheets

QMETH 551
Spring 2015

Instructor
Mark Hillier
Office: Paccar 465
Office Hours: Mondays 9:30–11:30am; Wednesdays 3:30–4:15pm
Office Phone: (206) 685-1912
e-mail: mhillier@uw.edu
Canvas Portal: http://canvas.uw.edu/

Course Description
This course covers modeling with spreadsheets for analyzing quantitative aspects of business
problems. Spreadsheet packages now have features that allow managers to perform sophisticated
quantitative analysis in the comfortable and intuitive environment of the spreadsheet. This gives
managers the power of quantitative analysis tools without forcing them to use unfamiliar
mathematical notation. Applications from a variety of business areas will be discussed, including
finance, marketing, and operations. Sample applications include cash flow problems, portfolio
optimization, pricing models and revenue management, municipal bond underwriting, project
management, supply chain management, facility location, shortest path and routing, equipment
replacement, advertising media selection, and budget-constrained project selection.

Required Materials
Hillier and Hillier, Introduction to Management Science, 4th or 5th Edition, McGraw-Hill/Irwin.
Course Packet includes copies of class slides, problem sets, and case studies. Available at RAMS
Copy Center (4144 University Way).

Course Packet
I have prepared extensive course notes that I use as overheads during lecture. These course notes
include examples that we will work together in class and other lecture material. The course notes are
not intended as material that you use to prepare for class; my intent is for them to make it easier for
you to listen, ask questions, and participate in class, rather than take lots of your own notes during
the lectures.

Software
Microsoft Excel (including Solver), Solver Table (available on Canvas), and Crystal Ball (Oracle has
agreed to provide new temporary Crystal Ball licenses for students in this class—instructions on
Canvas). Crystal Ball is also available on the computers in the Paccar Computer Lab.
QMETH 551 (Spring 2015) Page 2 Syllabus

Laptop Policy
You are welcome to bring a laptop to class to work the models we build in class. Completed
spreadsheets as well as spreadsheets with just the data are available for download on Canvas. You are
also welcome to just watch, take notes, and participate in discussion. I want to encourage your active
involvement in this course. Thus, I subscribe to the School’s policy that expects you will not access
email, surf the internet, or instant message others during class.

Academic Integrity
By being a student in this course you acknowledge that you are a part of a learning community at
the Foster School of Business that is committed to the highest academic standards. As a part of this
community, you pledge to uphold the fundamental standards of honesty, respect, and integrity, and
accept the responsibility to encourage others to adhere to these standards. Furthermore, as part of
the Foster MBA program, we have jointly agreed to conform to and uphold the MBA Honor Code.

Grading Policy
The course grade will be based on class participation, problem sets, two exams, and a student
presentation, with the following weights:
• Class Participation 5%
• Problem Sets 30%
• Midterm 25%
• Final Exam 25%
• Student Project and Presentation 15%

Study Groups
The problem sets and student projects will be done in groups of three to five. The problem set
groups will be self-selected on the first day of class. The groups for the presentation can be chosen
later. It is acceptable (and typical) that the student project groups be the same as the problem set
groups, but this is not required. At the end of the quarter, each member of the group will also
evaluate each of the other members of the group in terms of their relative contribution to the
group’s problem sets and/or student project and presentation. The individual evaluations will be
confidential, made directly to me through Canvas. Your evaluations from others in your group will
be combined and evaluated and individuals that did not make a fair contribution (or made
extraordinary contributions) to the group will have their overall problem set or project scores
adjusted accordingly.

Problem Sets
There will be six graded problem sets. These are to be done in groups of three to five. It is expected
that the group will either work together on all problems, or each member will do all the problems
individually, and then the group will compare and compile a single version from the individual
versions. Groups should not split up the problems, work on separate problems individually, and
combine. Discussing problems with others in the class (outside your group) is also acceptable, but
information sharing should be in the form of discussion and explanation, and should not include
copying of computer files (beyond your group). One member of each group should turn in the
solution to each assignment via Canvas no later than the due date and time indicated. If possible,
include all spreadsheets in a single workbook on separate tabs.
QMETH 551 (Spring 2015) Page 3 Syllabus

Exams
The midterm exam and final exam will both be take-home exams. You will be given approximately
one week for each. These exams are open book and open notes with no time limit (other than the
due date), but must be completed individually, without assistance from any other person.

Student Projects & Presentations

Student projects and presentations will be done in groups of three to five students. You may stay in
the same groups as for the problem sets, but this is not required. Presentations will be given at the
end of the quarter.

You may choose either of the following for the topic of your presentation:

1. Develop a spreadsheet model that addresses a decision that needs to be made.

Present the decision to be made, the spreadsheet model developed to address it, and
the results of the model.

2. Research a model that was developed by a business to address a decision that needed
to be made. There are many references to “real-world” applications in the textbook.
Most make reference to the journal Interfaces (available in the Foster library), which is
a good resource for management science models that have been developed and used
in business. Present a “case study” of the model, including discussion of the
decision to be made, demonstrating a spreadsheet version of the model that was
developed, and discussing the results obtained with the model. The model need not
have originally been a spreadsheet model (e.g., it may have been an LP solved using
an algebraic solver); if it is not, you will need to create a spreadsheet based upon the
ideas of their model to show the class how the model works in spreadsheet form.

In either case, the spreadsheet model should build upon or be related to one of the techniques
covered during the quarter. The presentations should be approximately 10 minutes after which there
will be a short question-and-answer period. The presentation (and spreadsheet submission) will be
graded for both content (two-thirds weight) and quality of presentation (one-third weight). Qualities
I will be looking for include: (1) does the model build effectively on the modeling techniques
discussed in class, (2) how well does the model address the decision being made, (3) is the
presentation clear and easy to understand, (4) can you convince the audience that the model is
effective and the solution a good one.

Deliverables:
• Wednesday, May 13 (by 10pm), please e-mail me a list of your team members, a brief
description of your topic, and a brief description of the model you intend to build or
discuss.

• Before 1pm on presentation day, please submit to Canvas (under Assignments>Student

Project) the spreadsheet model that your team has built and any other electronic items
used in your presentation (e.g., Powerpoint slides).
QMETH 551 (Spring 2015) Page 4 Syllabus

Schedule
Session #1: Modeling with Spreadsheets
Monday, March 30
Review: Text, Chapter 1
Session #2: Applications of Linear Programming
Wednesday, April 1
Review: Core Sessions #2-4; Text, Chapters 2-4
Monday, April 6, 1pm Due: Problem Set #1 (Linear Programming)
Session #3: Transportation & Assignment Models
Monday, April 6
Read: Text, Chapter 15 (available electronically on Canvas Syllabus)
Session #4: Network Models
Wednesday, April 8
Read: Text, Chapter 6
Monday, April 13, 1pm Due: Problem Set #2 (Transportation & Network)
Session #5: Applications of Integer Models
Monday, April 13
Review: Core Session #6; Text, Chapter 7
Session #6: Municipal Bond Underwriting Case Study
Wednesday, April 15
Prepare: Municipal Bond Case Study (back of packet)
Monday, April 20, 1pm Due: Problem Set #3 (Integer)
Session #7: Multiple Objectives and Goal Programming
Monday, April 20
Read: Text, Chapter 17 (available electronically on Canvas Syllabus)
Session #8: Nonlinear & Separable Programming
Wednesday, April 22
Read: Text, Sections 8.1–8.3
Monday, April 27, 1pm Due: Problem Set #4 (Multiple Objective & Nonlinear)
Monday, April 27 Session #9: Applications of Optimization
Monday, May 4, 1pm Due: Midterm Exam (Available Monday., April 27, 5pm)
Session #10: Evolutionary Solver
Monday, May 4
Read: Text, Sections 8.4–8.5
Wednesday, May 6 Session #11: Macros, VBA, Analytic Solver Platform
Session #12: Simulation and Crystal Ball
Monday, May 11
Review: Core Sessions #9-10; Text, Sections 20.1–20.8 (on Canvas Syllabus)
Session #13: Mountain Realty Case Study
Wednesday, May 13
Prepare: Mountain Realty Case Study (back of packet)
Wednesday, May 13, 10pm Due: Student Project Proposals
Monday, May 18, 1pm Due: Problem Set #5 (Evolutionary Solver and Simulation)
Session #14: Optimizing with Simulation
Monday, May 18
Read: Text, Section 20.9 (available electronically on Canvas Syllabus)
Session #15: Models for Analyzing Waiting Lines
Wednesday, May 20
Read: Text, Sections 11.1–11.7
Wednesday, May 27, 1pm Due: Problem Set #6 (Simulation)
Session #16: Analysis of Waiting Lines
Wednesday, May 27
Read: Text, Sections 11.8–11.9
Monday, June 1 Session #17: Student Project Presentations
Wednesday, June 3 Session #18: Student Project Presentations (if needed)
Wednesday, June 3, 10pm Due: Peer Evaluation Form
Monday, June 8, 1pm Due: Final Exam (Available Monday., June 1, 5pm)
QMETH 551 (Spring 2015) Page 5 Syllabus

Session Summaries
Session #1: Modeling with Spreadsheets
In this session we will discuss the role of models in managerial decision making, including the use
of spreadsheets, and provide an overview of the models and techniques to be covered in this
quarter. We discuss good and poor spreadsheet modeling, and how proper layout and formatting can
produce models that are easier to build, debug, read, and modify.

Session #2: Applications of Linear Programming with Spreadsheets

We discuss applications of linear programming with spreadsheets to problems in finance, marketing,
and operations.

Session #3: Transportation and Assignment Problems

In this session we will discuss the use of spreadsheet models to handle transportation, distribution,
and assignment problems.

Session #4: Network Models

Transportation and communication networks pervade our daily lives. In this session, we discuss
applications of network optimization. We then discover that most network optimization problems
are really linear programming problems. We discuss minimum-cost flow, maximum flow, shortest
path, and equipment replacement problems.

Session #5: Applications of Integer Models

In this session we address problems where some or all of the decision variables are required to
assume integer values and discuss the application of binary variables. We discuss applications in
finance, marketing, and operations.

Session #6: Municipal Bond Underwriting Case Study

In this session we discuss the application of binary integer models to a municipal bond underwriting
case. We also discuss other advanced applications of integer programming.

Session #7: Multiple Objectives and Goal Programming

In this session we look at problems that have more than one objective. Techniques for addressing all
of the objectives are discussed.

Session #8: Nonlinear and Separable Programming

Here we discuss spreadsheet models with a nonlinear objective function and/or nonlinear
constraints. What are the ramifications on solvability? We also will discuss the use of separable
programming to approximate a nonlinear model with a linear one.
QMETH 551 (Spring 2015) Page 6 Syllabus

Session #9: Applications of Optimization

In this session we combine various optimization techniques to develop advanced optimization
models addressing problems in project management and pricing and revenue management.

Session #10: Evolutionary Solver

We introduce genetic algorithms and the Evolutionary Solver to address problems that can not be
solved using the standard Solver.

Session #11: Macros, VBA, and Analytic Solver Platform

In this session we will discuss the use of macros and VBA, how they can aid in modeling, and how
they can be used to create a decision support system. Next, the Analytic Solver Platform will be
demonstrated.

Session #12: Simulation and Crystal Ball

In this session we discuss the use of the Crystal Ball Excel add-in for performing Monte-Carlo
simulation, and applications in finance, marketing, and operations.

Session #13: Mountain Realty Case Study

In this session we will use Crystal Ball to explore a business decision faced by Mountain Realty.

Session #14: Optimizing with Simulation

In this session we discuss how optimization can be performed with a simulation spreadsheet model.
The OptQuest add-in for Crystal Ball will be demonstrated.

Session #15: Models for Analyzing Waiting Lines

In this session we introduce analytical models and corresponding spreadsheet templates for
predicting the effects of congestion in waiting line systems. Single and multiple-server queues,
priority queues, and systems with finite queue capacity will all be discussed.

Session #16: Analysis of Waiting Lines

In this session, we utilize the queueing templates to make the following kinds of decisions: How
many servers should we use? How much space needs to be made available for the waiting line? How
many phone lines and representatives should a call center utilize? When should a priority scheme be
utilized?

Sessions #17-18: Student Presentations

Groups of three to five students will give a 10 minute presentation about a model that they have
developed or that a business has used to analyze a problem.