PSTAT 109: Lab 2Fall 2015Instructions: When you ?nish all problems, ask your TA to check your work.S

PSTAT 109: Lab 2Fall 2015Instructions: When you finish all problems, ask your TA to check your work.Submit written responses.Problem 1. The following table represents different probabilities for some events.EventsPriorProbabilitesConditionalProbabilitesJointProbabilitiesPosteriorProbabilitiesAiA1A2A3P (Ai )0.350.40.25P (B|Ai ) (Ai ∩ B)P (Ai |B)Assuming A1 , A2 , and A3 are mutually exclusive events whose union is the entire samplespace, copy the table above onto a new Excel worksheet and complete it.Problem 2 (The Monte Carlo Method). Essentially, the Monte Carlo method is a techniqueused to solve a numerical problem through the use of random sampling. It has applicationsin physics, engineering, computer graphics, and statistics. Here, we will use the Monte Carlomethod to obtain an approximate solution to the following birthday problem:What is the probability that (at least) 2 people in a group of 12 will have the samebirthday?(a) From page 106 of your textbook, what should the theoretical probability be forthis problem?(b) In cell A11, use the RANDBETWEEN function to randomly choose a number from theset {1, 2, 3, . . . , 364, 365}, then using the fill handle, copy and paste this formula 11times, from cell A12 to A22. This will be our first random sample of size 12. Eachnumber in this sample will represent a birth date (excluding leap year birthdays),and will simulate, say, a choosing a class of 12 students at random.(c) Highlight the sample and using the fill handle, create 199 more of these randomsamples. Start from column B and end to column GR. This will simulate, say, having200 random classroom each filled with 12 students.(d) Now, we need check if each of our samples have at least two identical numbers, i.e.,if we have at least two people with the same birthday. Let us focus on our firstsample in column A. Copy and paste the following formula into cell A25:IF(COUNTIF(A11:A22,A11)>1,1,0)This formula behaves as follows. The function COUNTIF will search for the value incell A11 in the given range A11:A22. This count will always be at least 1 (why?).If result of COUNTIF is greater than 1 (indicating that there is a matching valuesomewhere in the range), our formula will output a 1, otherwise, if there is nomatch, the output will be 0.(e) Not only do we to check if any of our numbers in our sample match the first value,we need to check if we have matches with the other 11 numbers. So using the fillhandle, copy and paste the same formula from cell A26 to A37. Be sure to useabsolute referencing on the range if you decide to use the fill handle approach.(f) Next, we need some indication that our sample actually has matching birthdays. Incell A37, write your own formula that indicates our sample has matching birthdays.(Hint: One way to do this is to use the COUNTIF and SUM function together.) Applystep (e) and (f) to the other 199 samples by highlighting the appropriate regionand using the fill handle.(g) Using the indicator functions constructed in the previous step, count the numberof samples that have matching birthdays. Take this number and divide by 200,and we now have an approximation solution to the birthday problem above. Howclose is your approximate answer to the theoretical answer? How might we obtainan even more accurate answer?Problem 3 (Simulating fair dice). In this section we will estimate the probability that thesum of two fair dice is seven.1. What is the theoretical probability that the sum of two fair dice is seven?2. Type =ROUNDDOWN(6*RND(),0)+1 in Cell E7, and fill in the range E7:F5006 withthis formula. This formula will generate equally likely numbers from the set 1, 2, 3, 4,5, 6. We can interpret each random number as the outcome of tossing a die.3. Type =E7+F7 in Cell G7. This number represents the result of summing the numberson two fair dice. Fill in the range G7:G5006 with this formula.4. Type =IF(G7=7,1,0) in Cell H7, and fill in the range H7:H5006 with this formula.Here, a 1 means that the sum of the pair of dice is seven, and is 0 otherwise.5. Type =average(H7:H7) in Cell I7, and fill in the range H7:H5006 with this formula.6. Plot the values of column A (x-axis) against the values of column I (y-axis). Pressthe key F9 to recalculate the random numbers. What happens to your graph as therandom numbers change? Is there anything that remains constant regardless of whatrandom numbers are chosen? Use the graph to estimate the probability that the sumof the numbers on two fair dice is seven.7. How do your results compare to the theoretical probability?