In this lecture we will examine various models. A worksheet will typicallly simulate a model by carrying-out its rules/formulas embodied in cells. Most cells in these workbooks use Excel commands that you already know, although a few contain more advanced Excel commands that we will briefly discuss here and cover in more detail later in the quarter. Note that all workbooks have non-input cells protected, but not with a password. If you want to change formulas/charts on the worksheet, you can click the Unprotect Sheet icon in the Changes group on the Review tab. Only the Projectile workbook has its cells hidden (with a password) because this is part of the solution to Lab #4. In all other workbooks you can see (but not change, unless you unprotect the worksheet yourself) the contents in the cells. Retirement Workbook: Suppose that your pension plan allows you to be paid in either a lump sum (one time) payment at retirement, or by an annuity (a yearly payment made on the first day of each year that you are still alive). Also suppose you know your yearly living expenses (and the percent increase in the annual cost of living), and the percent you can earn yearly on whatever money you have invested. You would like to choose between a lump sum and annuity, ensuring that you won't go broke during your retirement and that you'll have as much money as possible for your children. Of course, you are not sure how long you will live, so you need to see how your age affects the money you would have: e.g., if you die quickly, it is probably much better to take the lump sum; if you live a long time, it is probably much better to take the annuity. This workbook models up to 30 years of retirement, with a pension paid either by a lump sum or by an annuity. The amount of money you have at the end of each year is the amount you had invested at the end of the previous year, plus the annuity payment (if you chose that option), plus the amount of interest you earned on the money you had in the bank at the end of the last year, minus your living expenses adjusted for inflation. The green cells in Column B show the information that you can enter to better understand your options. You know B4:B6 when you retire; you can estimate B10 and B13 (and easily change these values with scrollers). Columns E:J computes the information you need based on the model and the values you entered. Columns K:N show a Data Table of values for different Interest Earnable, from .5% to 10% in increments of .5% Cells H5:J5 use the database function match. For example, H5 contains MATCH(TRUE, H6:H35, 0) which means, find where the first occurence of TRUE is in cells H6:H35. If it finds the first TRUE in the first cell, H6, the function evaluates to 1; in the second cell, H7, to 2; ...; in the last cell, H35, to 30. If it finds no cells in that range equal to TRUE, the value of the function is shown as #N/A. The 0 at the end means to keeping looking at cells until an exact match is found. We will discuss the MATCH function in detail soon. The cells H5:J5 are used to construct the data table (see cells M5:O5, which refer to these cells, in the Data Table for L5:O25). The left chart shows the amount of money you have in the bank at the end of each year (and whether/when you will go broke). This chart is plotted from the information in columns E:G. The right chart shows, over a variety of possible Earnable Interest rates, at what year an Aunnity becomes better, and in what year you will go broke (one line for a lump sum payment, one for annuity payments: the colors for these two options are the same as in the left chart). This chart is plotted from the information in the Data Table in columns K:N #N/A values are not plotted in the charts. For example, if we we use the original values in B4:B6, B11 and B12 (643,623, 53,820, 45,000, 4.2%, and 1.6%) and we look at a 4% interest rate earnable, we see that (a) if we take the lump sum, we would go broke a bit before year 20 (set Interest Rate Earnable to 4.0% on the scrollbar and you'll see it is year 18); (b) if we take the annuity, we would go broke in 27 years; (b) we would not go broke during the 30 (again, set the scrollbar to 4.0%) and you'll see a slight positive balance at year 30; (b) the annuity becomes better a bit after year 15 (again, set the scrollbar to 4.0% and you'll see on the left graph, that the curves cross around year 17). You can adjust the Interest Earnable and Cost of Living Increase to better understand your options (or change cells B4:B6). For example, given values for B4:B5 and the sliders, you can figure out the maximum Annual Living Expenses to not go broke: maybe it would be better to use a scroller for this cell, adjusting it until the plotted line(s) are 0 in year 30. ------------------------------------------------------------------------------ Projectile Workbook: Suppose that we want to shoot a cannon shell (given the speed with which it is fired) so that it hits an X,Y coordinate. At what angle should we fire the projectile? What is the minimum speed that the projectile needs to be able to hit the X,Y coordinate (and what is the angle). This workbook models a projectile moving in a gravitation field, in a vacuum. We enter its speed and firing angle (either directly, or using the scrollbars) in the gren cell B6 and B9 and the X,Y coordinate in cells B12 and B13. The cells B16:D800 models the flight of the projectile, charting its distance and height over time. The equations use an IF function to ignore what happens after the projectile returns to ground level (so it's Y is never negative). The X,Y coordinate also appears on the chart. Using scrollbars, with feedback from the chart, we can solve the problem of determining what angle makes the projectile "hit" the target given an initial speed. We can also do a bit more work and find the mininmum speed that can hit that coordinate. You will create this spreasheet yourself as part of Lab #4, and use it to find the minimum speed for the projectile and the angle to shoot it at. Here we see how the combination of human and computer can search for the solution to a problem. Later we will learn a way in Excel to have it automatically solve this kind of problem. ------------------------------------------------------------------------------ Tennis Workbook: CAUTION: It takes a long time to load this spreadsheet (because it computes a data table of a data table), and it will look strange until it is completely loaded. If you change a value, recomputation likewise takes a few seconds; if you change the values in R8 to R18 and click the Recompute button, it will take a minute to recompute/rechart the values. Given the probability for which player 1 beats player 2 on a SINGLE POINT, what is the probability that player 1 WINS A MATCH from player 2, using the standard rules (scoring) for tennis? For example, if the probability is 50%, we expect each player to win about 50% of the matches. How much better, per point, would player 1 have to be over player 2 to win 90% of the matches? Would he/she have to be a bit better (say win just 51% of the points) or have to be a lot better (say, win 90% of the points)? What is the relationship between the probability of winning a point and the probability of winning a match? This workbook models in detail one simulated tennis match, showing who wins each point, game, set and finally the whole match -player 1 (P1) or player 2 (P2). We enter the probablility (a number between 0 and 100) that P1 beats P2 on any given point in green cell B2 (if we enter a percentage outside [0,100] the cell turns red via conditional formatting). Logically, the model is complicated (and the Excel functions even more so). For each point (numbered in column A) a random number is generated in column B; if it is <= the probability that P1 wins, P1 wins that point, otherwise wins the point. Note that if the probability that P1 wins is 80% (or .8) then 80% of the random numbers generated in [0,1) will be <= .8 so P1 will win 80% of the points. Columns C:D keep track of how many points each player has for each GAME; when column E shows a GAME is won by some player (TRUE, conditionally formatted to appear orange), the next row in C:D shows one player having 0 points and the other having 1 point (because one player won the first point of the new GAME) Also in that row, one of the values in columns F or G is incremented, for the player who won that GAME. Likewise, columns F:G keep track of how many GAMES each player has won in each SET; when column H shows a SET is won by some player (TRUE, conditionally formatted to appear orange), the next row in F:G shows both players having won 0 GAMES in the new SET. Also in that row, one of the values in columns I or J is incremented, for the player who won that SET. Finally, columns I:J keep track of how many SETS each player has won in the MATCH; when column K shows a MATCH is won by some player (TRUE, conditionally formatted to appear orange), columns C:D, F:G, and I:J are reset to 0 and stay 0: there are really no more points, games, or sets to simulate, because the match is over. This is like the canon shell model not showing negative heights. If you examine the cells in columns C:D, F:G, and I:J you wil find lots of complicated IF functions that encode all the rules of tennis, as well as ensuring that the simulation stops after someone wins the match. Cells M1:M3 show who won the match (player 1 or 2) and the percentage of points that each player won; these percentages should be close to the ones in B1:B2, but because we are using random numbers, don't expect a perfect match. The formulas in cells M1:M3 use the SUMIF function and the MATCH function that was explained in the Retirement worksheet. So, columns A:M simulate playing one match. Columns O:P are a Data Table showing who won each of 200 matches that are simulated (WHICH IS WHY THIS WORKSHEET TAKES SO LONG TO RECALCULATE!). The top of Columns R:T shows the results of simulating these 200 games. This is simiar to the data table we constructed for throwing darts to compute PI (both use random numbers). ---------- If we want to explore different probabilities and focus at single games, we can turn off the automatic recalculation of data tables by selecting the Formulas Tab then clicking the Calculation Options icon in the Calculation group and then clicking Automatic Except for Data Tables. With this option set, we can press F9 to force recalculation of everything, including Data Tables. If you use this option, remember to reenable Automatic otherwise you might get confused when the Data Table doesn't automatically recompute) ---------- Finally, the information and chart below columns R:X show how the probability of a player winnning one point affects the probability of that player winning the match. R7:S18 looks like a Data Table, but it is in fact computed by a macro activated by pressing the Recompute button. We will discuss macros briefly soon, and extensively by the end of the course. Pressing recompute calculates using the data table in O:P 10 times, so it is really slow. Note that the chart shows that a small advantage on each point leads to dominance in a game. That is, when the point comparison is 50%/50% each player wins about 50% of the time; when the comparison is 52%/48%, player 1 wins close to 80% of the time; when the comparison is 54%/46%, player 1 wins close over 90% of the time. So, when one player is a bit better at winning points, we see that that player will dominate tennis matches. A better model would use different probabilities for winning when serving and winning when receiving (since these are different). Also, we could investigate different rules in tennis, to try to make a small difference in abilities not create such a skewed difference in winning matches. We could adjust the minimum number of points to win a game, minimum number of games to win a set, and minimum number of sets to win a match. We could also investigate how many points one needs to be ahead by to win a game (must be at least 2 in real tennis; and there must be a game spread of at least 2 to win a set). Addendum (2011): The second year I discussed this model in class, I had a bit more time to play with it while demonstrating it to my students. I set the probability of Player 1 winning a point to be 50%, and pressed F9 a few times to fill the data table (which plays 200 matches). For each, we observed what percentage of times Player 1 won a match. Since Player 1 had a 50% chance of winning each point, I expected that 1/2 the time Player 1 would win the match and half the time Player would win. Sure, sometimes I would expect Player 1 to win 10-20 more matches than Player 2, and sometimes vice-versa. But equally powerful opponents should win approximately an equal number of matches. But, Player 1 seemed to always win fewer matches than Player 2, by a pretty big margin too. The chart showing the Probability of Winning a Match given the Probability of Winning a Game looked reasonable (sloping up dramatically between 45% to 55%) so I just moved on in class. But in office hours later I tried this model again. When the probability of Player 1 winning a point was a few percentage points lower or higher than 50%, the model seemed to give a reasonable result, but right near 50% it was skewed, always showing Player 1 at a disadvantage. Given this statistical anomoly, I had to believe that I coded the tennis model incorrectly, but in a sublte way. So I started looking at what was going on in a match that Player 2 won. I focused on watching transitions that occured as each game and each set was won (with cells in rows E and H highlighted in orange) and looked to make sure the model recorded the win for the correct player. For a while, all the cells looke fine. But then I discovered an error in the model. I found a cell in Column H that showed that someone won a set (it contained TRUE and was colored orange) but neither column I nor J was incremented, so those columns indicated that neither player had won a set. When I looked farther down the column, I found another cell in Column H (and I and J) that showed the same problem. In both cases Player 1 and Player 2 each had won 5 games in the set. Then Player 1 won a 6th game. Now, to win the set, Player 1 would have to have won 6 or more games, and have won 2 more games than Player 2. This was not the case, but the H cell was incorrectly indicating that Player 1 had won the set. Subsequent cells in columns C, D, F, and G went back to the start of the next game in the new set (which was wrong), but the set was not recorded as a win for Player 1 (which was the correct behavior, because he would have to win the set by 2 games). Thus, in a close set with Player 1 up 6 to 5, the model would abort the set and reset both players to a new game and set. Player 1 would have more likely won the set in this case, so the model was biased a small amount (only in games where Player 1 had a 6 to 5 advantage over Player 2) agains player 1. (Later I found that this same problem also occured if Player 2 was up by 6 games to 5, and Player 1 won a game. In this case, it also aborted the set, but doign so did not bias the answer since each player was equally likely to win when they were tied). So I looked carefully at the cell in column H and discovered that I had made a small mistake, using a cell from column E when I should have used a cell from column F. Once I fixed this problem in all the cells in column H, when the probability of Player 1 winning a point was 50%, then this player would win approximately the same number of matches as Player 2 Afterward I realized another way to have discovered this error was to examine each cell in a row for symmetry (one player's win is another's loss and vice versa). I might have quickly discovered that I had entered the wrong column (E instead of F) because formula was not symmetric in F and G (because it had an extraneous E reference). Finally, note that this small bias doesn't affect the results much if the probability that Player 1 wins a point is much higher than the probability that Player 2 wins a point, which is why the other calculations in the chart came out looking approxmiately correct. So, what is the bottom line. I guess first of all not only must you be sure your model correctly models reality, but you must make sure your model itself is correct. As we have seen in this example, a small mistake might introduce not a big error (that would be easy to spot) but only small bias. For any give sequence of 200 matches, the results looked OK. It was only after running many sequences, with the probability of Player 1 winning a point 50%, that I was able to find a statistical anomoly, and then track it down and correct it. ------------------------------------------------------------------------------ Secretary Workbook: Suppose that we can interview up to 100 people to hire a secretary (that many have applied for the job). After interviewing each person, we must decide either to hire them or remove them from consideration for the job. What is a good strategy such that we will end up hiring a good secretary? The first question to ask is, "What determines a good choice." One possibility would be to maximize the percentage of time the best person (highest ranked) is hired; another would be to maximize the percentage of time one of the best 5 people (highest 5 ranked) is hired; or generally, to maximize the percentage of time one of the best B people (highest B ranked) is hired. The strategy that we will use is to interview some number (N) of candidates, not hiring any of them no matter how good they are. Then we continue interviewing more candidates in earnest (start real hiring), hiring the first one who is ranked better than the best of the first N candidates that we interviewed. So, the first N candidates just give us a feeling for how well qualified the pool of candidates is. How big should N be? Note: if N is very low, then we are likely to see FEW high-ranked candidate, so the rank of the one that we ultimately hire (who must be better ranked than the first N) will likely also be low. But likewise, if N is very large, we are likely to see ALL the top-ranked candidates before making real hiring starts, so the rank of the candidate we hire will also be low. So, we weed to chose an N high enough so that we see some high-ranked candidates, but not so high that we see too many (so some are still left to hire). This workbook models in detail this hiring strategy. We enter N, the number of candidates to interview before hiring really starts (we will not hire any of these N people) in the green cell B5. N must be [1,99] (or the cell will turn red, via conditional formatting). Columns A:D then compute the rank (goodness, where 1 is best) of the secretary we hire: column B generates a random [0, 100,000,000) goodness for each candidate; column C shows the rank of that candidate among all 100. It uses the Excel RANK function, which we will learn more about later (and which you can explore with Excel's help system now). Cell B17 shows the best rank of the first N candidates (the ones we interview but do not hire). This cell contains the formula =MIN(INDIRECT("C6:C"&(B1+5))) Assume B1 is 10 (we interview 10 candidates before hiring anyone). This formula catenates "C6:C" with B1+5 (we add 5 because the first Candidate starts in row 6, 5 beyond row 1), which computes 15, creating the STRING "C6:C15". INDIRECT turns this STRING into a range of cells C6:C15, which spans the ranks of the first 10 candidates interviewed. MIN then computes the rank of the best candidate. We will discuss the INDIRECT function in detail soon. Column D computes the rank of the candidate we hire (by using a few nested IF functions). It guarantees that the first N candidates aren't selected, and by candidate 100 (cell D105) a selection is made (if no one better than the first N is found, we always hire the last secretary interviewing, not matter what his/her ranking is). Cell B3 shows the rank of the candidate hired. Columns E:F show a Data Table of values for 500 different hiring, experiments; column F shows the rank of the candidate hired, for each experiment. Columns H:I show a histogram of the Data Table, showing the percentage of times the candidate hired has the specified rank (for all ranks between 1 and 100). The Column (not Scatter) Chart to the right graphically shows the histogram. Experiment by entering different numbers in B1 and observing the histogram. We will see more examples of column charts are histogramming later in the quarter. The information in columns K:V and the two charts below it compute how good is a strategy of first interviewing 5 to 45 people before making a hiring decision. Columns K:L use the histograms for these values of N to compute the percentage of times the top ranked person is hired, the top 2 ranked people are hired, ... Finally, M36:V46 looks like a two dimensional Data Table, but it is in fact computed by a macro activated by pressing the Recompute button. We will discuss macros briefly soon, and extensively by the end of the course. The left chart, which I developed first, shows what percentage of time (Y axis) the person hired is better than a certain rank (X axis). Each data series corresponds to a strategy: a different number of candidates interviewed before real hiring starts. It shows how effective each strategy is, but I was confused about how to select a good N. So, I looked at exactly this same data in a different way, in the chart on the right. It shows the percentage of time (Y axis) the 1st best, 2nd best, 3rd best, ... person is hired (each a different data series) based on the number of candidates interviewed before real hiring starts (the X axis). Although pressing Recompute will show slightly different plots each time, the N (on the X axis) that generally computes the highest percentages for each data series is somewhere between 20 and 35. So, it is best to interview a few dozen candidate before real hiring starts. With that approach, you are likely to hire the best candidate almost half the time, and likely to hire one of the top 10 candidate 80% of the time.