UNIVERSITY OF SOUTHERN CALIFORNIA
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: zhanghao@usc.edu
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. |Course Introduction | |
|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; | |
| |Card game simulation and analysis | |
|26 |Excel Basics |Practice: Excel tutorial; |
| |Workbook/sheet navigation, window split/freeze, column/row operations, sequences, |Ch 3 |
| |absolute/relative cell references, range names, auditing tools, menu items, functions | |
| |(Min, Max, Average, Count, If, And, Or, Sumproduct, Vlookup, Index, Match, Pmt, etc.) | |
|31 |Data Analysis |Practice: Ch7: 1-6 |
| |Data visualizing, searching, editing, sorting, filtering, tabulating (pivot tables), | |
| |importing (from files and Internet), “data table” analysis tool (with one or two changing | |
| |variables) | |
|Sept. |Optimization – Introduction |Practice: Ch 11: 1&2. |
|2 |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 | |
|7 |Optimization – Linear Programming |HW1: LP, due Sept. 14 |
|9 |Introduction: linear functions, linear vs. nonlinear programming, types of LP problems |Read: pp. 246-260, 265-267|
| |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 | |
|14 |Optimization – Network Models |HW2: Network, due Sept. 28|
|16 |Introduction: network components, network diagram |Practice: Ch12: 2&6. Read:|
|21 |Example: transportation |pp. 285-290, 294-296, |
| |Diagram, LP formulation, standard LP model, special (rectangle) layout |300-303 |
| |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 | |
|23 |Case: Flora Farmer’s Gladiolus Bulbs | |
|28 |Optimization – Integer Programming |HW3: IP, due Oct. 12 |
|30 |Introduction: Solver behavior, types of IP models, solver setup, relationship between IP |Practice: Ohio Bank |
|Oct. |and LP, binary variables |Problem. |
|5 |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 | |
|7 |Optimization – Nonlinear Programming |Practice: sample midterm |
|12 |Introduction: nonlinear functions, “nice” vs. “nasty” functions, global vs. local optimal |exam. |
| |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 | |
|14 |Midterm Exam (2:00pm-3:20pm) | |
|19 |Decision Analysis |HW4: Decision tree, due |
|21 |Introduction: uncertainty, payoff table, decision criteria (maximax payoff, maximin |Nov. 4 |
|26 |payoff, minimax regret, expected payoff), probability tree |Practice: Ch14: 2&8. Read:|
|28 |Decision tree basics: diagram, chance nodes, decision nodes, branches, rollback procedure |pp. 342-360. |
| |(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? | |
|Nov. |Simulation – Introduction | |
|2 |Motivation example, what/why/how, simulation and risk, random numbers, histogram, common | |
|4 |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 |Simulation – Finance Examples |HW5: Simulation, due Nov. |
|11 |Example: Butson store (cash budgeting) |30 |
|16 |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 | |
|18 |Simulation – Marketing Examples |Practice: Ch15: 2&5. |
| |Example: new product introduction (Bass diffusion model) |Read: pp. 370-415. |
| |Modeling two types of customers, finding the year reaching 50% market share | |
| |Example: customer retention | |
| |Crystal Ball tip: building an open-end CB model | |
|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 |Simulation – Game Examples |Practice: sample final |
|Dec. |Example: Craps game (I & II) |exam. |
|2 |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 | |
|9 |Final Exam (2:00pm-4:00pm) | |
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- dr jessie hayden elementary school midway city
- senate committee on education
- an evaluation of district school media curriculum programs
- probate guardianships of the person california courts
- southern california rapid transit district scrtd law
- university of southern california
- site profiles accelerating student success ms word
- notice of proposed settlement williams case ca dept of