MIS 646 Business Analysis and Modeling - PowerPoint PPT Presentation

1 / 49
About This Presentation
Title:

MIS 646 Business Analysis and Modeling

Description:

... such as Prof's Winston, Ragsdale, Savage, Grossman and a number of others ... do not fall in love with data. How did the data get where you got it from? ... – PowerPoint PPT presentation

Number of Views:209
Avg rating:3.0/5.0
Slides: 50
Provided by: marki152
Category:

less

Transcript and Presenter's Notes

Title: MIS 646 Business Analysis and Modeling


1
MIS 646 Business Analysis and Modeling
  • The Art Craft of Modeling

2
Objectives
  • Technique Review Absolute and relative cell
    addressing
  • FunWithFunctions.xls
  • Introduce some general mathematical modeling
    principles
  • Introduce spreadsheet based modeling through
    examples based on simple yet realistic business
    problems
  • Continue to add to growing level of Excel
    expertise in context of business modeling

3
An acknowledgement
  • I know this course will be hard for some of you
  • not a plug and chug course
  • be creative, be a problem solver
  • its good for classes to be hard, to push your
    intellectual envelope (a personal example)
  • Homework probs. often similar to some example in
    the chapter, and sometimes NOT
  • the text is not a cookbook
  • real problems rarely yield to cookbook answers
  • I learned (and have used) much from
  • Prof. Stephen Pollock (IOE 640 Modeling Studio)
  • Prof. Stephen Powell (new book on art and craft
    of spreadsheet modeling)
  • Other spreadsheet modeling pioneers such as
    Profs Winston, Ragsdale, Savage, Grossman and a
    number of others

4
What will endure?
  • Barrage of improvement techniques, tools and
    philosophies
  • Quality circles, TQM, BPR, just-in-time, Japanese
    production methods, Lean, Six Sigma
  • No magic, all have something to contribute
  • Scientific method
  • Observe, classify, theoretical conjecture,
    experimental refutation, REPEAT
  • Modeling, improvement, experimentation,
    evaluation
  • Common sense and holistic view
  • Intuition, understanding underlying system,
    synthesis skills, working knowledge of the basics
    (physics of the process, statistical thinking,
    psychology, business fundamentals)
  • Balancing the quantitative and qualitative
  • Systems analysis

5
Models provide a bridge
Simplified abstraction of reality
Capture essence of problem
Model
Problem
Excel Workbook (calculations)
Interpretation
Decisions
Analysts World
Real World
From Monahan, G., Management Decision Making,
Cambridge University Press, 2000
6
Why do we model for decision making?
  • Building model forces detailed examination and
    thought about a problem
  • structures our thinking
  • must articulate our assumptions, preconceived
    notions
  • Model building may illuminate solution without
    actually using the model
  • Searching for general insights
  • form of relationship between key variables
    involved in decision
  • importance of various parameters on decisions
  • Example Mystery data
  • Looking for specific numeric answers to a
    decision making problem
  • If we add 1 tech between 7a-3p, how much
    reduction can we expect in test turnaround time?
  • Foam

Serious Play How the World's Best Companies
Simulate to Innovateby Michael Schrage, Tom
Peters
7
A Simple Modeling Process
Problem definition
questions about real system
necessary corrections and enhancements
Model construction
assumptions
Verification and Validation Testing
predictions
influence diagram mathematical formulas computer
program spreadsheet
Verification Making sure the model does what you
think it does and Validation that it captures
reality well enough to be useful
test cases walk-throughs compare with real system
Exercise the model
8
Hierarchy of Modeling Skills
Business analytics (mgt science) tools and
applications
Advanced modeling skills
Basic modeling skills
Numeracy and logical skills
9
Basic Modeling Concepts
Inputs
relationships
Outputs
roles in model
constraints
Decision Variables
relationships
FYI The above is NOT an influence chart (youll
see in a minute)
10
Prep Example 2.1 takeaways
  • Inputs, output, decision variables
  • Basic spreadsheet design
  • IF, AND, OR
  • DONT HARD CODE VALUES!
  • Range names
  • Formatting to aid model understanding
  • Layout and documentation

