We will create a simulation of the dealer for the Blackjack…
We will create a simulation of the dealer for the Blackjack card game and compute the probabilities of each possible score and card count for the dealer’s game. A description of Blackjack from Wikipedia follows: The object of the game is to win money by creating card totals higher than those of the dealer’s hand but not exceeding 21, or by stopping at a total in the hope that the dealer will bust. Number cards count as their number, the jack, queen, and king (“face cards” or “pictures”) count as 10, and aces count as either 1 or 11 depending on whether or not counting it as 11 would cause a bust. If a player exceeds 21 points, they bust and automatically lose. A total of 21 on the starting two cards is called a “blackjack” or “natural,” and is the strongest hand. [These rules apply to the dealer as well] … After the players have finished playing, the dealer’s hand is resolved by drawing cards until the hand achieves a total of 17 or higher. 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 “Blackjack Sims” contains 1000 randomized simulations for the top 10 cards in the card deck used by the dealer in columns F:O (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:O of “Blackjack Sims”; all your work should be contained in the other columns of the “Blackjack Sims” spreadsheet. To compute the dealer’s card total for each simulation, 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 card total and card count based on the values in columns BA:BB. Your work will proceed and be graded as follows: 1. (20 points) In the cells P8:Y1007, you will use the cards shown in columns F:O and the lookup table in columns A:B to translate each card into the value that it receives in Blackjack. Note that aces are given a value of 1; we will deal with the value 11 case 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 5-point penalty. 2. (15 points) In the cells Z8:AH1007, you will compute the running sums of the card totals as the cards are taken by the dealer. Note that the column Z should contain the total score of the first two cards, column AA should contain the total score of the first three cards, and so on until column H contains the total score of all ten cards; the dealer will never have a hand with a single card. 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 5-point penalty. 3. (15 points) In the cells AI8:AQ1007, you should modify the card totals contained in the columns Z:AH to account for the score of aces as 11 points whenever possible — that is, if the dealer’s hand has an ace and the card total is less than 11, then you should make the ace count for 11 points instead of 1 to improve the dealer’s card total). An easy way to check on this is to find a hand where you get blackjack with two cards, and check that the score on column Z is 11 and the card total in column AI is 21. 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 5-point penalty. 4. (20 points) In the cells AR8:AZ1007, you should implement the dealer logic for taking each new card based on the total scores of columns AI:AQ. That is, if your card total with two cards is lower than 17, then you should take a third card; if your card total with three cards is lower than 17, you should take a fourth card; and so on. The card totals appearing in this columns should only correspond to cards that the dealer will take from the deck; that is, only the last/highest score in each row should be 17 or higher. It is okay if the last score in the row is higher than 21, as that means that the dealer busted. If you do not attempt Part 3, use the total scores from Part 2 instead (columns Z:AH). 5. (10 points) In the cells BA8:BA1007, you should record the dealer’s final score (e.g., the last score that appears in the row across columns AR:AZ). In the cells BB8:BB1007, you should record the number of cards the dealer needed to reach that score. 6. (10 points) In column BE, use the pre-defined tables to compute the probabilities of the dealer hand’s card total and number of cards. 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. 7. (10 points) Use the tables to generate two charts 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-4, copying cells across 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.
Read Details