Modelling and Business Decisions by lifemate


									Modelling and Business
       Robert Zimmer
     Room 6, 25 St James
This course is about building models
       and making decisions
► It is about organising information
► It is about being able to ask what-if
► It is about applying powerful mathematical
  models (I might try to teach you some
  maths when you aren’t looking but that is
         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
  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
      answer questions like this

► 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
   16,000 financial advisors
   Developed a model to design product features
    and pricing options to better reflect customer
   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
   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
   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
   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
► Option 2: You will not listen to me and stay
► 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
•   Everyone uses models to make decisions.
•   Types of models:
    – Mental (arranging furniture)
    – Visual (blueprints, road maps)
    – Physical/Scale (aerodynamics, buildings)
    – Mathematical (what we’ll be studying)
      Mathematical Models
Profit = Revenue - Expenses
   Profit = f(Revenue, Expenses)
   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.
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

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

         Alternative A                  $1,500

Initial state
                          Heads (50%)
          Alternative B
           (Flip coin)                  $1,000
                          Tails (50%)
     Chapter 2
Introduction to Spreadsheet
            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
  solving real business problems.
  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
► 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
► Spreadsheet models should be designed with
  readability in mind.
► Several features that improve readability
     ►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
    Example 2.1 – Building a
► 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
Case 1:
Demand outstrips order (B3 > B4)
In that case everything gets sold for 10
  Revenue is then simply 10*B4
(since B4 is the number ordered)
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 =

           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
 ► It can be made more readable by using range
   names. The formula would then read
   Variable_cost*Order + IF(Demand >
   Order, Selling_price*Order,
   10*Demand+Salvage_value* (Order-

To top