MIS 200 Personal Productivity with IT

1
MIS 200 Personal Productivity with IT

• The Art Craft of Spreadsheet Modeling

2
Objectives

• Introduce idea of building and using spreadsheet
  based models for helping analyze business
  problems
• Continue to add to growing level of Excel
  expertise in context of business modeling

3
Models

• Simplified representation or abstraction of
  reality.
• Capture essence of system without unnecessary
  details
• Models tailored for specific types of problems
• Models help us understand the world
• Prediction (What if?)
• Optimization (Whats best?)

4
A Few Types of Models

• Physical or scale model
• crash test dummy
• architectural model
• Computer simulation model
• flight trainer
• discrete event model (e.g. SimCity)
• Mathematical model
• Regression
• FMA
• Optimization model

5
Descriptive vs. Prescriptive Models

• Descriptive Model
• Describes a system in terms of parameters and
  variables
• If we change some input parameter, what will
  happen to our output performance measure?

• Prescriptive Model
• Suggests good or optimal solutions
• Also made up of parameters and variables
• Searches over many possible solutions to find
  best solution (in some sense)

6
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
7
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 lab tech between 7a-3p, how much
  reduction can we expect in test turnaround time?

8
Basic Modeling Concepts
Inputs
relationships
Outputs
roles in model
constraints
Decision Variables
relationships
9
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
10
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?

11
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.
12
A Simple Modeling Process
Create model
forces detailed examination and thought about a
problem

• structures our thinking
• must articulate our assumptions, preconceived
  notions

Validate model - Does it mimic reality well
enough?
Modify model
Use the model to support decision making

• Searching for general insights
• Specific numeric answers

13
Exercising the ModelThings we might do

• How do input and/or decision variable values
  affect outputs (sensitivity analysis)?
• Find values of decision variables that minimize
  or maximize the outputs (optimization)
• Create graphic representation of model parameter
  relationships (visualization)

14
Remember
15
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
• 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 protected 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
16
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
17
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
18
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
• How do the output values change when the input
  values change over some relevant range
• How does profit change for various values of unit
  cost in the range 0.10-0.30?

Adapted from from The Art of Modeling with
Spreadsheets, Powell. S.G. and Baker, K.R., John
Wiley and Sons, Inc., USA, 2004
19
In-Class ModelingCaseFinding Break-Even Point

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

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

21
GreatThreads.xls