Intro

Intro to MonteCarlo.xls
This workbook introduces Monte Carlo Simulation with a simple example.

In this book, we use Microsoft Excel to simulate chance processes.

Typically, we use Excel to draw a sample, then compute a sample statistic, e.g., the sample average.
We can repeat this process, again and again, keeping track of the result each time. We then look
at the empirical histogram and summary statistics of the results in order to discover or illustrate the
statistical properties of the chance process. This method of studying a chance process is called
Monte Carlo simulation.

The OneFreeThrow sheet shows how Excel can be used draw a random number and simulate a free throw.

The Sample sheet has the outcome of 100 free throw attempts.

There are other hidden sheet in this workbook.

Page 1
 Intro

ate a free throw.

Page 2
 OneFreeThrow

To simulate a free throw, we use a random draw in an IF statement. Scroll down to see how.

The random draw uses the formula, "=RANDOM()", to get a uniformly distributed number in the 0,1 interval.
Click in cell B5 to see the formula. Hit F9 (or CTRL - =) to draw a new number. For more on random number generation, see
Random draw #MACRO?

The RANDOM() function is the foundation of the Monte Carlo Simulation because it's our roulette wheel,
but we need one more concept to complete the simulation. We need a way to tell Excel that Larry
has a 90% chance of making the free throw.
Fortunately, this is easily done with an IF statement.

Take a look at the formulas in cells B18, D18, and D18.

Cell D18 says, "if the value in cell B18 is less than 0.9, then the value of this cell is 1, else the value is 0."
The structure is "expression to be evaluated," "value if true," "value if false."
Notice the difference between cells D18 and D19--yet they end up doing the same thing.
While you are at it, try to figure out what's going on in cells C18 and C19.

Random draw #MACRO? ### ###

### ###

Combining the RAND and IF statements into a single formula, we get a virtual 90% free throw shooter:

Larry Bird
#MACRO?
Simulated
Notice that this outcome will occasionally differ from the one above. The reason is that
it's based on a different draw from the uniform distribution.

Hit F9 (or CTRL - =) repeatedly (say 30 times in a row) to see how Larry's doing.
As long as the value stays at 1, Larry hit the free throw. When it changes to 0, he missed.
Question:
"I noticed that sometimes he misses in cell B23, but makes it in cells C18 through D19. What's going on?"

Answer:
Excel is drawing different random numbers in cells B18 and B23. Every time it sees
RANDOM() in a cell, Excel draws a new random number.

Question:
"Sometimes I see the word "Calculate" at the bottom of the screen. What does that mean?"

Answer:
Excel can be set up to automatically calculate the values of every formula whenever
the user enters new information anywhere in the workbook OR it can be in Manual Calculate
mode, which means that it will not automatically recalculate. Manual Calculation is needed for
Monte Carlo simulation to prevent constant redrawing of random numbers. The "Calculate"
message in the Status Bar at the bottom of the screen alerts the reader that Excel has not

Page 3
 OneFreeThrow

automatically recalculated the cells in the workbook. If you hit F9, the "Calculate" message
will disappear. Section 9.3 explains about Calculation in more detail and shows how to set the calculation mode.

Page 4
 OneFreeThrow

0,1 interval.
ndom number generation, see sections 9.2 and 9.3.

Page 5
 OneFreeThrow

calculation mode.

Page 6
 Sample

Percentage Made #MACRO? This sheet contains a single repetition of 100 draws (free throw attempts)

Attempt Result Larry Bird hits 90% of his free throws. The formula in Cell B4 informs Exc
1 #MACRO? The exact same formula is used in the 100 cells from B4 to B
2 #MACRO? Hit F9 to simulate another 100 free throws.
3 #MACRO? How did Larry do this time?
4 #MACRO?
5 #MACRO? Hit F9 again and again. Watch cell B1.
6 #MACRO? How much spread or bounce is there?
7 #MACRO?
8 #MACRO? Wouldn't it make sense to keep track of Larry's sample percentage?
9 #MACRO? Click on the button below to do so.
10 #MACRO?
11 #MACRO?
12 #MACRO?
13 #MACRO? Curious about streaks? Click on the button below.
14 #MACRO? Click this button several times.
15 #MACRO?
16 #MACRO?
17 #MACRO? Do you think Max Streaks follow a normal distribution?
18 #MACRO? Click on the button below to see a Monte Carlo simulation that answers th
19 #MACRO?
20 #MACRO?
21 #MACRO?
22 #MACRO?
23 #MACRO?
24 #MACRO?
25 #MACRO?
26 #MACRO?
27 #MACRO?
28 #MACRO?
29 #MACRO?
30 #MACRO?
31 #MACRO?
32 #MACRO?
33 #MACRO?
34 #MACRO?
35 #MACRO?
36 #MACRO?
37 #MACRO?
38 #MACRO?

