# Modelling and Business Decisions by lifemate

VIEWS: 3 PAGES: 41

• pg 1
```									Modelling and Business
Decisions
Robert Zimmer
Room 6, 25 St James
This course is about building models
and making decisions
► It is about organising information
questions
► It is about applying powerful mathematical
models (I might try to teach you some
maths when you aren’t looking but that is
incidental)
Example of a decision:
should I have another beer?
► Organising    Information:
   How much money I have
   How much money a beer costs
   How drunk am I?
   Do I have to drive?
   How fat am I?
   How much do I like the people in the pub?
   How much do I like the people at home?
► What-if   questions
 What if I can convince the barman to give me a
half-price beer
 What if I decide that I like these people twice
as much as I did
…
► The Maths here would be difficult
► Luckily for us, Microsoft has implemented a
special beer decision function: the famous
Beer Decision Algorithm within excel
► So all we need to do is put all of our figures
on a spreadsheet, press the = button to pull
up the formula function and type
BDA(A1,A2,….)
or whatever
► Then   Bob’s our uncle. We know whether to
have the drink or not
► Because we did this on a spreadsheet it is
flexible enough for us to change the
parameters (that is, inputs) and find out if
we should change our decisions
Another Question
► What is the most money I am prepared to
pay for this drink? That is at what price
does the pleasure of the drink become less
than its price?
Some more questions
► What  is the geometric shape of all the
points at which the pleasure of the beer
exactly matches the pain of the payment?
► How will my pleasure, my weight, and my
mental state compare if instead of a beer I
have chips?
► or do my Java coursework?
After this course you will be able to

► Some names for what we are studying:
operations research, decision science,
management science

► Our   tool of choice: the humble spreadsheet.
It’s not just beer: example 1
► Merril   Lynch
 5 million customers
 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
It’s not just beer: example 2
► Jan   de Wit Co.
 Brazil’s 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
► 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
Our modus operandi
1.   Make a mathematical model
2.   Implement it in excel
3.   Play with it to find out how the answers
depend on the input variables
4.   Use the inbuilt mathematical functions to
do complicated analyses
5.   Use the excel graphic packages to make
diagrams
How you will learn to do this
► Option  1: You will listen to me, go to the
labs, and not think about the subject in
between
► Option 2: You will not listen to me and stay
home
► Option 3: You will listen to me and do
everything I tell you to
► Option 4: You won’t leave off your
modelling practice, even to listen to me
Models
•   Everyone uses models to make decisions.
•   Types of models:
– Mental (arranging furniture)
– Physical/Scale (aerodynamics, buildings)
– Mathematical (what we’ll be studying)
Mathematical Models
Profit = Revenue - Expenses
or
Profit = f(Revenue, Expenses)
or
Y = f(X1, X2)
Characteristics of Models
•   Models are simplified versions of the things
they represent
•   A valid model accurately represents the
relevant characteristics of the object or
decision being studied
•   So a large part of the art: is what is relevant
and what can be abstracted away
Benefits of Models
•   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.
Maths
Y = f(X1, X2, …, Xn)
► 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
Identify   Formulate &
Problem     Implement    Analyze       Test     Implement
Model       Model       Results    Solution

unsatisfactory
results
1.4 Seven-Step Modeling Process

Step 1: Problem Definition - Define the
problem including the objectives and the
parts of the organization that must be
studied.
Step 2: Data Collection – Collect the data to
estimate the value of parameters that affect
the organization’s problem.
Step 3: Model Development – Develop an
analytical or simulation model.
Step 4: Model Verification – Determine
whether the model is an accurate
representation of reality.
Step 5: Optimization and Decision Making –
Given the model and a set of possible decisions,
the analyst must choose the decision that best
meets the organization’s objectives.
Step 6: Model Communication to Management
– The analyst presents the model and the
recommendations to the organization.
Step 7: Model Implementation – If the
organization accepts the model then the analysts
assists with implementation. Implementation must
be monitored constantly to ensure that the model
enables the organization to meets its objectives.
► 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!
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 you’ve 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
► Nowsuppose you’ve 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
Payoffs

Alternative A                  \$1,500

Initial state
\$2,000
Alternative B
(Flip coin)                  \$1,000
Tails (50%)
Chapter 2
Modeling
2.1 Introduction
► Excel skills are critical. There is an Excel
tutorial on the CD-ROM that accompanies
the book.
► Excel’s features will provide insight into
Concepts and Best Practices
► Most  mathematical models, including
variables, and outputs.
► The model inputs are given values that are
fixed.
► The decision variables are values that a
decision maker has control over.
► The model outputs are the ultimate values
of interest.
► Spreadsheet    modeling is the process of
entering the inputs and decision variables into
a spreadsheet and then relating them
appropriately, by means of formulas, to
obtain the outputs.
► Once a model is created there are several
directions in which to proceed.
 Sensitivity analysis to see how one or more
outputs change as selected inputs or decision
variables change.
 Finding the value of a decision variable that
maximizes or minimizes a particular output.
 Create graphs to show graphically how certain
parameters of the model are related.
► Good  spreadsheet modeling practices are
essential.
► Spreadsheet models should be designed with
► Several features that improve readability
include:
►A  clear logical layout to the overall model
► Separation of different parts of a model
► Clear headings for different sections of the model
► Liberal use of range names
► Liberal use of formatting features
► Liberal use of cell comments
► Liberal use of text boxes for assumptions, lists or
explanations
Example 2.1 – Building a
Model
► Randy    Kitchell is a NCAA t-shirt vendor. The
fixed cost of any order is \$750, the variable
cost is \$6 per shirt.
► Randy’s selling price is \$10 per shirt, until a
week after the tournament when it will drop
to \$4 apiece. The expected demand at full
price is 1500 shirts.
► He wants to build a spreadsheet model that
will let him experiment with the uncertain
demand and his order quantity.
Ex. 2.1(cont’d) - Building a Model
► The logic behind the model is simple. An
Excel IF function will be used.
► Inthis model the profit is calculated with
the formula

Profit = Revenue – Cost

and the Cost = 750 + 6*B4
Revenue
Case 1:
Demand outstrips order (B3 > B4)
In that case everything gets sold for 10
dollars
Revenue is then simply 10*B4
(since B4 is the number ordered)
Revenue
Case 2:You have ordered too many.
That is order (B3) is less than peak demand
Then you can only sell B3 at 10 dollars and
the rest (B4-B3) at 4 dollars

Revenue = 10*B3+4*(B4-B3)
Revenue Formula
Revenue =

IF(B3>B4,10*B4,10*B3+4*(B4-B3))
Profit Formula
Profit =
IF(B3>B4,10*B4,10*B3+4*(B4-B3)) –
(750 + 6* B4)
More Flexibility
Ex. 2.1(cont’d) - Building a Model
► The  formula can be rewritten to be more
flexible.
=-B3-
B4*B9+IF(B8>B9,10*B8+B6*(B9-B8))