11
Prep Example 2.2 takeaways
  • Numeric and graphical what if?
  • Absolute and relative cell addressing for
    effective formula copying
  • Distinguishing base input cells
  • Thinking about structure of outputs
  • Row vs. column orientation
  • Charting basics

12
In-Class Modeling CaseFinding Break-Even Point -
Handout
  • The Great Threads Company sells hand-knit
    sweaters. Great Threads is planning to print a
    brochure of its products and undertake a direct
    mail campaign.
  • The cost of printing the brochure is 20,000 plus
    0.10 a catalog. The cost of mailing each catalog
    is 0.15. In addition, the company will include
    direct reply envelopes in its mailings. It incurs
    0.20 in extra cost for each direct mail envelope
    that is used by a respondent.
  • The average size of a customer order is 40, and
    the companys variable cost per order averages
    around 80 of the orders value.

13
Great Threads Case -- continued
  • The company plans to mail 100,000 catalogs. It
    wants to develop a spreadsheet model to answer
    the following questions
  • How does a change in the response rate affect
    profit?
  • For what response rate does a company break even?
  • If the company estimates a response rate of 3,
    should it proceed with the mailing?
  • How does the presence of uncertainty affect the
    usefulness of the model?

14
Heuristic 1 - Simplify the Problem
  • Focus on the connections between the key
    decisions to be made and the outcomes of those
    decisions.
  • What are the central trade-offs that make these
    decisions difficult and try to build a model that
    helps explore those tradeoffs.

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
15
Heuristic 2 - Decompose the Problem
  • Can we break up the problem in such a way that we
    create submodels for each part?
  • What is linkage between submodels?

16
Heuristic 3 - Identify inputs and outputs
  • Categorizing variables
  • inputs, parameters
  • decision variables
  • performance measures, outputs

Dont worry so much at this point about how
youre going to get from the inputs to the
outputs.
17
Ex0203-BreakevenAnalysis-template.xls
The relationships between inputs and outputs are
relatively straight forward in this simple model.
Lets build it.
18
In-Class Model Building
  • Modeling principles
  • Previous and following slides have a number of
    general modeling principles and heuristics
  • We will discuss them while we build the
    BreakEvenAnalysis
  • Download Ex0203-BreakevenAnalysis-template.xls
  • Spreadsheet design and development practices
  • Excel techniques
  • Recommend reading/working/understanding all the
    Examples in Chapter 2 (Ex 2.1 Ex 2.6)
  • Will help with HW1 and future HWs
  • Ill post my annotated versions

19
More on inputs and outputs
  • Use simple graphical techniques such as influence
    charts to help you conceptualize which inputs
    affect which outputs.
  • Many variables will be intermediate inputs and/or
    outputs i.e. they will end up as formulas
  • Base inputs are those that you will enter as
    numbers
  • Draw simple graphs that might show shape of
    outputs as function of inputs.

Visualize
20
Starting to Model Influence Charts
Base input
Decision variable
Intermediate variable
Output variable
influential relationship
Lets first build one for very simple example
(Prob 2.20 on p64) and then build influence
diagram for Copy Shop problem that was assigned
as a homework problem.
21
Creating influence charts - Handout
  • Somewhat of loosely structured approach to
    sketching model relationships
  • Each variable becomes a shape
  • No variable should appear more than once
  • Start with the final output and work backwards
  • What immediate inputs needed to get final output?
  • Continue to work backwards from these
    intermediate inputs until you reach base inputs
    (e.g. numbers) asking same question
  • Arrow pointing from shape A to shape B means A
    influences B
  • Dont worry about details of how A influences B
  • The final output should NOT have any outgoing
    arrows. All other shapes should have at least one
    arrow coming out of it
  • Base inputs will NOT have any incoming arrows
  • Make up special symbols for special quanitities
  • I use double lined shape borders for uncertain
    variables