Page 7
 Sample

39 #MACRO?
40 #MACRO?
41 #MACRO?
42 #MACRO?
43 #MACRO?
44 #MACRO?
45 #MACRO?
46 #MACRO?
47 #MACRO?
48 #MACRO?
49 #MACRO?
50 #MACRO?
51 #MACRO?
52 #MACRO?
53 #MACRO?
54 #MACRO?
55 #MACRO?
56 #MACRO?
57 #MACRO?
58 #MACRO?
59 #MACRO?
60 #MACRO?
61 #MACRO?
62 #MACRO?
63 #MACRO?
64 #MACRO?
65 #MACRO?
66 #MACRO?
67 #MACRO?
68 #MACRO?
69 #MACRO?
70 #MACRO?
71 #MACRO?
72 #MACRO?
73 #MACRO?
74 #MACRO?
75 #MACRO?
76 #MACRO?
77 #MACRO?
78 #MACRO?
79 #MACRO?

Page 8
 Sample

80 #MACRO?
81 #MACRO?
82 #MACRO?
83 #MACRO?
84 #MACRO?
85 #MACRO?
86 #MACRO?
87 #MACRO?
88 #MACRO?
89 #MACRO?
90 #MACRO?
91 #MACRO?
92 #MACRO?
93 #MACRO?
94 #MACRO?
95 #MACRO?
96 #MACRO?
97 #MACRO?
98 #MACRO?
99 #MACRO?
100 #MACRO?

Page 9
 Sample

raws (free throw attempts).

mula in Cell B4 informs Excel of this chance process.

n the 100 cells from B4 to B103. Click on a few of these cells to see for yourself.

sample percentage?

simulation that answers this question.

Page 10
Q&A for MonteCarlo.xls
1) Run a Monte Carlo with 1,000 repetitions of the percentage made of 100 free throws.
Use the Take a Picture button to take a picture of your results.
Copy and paste the picture in a Word document.

2) Run a Monte Carlo with 10,000 repetitions of the percentage made of 100 free throws.
Take a picture, copy it, and paste it in the Word document below the 1,000 repetition Monte Carlo.

3) In what sense is the 10,000 repetition Monte Carlo better than the 1,000 repetition Monte Carlo in this example?

4) Run a Max Streak Monte Carlo.

Take a picture, copy it, and paste it in the Word document.

5) What are the chances that the virtual Larry Bird gets a max streak of 51 in a row or more?
Explain how you arrived at your answer.
n Monte Carlo.

rlo in this example?

Monte Carlo Simulation of Sample Percentage with 1000 Repetitions

Sample Sample
Number Percentage
1 87% Excel Tip: Pause the cursor over one of
2 94% Time Elapsed 0 secs
3 92%
4 90% Empirical Histogram for 1000 Repetitions
180
5 91%
160
6 93% Summary Statistics
140
7 89% Average = 89.86%
8 92% 120 SD = 3.029%
Max = 98%
9 89% 100
Min = 81%
10 86% 80
11 90%
60
12 90%
40
13 93%
14 90% 20
15 93% 0
16 85%
%

%
%

0%
78

80

82

86

90

94
84

88

92

96

98
17 89%

10
18 85%
19 94%
20 91%
21 90%
22 91%
23 89%
Empirical Histogram for 1000 Repetitions
24 90%
160
25 98%
26 92% 140 Summary Statistics
27 89% Average = 90.05%
120
28 90% SD = 2.978%
29 88% 100 Max = 98%
Min = 80%
30 87% 80
31 90%
60
32 94%
33 88% 40
34 90% 20
35 94%
36 92% 0
78% 80% 82% 84% 86% 88% 90% 92% 94% 96% 98% 100%
 40

20

