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.
Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
36
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
  

37
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?
38
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//panko
  .cba.hawaii.edu/ssr/Mypapers/whatknow.htm
• http//sprig.section.informs.org/
• Spreadsheet oops http//www.eusprig.org/stories.ht
  m
• The Spreadsheet Projecthttp//mba.tuck.dartmouth.
  edu/spreadsheet/
• 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

39
Many dimensions of model quality

• Modularity
• Reusability
• Automation
• Clarity
• Flexibility
• Power
• Maintainability

• Elegance
• Usability
• Aesthetics
• Scope
• Validity
• Correctness
• Acceptability

40
Uncertainty The Gorilla in the Room

• Were ignored uncertainty so far
• Fun with Uncertainty next time
• Probability and statistics are the language of
  uncertainty
• 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

41
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

42
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.
43
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
44
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

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