Spreadsheet Modeling

1
Spreadsheet Modeling Decision Analysis

• A Practical Introduction to Management Science
• 4th edition
• Cliff T. Ragsdale

2
Introduction to Modeling Problem Solving
Chapter 1
3
Introduction

• 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 todays
  managers.

4
What is Management Science?

• A field of study that uses computers, statistics,
  and mathematics to solve business problems.
• Also known as
• Operations research
• Decision science

5
Home Runs in Management Science

• Merril Lynch
• 5 million customers
• 16,000 financial advisors
• Developed a model to design product features and
  pricing options to better reflect customer value
• Benefits
• 80 million increase in annual revenue
• 22 billion increase in net assets

6
Home Runs in Management Science

• Jan de Wit Co.
• Brazils largest lily farmer
• Annually plants 3.5 million bulbs and produces
  420,000 pots 220,000 bundles of lilies in 50
  varieties.
• Developed model to determine what to plant, when
  to plant it, and how to sell it.
• Benefits
• 26 increase in revenue
• 32 increase in contribution margin

7
Home Runs in Management Science

• NBC
• Must determine program schedules
• Schedules must meet advertisers demographic and
  cost requirements
• Developed optimization model to determine optimal
  timing and pricing of commercials
• Benefits
• 50 million increase in annual revenue

8
Home Runs in Management Science

• Samsung Electronics
• Leading DRAM manufacturer
• Semiconductor facilities cost 2-3 billion
• High equipment utilization is key
• Developed comprehensive planning and scheduling
  system to control WIP
• Benefits
• Cut cycle times in half
• 1 billion increase in annual revenue

9
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 of
  phenomenon.
• Spreadsheets provide the most convenient way for
  business people to build computer models.

10
The Modeling Approach to Decision Making

• Everyone uses models to make decisions.
• Types of models
• Mental (arranging furniture)
• Visual (blueprints, road maps)
• Physical/Scale (aerodynamics, buildings)
• Mathematical (what well be studying)

11
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

12
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 real-world
  counterparts.
• Feasibility - models can be used to do things
  that would be impossible.
• Models give us insight understanding that
  improves decision making.

13
Example of a Mathematical Model

• Profit Revenue - Expenses
• or
• Profit f(Revenue, Expenses)
• or
• Y f(X1, X2)

14
A Generic Mathematical Model
Y f(X1, X2, , Xn)
Where
Y dependent variable (aka
bottom-line performance measure) Xi
independent variables (inputs having an impact on
Y) f(.) function defining the relationship
between the Xi Y
15
Mathematical Models Spreadsheets

• Most spreadsheet models are very similar to our
  generic mathematical model

Y f(X1, X2, , Xn)

• Most spreadsheets have input cells (representing
  Xi) to which mathematical functions ( f(.)) are
  applied to compute a bottom-line performance
  measure (or Y).

16
Categories of Mathematical Models
Model Independent OR/MS Category Form of
f(.) Variables Techniques

• Prescriptive known, known or under LP, Networks,
  IP,
• well-defined decision makers CPM, EOQ, NLP,
• control GP, MOLP
• Predictive unknown, known or under Regression
  Analysis,
• ill-defined decision makers Time Series
  Analysis,
• control Discriminant Analysis
• Descriptive known, unknown or Simulation,
  PERT, well-defined uncertain Queueing,
  Inventory Models

17
The Problem Solving Process
Formulate Implement Model
Identify Problem
Analyze Model
Test Results
Implement Solution
unsatisfactory results ???
18
The Psychology of Decision Making

• Models can be used for structurable aspects of
  decision problems.
• Other aspects cannot be structured easily and
  require intuition and judgment.
• Caution Human judgment and intuition is not
  always rational!

19
Anchoring Effects

• Arise when trivial factors influence initial
  thinking about a problem.
• Decision-makers usually under-adjust from their
  initial anchor.
• Example
• What is 1x2x3x4x5x6x7x8 ?
• What is 8x7x6x5x4x3x2x1 ?

20
Framing Effects

• Refers to how decision-makers view a problem from
  a win-loss perspective.
• The way a problem is framed often influences
  choices in irrational ways
• Suppose youve been given 1000 and must choose
  between
• A. Receive 500 more immediately
• B. Flip a coin and receive 1000 more if heads
  occurs or 0 more if tails occurs

21
Framing Effects (Example)

• Now suppose youve been given 2000 and must
  choose between
• A. Give back 500 immediately
• B. Flip a coin and give back 0 if heads occurs
  or give back 1000 if tails occurs

22
A Decision Tree for Both Examples
23
Good Decisions vs. Good Outcomes

• Good decisions do not always lead to good
  outcomes...

• A structured, modeling approach to decision
  making helps us make good decisions, but cant
  guarantee good outcomes.

24
End of Chapter 1