UNIVERSITY OF SOUTHERN CALIFORNIA



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

Marshall School of Business, University of Southern California

Instructor: Hao Zhang Grader: TBA

Office: Bridge Hall 401G Email: TBA

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

Phone: (213) 821-2279

Email: zhanghao@usc.edu

Textbook

Management Science & Decision Technology, by Jeffrey D. Camm and James R. Evans (South-West College Publishing, 2000).

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 through 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 five broad classes of managerial problems:

1. Data Analysis: How to summarize available data into useful information. The cost of collecting data has declined 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. Estimation and Forecasting: How to find important information and implications from historical data and how to extrapolate past data into the future. We will explore a handful of models and techniques for estimation and forecasting.

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

5. 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 Pivot Table, Filters, etc. for processing data, Solver for finding optimal decisions, Time series models for forecasting, Treeplan for drawing decision trees, and Crystal Ball for analyzing risk and uncertainty.

Grading

Grades will be curved and will be based on four homework assignments, two exams, and class participation according to the following weights:

Homework assignments 30%

Midterm exam 30%

Final exam 35%

Class participation 5%

Exams will be in-class and open book/open notes. No make up exam will be given. Late homework submission will not be graded.

Blackboard Usage

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

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

|Dates |Topics |Exercises |

|Aug. |Course Introduction |Read: Ch 1, pp. 2-13. |

|24 |Objective, outline, skill sets, textbook, expectations | |

| |Introduction to Modeling | |

| |Definition of modeling, types of models, functional area applications, modeling steps; | |

| |A simple profit model in math and in Excel; | |

| |Card game simulation and analysis | |

|26 |Excel Basics |Exercise: Excel tutorial |

| |Workbook/sheet navigation, window split/freeze, column/row operations, sequences, |(self practice). |

| |absolute/relative cell references, range names, auditing tools, menu items, basic | |

| |functions (Min, Max, Average, Count, etc.) | |

|31 |Excel Basics |Read: Ch 2, pp. 49-71. |

| |Intermediate functions (If, And, Or, Sumproduct, Vlookup, Index, Match, Pmt, etc.) |Exercise: self-practice set |

| |Data Analysis |1. |

| |Data searching, editing, sorting, filtering, tabulating (pivot tables) | |

|Sept. |Data Analysis |Read: Ch 2, pp. 49-71. |

|2 |Data importing (from file and Internet), “data table” analysis tool (with one or two |Exercise: self-practice set |

| |changing variables); |2. |

| |Practice: database management for global manufacturer Hanover, Inc. | |

|7 |Data Analysis |Read: Ch 1, pp. 13-15. |

| |Data visualization | |

| |Review of data analysis and Hanover example | |

| |Example: Santa Cruz MicroProducts | |

| |Building Excel model, finding the best product mix by data table and by Solver | |

|9 |Introduction to Optimization |Read: Ch 4, pp. 141-150. |

| |Optimization model components, types of optimization models, types of linear constraints, |Exercise: self-practice set |

| |Excel model layouts and guidelines, solution possibilities |3. |

| |Linear Programming | |

| |Example: product mix (Santa Cruz MicroProducts) | |

| |Math formulation, Solver setup | |

| |Example: investment allocation (Kathryn) | |

| |Alternative Excel models, universal linear programming template, intuitive solution | |

|14 |Linear Programming |Read: Ch 4, pp. 150-154. |

| |Example: allocation of marketing effort (Phillips, Inc.) |HW1 (due 9/23): linear |

| |Choosing decision variables, handling upper/lower bounds efficiently, modeling and Solver |programming. |

| |tips | |

| |Example: diet problem (Colorado Cattle Company) | |

| |Standard linear programming form, bottleneck constraints | |

|16 |Linear Programming |Read: Ch 4, pp. 155-160. |

| |Example: multiperiod production planning (Suzie’s Sweatshirts) | |

| |Network diagram, inventory balance equation, alternative models, solution intuition | |

| |Example: working capital management (Vohio, Inc.) | |