22
Fixed cost
Variable cost
Selling price
Sales volume
Total cost
Revenue
Profit
Possible model enhancements to capture more
complex relationships?
23
  • Plan general structure and format of model
  • use influence diagrams for logical structure
  • blank spreadsheet like a blank canvas plan
    the physical structure
  • Enter inputs (parameters) and decision variables
  • Develop relationships between them via formulas
    to the model outputs
  • Then we can exercise the model
  • use it to explore situation of interest
  • What If? or Whats Best?

24
Exercising the ModelThings we might do
  • How do input and/or decision variable values
    affect outputs (what if? and sensitivity
    analysis)?
  • Find values of decision variables that minimize
    or maximize the outputs (optimization)
  • Create graphic or symbolic representation of
    model parameter relationships (visualization,
    data exploration)
  • Communicate with others about the nature of the
    problem
  • Example AutoLeaselator.xls

25
A few spreadsheet design tips
  • Clear, logical layout of overall model
  • Sketch out on paper if that helps you
  • Separation of different model parts across
    multiple worksheets or different sections of same
    worksheet
  • Clear headings for different model sections and
    the inputs, outputs and decision variables
  • DONT HARD CODE VALUES INTO FORMULAS!
  • Isolate your input parameters so that it is clear
    that these values can be changed by the user
    (which might be you)
  • Start small get individual sections designed
    and working, add complexity as needed
  • Design for use
  • Anticipate how you or others will use the
    spreadsheet
  • Think about navigation
  • Think about extensibility
  • Think about data integrity
  • Think about protecting worksheet from unwanted
    changes

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
26
A few spreadsheet design tips
  • Keep it simple
  • Break up massive formulas into pieces
  • Use range names
  • Design for communication
  • Use visual cues to guide user
  • Judicious use of formatting and conditional
    formatting
  • Dynamic labels (formulas that return different
    text values depending on value in other cells)
  • Document important data and formulas
  • Cell comments and text boxes
  • Explanation sheets
  • Range names are a form of documentation

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
27
A few spreadsheet construction tips
  • Build one module at a time
  • Do formula auditing as you go by predicting
    results of each formula
  • Learn to be a formula copy and paste expert
  • Thoroughly understand relative, absolute, and
    mixed cell referencing
  • Understand how range names behave when copied
    (even across sheets)
  • Try to plan structure of sheet to take advantage
    of copying and pasting
  • Use the function wizard to learn about and ensure
    correct function syntax
  • Use range names but dont range name everything
  • Use contrived input data to make any error very
    noticeable

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
28
A few spreadsheet testing tips
  • Check plausibility of numbers (smell test)
  • Make rough estimates, calculator, extreme cases
  • Check formula correctness
  • Manually (by yourself and others)
  • Display individual cell references
  • Display all formulas
  • Use Auditing Toolbar
  • Use built in Error Checking
  • Check plausibility of model
  • Use your model with a variety of inputs
  • Does its performance pass the smell test?
  • Do sensitivity analysis (we will talk much more
    about this)

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
29
Heuristic 4 - Build a prototype and refine it
  • Toys are good
  • Try to get a simple model working.
  • By "working" we mean, it takes some inputs, does
    some sort of calculation and creates some
    outputs.
  • Make whatever assumptions are needed to try to
    get a model working.
  • Document these assumptions.
  • Use metaphors, analogies, similarities

30
Heuristic 5 - Identify Parameters and Perform
Sensitivity Analysis
  • One of the primary reasons we build models is to
    change certain values and see what happens to
    outputs of interest.
  • Some parameters we might do sensitivity analysis
    on because we are unsure of their values
  • Other parameters we might do sensitivity analysis
    on because we actually have some control over
    their value
  • Excel lends itself well to sensitivity analysis
    via copying formulas, data tables, graphs,
    scenarios, and even simulation
  • Parameterization call it alpha
  • Sales f(a,Price),
    Example Sales 1000-aPrice

