Spreadsheet Modeling _ Decision Analysis - PowerPoint

Document Sample
Spreadsheet Modeling _ Decision Analysis - PowerPoint Powered By Docstoc
					LM 505: Operation Research

Spreadsheet Modeling & Decision Analysis
A Practical Introduction to Management Science Cliff T. Ragsdale

Quant. Decision Making

Chapter 1
Introduction to Modeling & Problem Solving

Outline of Lecture
 Administrative

Issues  Course Overview  Definition of Operations Research  Introduction to Linear Programming

Course Overview

– Objective of Course – Topics Covered – Grading

What we will cover...
Linear Programming  Sensitivity Analysis  Network Models  Integer Programming  Goal Programming  Process Simulation in Excel  Decision Analysis

Provide a set of quantitative tools for decision analytical support

We face numerous decisions in life & business  We can use computers to analyze the potential outcomes of decision alternatives  Spreadsheets are the tool of choice for today’s managers

What is Management Science?
A field of study that uses computers, statistics, and mathematics to analyze and solve business problems Operations Research, Decision Science, and OR/MS  This book focuses on using OR/MS tools in spreadsheets  Everyone using spreadsheets for model building and decision making is an OR/MS practitioner

What is Operations Research?
Scientific approach to decision making that involves the operations of an organized system (Hiller-Lieberman)  A branch of applied mathematics wherein the application is to the decision making process (Gross)

What is Operations Research?

Definition according to Morse and Kimball, 1954:
– A Scientific method of providing executive departments with a quantitative basis for decisions regarding operations under their control


My definition:
– The allocation of limited resources among competing activities

OR supports decision making

A Brief History
Really grew out of WWII efforts  Axis superiority in weapons and doctrine  Allies developing and employing new technologies  Scientists, and scientific method, called upon to help

– Study weapons and weapons effects – Study tactical employment – Study strategy for the war

A Brief History

Post-war leaders institutionalized analysis
– Many military and non-military contributions

Civilian growth as well
– INFORMS in 1994 – Many companies have analysis shops


Computers and OR/MS grown together
– Larger problems, new techniques – Techniques becoming “mainstream”

Applications of OR
Airline flight crew scheduling (AA)  Choosing manufacturing lot sizes (Pfizer)  Where to build a new distribution facility (Dell)  How to make a current facility more efficient? (FedEx)  Others?

The Modeling Approach to Decision Making
 

Everyone uses models to make decisions No model captures all of reality; why do we still need a model?
– A model is valuable if it adds value.

What about intuition?
– – – – – Problem recognition Model formulation Interpretation Implementation Also: Optimality conditions

Characteristics of Models
Models are usually simplified versions of the things they represent  A valid model accurately represents the relevant characteristics of the object or decision being studied  Tailored to specific applications  Many types of models  Humans use models to conduct analysis

Benefits of Modeling
Economy - it is often less costly to analyze decision problems using models  Timeliness - models often deliver needed information more quickly than their realworld counterparts  Feasibility - models can be used to do things that would be impossible  Models help provide insight & understanding to improve decision making


Types of models:
– Mental (arranging furniture) – Visual (blueprints, road maps) – Physical/Scale (aerodynamics, buildings) – Mathematical (what we’ll be studying)

 

Quantitative vs. Qualitative models Deterministic vs. Stochastic models

OR Models

Deterministic (this course)
– – – – – – – – Linear Programming Integer Programming Network Models Nonlinear Programming Queuing models Simulation Renewal theory Reliability theory



Example of a Mathematical Model
Profit = Revenue - Expenses or Profit = f(Revenue, Expenses) or Y= f(X1, X2)

A Generic Mathematical Model
Y = f (X1, X2, …, Xn)

Y = dependent variable (a bottom line performance measure)
Xi = independent variables (inputs having an impact on Y)
f (.) = function defining the relationship between the Xi and Y

Categories of Math Models
Model Category

Form of f(.)
known, well-defined

Independent Variables
known or under decision maker’s control
known or under decision maker’s control

OR/MS Techniques
LP, Networks, IP, CPM, EOQ, NLP, GP, MOLP
Regression Analysis, Time Series Analysis, Discriminant Analysis


unknown, ill-defined


known, well-defined

unknown or uncertain

Simulation, PERT, Queueing, Inventory Models

Creating the Model
Modeling Assumptions
Independent Variables

Dependent Variables

Mathematical Model

Output and Analysis

What is a “Computer Model”?
A set of mathematical relationships and logical assumptions implemented in a computer as an abstract representation of a real-world object or phenomenon  Spreadsheets provide the most convenient way for business people to build computer models

Why Spreadsheets?
Merge data, formulas, and graphics into a single model  Immediate feedback of model changes as a function of input changes  User friendly with tremendous built-in computing power and flexibility/expandability  Used by millions, including managers  Availability!
Ref: Vazsonyi, Interfaces Vol 23, No 5, 1993

Math Models & Spreadsheets

At a conceptual level, most spreadsheet models are very similar to our generic mathematical model: Y= f(X1, X2, …, Xn)


Most spreadsheets have cells representing inputs (or Xi) to which mathematical functions, f(.), are applied to compute some bottom line performance measure (or Y)

The Problem Solving Process
Formulate & Implement Model

Identify Problem

Analyze Model

Test Results

Implement Solution

Unsatisfactory Results Decision Making Portion

The Decision Making Process
Structuring the Problem Define the Problem Identify Alternatives Determine Criteria

Analyzing the Problem Evaluate Alternatives Choose Alternative

The Decision Making Process
Structuring the Problem Define the Problem
Identify Alternatives

Determine Criteria

Analyzing the Problem Qualitative Analysis

Summary Evaluation Quantitative Analysis

Make the Decision

Decisions vs. Outcomes

Good decisions do not always lead to good outcomes...
– If the route you normally take to work is congested you may (rightly) decide to take an alternate route. If the alternate road is closed you may be late for class (a bad outcome), but that doesn’t mean you made a bad decision.


A structured, modeling approach to decision making helps us make good decisions, but can’t guarantee good outcomes

End of Chapter 1