| |Network diagram, flow balance equation, solution diagram, solution intuition | |

|21 |Linear Programming |Read: Ch 4, pp. 160-177. |

| |Example: transportation problem (Hanover, Inc.) | |

| |Connection with Hanover data analysis example, network diagram, special (rectangle) Excel | |

| |layout | |

| |Sensitivity Analysis | |

| |Changing right-hand sides, bottleneck constraints, shadow prices; changing objective | |

| |coefficients, basic (non-zero) variables; Solver sensitivity report | |

| |Homework 1 Q&A | |

|23 |Homework 1 Student Presentation |Read: Ch 5, pp. 200-206, |

| |Integer Programming |212-216. |

| |Introduction: integer and binary variables, Solver setup | |

| |Example: production planning (Queen City, Inc.) | |

| |Difference between linear and integer solutions | |

| |Example: project selection (Burke Construction) | |

| |Describing logical relationships by binary variables and linear constraints, binary | |

| |variables in objective function | |

|28 |Integer Programming |Read: Ch 5, pp. 207-208, |

| |Example: production planning with setups (Chemco, Inc.) |handout. |

| |Inventory model with and without setups, network diagram, constraints linking binary |Extra-credit exercise: what |

| |variables and continuous variables |if each worker works any |

| |Example: staff scheduling (Airport Services, Inc.) |five days (not necessarily |

| |Choosing decision variables, binary coverage table |consecutive) in a week? |

|30 |Integer Programming |Read: Ch 5, pp. 209-211, |

| |Example: location problem (Sun Bank) I |216-218. |

| |Choosing decision variables, modeling adjacency relationships, linking principal place of |HW2 (due 10/12): integer & |

| |business with surrounding branches, creating coverage table |nonlinear programming. |

| |Example: location problem (Sun Bank) II | |

| |Preventing a branch from double counting | |

|Oct. |Nonlinear Programming |Read: Ch 5, pp. 218-221, |

|5 |Introduction: nonlinear functions, “nice” vs. “nasty” functions, global vs. local optimal |223-224, 226-228, 229-231. |

| |solutions, nonlinear constraints, Solver setup | |

| |Example: advertising budget allocation (Phillips, Inc.) | |

| |Determining nonlinear objective function (relationship between profit and advertising | |

| |money) through regression | |

| |Example: facility location (Jack’s Job Shop) | |

| |Determining nonlinear objective function through geometry | |

|7 |Nonlinear Programming |Read: Ch 5, pp. 221-223, |

| |Example: Markowitz portfolio optimization model |228-229. |

| |Expressing expected portfolio return from allocation variables, expressing portfolio | |

| |variance from allocation variables, variance-covariance matrix, 3-stock Excel model, | |

| |30-stock Excel model, sensitivity analysis, risk-return frontier | |

| |Homework 2 Q&A | |

|12 |Homework 2 Student Presentation |Exercise: sample midterm |

| |Midterm Review I |exam. |

| |Practice Problem 1: traffic network throughput | |

| |Practice Problem 2: baseball player selection | |

|14 |Midterm Review II |Exercise: sample midterm |

| |Practice Problem 3: emergency vehicle locations (I & II) |exam. |

| |Practice Problem 4: landfill locations | |

| |Discussion: sample midterm exam solutions | |

|19 |Midterm Exam (4:00pm-5:50pm) | |

|21 |Forecasting |Read: Ch 3, pp. 97-108. |

| |Introduction: | |

| |Time series components and decomposition, forecasting model classification, forecast | |

| |errors (mean absolute deviation, root mean square error, mean absolute percentage error) | |

| |Simple moving average model (data with no trend or seasonality): | |

| |Selecting window size based on errors | |

| |Simple exponential smoothing model (data with no tread or seasonality): | |

| |Selecting the smoothing constant through data table or Solver | |

| |Double moving average model (data with trend): | |

| |Linear trend equation, level and trend | |

|26 |Midterm Exam Discussion |Read: Ch 3, pp. 109-113. |

| |Forecasting | |