31
Advanced modeling skills
  • Make heroic assumptions
  • Assume you know something you DONT
  • Assume something is true that you know is FALSE
  • Imagine the answer think backward from the
    desired result
  • What will the outcome look like? Is it a number,
    a graph, various tables of numbers, a
    recommendation?
  • Model the data be skeptical
  • do not fall in love with data
  • How did the data get where you got it from?
  • Separate idea generation from evaluation
  • Quiet the critic
  • Accept that modeling may feel like muddling
    through
  • many right answers

32
Heuristic 6 - Separate the creation of ideas from
their evaluation.
  • Before starting to build the first prototype, you
    will think of a bunch of reasons why the first
    model is "wrong ignore that voice.
  • When in the idea phase (divergent thinking) don't
    do too much evaluation as you may miss an
    insightful approach.
  • It's analogous to common brainstorming techniques.

33
Heuristic 7 - Work backward from the desired
answer
  • Try to imagine what the "answer(s)" or
    "deliverable(s)" will look like and then figure
    out how to get there.
  • Back in to the answer vary the inputs to get
    the answer you want
  • Example Goal Seek to find break even point
  • Will the answer be a single number, a graph, a
    table of numbers, a probability, a range, some
    combination of these?
  • Invoke Heuristic 7 and don't criticize your
    "answer" too much as being undoable.
  • This technique can help overcome modelers block
    when you just aren't sure how to even get
    started.
  • You may even try to create "The Magic One Pager".

34
Heuristic 8 - Focus on model structure, not on
data collection
  • Don't get too enamored with data.
  • It's retrospective.
  • Collection biases (IS and/or human)
  • Data available not necessarily data needed
  • Don't let lack of data stop your modeling.
  • Often useful insights can come from good models
    and limited data.
  • Building a model will help focus your data
    collection.
  • Model building can proceed while working on data
    collection.
  • Just the process of building a model may obviate
    the need for data collection or even for more
    modeling.

