| Home |
 |
Chapter3: |
|
Download all Excel spreadsheets for this chapter.
When it comes to discrete fair division schemes such as the method of sealed bids, spreadsheets can aid in the calculations. Suppose, for example, that in her last will and testament, Grandma leaves just three valuable items to her four grandchildren (Art, Betty, Carla, and Dave) - a house, a Rolls Royce, and a Picasso painting - with the stipulation that the items must remain with the grandchildren (not sold to outsiders) and must be divided fairly. The bids that each of the grandchildren make on the items in the estate are shown in Figure 3.1.
To start the process of dividing up Grandma's estate, we sum up each to the grandchildren's bids and divide by 4 to compute each grandchild's fair share. In cell B6, we use the formula SUM(B2:B4) and then copy it to cells C6 through E6. Below, in cell B7, we use the formula B6/4 and copy it into cells C7 through E7.
Next, we calculate the maximum bid on each of the items in the estate. In cell G2, we place the formula =MAX(B2:E2) and copy it down column G into cells G3 through G4. To decide on the recipient of each item, we can use a nested IF-THEN-ELSE command in column H. In cell H2, we use the formula
=IF(B2=G2,"Art",IF(C2=G2,"Betty",IF(D2=G2,"Carla","Dave")))
and copy it down column H to cells H3 through H4. Of course, this method assumes there are no tie bids since it determines the recipient to be the first grandchild with the maximum bid.
Finally, we calculate how much cash each grandchild owes the estate (or gets from the estate). A key formula in this process occurs in cell B9. The formula
=IF($H2=B$1,B2,0),
will put a '0' in cell B9 if Art did not have the high bid on the house and will put the amount of his bid in cell B9 if Art did have the maximum bid. By the judicious choice of $s in this formula, we can copy this formula to all cells in the block B9 through E11. Notice that our implementation of the method of sealed bids does not consider the possibility of any tie bids. In cell B12 we use the formula =B7-B4 to compute how much cash Art receives from the estate based on what he feels is his fair share. By copying this formula to cells C12 through E12 we can also compute how much each other grandchild receives from the estate based on what they feel are their fair shares. Cell G12, containing the formula =SUM(B12:E12), will hold the amount of extra cash the estate has as calculated from row 12. Cells B13 through E13 will each contain the same number -- negative the value of 1/4 of the value in cell G12. To accomplish this, we simply use the formula =-$G$12/4 in cell B13 and copy it across row 13. Finally, cells B15 through E15 compute the amount of cash each player receives from the estate. Cell B15 contains the formula =B12+B13 and cells C15 through E15 are copies.
At this point, it must dawn on the reader that implementing this process on a spreadsheet does not appear to save much time from doing all the calculations by hand. So what is the point? Suppose Carla's bid on the Rolls Royce was read wrong the first time. Instead of $47,000, her bid was actually $57,000. Would you like to redo all the calculations? Luckily, with a dynamic spreadsheet, only the value in cell D3 needs to be changed. The spreadsheet will automatically recalculate the results. Behold the power of spreadsheets! Figure 3.3 shows the result of a recalculation based on a change in cell D3 only.
Three players (Ana, Ben, and Cara) must divide a cake among themselves. Suppose the cake is divided into 3 slices (s1, s2, and s3). The value of each of the 3 slices in the eyes of each of the players is shown in Figure 3.4 below.
We design a spreadsheet that will indicate, for each player, which of the slices are deemed fair shares. We start by computing the total value that each player places on the cake in cells F2 through F4. To do this, we put the formula =SUM(B2:D2) in cell F2 and copy it down column F as shown in Figure 3.5.
We proceed by making a table which gives the percentage value that each player places on each piece. The formula =B2/$F2*100 is placed in cell B6 and, by a foxy use of the $ sign, copied to every cell in the grid from B6 to D8. Lastly, cells B10 through D12 will carry the desired information about which slices are deemed fair by each player. To decide, we simply look in the grid of cells from B6 to D8 and check which values are greater than or equal to 33 1/3%. To accomplish this, the formula
=IF(B6>=100/3,"FAIR","NOT FAIR")
is placed in cell B10 and copied into this block of cells.
Walking
Exercise 3.1 [Excursions, Chapter 3, Exercise 5] Three players (Ana, Ben, and Cara) must divide a cake among themselves. Suppose the cake is divided into 3 slices (s1, s2, and s3). The value of each of the 3 slices in the eyes of each of the players is shown in Figure 3.6 below.
Design a spreadsheet that will indicate, for each player, which of the slices are fair shares.
Exercise 3.2 [Excursions, Chapter 3, Exercise 8] Four players (Abe, Betty, Cory, and Dana) must divide a cake among themselves. Suppose the cake is divided into 4 slices (s1, s2, s3, and s4). The value of each of the 4 slices in the eyes of each of the players is shown in Figure 3.7 below.
Design a spreadsheet that will indicate, for each player, which of the slices are fair shares.
Jogging
Exercise 3.3 [Excursions, Chapter 3, Exercise 42] Three heirs (A, B, and C) wish to divide up an estate consisting of a house, a small farm, and a painting, using the method of sealed bids. The heirs' bids on each of the items are given in Figure 3.8.
(a) Design a dynamic spreadsheet that will compute the outcome of this discrete fair-division problem.
(b) Suppose C were to bid $0 on each item in the estate. How much cash would C receive from the estate?
(c) Suppose C were to bid $100 on each item in the estate. How much cash would C receive from the estate?
(d) Suppose C were to bid $58,000 on each item in the estate. How much cash would C receive from the estate?
(e) Through trial and error, hypothesize how C could maximize the amount of cash she receives from the estate (without getting the house, farm, or painting)?
Exercise 3.4 [Excursions, Chapter 3, Exercises 39, 43, 59] Three sisters (A, B, and C) wish to divide up 4 pieces of furniture they shared as children using the method of sealed bids. Their bids on each of the items are given in Figure 3.9.
(a) Design a spreadsheet that will compute the outcome of this discrete fair-division problem.
(b) Suppose three people (D, E, and F) wish to divide up 4 items using the method of sealed bids. Use your design from above to compute the outcome of this fair-division problem where their bids on each of the items are given in Figure 3.10.
Figure 3.10: Items to be divided in Exercise 3.4.
(c) Suppose three people (R, S, and T) wish to divide up 4 items and some of the bids are negative. For example, three women (Ruth, Sarah, and Tamara) share a house and wish to divide the chores: bathrooms, cooking, dishes, and vacuuming. For each chore, they privately write the least they are willing to receive monthly (their negative valuation) in return for doing that chore. The results are shown in Figure 3.11.
Figure 3.11: Chores to be divided in Exercise 3.4.
Will the spreadsheet design used to solve the above two fair-division problems still work to solve this problem? Note: For doing dishes, the high bid is $-15.
Exercise 3.5 [Excursions, Chapter 3, Exercise 66] Suppose that in her will Grandma stipulates that her estate be divided among the 4 heirs as follows: Art, 25%; Betty, 35%; Carla, 30%, and Dave, 10%.
(a) Design a spreadsheet that will divide the estate using the method of sealed bids. One possible output of the spreadsheet is given in Figure 3.12.
(b) Suppose Dave wants to maximize the cash he receives from the estate and is not interested in any of the three items, but knows what others are bidding on each of the items. Experiment with the spreadsheet to determine the amount Dave should bid on each item.
|