| |Double exponential smoothing model (data with trend): | |

| |Smoothing constants initialization, selecting smoothing constants through data table or | |

| |Solver | |

| |Additive seasonality model (data with seasonality) | |

|28 |Forecasting |Read: Ch 3, pp. 113-117, |

| |Multiplicative seasonality model (data with seasonality) |121-123. |

| |Holt-Winters additive model (data with trend and seasonality) |HW3 (due 11/16): forecasting|

| |Holt-Winters multiplicative model (data with trend and seasonality) |& decision analysis. |

| |Forecasting with regression: | |

| |Simple linear regression models, Excel “add trendline” tool | |

|Nov. |Decision Analysis |Read: Ch 6, pp. 247-253, |

|2 |Introduction: |254-257. |

| |Decision alternatives, outcomes, payoff table, decision criteria (average-payoff, | |

| |aggressive, conservative, opportunity-loss, and expected-value criteria) | |

| |Decision tree basics: | |

| |Diagram, decision nodes, event nodes, branches | |

| |Example: experimental drug development | |

| |Drawing decision tree, Treeplan Excel add-in, rollback procedure (two methods) | |

|4 |Decision Analysis |Read: Ch 6, pp. 253-254, |

| |Example: new product introduction |266-268. |

| |Drawing decision tree, Treeplan tips, sensitivity analysis by one- and two-variable data | |

| |tables | |

| |Example: who wants to be a millionaire? | |

| |Solving decision tree by Treeplan | |

|9 |No Lecture (self practice) | |

|11 |Decision Analysis | |

| |Example: DriveTek subcontracting problem | |

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

| |Homework 3 Q&A | |

|16 |Homework 3 Student Presentation |Read: Ch 7, pp. 286-301. |

| |Monte Carlo Simulation | |

| |Introduction: | |

| |Simulation and risk, random numbers, histogram, 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,| |

| |rolling one or two dice and viewing distribution (dice roller simulator I & II) | |

| |Example: profit model of a firm | |

| |Creating base model, generating discrete random variables by Rand(), defining random | |

| |variables in Crystal Ball, defining output cell in Crystal Ball, analyzing simulation | |

| |result | |

|18 |Monte Carlo Simulation |Read: Ch 7, 304-309. |

| |Example: newsvendor problem |HW4 (due 12/2): |

| |Key trade-off in newsvendor problems, profit formula, base model with fixed order |simulation. |

| |quantity, Crystal Ball simulation, finding best order quantity via data table and decision| |

| |table tools | |

| |Example: pricing stock options | |

| |Daily or weekly stock price formula, types of stock options, strike price and expiration | |

| |date, fair price of an option, Crystal Ball simulation | |

| |Crystal Ball tips: Crystal Ball functions and decision table tool | |

|23 |Monte Carlo Simulation |Read: Ch7, pp. 321-323. |

| |Example: bidding for contract (Miller Construction Company) | |

| |Modeling competitors’ bids with and without correlation, choosing Miller’s bid through | |

| |decision table tool | |

| |Crystal Ball tip: correlation matrix tool | |

| |Example: Craps game I | |

| |Casino games, rules of Craps game, model layout, formula for win or loss in the “come-out”| |

| |phase, modeling the “point” phase with an arbitrary number of rolls, formula for win or | |

| |loss of the overall game, finding winning probability through Crystal Ball | |

|25 |No Lecture (Thanksgiving Day) | |

|30 |Monte Carlo Simulation |Read: Ch7, pp. 310-311. |

| |Example: Craps game I |Exercise: sample final exam.|

| |Introducing an alternative model | |

| |Example: Craps game II | |

| |Multiple rounds with winning probability from part I, terminating the game after player | |

| |losing all money or reaching round 100 | |

| |Crystal Ball tip: tornado chart tool | |

| |Homework 4 Q&A | |

|Dec. |Homework 4 Student Presentation |Exercise: sample final exam.|

|2 |Monte Carlo Simulation | |

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

| |Final Review | |

|9 |Final Exam (4:30-6:30pm) | |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download