Learning Center
Plans & pricing Sign in
Sign Out

Simulation Examples in EXCEL

VIEWS: 238 PAGES: 18

									Simulation Examples in
   Montana Going Green
                 Example 1
• Flip of a fair coin
•   Inputs: Number of trials (flips), N
•   Output: P(H), P(T)
•   Step 1: Set Counter H and T at 0.
•   Step 2: For I = 1 to N (number of flips) do
•     Step 3: Generate a random number, xi, between [0,1]
•     Step 4: If 0 < xi < .5, then H=H+1, else T=T+1
•   Step 5: Calculate P(H) = H/N and P(T)=T/N
•   Output P(H) and P(T)
•   Stop
              Example 2
• Area under a nonnegative curve such as
  y=x3 from [0,2].
INPUT               The total number of random points, N. The nonnegative function, f(x),

                    the interval for x [a,b] and an interval for y [0,M] where M > max


OUTPUT              The approximate area under the curve, f(x) over the interval [a,b]

Step 1. Specify the function, f(x) and set all counters at 0

Step 2.             For i from 1 to N do step 3-5

          Step 3.           Calculate random coordinates in the rectangular region:

                            a<xi<b, 0<yi<M

          Step 4.           Calculate f(xi)

          Step 5.           Compare f(xi) and yi . If yi <f(xi) then increment counter by 1.

                            Otherwise, do not increment counter.

Step 6.             Estimate the area by A  M  (b  a) 

Let’s go to EXCEL
               Example 3
        Buffon Needle Experiment
• In mathematics, Buffon's needle problem is a question
  first posed in the 18th century by Georges-Louis Leclerc,
  Comte de Buffon:
   – Suppose we have a floor made of parallel strips of wood, each
     the same width, and we drop a needle onto the floor. What is the
     probability that the needle will lie across a line between two
• Buffon's needle was the earliest problem in geometric
  probability to be solved; it can be solved using integral
  geometry. The solution, in the case where the needle is
  not greater than the width of the strips, can be used to
  design a Monte Carlo method for approximating the
  number π.
Needle Experiment

Buffon Needle Experiment

Let x be a uniform (0,1) that expresses the location of the end of the needle.
Let y be a uniform (o, pi) that gives the angle of the needle (0 is horizontal)
Let t = x+1*sin(y) be the horizontal projection of the needle
If t(i) > 1 Needle intersects line

Pi= 2 * (length of needle)/distance between parallel line * (number of tosses)/number crossing parallel line

If you allow the needle to be equal to the distance between parallel lines then Pi=2 * number of tosses/# crossing lines
               Fun Game
               Monty Hall
• Here is the typical scenario. Monty has the
  grand prize behind one of three doors. The
  contestant picks a different door. Monty
  choose the a different door (not yours and
  not the grand prize). Do you stay with your
  pick or switch to the other door?
• This question became a big discussion
  with in Parade Magazine.
• The problem was originally posed in a letter by Steve
  Selvin to the American Statistician in 1975. A well-known
  statement of the problem was published in Marilyn vos
  Savant's "Ask Marilyn" column in Parade magazine in
• Suppose you're on a game show, and you're given the
  choice of three doors: Behind one door is a car; behind
  the others, goats. You pick a door, say No. 1, and the
  host, who knows what's behind the doors, opens another
  door, say No. 3, which has a goat. He then says to you,
  "Do you want to pick door No. 2?" Is it to your advantage
  to switch your choice?
• —Whitaker/vos Savant 1990
• Throughout the many years of Let's Make A Deal's popularity,
  mathematicians have been fascinated with the possibilities
  presented by the "Three Doors" ... and a mathematical urban
  legend has developed surrounding "The Monty Hall Problem." The
  CBS drama series NUMB3RS featured the Monty Hall Problem in
  the final episode of its 2004-2005 season. The show's
  mathematician offered his own, very definite solution to the problem
  involving hidden cars and goats.
• The 2008 movie 21 opens with an M.I.T. math professor (played by
  Kevin Spacey) using the Monty Hall Problem to explain
  mathematical theories to his students. His lecture also includes the
  popular "goats and cars behind three doors" example favored by
  many versions of the Problem
Let’s go to EXCEL
             Other problems
• Service with a smile: A small service department
  has one server. Customers arrive according to
  the distribution in Table 1. The server can
  handle the customers according to the
  distribution in Table 2. You want to use a
  simulation to estimate the busy time of the
  server, the average length of the queue, the
  average waiting time of customers, and the
  average number of customers present. Use a
  simulation with one hundred events.
  Tables for Problem
Table 1

Interarrival time        Probability
1                        0.20
2                        0.30
3                        0.35
4                        0.15

 Table 2

Service time (minutes)   Probability
1                        0.35
2                        0.40
3                        0.25

To top