0
78% 80% 82% 84% 86% 88% 90% 92% 94% 96% 98% 100%
37 89%
38 89%
39 89%
40 87%
41 93%
42 89%
43 90%
44 88%
45 89%
46 94%
47 92%
48 87%
49 90%
50 88%
51 88%
52 90%
53 94%
54 86%
55 87%
56 87%
57 93%
58 89%
59 91%
60 90%
61 87%
62 86%
63 91%
64 85%
65 91%
66 92%
67 86%
68 90%
69 97%
70 88%
71 86%
72 87%
73 91%
74 90%
75 91%
76 93%
77 90%
78 87%
79 93%
80 95%
81 83%
82 88%
83 90%
84 93%
85 88%
86 86%
87 93%
88 89%
89 92%
90 85%
91 88%
92 90%
93 97%
94 89%
95 95%
96 92%
97 90%
98 92%
99 90%
100 91%
101 86%
102 90%
103 90%
104 91%
105 85%
106 90%
107 92%
108 89%
109 88%
110 91%
111 90%
112 96%
113 92%
114 90%
115 93%
116 90%
117 90%
118 90%
119 91%
120 86%
121 90%
122 86%
123 90%
124 92%
125 90%
126 93%
127 92%
128 91%
129 86%
130 96%
131 89%
132 88%
133 93%
134 92%
135 90%
136 89%
137 90%
138 91%
139 86%
140 87%
141 92%
142 92%
143 89%
144 90%
145 90%
146 89%
147 88%
148 88%
149 90%
150 87%
151 86%
152 85%
153 91%
154 91%
155 91%
156 92%
157 91%
158 87%
159 92%
160 91%
161 86%
162 91%
163 92%
164 90%
165 93%
166 88%
167 97%
168 86%
169 89%
170 84%
171 91%
172 91%
173 87%
174 92%
175 88%
176 89%
177 87%
178 88%
179 91%
180 92%
181 89%
182 88%
183 92%
184 90%
185 89%
186 90%
187 89%
188 90%
189 92%
190 88%
191 94%
192 87%
193 90%
194 94%
195 90%
196 87%
197 87%
198 85%
199 88%
200 92%
201 90%
202 82%
203 89%
204 86%
205 93%
206 93%
207 92%
208 89%
209 92%
210 87%
211 90%
212 94%
213 92%
214 87%
215 94%
216 89%
217 89%
218 95%
219 90%
220 92%
221 90%
222 93%
223 90%
224 87%
225 86%
226 90%
227 95%
228 93%
229 86%
230 87%
231 86%
232 92%
233 84%
234 87%
235 90%
236 86%
237 84%
238 94%
239 92%
240 90%
241 86%
242 88%
243 90%
244 94%
245 94%
246 86%
247 91%
248 91%
249 95%
250 92%
251 90%
252 98%
253 92%
254 94%
255 84%
256 90%
257 87%
258 85%
259 94%
260 87%
261 88%
262 93%
263 90%
264 89%
265 91%
266 90%
267 86%
268 93%
269 91%
270 91%
271 91%
272 83%
273 89%
274 84%
275 93%
276 90%
277 92%
278 87%
279 90%
280 95%
281 92%
282 89%
283 90%
284 95%
285 87%
286 87%
287 90%
288 84%
289 88%
290 94%
291 91%
292 93%
293 90%
294 89%
295 88%
296 92%
297 93%
298 89%
299 94%
300 88%
301 89%
302 93%
303 85%
304 88%
305 95%
306 88%
307 88%
308 89%
309 95%
310 86%
311 89%
312 91%
313 93%
314 91%
315 89%
316 87%
317 91%
318 83%
319 90%
320 93%
321 91%
322 93%
323 87%
324 91%
325 94%
326 91%
327 92%
328 86%
329 95%
330 90%
331 89%
332 89%
333 93%
334 91%
335 92%
336 90%
337 93%
338 86%
339 92%
340 90%
341 90%
342 89%
343 87%
344 93%
345 93%
346 90%
347 87%
348 87%
349 86%
350 86%
351 84%
352 88%
353 94%
354 93%
355 92%
356 90%
357 91%
358 89%
359 85%
360 89%
361 91%
362 90%
363 91%
364 93%
365 87%
366 87%
367 92%
368 87%
369 88%
370 82%
371 89%
372 85%
373 89%
374 90%
375 86%
376 91%
377 92%
378 84%
379 93%
380 94%
381 92%
382 87%
383 88%
384 94%
385 88%
386 95%
387 89%
388 96%
389 86%
390 96%
391 86%
392 88%
393 91%
394 93%
395 86%
396 88%
397 96%
398 84%
399 85%
400 91%
401 87%
402 86%
403 94%
404 90%
405 86%
406 87%
407 94%
408 87%
409 87%
410 88%
411 92%
412 90%
413 89%
414 91%
415 89%
416 93%
417 88%
418 89%
419 85%
420 88%
421 83%
422 93%
423 94%
424 92%
425 89%
426 92%
427 89%
428 87%
429 90%
430 90%
431 87%
432 90%
433 86%
434 94%
435 89%
436 85%
437 89%
438 90%
439 88%
440 94%
441 93%
442 88%
443 91%
444 93%
445 86%
446 96%
447 92%
448 88%
449 88%
450 91%
451 88%
452 91%
453 91%
454 92%
455 95%
456 94%
457 95%
458 89%
459 89%
460 90%
461 92%
462 90%
463 86%
464 92%
465 89%
466 87%
467 89%
468 90%
469 91%
470 89%
471 90%
472 86%
473 91%
474 83%
475 85%
476 90%
477 96%
478 93%
479 90%
480 86%
481 90%
482 92%
483 85%
484 93%
485 91%
486 88%
487 90%
488 92%
489 88%
490 91%
491 90%
492 84%
493 92%
494 92%
495 88%
496 91%
497 87%
498 87%
499 95%
500 87%
501 93%
502 92%
503 93%
504 82%
505 92%
506 92%
507 94%
508 89%
509 90%
510 88%
511 91%
512 91%
513 90%
514 91%
515 95%
516 87%
517 88%
518 89%
519 90%
520 90%
521 90%
522 93%
523 95%
524 85%
525 88%
526 88%
527 86%
528 89%
529 82%
530 91%
531 85%
532 92%
533 90%
534 92%
535 91%
536 95%
537 98%
538 88%
539 91%
540 87%
541 91%
542 91%
543 88%
544 94%
545 89%
546 88%
547 92%
548 90%
549 94%
550 89%
551 94%
552 94%
553 84%
554 85%
555 87%
556 91%
557 89%
558 93%
559 89%
560 82%
561 93%
562 90%
563 90%
564 87%
565 91%
566 86%
567 93%
568 89%
569 94%
570 88%
571 90%
572 90%
573 92%
574 90%
575 92%
576 91%
577 90%
578 90%
579 88%
580 91%
581 93%
582 95%
583 85%
584 90%
585 87%
586 96%
587 92%
588 88%
589 86%
590 91%
591 92%
592 89%
593 86%
594 87%
595 89%
596 93%
597 86%
598 88%
599 91%
600 89%
601 94%
602 85%
603 87%
604 88%
605 93%
606 89%
607 95%
608 91%
609 90%
610 81%
611 90%
612 92%
613 93%
614 88%
615 86%
616 97%
617 86%
618 89%
619 92%
620 88%
621 88%
622 81%
623 91%
624 88%
625 88%
626 94%
627 92%
628 89%
629 91%
630 84%
631 91%
632 88%
633 88%
634 89%
635 87%
636 90%
637 90%
638 86%
639 88%
640 90%
641 88%
642 96%
643 92%
644 87%
645 86%
646 92%
647 90%
648 89%
649 94%
650 83%
651 89%
652 89%
653 90%
654 91%
655 88%
656 97%
657 90%
658 93%
659 92%
660 87%
661 90%
662 91%
663 83%
664 92%
665 93%
666 93%
667 92%
668 93%
669 87%
670 88%
671 87%
672 95%
673 91%
674 89%
675 94%
676 89%
677 86%
678 93%
679 86%
680 93%
681 89%
682 90%
683 94%
684 87%
685 88%
686 92%
687 89%
688 93%
689 86%
690 85%
691 91%
692 94%
693 90%
694 86%
695 83%
696 85%
697 88%
698 94%
699 90%
700 89%
701 95%
702 89%
703 90%
704 89%
705 94%
706 87%
707 88%
708 92%
709 88%
710 92%
711 93%
712 91%
713 86%
714 84%
715 92%
716 95%
717 87%
718 83%
719 90%
720 93%
721 93%
722 94%
723 90%
724 86%
725 91%
726 88%
727 90%
728 88%
729 91%
730 90%
731 93%
732 92%
733 88%
734 91%
735 93%
736 84%
737 84%
738 91%
739 90%
740 92%
741 90%
742 84%
743 93%
744 88%
745 92%
746 97%
747 84%
748 89%
749 91%
750 92%
751 90%
752 89%
753 89%
754 94%
755 91%
756 94%
757 93%
758 90%
759 91%
760 90%
761 87%
762 93%
763 90%
764 91%
765 93%
766 90%
767 92%
768 91%
769 88%
770 86%
771 94%
772 89%
773 90%
774 86%
775 88%
776 95%
777 92%
778 93%
779 91%
780 94%
781 93%
782 87%
783 87%
784 88%
785 94%
786 85%
787 90%
788 90%
789 92%
790 92%
791 86%
792 83%
793 91%
794 89%
795 90%
796 94%
797 85%
798 92%
799 92%
800 89%
801 86%
802 91%
803 93%
804 91%
805 85%
806 95%
807 85%
808 91%
809 85%
810 88%
811 95%
812 88%
813 89%
814 92%
815 83%
816 87%
817 91%
818 90%
819 90%
820 87%
821 91%
822 93%
823 93%
824 92%
825 93%
826 93%
827 87%
828 90%
829 85%
830 94%
831 86%
832 90%
833 85%
834 90%
835 93%
836 94%
837 85%
838 91%
839 92%
840 84%
841 92%
842 89%
843 93%
844 93%
845 87%
846 88%
847 86%
848 86%
849 88%
850 87%
851 87%
852 88%
853 89%
854 89%
855 89%
856 91%
857 93%
858 87%
859 93%
860 85%
861 91%
862 86%
863 92%
864 91%
865 90%
866 88%
867 88%
868 93%
869 94%
870 89%
871 85%
872 96%
873 92%
874 85%
875 92%
876 91%
877 90%
878 92%
879 90%
880 87%
881 87%
882 93%
883 92%
884 93%
885 88%
886 93%
887 89%
888 90%
889 95%
890 93%
891 90%
892 85%
893 88%
894 89%
895 90%
896 91%
897 89%
898 89%
899 94%
900 89%
901 86%
902 91%
903 93%
904 94%
905 90%
906 93%
907 92%
908 89%
909 87%
910 86%
911 87%
912 91%
913 90%
914 90%
915 96%
916 91%
917 82%
918 91%
919 88%
920 92%
921 88%
922 88%
923 89%
924 81%
925 93%
926 94%
927 86%
928 85%
929 90%
930 90%
931 91%
932 95%
933 94%
934 90%
935 93%
936 92%
937 86%
938 93%
939 91%
940 92%
941 89%
942 84%
943 89%
944 85%
945 92%
946 94%
947 91%
948 92%
949 85%
950 87%
951 88%
952 92%
953 81%
954 95%
955 90%
956 93%
957 91%
958 90%
959 90%
960 92%
961 95%
962 90%
963 90%
964 90%
965 93%
966 90%
967 92%
968 85%
969 93%
970 89%
971 93%
972 92%
973 90%
974 88%
975 86%
976 92%
977 92%
978 89%
979 86%
 980 92%
