Using simulations in Excel to explore probability

1
Using simulations in Excel to explore probability

• Tim Pelton
• tpelton_at_uvic.ca
• University of Victoria

2
Outline

• What is a simulation?
• Why use spreadsheets for simulations in schools?
• Concepts developed with simulations
• Hints and tips for spreadsheets
• Some examples

3
What is a Simulation?

• An simplified/imperfect model of reality -
  without the cost or mess
• A mind experiment - with external working memory
  and processing
• A way to examine stochastic problems - for which
  we may not have an analytic procedure to derive a
  solution, or for which we want an intuitive
  demonstration

4
Why use spreadsheets for simulations in school?

• Create a developmental link
• Carry out concrete experiment
• Examine large virtual sample
• Abstract analysis
• Build conceptual understanding
• Problem based learning - examining real questions
  with meaningful models
• Because its there - cost, skills, transfer

5
Concepts developed with simulations

• Nature of random numbers
• Transformation of random numbers into simulated
  outcomes
• Understanding of probabilities of random events -
  combinations of outcomes
• Law of large numbers

6
Excel tips

• cmdT (F4) - toggle reference type
• cmd (F9) - recalculate
• ctrlshiftreturn - array calculations
• fill special - start a pattern select the start
  and drag to fill with the pattern
• rand(), if(), countif(),frequency()
• lock, hide, protect
• color, no-grid, highlight parameters

7
The Simpsons phone

• Compress a process that would be unmanageable if
  simulated with fidelity
• Examine
• Relationship between proportion of calls and
  probabilities
• Sum of probabilities
• Confidence in estimated probabilities
• Effect of sample size on confidence
• Extending models
• Nature of random numbers

8
Coin toss

• What is the probability of each of the possible
  outcomes
• Concrete - real, but fairly slow, may discourage
  repetitions
• Abstract - quick, procedural
• Simulation supports inspection, connections,
  fiddling with sample size and probabilities

9
Traffic lights

• One of many sample lessons on the Internet (PBS
  Mathline)
• Understand Independent events
• Possible extensions (e.g., wait time)
• Motivate analytic solution
• Examine possible outcomes

10
Monte Hall Problem

• Understand the problem
• Determine useful parameters
• Identify valid simplifications
• Extend a working simulation

11
Birthday Problem

• How might you simulate this?
• What does likely mean?
• How might you extend this?
• Can you work this out analytically?

12
Challenges

• Dice 5 of a kind, straight, full house
• Buffons needle experiment
• What are your ideas?

13
Thanks

• tpelton_at_uvic.ca
• http//web.uvic.ca/tpelton/cprob/combprob.html
• Recommended book
• Neuwirth, Erich Arganbright, Deane (Dec
  2003)The active modeler Mathematical Modeling
  with Microsoft Excel. Brooks-Cole