Please complete using Microsoft Visual Basic for Applications (VBA) Programming Language.

Part A: Create a simulation for one pair of teams

- Create a simple simulation that compares a single pair of teams (row 1) on an athletic measure

- Instead taking the raw number for each team and taking the highest of the two,

pass the athletic measure and standard deviation for one team to the NormDist function.

This will create a new number from inside the probability distribution.

- Do the same with the other team and compare the two. The one with the larger value wins.

- Place the name of the winner and the number of wins (1 at this point) in the appropriate columns.

- Create a simple loop that runs the simulation the predetermined number of trials. Compare the two teams on the Athletic Measure and keep track of the wins for each team.

- Output the number of wins to the appropriate location on the Brackets sheet.

Part B: Run the simulation for all pairs of teams

- Create a loop that surrounds the code for one pair so that it runs for all pairs in the sheet.

- About 75% of the Part A code will be inside the new loop. Indent that code so it is at least one level indented from the new loop.

- Instead of hardcoding the upper limit of the loop, calculate it based on number of teams.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Option Explicit

Option Base 1 ' **** did you remember option Base 1

' ******** DSS 620 Template ---- Created by John A. Michl jmichl@sju.edu ********

' **

' ** Module 5 Exercise 2 Template 1.5

' ** Template Revision: 6/8/2018 by John A. Michl

' ** Student comments should use apostrophe only

' **

' ** Assignment modified by STUDENT NAME on DATE

' **

' ******************************************************************************

Sub BracketSimulation()

' **** All variables are provided; use only these variables

Dim i As Integer ' counter for For Next loop

Dim intGame As Integer ' game number (e.g. game 1 = seed 1 vs 16)

Dim nTeams As Integer ' number of teams in bracket

Dim intTrials As Integer ' number of simulations per pair

Dim nTeam1 As Integer ' temporarily hold index number for team 1

Dim nTeam2 As Integer ' temporarily hold index number for team 2

Dim sngMeas1 As Single ' temporarily hold performance measure for team 1

Dim sngMeas2 As Single ' temporarily hold performance measure for team 2

Dim sngSDev1 As Single ' temporarily hold standard deviation for team 1

Dim sngSDev2 As Single ' temporarily hold standard deviation for team 2

Dim nWins1 As Integer ' temporarily hold number of wins for team 1

Dim nWins2 As Integer ' temporarily hold number of wins for team 2

nTeams = 16 ' variable to enable other size brackets,

' clear old values

With Range("Bracket_anchor")

Range(.Offset(1, 2), .Offset(8, 8)).ClearContents

End With

MsgBox "Values cleared. Ready to run simulation."

'***** NOTE: Part A will have one loop that loops through the pairs of teams

'***** Part B will add a loop that will run a set number of trials (nTrials) between

' each pair of teams.

'******* Run simulation that runs one game for each pair

'>>

' **** Initialize nWins1 and nWins2 to reset to 0 in prep for For/Next loop

'>>

' **** Retrieve index numbers for teams 1 and 2 from Bracket sheet

'>>>>>RETRIEVE INDEX NUMBERS

' **** Retrieve athletic measure (column L) and standard deviation (M) from Data sheet

' **** assign to sngMeas1, sngSDev1, sngMeas2, sngDev2

'vvvvvvvvv Your code below

'>>> RETRIEVE MEASURES

'^^^^^^^^ Your code above

' **** Use the NormDist function for each team, compare and pick a winner

' Increment the appropriate nWins1 or nWins2 variable.

' vvvvvvv Your code below

'>>>>>COMPARE NORMDIST FUNCTIONS TO PICK WINNER

' ^^^^^^^ Your code above

' **** Output the team names, nWins1, nWins2, sngMeas1, and sngMeas2 to appropriate row in Brackets sheet

' vvvvvv Your code below

'>>>> OUTPUT RESULTS

' ^^^^^^ Your code above

' ^^^ Close For/Next loop above for Part A directly above. Close Part B loop further up.

End Sub

Function NormDist(measure As Single, measure_SD As Single) As Single

' This function will return a value to the calling sub

' It will will use the worksheet function NORM.INV which requires three parameters.

' Parameter 1: probability -- uses RND to generate a random probability

' Parameter 2: mean -- this would be the measure passed over from the calling sub

' Parameter 3: standard deviation, this would be standard deviation of the mean

' and should be passed over from the calling sub.

' Initialize the random number generator

Randomize

NormDist = Application.WorksheetFunction.Norm_Inv(Rnd, measure, measure_SD)

End Function