ST2352 2014 Lab 2: Elementary Simulation

This Lab allows you to study the inverse cdf method in Monte Carlo simulation, and to simulate from discrete bivariate and multivariate distributions. With Problem set 2 it allows you to revise some of the elements of probability distribution theory.

Open the Excel file Lab2.xlsx; course web site

There are severalWorksheets (tabs at bottom left).

  1. The Binomial.for.lab2sheet contains 1000 simulated values of a random variable W following the Binomial distribution with n=10 and p=0.3. You can force a recomputation by Calculate Now (on the Formula tab) or by pressing the F9 key. These 1000 cells have been named as W.Lab2 through the Name Manager (on the Formula Tab above the ‘Ribbon’ at the top of the screen). The frequency table summarises the variation in the observed W values; for comparison the Binomial probabilities are shown. The table has been partially completed.
  1. Inspect the formulae used to compute the frequencies, the relative frequencies and the probabilities. You can get help on these functions by clicking the fxsymbol in the Formula Bar. The Binom.Dist function takes 4 arguments. What is the role of the each of these?
  2. Complete the table by extending the calculations to the empty cells. Do this by extending the calculations to the other cells. Use Copy-Down by (i) selecting the entire shaded eg by Clicking on the first cell and Shift-Clicking on the last; and (ii) copying down by Ctrl-D. (There are other ways)
  3. To plot these (i) select the two columns RelFreq and Binom.Probs (including the headers); and (ii) Use the Insert tab (above Ribbon) to insert a Column chart. You can customise the Chart (or parts of it, eg data, eg axes) by selecting it (or parts) and following options presented by Right-Click. Alternatively use the options now available in the Design and Layout tabs. To modify the values on the bottom axis, and the legend, use Select Data and the Edit dialogue.

Later, outside the lab

  1. The theoretical Expected Value of W is defined as . Calculations such as this can readily be computed in EXCEL by using the SUMPRODUCT function. The Variance can be calculated various ways; one is as . Confirm numerically the theoretical formulae for the Binomial distribution; .
  2. The SUMPRODUCT formulae above, used with the RelFreqs (rather than Probs), will compute the average and variance for the 1000 sampled values. These have also been computed by the AVERAGE and VAR.P functions. You should get exactly the same numerical values whichever approach (SUMPRODUCT, AVERAGE,VAR.P) you use. Why, mathematically?
  3. Change the values of n (to less than 10, otherwise you have to extend your table) and p. What happens to the shape of the chart.Do you still get agreement between the probs and the relfreqs?
  4. Extend the calculations to 10,000 values of W. It’s easiest to extend the number of rows via the ‘Series’ facility, having selected one cell in the first column of indices, and using Copy Down.
  5. Reduce the calculations to the first 100 values of W. One easy way to do this is to delete the rows you don’t want to include.
  1. The sheet Discrete Bivariate for Lab2 sets out the form of the calculations used in class.
  1. Complete the table to be used for the VLOOKUP calculations.
  2. Hence generate 1000 random values from this bivariate distribution, and thus random values of X, of Y etc
  3. Compute the averages of each of these columns.
  4. Complete the frequency tables and hence the charts.

Later, outside the lab

  1. Compute the marginal probability and frequency distributions for X and Y
  2. Use the frequency tables to compute the averages and confirm that these agree with 2c above. Contrast with the expected values that you may compute from the joint probability distribution.
  3. Construct a joint frequency table for the observed values of the joint bivariate random variable (W1,W2) where these represent (X+Y) and min(X,Y) respectively. Confirm that the relative frequencies correspond to the theoretical probabilities that you may compute directly from the probabilities