Modelling and Business Decisions

1
Modelling and Business Decisions

• Robert Zimmer
• Room 6, 25 St James

2
This course is about building models and making
decisions

• It is about organising information
• It is about being able to ask what-if questions
• It is about applying powerful mathematical models
  (I might try to teach you some maths when you
  arent looking but that is incidental)

3
Example of a decisionshould 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?

4

• 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

5

• 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

6

• Then Bobs 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

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

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

9
After this course you will be able to answer
questions like this

• Some names for what we are studying operations
  research, decision science, management science
• Our tool of choice the humble spreadsheet.

10
Its not just beer example 1

• 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

11
Its not just beer example 2

• 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

12

• 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

13
Our modus operandi

• Make a mathematical model
• Implement it in excel
• Play with it to find out how the answers depend
  on the input variables
• Use the inbuilt mathematical functions to do
  complicated analyses
• Use the excel graphic packages to make diagrams

14
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 wont leave off your modelling
  practice, even to listen to me

15
(No Transcript)
16
Models

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

17
Mathematical Models

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

18
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

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

20
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

21
Formulate Implement Model
Identify Problem
Analyze Model
Test Results
Implement Solution
unsatisfactory results
22
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
  organizations 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.

23

• 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 organizations 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.

24

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

25
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

26

• 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

27
(No Transcript)
28
Chapter 2

• Introduction to Spreadsheet Modeling

29
2.1 Introduction

• Excel skills are critical. There is an Excel
  tutorial on the CD-ROM that accompanies the book.
  
• Excels features will provide insight into
  solving real business problems.

30
2.2 Basic Spreadsheet Modeling Concepts and Best
Practices

• Most mathematical models, including spreadsheet
  models, involve inputs, decision 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.

31

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

32

• Good spreadsheet modeling practices are
  essential.
• Spreadsheet models should be designed with
  readability in mind.
• 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

33
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.
• Randys 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.

34
Ex. 2.1(contd) - Building a Model

• The logic behind the model is simple. An Excel IF
  function will be used.

35

• In this model the profit is calculated with the
  formula
• Profit Revenue Cost
• and the Cost 750 6B4

36
Revenue

• Case 1
• Demand outstrips order (B3 gt B4)
• In that case everything gets sold for 10 dollars
• Revenue is then simply 10B4
• (since B4 is the number ordered)

37
Revenue

• Case 2You 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 10B34(B4-B3)

38
Revenue Formula

• Revenue
• IF(B3gtB4,10B4,10B34(B4-B3))

39
Profit Formula

• Profit
• IF(B3gtB4,10B4,10B34(B4-B3))
• (750 6 B4)

40
More Flexibility
41
Ex. 2.1(contd) - Building a Model

• The formula can be rewritten to be more
  flexible.-B3-B4B9IF(B8gtB9,10B8B6(B9-B8))
• It can be made more readable by using range
  names. The formula would then read-Fixed_order_c
  ost-Variable_costOrder IF(Demand gt Order,
  Selling_priceOrder, 10DemandSalvage_value
  (Order-Demand)