IOM 547: Designing Spreadsheet-Based Business Models (Fall 2010)

Marshall School of Business, University of Southern California

Instructor: Hao Zhang TA: TBA

Office: BRI 401G Email: TBA

Office hours: 11:00am-12:00pm, Tu & Thur

Phone: (213) 821-2279

Email:

Textbook

Management Science: The Art of Modeling with Spreadsheets (2nd ed, Excel 2007 Update), by Stephen G. Powell and Kenneth R. Baker (John Wiley & Sons, 2008).

Course Objective

Spreadsheets are convenient and widely available platforms for organizing information and performing “what if” analyses. Excel therefore, has become an indispensable tool for business analysis. This course will focus on structuring, analyzing and solving managerial decision problems on Excel spreadsheets.

The goal of the course is to help you acquire the skills of logical reasoning with formal models and become an effective modeler who can build sound models to solve real-world business problems. The course is about modeling, not about becoming an Excel expert per se.

We will study four broad classes of managerial problems:

1.  Data Analysis: How to summarize available data into useful information. The cost of collecting data has declined fairly dramatically and most firms now have a fair amount of data. The first few, perhaps the most useful, steps in understanding and structuring a business decision is to find out what data is available and organizing it to support decision making.

2.  Resource Allocation: How to optimally allocate a limited pool of resources among available opportunities. This is the most common managerial problem, occurring in every functional area. Examples in finance include constructing an optimal risk-return portfolio, and capital budgeting. Examples in marketing include media planning, and sales force territory planning. In operations management resource allocation problems arise in capacity, logistics and operations planning.

3.  Decision Analysis/ Contingent Decisions: How to synthesize a sequence of decisions involving uncertainty. An intuitive approach to handling uncertainty is to explore the possibility of deferring a decision until some uncertainty is resolved, especially when the stakes are high. If we can we should make sequence of decisions instead of one big decision. Business examples where such decision techniques are used include dynamic portfolio management, new product development, and capacity expansion planning.

4.  Risk Analysis: How to incorporate uncertainty in problem parameters. Almost always managerial decisions are based on anticipated states of the business environment. Clearly as the decision horizon becomes longer there is an increase in uncertainty. Managers have to carefully consider different potential scenarios while making decisions. In this part of the course we will learn how to explicitly incorporate uncertainty into business models.

Excel Skills and Software Usage

Previous knowledge of Excel is not required. Knowing how to enter formulae involving relative and absolute cell addresses and how to illustrate using chart wizard is sufficient. We will learn to use Pivot Table, Filters, etc. to extract useful information from raw data, Solver to find optimal resource allocations, TreePlan to analyze contingent decisions, and Crystal Ball to simulate the effects of uncertainty.

Grading

Final grade will be assigned according to the weighted average of the following parts:

Homework assignments 30%

Midterm exam 25%

Final exam 40%

Class participation 5%

Practices and Assignments

During the semester, there will be optional individual practices and five mandatory group homework assignments. Each group should submit one hardcopy of the answer report in class and accompanying Excel files via Blackboard.

Blackboard Usage

Blackboard will serve as the information center for this course. Powerpoint slides, example files, assignments, solutions, announcements, and supplementary reading materials will all be posted there.

Laptop Usage

Expect to bring a laptop to each class, unless otherwise notified. However, laptop should be used under instruction and with courtesy.

Notice on Academic Integrity

The use of unauthorized material, communication with fellow students during an examination, attempting to benefit from the work of another student, and similar behavior that defeats the intent of an examination or other class work is unacceptable to the University. It is often difficult to distinguish between a culpable act and inadvertent behavior resulting from the nervous tensions accompanying examinations. Where a clear violation has occurred, however, the instructor may disqualify the student’s work as unacceptable and assign a failing mark on the paper.

For Students with Disabilities

Any student requesting academic accommodations based on a disability is required to register with Disability Services and Programs (DSP) each semester. A letter of verification for approved accommodations can be obtained from DSP. Please be sure the letter is delivered to the office as early in the semester as possible. DSP is located in STU 301 and is open 8:30 a.m. - 5:00 p.m., Monday through Friday. The phone number for DSP is (213) 740-0776.

Time Table (Tentative)

