Spreadsheet Modeling & Decision Analysis: - PowerPoint 2 by jAPU0Zk


									Introduction to Modeling
   & Problem Solving

 We face numerous decisions in life &
 We can use computers to create
  models and 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 solve
  business problems.
 Also known as:
  – Operations research
  – Decision science
               Home Runs
         in Management Science
 Waste Management
  – Leading waste collection company in North
  – 26,000 vehicles service 20 million residential &
    2 million commercial customers
  – Developed vehicle routing optimization system
  – Benefits:
      Eliminated 1,000 routes
      Annual savings of $44 million
 Changing the real truck routes just to see
  what happens would be foolish.
 Instead, they used a model
 Models are just representations of real
 Groups: think of 5 models
             The Modeling Approach
               to Decision Making
 Everyone uses models to           exams   Homew ork

                                                        GBS220 Quantitative Methods in Business

  make decisions.                                             0                                                                                         8/22/2006 10:43

 Types of models:
                                                              Homework and projects (100 possible)
                                                              Watch out. Homew ork not turned in w hen I call for it in class w ill
                                                              not usually be accepted.
                                                                                               Points avail Points earned
                                                                                               to date      to date
                                                              Linear programming               20
                                                              Probability                      20
                                                              Decision analysis                20

   – Mental (arranging furniture)
                                                              Project management               20
                                                              Inventories                      20

   – Visual (blueprints, road                                 Totals                                      100                     0

                                                              Exams (400 possible)

                                                                                               Points avail Points earned
                                                                                               to date      to date
                                                                           1                           100
                                                                           2                           100

   – Physical/Scale
                                                                           3                           100
                                                                           4                           100
                                                              Totals                                   400                0

                                                                                 Grades                                               Total points earned to date (est.)     0.00
                                                                    A                90%              450.00                          Total points avail to date (est)     500.00

     (aerodynamics, buildings)                                      B
                                                                                                                                      Grade to date (est)                  0.00%

   – Mathematical (what we’ll
     be studying: net income,
     dow jones stock average)
 Spreadsheets
     What is a “Computer Model”?

 A set of mathematical relationships called
  “functions,” which are based on logical
  assumptions and programmed into a
  computer as a representation of a real-
  world object or phenomenon.
        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
         Benefits of Modeling
 Economy - It is often less costly to
  analyze decision problems using
 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.
  Spreadsheets and Functions
 In pairs, create a spreadsheet that
  calculates profit, based on revenues and
  expenses, that is:
      Profit = Revenue - Expenses
   Spreadsheets and functions
 Your spreadsheet is a mathematical model and
  the spreadsheet is the tool business people
  usually use.
 Business people like spreadsheets because
  they are a lot like accounting worksheets.
 However, mathematicians have a different
  approach. Instead of spreadsheets, they specify
  mathematical models as functions and use
  mathematical notation.
 Moving from spreadsheets to
 mathematical function notation
     C1 = A1 * B1
     Profit = Revenue - Expenses
     Profit = f(Revenue, Expenses)
     Y=   f(X1, X2)
      A Generic Mathematical Model

               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
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).
     The Problem Solving Process

Identify   Formulate &
Problem     Implement    Analyze       Test     Implement
              Model       Model       Results    Solution

Good Decisions vs. Good Outcomes
 Good decisions do not always lead to good

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

To top