modeling introduction to models and modeling for decision support
TRANSCRIPT
Modeling
Introduction to Models and Modeling for Decision
Support
What we’ll do today and where we are going
Prelude: What will endure? A general modeling overview Discuss and apply general principles for building and using
spreadsheet based decision support models we’ll build a spreadsheet model for the JCHP Break Even case We’ll use that model to answer some questions in the case Use some actual spreadsheet models for helping with staffing (Cust.
Service Reps), inpatient obstetrical facility planning, OP Clinic resource planning
Next Time: Modeling uncertainty
What makes many managerial decision problems hard?
Uncertainty key inputs, the future, relationships between inputs and outputs
Complex relationship between variables the physics of healthcare processes and services
Massive number of alternatives schedules, plans, routes, scenarios
Multiple, often conflicting objectives minimize patient wait time and minimize labor cost
Difficulty quantifying outcomes and making tradeoffs capacity cost vs. wait time
Obtaining and using data take your information services person to lunch
Organization and political constraints and pressures reality
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 PDSA cycle
Common sense and holistic view Intuition, understanding underlying system, synthesis skills, working knowledge of
the basics (physics of operations, statistical thinking, psychology, business fundamentals)
Balancing the quantitative and qualitative Systems analysis
“Dancing With Systems” (Meadows)
Systems approach1. Systems view – broad and holistic
System Performance Systems as interacting subsystems
2. Means – ends analysis Start with objective, figure out how to get there
3. Creative alternative generation Many process improvement tools focus on this
4. Modeling, improvement, experimentation, evaluation
5. Iteration – complexity forces this Again, NO MAGIC, much hard work needed Use techniques and tools best suited for problem at hand
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 (What’s best?)
Examples – a what if? and a what’s best?
Models provide a bridge
Problem
Decisions
Model
Interpretation
Excel Workbook(calculations)
From Monahan, G., “Management Decision Making”, Cambridge University Press, 2000
“Real”World
AnalystsWorld
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?
Serious Play: How the World's Best Companies Simulate to Innovateby Michael Schrage, Tom Peters
A “Simple” Modeling Process
Problem definition
Model construction and data collection
Verification and Validation
Testing
Exercise the model
assumptions
mathematical formulascomputer programspreadsheet
test caseswalk-throughscompare with real system
necessary corrections and enhancements
predictions
questions about real system
Administrators – You have final say on Assumptions &
Validation (Butler)
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 mining)
Exercising the ModelThings we might do
"All models are wrong; some are useful."
- W. Edwards Deming
The role of spreadsheets in HCM 540
Provides a readily available, extremely powerful, yet “easy” to use platform for modeling and exploring business problems
Allows any business professional to become an end-user modeler
A powerful way to present and illustrate complex ideas me, from a teaching perspective you, presenting your analysis and ideas in health care
professional settings
Why Spreadsheets? Spreadsheets are the de facto standard platform for modeling and
analysis in business today “The language of business”
Excel has rich set of modeling and analysis tools Many sophisticated add-ins available Spreadsheet based modeling wave in many top business schools
(Indiana U., Ivey, Dartmouth, Michigan, etc.) at both UG and MBA level
End user decision support system development via VBA Huge installed base of Excel users Can tie with other products such as database management systems Smoking Cessation example
Excel is Unbelievably Powerful Platform for Business Analysis
1. Data is good.
2. Data is often not enough, need models too.
3. Models+Data+VBA = Decision support system
Art & Craft of Modeling 14
A Brief History of Spreadsheets(D.J. Power)
“spread sheet” – spread out a sheet of paper so you can see the columns and rows
1979 – Bricklin, Frankston, Fylstra developed VisiCalc (“visible calculator”) for the Apple
Kapor developed Lotus 1-2-3 in early 80’s and it quickly became the “killer app” for the new IBM PC
Excel written for Apple Mac in 1984-85 and for PC in late 80’s first GUI version of a spreadsheet
IBM buys Lotus in 1995, Microsoft Excel steadily corners spreadsheet market (estimated at 90% currently)
Spreadsheets are the de facto standard business analysis tool
Errors in Spreadsheet Models Many research studies have found frightening levels
of error rates in important spreadsheets used in numerous industries http://panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm
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”
Use good spreadsheet design techniques range names, cell protection, comments, separation of
model components plan the application review by others
Components of a Decision Support Model
Inputs
Decision Variables
Outputs
relationships
relationships
roles in model
constraints
Perspective matters
Basic modeling skills
Categorizing variables inputs, parameters decision variables performance
measures, outputs
Decomposition – divide and conquer
avoid “mega-models” get small parts working and
then put them together
ModelInputs & dec. var outputs
Argo Proposal Model
ProfitCost Revenue
Enrollees Capitation Rate
Influence DiagramsStarting to Model
Input variableDecision variable
Output variableinfluential relationship
Break Even Influence DiagramJCHP Case
Major output variable or performance measure?
Input variables? Which inputs influence
outputs or other inputs?
(1) Let’s build the influence diagram
together
(2) Then let’s build and exercise a
spreadsheet based model for this
problem.JCHP-BreakEven-01-Shell.xls
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 What’s Best?
Spreadsheet modeling basics
Inputs OutputsFormulas
A few spreadsheet design tips Clear, logical layout of overall model Separation of different model parts across multiple ranges and
even worksheets Clear headings for different model sections and the inputs,
outputs and decision variables Use range names DON’T “hard code” critical values into formulas Name your worksheet tabs Strive for “live” spreadsheets
Changing a base input value should result in everything updating automatically with a “twinkle” of the spreadsheet
More spreadsheet design tips
Use formatting bold, italics, fonts, color, indenting, etc.
Use cell comments Use text boxes for assumptions, lists, and
other model annotations We’ll cover many more as we start to build
spreadsheet based models
Numeracy and logical skills
Make quick rough numerical estimates Cost per patient?
Use special cases to test limits of calculation What if zero enrollees? What if 5000 enrollees?
Check consistency of units Example: X/year + Y/month = goofy results
“sniff test” Does a break-even point of 20 patients “smell right”? A simple finance example regarding NPV
Look at SmellTest tab in the JCHP Shell file we just worked with
More basic modeling skills
Parameterization – “call it alpha” Demand = f(,Price), Example: Demand = 2000-*Price
Back in to the answer – vary the inputs to get the answer you want Goal Seek – finding the break even point
Sensitivity analysis which input variables have biggest impact on important output variables? Tornado diagrams – we’ll visit these shortly
Advanced modeling skills
Make heroic assumptions Assume you know something you DON’T Assume something is true that you know is FALSE
Imagine the answer – think backward from the desired result what set of predictions or information do you wish you had to help you make this
decision? design the magic 1-page report
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
More advanced modeling skills
Prototyping – get something working, build a “toy” start simple, add complexity as needed
Use metaphors, analogies, similarities Emergency department as a “funnel”
Sketch a graph – visualize
Use families of mathematical relationships
0 1 1y x xy e by ax
GolfClubs-TrendLines.xls
Example: Inpatient Obstetric Capacity Planning Model
Parameter Units SymbolArrival Rate of Patients pats/day aALOS days bC-section rate % c... several more
Mathematical equations
(2) Stochastic Model(s)
(1) Inputs
Performance Measure Units Symbol
Expected Occupancy patients E[O]Probability of No Bed #N/A P[no bed avail]
(3) Outputs
Predict these
We’ll actually build these
kinds of models later in the
term. OBMODELS-HCM540.XLS
LDR Postpartum
Many dimensions of model quality
Modularity Reusability Automation Clarity Flexibility Power Maintainability
Elegance Usability Aesthetics Scope Validity Correctness Acceptability
Uncertainty: The Gorilla in the Room
We’re ignored uncertainty so far Fun with Uncertainty
Probability and statistics are the language of uncertainty Sensitivity Analsysis = “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?
Monte-carlo simulation Dynamic uncertainty and process physics
Art & Craft of Modeling 30
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