Date / Topics / Assignments
Aug.
24 / Course Introduction
Objective, outline, skill sets, textbook, expectations
Introduction to Modeling
Definition of modeling, types of models, functional area applications, modeling steps;
A simple profit model;
Card game simulation and analysis
26 / Excel Basics
Workbook/sheet navigation, window split/freeze, column/row operations, sequences, absolute/relative cell references, range names, auditing tools, menu items, functions (Min, Max, Average, Count, If, And, Or, Sumproduct, Vlookup, Index, Match, Pmt, etc.) / Practice: Excel tutorial; Ch 3
31 / Data Analysis
Data visualizing, searching, editing, sorting, filtering, tabulating (pivot tables), importing (from files and Internet), “data table” analysis tool (with one or two changing variables) / Practice: Ch7: 1-6
Sept.
2 / Optimization – Introduction
Model components, success stories;
Example: boat manufacturer
Finding the best product mix via data table
Finding the best product mix via Solver
Solver setup, spreadsheet model layout, math formulation / Practice: Ch 11: 1&2.
7
9 / Optimization – Linear Programming
Introduction: linear functions, linear vs. nonlinear programming, types of LP problems
Example: Veerman furniture (allocation problem)
Universal template for LP models
Binding/non-binding constraints
Solution pattern and intuition
Changing right hand sides and objective coefficients
Solver sensitivity report, shadow prices
Example: Dahlby outfitters (covering problem)
Handling lower bound and upper bound constraints
Example: Diaz coffee company (blending problem)
Blending constraints from nonlinear to linear / HW1: LP, due Sept. 14
Read: pp. 246-260, 265-267
14
16
21 / Optimization – Network Models
Introduction: network components, network diagram
Example: transportation
Diagram, LP formulation, standard LP model, special (rectangle) layout
Example: assignment
Solving through “greedy” algorithm
Solving as a transportation problem
Example: oil pipeline
General network models, flow balance equation
Various questions: maximum flow, minimum cost, shortest path, etc.
Example: inventory management
Diagram, inventory balance equation
Example: tuition planning
Solution pattern and intuition, what-if analysis / HW2: Network, due Sept. 28
Practice: Ch12: 2&6. Read: pp. 285-290, 294-296, 300-303
23 / Case: Flora Farmer’s Gladiolus Bulbs
28
30
Oct.
5 / Optimization – Integer Programming
Introduction: Solver behavior, types of IP models, solver setup, relationship between IP and LP, binary variables
Example: machine tool company
LP relaxation, difference between linear and integer solutions
Example: staff scheduling
Choosing decision variables, creating binary coverage table
Example: project selection
Describing logical relationships by binary variables and linear constraints, binary variables in objective function
Example: baseball player selection
Example: emergency vehicle locations
Choosing decision variables, creating coverage table, linking vehicle with districts, preventing double counting population
Example: fixed or setup costs
Using binary variables for go/no go of activities, linking binary variables with regular (continuous) variables, handling threshold requirements
Example: facility location
Linking plant status with production/shipping quantities / HW3: IP, due Oct. 12
Practice: Ohio Bank Problem.
7
12 / Optimization – Nonlinear Programming
Introduction: nonlinear functions, “nice” vs. “nasty” functions, global vs. local optimal solutions, challenge posted by nonlinear programming models, solver setup and tips
Example: portfolio optimization
Expressing expected portfolio return from allocation variables, expressing portfolio variance from allocation variables, variance-covariance matrix, 5-stock Excel model, 30-stock Excel model, sensitivity analysis, risk-return frontier
Example: facility location
Determining nonlinear objective function through geometry
Example: revenue maximization with sensitive demand / Practice: sample midterm exam.
14 / Midterm Exam (2:00pm-3:20pm)
19
21
26
28 / Decision Analysis
Introduction: uncertainty, payoff table, decision criteria (maximax payoff, maximin payoff, minimax regret, expected payoff), probability tree
Decision tree basics: diagram, chance nodes, decision nodes, branches, rollback procedure (two approaches)
Example: experimental drug development
Drawing decision tree, Treeplan Excel add-in, rollback procedure (two methods)
Exampel: new product introduction
Drawing decision tree, Treeplan tips, sensitivity analysis by one- and two-variable data tables
Example: DriveTek subcontracting
Solving decision tree by Treeplan, changing cost of mechanical method (one-variable sensitivity analysis), changing success probabilities of electronic and magnetic methods (two-variable sensitivity analysis)
Example: who wants to be a millionaire? / HW4: Decision tree, due Nov. 4
Practice: Ch14: 2&8. Read: pp. 342-360.
Nov.
2
4 / Simulation – Introduction
Motivation example, what/why/how, simulation and risk, random numbers, histogram, common probability distributions (two points, binomial, Poisson, uniform, triangular, normal), simulation modeling process
Practice: generating random numbers by Rand(), drawing distribution by histogram data analysis tool, distributions of one and two dice (dice roller simulator)
Example: profit model of a firm
Creating base model, simulating with Crystal Ball
Crystal Ball basics: defining random variables and output cell(s), analyzing simulation results
9 / No Lecture (self practice)
4
11
16 / Simulation – Finance Examples
Example: Butson store (cash budgeting)
Finding critical variables through Tornado chart, simulation by Crystal Ball, defining correlation between variables
Example: Netscape valuation
Finding critical variables, choosing distributions, sensitivity analysis, lesson from the history
Example: stock option pricing
Various options, stock price model, fair price of an option
Crystal Ball tips: copy/paste/clear cell definitions, tornado chart tool, correlation matrix tool, CB functions / HW5: Simulation, due Nov. 30
18 / Simulation – Marketing Examples
Example: new product introduction (Bass diffusion model)
Modeling two types of customers, finding the year reaching 50% market share
Example: customer retention
Crystal Ball tip: building an open-end CB model / Practice: Ch15: 2&5.
Read: pp. 370-415.
23 / Simulation – Operations Examples
Example: Hastings sportswear (Newsvendor problem)
Key trade-off in newsvendor problems, profit formula, base model with fixed order quantity, finding best order quantity via data table and decision table tools
Example: contract bidding
Modeling competitors’ bids with and without correlation, choosing Miller’s bid through decision table tool
Example: order due-date
Crystal Ball tip: grid search and decision table tool
25 / No Lecture (Thanksgiving Day)
30
Dec.
2 / Simulation – Game Examples
Example: Craps game (I & II)
Casino games, rules of Craps game, model layout, formula for win or loss in the “come-out” phase, modeling the “point” phase, formula for win or loss of the overall game, finding winning probability through Crystal Ball, terminating the game after player losing all money or reaching round 100
Example: NCAA basketball tournament
Modeling games with random outcomes according to Sagarin’s ratings, managing multiple rounds of games from 64 teams down to the final two and the champion / Practice: sample final exam.
9 / Final Exam (2:00pm-4:00pm)