We will create a simulation of the dealer for the Poker card…
We will create a simulation of the dealer for the Poker card game and compute the probabilities of each possible hand. A list of poker winning hands from ChatGPT appears below: Note that I have crossed out Royal Flush and Straight Flush because their probabilities are very low; they can be counted under standard “flush”, even if in sequence. You will begin by downloading an Excel XLSX file containing a template spreadsheet for this exam question at the link below: Link to XLSX template The template’s spreadsheet called “Poker Sims” contains 1000 randomized simulations for the top 10 cards in the card deck used by the dealer in columns F:J (do not worry, for I used formulas to generate them instead of writing them by hand). Do not change anything in the “Aux” spreadsheet or in columns F:J of “Poker Sims”; all your work should be contained in the other columns of the “Poker Sims” spreadsheet. To compute the game for each hand, you will fill the cells P8:BB1007 with appropriate formulas as detailed below. Once you complete this work, you will compute the probabilities of each hand score and card count based on the values in columns BA:BB. Your work will proceed and be graded as follows: 1. (10 points) In the cells K8:O1007, you will use the cards shown in columns F:J and the lookup table in columns A:C to translate each card into a numerical ID for the card value. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 2. (10 points) In the cells P8:T1007, you will use the cards shown in columns F:J and the lookup table in columns A:C to obtain a numerical ID for the suit of each card. As evident in the table, spades have ID 1, diamonds have ID 2, clubs have ID 3, and hearts have ID 4. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 3. (10 points) In the cells U8:AG1007, you will compute counts of how many cards in the hand have a given value, from 1 (ace) to 13 (king). These counts will allow you to figure out the game in the hand later. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 4. (5 points) In cells AH8:AH1007, you will implement a check that all cards in the hand have been counted correctly in the previous 13 columns. 5. (10 points) In the cells AI8:AL1007, you will compute counts of how many cards in the hand belong to a given suit, from 1 (spades) to 4 (hearts). These counts will allow you to figure out the game in the hand later. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 6. (5 points) In cells AM8:AM1007, you will implement a check that all cards in the hand have been counted correctly in the previous 4 columns. 7. (32 points) In the cells AN8:AU1007, you will implement the poker game “logic” to identify the kind of poker hand obtained. These cells should contain “Y” for the column corresponding to the hand obtained and “N” for the columns corresponding to all other hands. Thus, there should only be one “Y” and eight “N” in each row. You should be able to identify the hand based on the counts for each card value and suit computed earlier. You should be careful to flag only the best hand for the cards given; for example, a “full house” should not be counted as “3 of a kind” or “2 of a kind”. You should write eight distinct formulas, one per winning hand/column, and copy those formulas to all 1000 rows. Each correct identification is worth 4 points. 8. (8 points) In the cells AV8:BC8, you will compute the probabilities of the eight possible hands. That is, count how many trials scored each possible card total/number of cards and divide those values by the total number of trials. Show probabilities as percentages with one decimal. You should use a single formula that is reused in all cells (by copying-and-pasting or filling up/down and left/right), or you will receive a 3-point penalty. 9. (10 points) Use the table from the previous part to generate a chart illustrating these probabilities. Your charts should follow the Excel coding specifications given in the class. You should reuse existing spreadsheet cells as chart features (e.g., title and labels) whenever possible, but you do not need to create new cells otherwise. Make the two charts appear in their own sheets in the workbook. For parts 1-3, 5, and 8, copying cells may change some cell’s borders; you do not need to “fix” these, as they do serve as indicators of using formula replication as required by the problem.