35
Heuristic 9 Hypothesize some mathematical form
for an important input-output relationship.
(http//www.analyzemath.com/precalculus.html)
Advantages of using simple functions instead of
the data?
Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
36
Linear response (one variable)
  • The linear function is easy to understand.
  • Its graph is a straight line.
  • When x changes by 1 unit, y change by b units.
  • The constant a is called the intercept, and b is
    called the slope
  • Often applicable within a limited range of x even
    if not globally applicable

37
Power Function
  • The power function is a curve except in the
    special case where the exponent b is 1. Then it
    is a straight line. The shape of the curve
    depends primarily on the exponent b.
  • If b gt1, y increases at an increasing rate as x
    increases.
  • If 0 lt b lt 1, y increases, but at a decreasing
    rate, as x increases.
  • If b lt 0, y decreases as x increases.
  • An important property of the power curve is that
    when x changes by 1, y changes by a constant
    percentage, a constant percentage, and this
    percentage is approximately equal to b.

38
Exponential function
  • The exponential function also represents a curve
    whose shape depends primarily on the constant b
    in the exponent.
  • If b gt 0, y increases as x increases.
  • If b lt 0, y decreases as x increases.
  • An important property of the exponential function
    is that if x changes by 1 unit, y changes by a
    constant percentage, and this percentage is
    approximately equal to 100 x b.
  • Another important note about the equation is that
    it contains e, the special number 2.7182. In
    Excel, e to any power can be calculated by the
    EXP function.

39
The Golf Clubs Pricing Problem
  • This example is divided into three parts
  • estimating the relationship between price
    demand
  • creating the profit model
  • Optimize price to maximize profit
  • Visualizing and modeling demand versus price

40
Case 1. Retirement Planning from The Art of
Modeling with Spreadsheets, Powell. S.G. and
Baker, K.R., John Wiley and Sons, Inc., USA, 2004
The client is currently forty-six years old, with
an income of about 126,000 per year. His goal is
to retire between ages sixty-two and sixty-seven
and to have enough savings to live comfortably in
about the same fashion he does now (with some
money available for expanded travel). The
clients accumulated savings for retirement total
137,000. His employer contributes around 10,000
per year into the retirement fund, while he has
been contributing 7,500. How much should he be
saving?
41
Errors in Spreadsheet Models
  • Many research studies have found frightening
    levels of error rates in important spreadsheets
    used in numerous industries
  • What we know about spreadsheet errorshttp//pubpa
    ges.unh.edu/rbg/ss_research/papers/panko_2000_wha
    tknow.pdf
  • http//sprig.section.informs.org/
  • http//www.sciencenews.org/articles/20051217/matht
    rek.asp
  • Spreadsheet oops http//www.eusprig.org/stories.ht
    m
  • The Spreadsheet Projecthttp//mba.tuck.dartmouth.
    edu/spreadsheet/
  • Spreadsheet Analyticshttp//www.usfca.edu/researc
    h/spreadsheetanalytics/
  • Nature of end-user spreadsheet development
  • non-IS developers, ad-hoc, iterative, under time
    pressure
  • spreadsheets are very flexible development
    environment
  • designed for personal use

42
Many dimensions of model quality
  • Modularity
  • Reusability
  • Automation
  • Clarity
  • Flexibility
  • Power
  • Maintainability
  • Elegance
  • Usability
  • Aesthetics
  • Scope
  • Validity
  • Correctness
  • Acceptability

43
Uncertainty The Gorilla in the Room
  • Were ignored uncertainty so far
  • Fun with Uncertainty next time
  • Probability and statistics are the language of
    uncertainty
  • DistributionReview.xls lets do Discrete tab
  • Sensitivity Analysis What matters in this
    decision?
  • which variables might I want to explicit model as
    uncertain and which ones might I just as well fix
    to my best guess of their value?
  • On which variables should we focus our attention
    on either changing their value or predicting
    their value?
  • Well use data tables, graphs, TopRank add-in
  • Monte-carlo simulation
  • Explicit modeling of uncertainty
  • _at_Risk makes this easy within spreadsheets

44
The 7-11 Problem (Chapter 1 of PMS)
  • One cash register
  • Worried about customer wait times
  • Considering new cash register technology that
    could speed up checkout times
  • Considering additional cash register stations

45
7-11 Influence Diagram
  • Major output variable or performance measure?
  • Input variables?
  • Which inputs are likely decision variables?
  • Which inputs influence outputs or other inputs?
  • Model? Where do we need a model? Which
    relationship is the most complex?

Lets build it and analyze it.
46
7-11 StaffingUsing a Descriptive Queueing Model
(1) Inputs
Given these
Predict these
(2) Queueing Model(s)
Mathematical equations
(3) Outputs
Example_7-11.xls
47
Numeracy and logical skills
  • Make quick rough numerical estimates
  • 7-11 about how long are times at cash register?
  • Use special cases to test limits of calculation
  • 7-11 arrival rateservice rate
  • Check consistency of units
  • 7-11
  • smell test
  • 7-11 Does A? resulting in W? smell right

48
Reality ChecksNeither building nor consuming
models is easy
  • Model formulation and data collection are
    intertwined
  • Entire process filled with feedback loops and
    iteration
  • Modeling is a craft and is far from
    straightforward
  • Building models can be complex and time consuming
  • Presenting results from modeling/analysis efforts
    can be very challenging
  • Models can be given unjust credibility
  • Massive amounts of time can be spent on
    collecting, extracting, cleaning and massaging
    data
  • Many people do not understand nor trust
    mathematical models
  • Many factors beyond model results affect real
    decision making and implementation of change
  • Often key data simply does not exist
  • Paralysis by analysis

A real model used for managing call centers
49
A 7-step (Idealized) Modeling Process (PMS 1.4)
  • Define the problem
  • Observe system / collect data
  • Formulate a model
  • Verify/validate model and use for prediction
  • Use model to select among alternatives
  • Present results to decision makers
  • Implement solution and evaluate outcomes
Write a Comment
User Comments (0)
About PowerShow.com