981 93%
982 92%
983 93%
984 94%
985 92%
986 89%
987 90%
988 89%
989 92%
990 89%
991 94%
992 95%
993 90%
994 82%
995 88%
996 87%
997 92%
998 91%
999 94%
1000 90%
 Repetitions Min = 81%

Max = 98%
: Pause the cursor over one of the bars, the number of samples (or height of the bar) is displayed. Average = 89.86%
SD = 3.029%

ons
%

0%
96

98

10

ons

96% 98% 100%

96% 98% 100%
Min = 81%

Max = 98%
Average = 89.86%
SD = 3.029%
 Number
of
Bin Value Samples
78% 0
79% 0
80% 0
81% 4
82% 7
83% 11
84% 19
85% 39
86% 67
87% 77
88% 93
89% 107
90% 155
91% 107
92% 111
93% 94
94% 59
95% 30
96% 11
97% 6
98% 3
99% 0
100% 0
Monte Carlo Simulation of Sample Percentage with 1000 Repetitions
Max
Sample Sample
Number Streak
1 16
2 25 Time Elapsed 0.00 secs
3 20
4 14 300 Empirical Histogram for 1000 Repetitions
5 45 Summary Statistics
250
6 25 Average = 26.8
7 19 SD = 9.8
200
8 29 Max = 72
9 21 150 Min = 11
10 23
11 21 100
12 16
13 23 50
14 26
15 14 0
0 to 6 to 11 16 21 26 31 36 41 46 51 56 61 66 71 76 81 86 91 96
16 22 5 10 to to to to to to to to to to to to to to to to to to
15 20 25 30 35 40 45 50 55 60 65 70 75 80 85 90 95 100
17 30
18 30
19 24