Excel Spreadsheet - UW-Madison Actuarial Club by shuifanglj

VIEWS: 5 PAGES: 39

									     Welcome to the Actuarial Club's Excel I & II Review
This is an Excel worksheet.
Together, all the worksheets make a workbook.
Let's try navigating the worksheet and workbook using the keyboard.

Try moving around the following data with the arrow keys.
        1        1           2         3          5          8
      13        21          34        55         89        144
     233       377         610       987       1597       2584

Jump to the end of data and over "whitespace" by using the arrow keys and holding down the ctrl key.
Also, see what happens when holding down ctrl and shift. (Shift is a highlighting function)
This is useful when highlighting a data set.
         1        17                            33         49
         2        18                            34         50
         3        19                            35         51
         4        20                            36         52
         5        21                            37         53
         6        22                            38         54
         7        23                            39         55
         8        24                            40         56


        9        25                              41        57
       10        26                              42        58
       11        27                              43        59
       12        28                              44        60
       13        29                              45        61
       14        30                              46        62
       15        31                              47        63
       16        32                              48        64

To move between worksheets, use ctl+page up and ctl+page down.
Entering Data
To enter text of a number into a cell, just type what you want to see.

Enter the numbers 1 through 10 below:                             Now try it again, but just enter 1 and 2, then highlight




Formula Intro
In Excel, you can also enter an equation. Try to enter (1+2)*(3+4) below:


The above equation did not work. Why?
For Excel to evaluate an expression, you need to prefix it with an equals sign (=).
Enter =(1+2)*(3+4) below:


Notice that when a cell contains an expression, that expression shows up in the formula bar.
To quickly edit any cell, click the mouse in the formula bar while on that cell.
Go back and edit the prevoius expression to be =(1+2)/(3+4)

Formulas that reference other cells

To add the following numbers, type =A32 + A33 in the yellow space below.
        4
        5


Multiply the following numbers by using = and then the arrow keys or the mouse.
         6
         7


Notice what happens when editing formulas that reference other cells (color-coding).

Using Built-In Excel Functions
Sum the following numbers using the AutoSum button from the toolbar.
   0.343
   0.441
   0.189
   0.027


Clicking on the "fx" button next to the formula bar will bring up a list of possible functions.
Use that list of functions to compute the following values for this data set.
         1         1.3        1.2       0.8        1.5
      0.7          1.1        0.9       0.8        1.3
         1         1.2        0.8       1.3        1.7
Average:
Std dev:
Max:
Min:
t again, but just enter 1 and 2, then highlight and drag down to extend.




p in the formula bar.




lor-coding).




ossible functions.
Dragging Formulas
Enter the product of B7 and C7 in column D7.
Copy D7 down to the cells below by dragging on the "block" in the lower right.

                         Products
 0.006047      10
 0.040311      10
 0.120932      10
 0.214991      10
 0.250823      10
 0.200658      10
 0.111477      10
 0.042467      10
 0.010617      10
 0.001573      10
 0.000105      10

Notice how the formula adjusted itself down the column.



Below, total the numbers in column A.
Then, find what percent of the total each value is (i.e. =A27/A37).

Values      % of Total
    20
    34
    54
    12
    29
    67
    71
    32
     9
    85


Notice that the formulas dragged down into cells don't make sense for this equation. We have to "lock" the cell we do
When copying formulas, use a $ before a row and/or column to "lock" that row and/or column.
Try this formula again, but this time lock the appropriate cell.
You may prefer to use F4 to toggle through the locking configurations.

Copy & Paste
Let's say you wanted to use the values you just computed somewhere else. Copy and paste the percentages you just
Hint: Right-click is very helpful.
Notice the formulas transferred, referencing cells that don't exist. We have to use paste special.
Now, try the same thing, but use Edit->Paste Special, values
. We have to "lock" the cell we don't want to change.




nd paste the percentages you just found into the space below:
aste special.
SSN                 Last
            First Name Name              Date  Hire     Pay
                               Date of Birth ofDate of Termination   First, the column widths need to be adjust
11359320    Scrappy Doo           21777 30907 33455 109691.7         (Hover between letters and adjust or just dou
303595708   Yakky Doodle          22628 33798 35418       38979.8
11395990    Scooby Dum            17669 29661 32305       42804.3    Secondly, the SSN's are obviously not dis
72396415    Blue    Falcon        24351 34148 36245 82358.49         We can correct this through special forma
6437448     Deputy Fishtail       25976 33304 35988 35693.78         Format-> Cells -> Number -> Special -> SSN
164117890   Dino    Flintstone    18792 29269 31251 48954.46
288130756   Fred    Flintstone    26815 34304 36422 57453.25         Next, the DOB's, DOH's, and DOT's need t
273432084   Pebbles Flintstone    22403 34639 36547 106801.9
285136031   Wilma Flintstone      18595 29605 32081 109544.7         Lastly, we need to the clean up the pay co
12393047    Great Fondoo          25862 32678 33820 96145.35
10410730    Chance Furlong        18880 32069 34822 81688.77
312138261   Wally Gator           14260 28004 29752 92004.67
52410194    Great Gazoo           24240 32853 34186 79962.61
321139000   Space Ghost           15367 27061 29421 79138.16
125295014   Magilla Gorilla       24206 31313 34152 56854.37
350053440           Hound
            Huckleberry           24471 34526 36780 73516.08
44072639    Jabber Jaw            19031 32435 34790 97960.67
174258534   Astro Jetson          16191 29766 31413 53241.81
177255929   Elroy   Jetson        23920 34182 36967 89531.83
266375236   George Jetson         19028 32629 34817 60281.29
256435868   Jane    Jetson        25111 32608 34928       83705.3
229598693   Judy    Jetson        22437 32783 35764 30844.96
e column widths need to be adjusted.
etween letters and adjust or just double click)

 y, the SSN's are obviously not displayed appropriately.
correct this through special formatting.
> Cells -> Number -> Special -> SSN

e DOB's, DOH's, and DOT's need to be formatted.

we need to the clean up the pay column so it's easier to read.
IF Statements:
Let's say you're running a recreational basketball league.
There are two divisions. The juniors division is for people under age 18, and the adult division is for those
Below you have a list of players and their ages. We need to determine which division each player falls int
For this, we will use an IF statement, i.e. if the age is less than 18, we want the team to be "Juniors" and if its 18
Follow Excel's guidelines when entering the IF statement.
If the desired output is text, you must enclose it in "quotes" within the IF formula.

Player             Age         Team
Superman                  35                              CountIF Functions:
Batman                    14                               Now, we want to find how many players are on each
Spider Man                16                               We will use the counif function based on the player's
Mighty Mouse              18                               Countif sums the cells in the "range" which satisfy the "cr
Robin                     25
Lois Lane                 59                              Team       # of Players
Bruce Wayne               17                              Adults
Clark Kent                15                              Juniors
Green Lantern             78
Skeletor                  23
He-Man                    12
Danielle Kuhn             22

SumIF Functions:
Now the teams are each having a party, and each player submitted how many pieces of pizza he or she d
We want to know how many pieces of pizza we need for each party.
We will use a sumif formula.
("Range" are the cells we want to test for the "criteria". "SumRange" are the cells we actually want to add

Player             Team        Slices ordered
Mark Matthews                          10                 Team PartySlices Ordered
Batman                                  2                 Adults
Spider Man                              5                 Juniors
Mighty Mouse                            7
Kirk Rodgers                            9
Lois Lane                               2
Bruce Wayne                             1
Clark Kent                              4
Green Lantern                           6
Skeletor                               12
He-Man                                  8
Kyle Tolzien                          200
dult division is for those 18 and older.
 on each player falls into.
be "Juniors" and if its 18 or more (not less than 18), the team should read "Adults".




ny players are on each team.
n based on the player's AGE for this.
nge" which satisfy the "criteria".




 es of pizza he or she desires.


we actually want to add if the criteria is satisfied.)
                                                                                                     Status
Logic Functions                                                      Name           Canal Worker   01/01/1999
All logic functions and criteria in excel return TRUE or FALSE.      ADKINS LE            1          Active
                                                                     CARRIN BL                       Active
                                                                     HUCKEBA TL                      Active
                                                                     PARRISH JM                      Active
And Function:                                                        PATE NW             1           Active
The And function uses 2 or more arguments and returns a              PIERCE PH                       Active
value of "TRUE" if all arguments within the function are true.       TAYLOR OM                       Active
If any of the arguments are false, the function will return a        ABERCROMBIE RH                  Active
vaue of "FALSE".                                                     ACREE CW                        Active
                                                                     ADDISON C                       Active
In column O, use the And function to determine which employees       ADKINS, MICHAEL                 Active
were canal workers active in 1999 and retired in 2000.               AHRENT TL                       Active
                                                                     ALFORD L                        Active
***If it is hard to read through the TRUE and FALSE,                 ALLEN HD                        Active
we could add an IF statement to help make it easier                  AMAN CL                         Active
to identify the TRUEs                                                ANDREWS JA          1           Active
                                                                     ARD JR                          Active
Or Function:                                                         ATCHISON RJ                     Active
The Or function uses 2 or more arguments and returns a               ATKINS CW                       Active
value of "TRUE" if any one arguments within the function are true.   ATWELL ME                       Active
If all of the arguments are false, the function will return a        BABB FE                         Active
vaue of "FALSE".                                                     BABB JR JO          1           Active
                                                                     BAILEY CE                       Active
In column P, use the Or function to determine which employees        BAILEY DK                       Active
transferred or cashed out in 2000                                    BAKER JR RW                     Active
                                                                     BANKS CF                        Active
                                                                     BANKSTON PH                     Active
                                                                     BARBEE AH                       Active
                                                                     BARFIELD ML                     Active
                                                                     BARNES JK           1           Active
                                                                     BAXLEY EF                       Active
                                                                     BAXLEY RD                       Active
                                                                     BEARDEN DW                      Active
                                                                     BEGGS TE                        Active
                                                                     BESORE OC                       Active
                                                                     BEST DA                         Active
                                                                     BEST U                          Active
                                                                     BIRD TD                         Active
                                                                     BLACKMON SA                     Active
                                                                     BLOUNT EN                       Active
                                                                     BOATRIGHT LM                    Active
                                                                     BOLDEN EL                       Active
                                                                     BOONE CR                        Active
                                                                     BOONE JR AG                     Active
                                                                     BOSTON FE                       Active
                                                                     BOUINGTON TK                    Active
                                                                     BOYD KE                         Active
                                                                     BRADLEY DK                      Active
                                                                     BRANCH CK                       Active
                                                                     BREMER DD                       Active
                                                                     BROOKS KD                       Active
                                                                     BROOKS MA                       Active
BROWN FL              Active
BROWN TL              Active
BROWNELL JC           Active
BROWNELL JR WF        Active
BROWNING TG           Active
BRYANT JC             Active
BRYANT W          1   Active
BUNYON JN             Active
BURKETT DM            Active
BURKETT ML            Active
BURNS VL              Active
BUSH TM               Active
BUTLER AR             Active
BUTLER DL             Active
BUZBEE HF             Active
BUZZETT CB            Active
BYRD JB               Active
BYRD LE               Active
CALDWELL HL           Active
CANNON JG             Active
CARDEN JW             Active
CARTER HD         1   Active
CARTER JR JC          Active
CHAMBERS DF           Active
CHUMNEY BJ            Active
CHUMNEY TM            Active
CLARK JE              Active
COLLINSWORTH DJ   1   Active
COMBS WR              Active
CONLEY BR             Active
CONNER JM             Active
CONOLEY JR RE         Active
CORDOVA JR LA     1   Active
COULTER AA            Active
COX JC                Active
COX JH                Active
CRAFT TA              Active
CRAIG JT              Active
CREAMER KS            Active
CREAMER RK            Active
CREECH JR KA          Active
CREWS TW              Active
CROOM AJ              Active
CRUEL JL              Active
CULVER CC             Active
CUMBIE HW             Active
CUNNINGHAM SE         Active
DANIELS JE            Active
DANIELS JW            Active
DANNELLY RC           Active
DAVES, JR KD      1   Active
DAVIDSON WT           Active
DAVIS II MC           Active
DAVIS II RG           Active
DAVIS JH              Active
DAVIS JR WJ          Active
DAVIS RD             Active
DAVIS TG             Active
DAVIS TR             Active
DAVIS TT         1   Active
DAWSON DA            Active
DEAN LD              Active
DEARINGER LL     1   Active
DEESON WR            Active
DEWBERRY TW          Active
DIFATTA CE           Active
DIXON JR TW          Active
DOUGHTY EM           Active
DUGGAR ED            Active
DUNIGAN WL           Active
DYKES CM             Active
DYKES FE             Active
DYKES KA             Active
ETHEREDGE CT         Active
EUBANKS JR JC        Active
EVANS JE             Active
EVERETT CC           Active
FADIO JG             Active
FAIN GH              Active
FARMER GS            Active
FARMER RL        1   Active
FARMER TB            Active
FENAES BL            Active
FIELDS EC            Active
FIELDS III WC        Active
FILMORE JJ           Active
FLOYD MA             Active
FLOYD P              Active
FONTAINE FL          Active
FOSTER DE            Active
FOWLER JD            Active
FOX JR AE            Active
FOX LO               Active
FRANKLIN JR OB       Active
FREEMAN W            Active
FRENCH JR            Active
GAINEY ME            Active
GAINOUS DC           Active
GASKILL TA           Active
GAY BL               Active
GAY TM               Active
GENTRY CL            Active
GILBERT JL           Active
GLASS JT             Active
GODFREY IV EM        Active
GODWIN ST            Active
GORDON AJ            Active
GRAHAM JR LD         Active
GRANT WT             Active
GRAY RL              Active
GREEN GL           Active
GRICE CW           Active
GRINER CL      1   Active
GUFFEY AM          Active
HADDOCK DH     1   Active
HADDOCK JC         Active
HALL JC            Active
HALL R             Active
HAMILTON JR        Active
HAMM TW            Active
HANNA JB       1   Active
HARRELSON GD       Active
HARRIMAN MD        Active
HARVEY C           Active
HATCHER LG         Active
HATCHER WD         Active
HAUN WM            Active
HAWKINS F          Active
HAYWARD W          Active
HEATH JP           Active
HENDELS LW         Active
HENDERSON MD   1   Active
HEWETT JE          Active
HICKS C            Active
HILL CM            Active
HILL LR            Active
HILL MD            Active
HINOTE JD          Active
HJORT GM           Active
HOGAN RG           Active
HOPPER SL          Active
HOUSE JR WM        Active
HOWARD JH          Active
HOWARD OB          Active
HOWZE MW           Active
HUGGINS MW         Active
HUGHES JA          Active
HUGHES NO          Active
HUNTER E           Active
HUNTER VM          Active
HYSINGER JD        Active
HYSMITH OD         Active
INGRAM CH          Active
JACKSON JL         Active
JAMES GA           Active
JENKINS JJ         Active
JOHN M         1   Active
JOHNSON BM         Active
JOHNSON MP         Active
JOHNSON RS         Active
JOHNSON WG         Active
JONES F            Active
JONES JL           Active
JONES JR H     1   Active
JONES LL           Active
JONES RL               Active
JONES TM               Active
JONES VD               Active
JONES WE               Active
JORDAN BD              Active
KEEL RL                Active
KEELS HB               Active
KEITH MC               Active
KELLEY JE              Active
KEMP TV                Active
KENNEDY WT             Active
KENNINGTON III M       Active
KEY W                  Active
KINARD DE              Active
KING TJ                Active
KING VL                Active
KITCHEN JM             Active
KNIGHT RH              Active
KNOWLES BD         1   Active
KNOX RG                Active
LAMB CE                Active
LAMBERSON JM           Active
LAND CD                Active
LANE JK                Active
LANE WA                Active
LANGSTON DM            Active
LANIER RF              Active
LARRY TONY             Active
LAYTON LM              Active
LEE FW                 Active
LEE HJ                 Active
LEE III OB             Active
LEE JA                 Active
LEE LL                 Active
LEE R              1   Active
LEE TE                 Active
LEWIS CL               Active
LEWIS DJ               Active
LEWIS FR               Active
LEWIS HM               Active
LEWIS JR E             Active
LEWIS JW               Active
LEWIS WD               Active
LIKELY RO              Active
LINTON CD              Active
LOLLEY SA              Active
LOWERY EA              Active
LOWRANCE KG            Active
LUCAS SA               Active
LYLE MS                Active
LYLES RM               Active
LYNN JR RE             Active
LYNN WJ                Active
MAIDEN JR ER           Active
MALLON JW              Active
MAPLES DC              Active
MARSH JC               Active
MARSHALL DC            Active
MARTIN JR              Active
MARTIN ME              Active
MARTIN RR              Active
MATHES HL              Active
MC CLAMMA CE       1   Active
MCANALLY SJ            Active
MCCOY BR               Active
MCCROAN DB             Active
MCDANIEL TD            Active
MCFANN CB              Active
MCGILL DL              Active
MCINNIS GM             Active
MCLAWHON MS            Active
MCMILLIAN III TL       Active
MCNEAL KD              Active
MELVIN BJ              Active
MERCER JW              Active
MILES II CM            Active
MILLENDER CA           Active
MILLER SW          1   Active
MIMS P                 Active
MINGER RE              Active
MITCHELL JM            Active
MITCHELL JT            Active
MITCHELL TL            Active
MIXON JR AD            Active
MONTEIRO A             Active
MOORE JR BB            Active
MORK TR                Active
MYERS EC               Active
MYERS GL               Active
MYERS L                Active
NEEL HB                Active
NELSON EW              Active
NELSON RK              Active
NEWELL AG              Active
NEWELL MP              Active
NICKEY MR              Active
NICKSON DM             Active
NICODEMUS JR CN    1   Active
NIXON CJ               Active
NIXON MT               Active
NORRIS MA              Active
NORTON BK              Active
NUNERY BF              Active
NUNNERY BD         1   Active
OAKLEY JA              Active
OLIVER TR              Active
ONEAL SR               Active
OWENS JW               Active
OWENS SL               Active
PACE RC                Active
PARKER CR        1   Active
PARSONS BD           Active
PATE JI              Active
PATE JM              Active
PEAK EL              Active
PEDDIE GF            Active
PEIFFER BR           Active
PETERS LW            Active
PETERS RE            Active
PETTIS CM            Active
PHILLIPS GE          Active
PIERCE JR ER         Active
PIPPIN III FR    1   Active
PITTMAN SR A         Active
PLAYER DL            Active
POSEY MR             Active
PRICE JE             Active
PRICE TL             Active
PRICE TS             Active
PRICE WB             Active
PRIDGEON BM          Active
PRIDGEON DS          Active
PUGH JE              Active
PURSWELL JM          Active
PUTNAL EK            Active
QUINN BC             Active
RABON HJ             Active
RAY JJ               Active
RAY L                Active
REEVES JE            Active
RHODES QJ            Active
RHODES TL            Active
RICH JR DM           Active
RICHARDSON DE        Active
RICKARDS AD      1   Active
RILEY JE             Active
RILEY TM             Active
ROBERTS GA           Active
ROBINSON RL          Active
ROLSTAD JR RS        Active
RUSS FB              Active
RUSS WF              Active
SANDERS MT           Active
SAPP RL              Active
SASNETT GW           Active
SASSER DF            Active
SATTERFIELD BA       Active
SCARABIN RM          Active
SCHEFFER LW          Active
SCHOTT III DR        Active
SCOTT DG             Active
SCOTT TM             Active
SEWELL JP            Active
SEXTON CG            Active
SHACKLEFORD A        Active
SHEARER JA           Active
SHERRILL BC          Active
SHULER MR            Active
SHULER PS            Active
SIMMONS JR GW        Active
SIMS JR D            Active
SIMS JR T            Active
SIPLES WS            Active
SIPRELL SL           Active
SMITH HL             Active
SMITH JA             Active
SMITH JM             Active
SMITH JR CL          Active
SMITH JR SR          Active
SMITH RE             Active
SPEARS CE            Active
STANLEY JE           Active
STANSEL TE           Active
STEWART EV           Active
STRADER JD           Active
STRANGE AD           Active
STRICKLAND AR    1   Active
STRIPLIN JR RB       Active
SUTTON LC            Active
TAYLOR K             Active
TAYLOR MA            Active
TAYLOR ME            Active
TEAT BR              Active
TEAT EL              Active
THARPE CJ            Active
THOMAS C             Active
THOMAS CB            Active
THOMAS D             Active
THOMAS DG            Active
THOMAS J             Active
THOMAS JR I          Active
THOMPSON JR DE       Active
TODD JJ              Active
TRAYLOR TM           Active
TULL DL              Active
TURNER JR            Active
UPCHURCH CL          Active
VARNER JL            Active
VARNES JR RW         Active
VARNES TJ            Active
VATHIS GJ            Active
VATHIS RL            Active
WAHL RG              Active
WALDORFF JR ME       Active
WALKER PC            Active
WALKER TJ            Active
WALKER WJ            Active
WARD AJ              Active
WARD JH              Active
WATSON WT            Active
WATTS JR          Active
WEBB CM           Active
WEEKS RJ          Active
WELCH ED          Active
WEST HT           Active
WESTRY G          Active
WHITE SR          Active
WHITFIELD JR JP   Active
WIGSTEN DM        Active
WILLIAMS AJ       Active
WILLIAMS DA       Active
WILLIAMS GB       Active
WILLIAMS GL       Active
WILLIAMS JL       Active
WILLIAMS JR GT    Active
WILLIAMS TD       Active
WILLIAMS TM       Active
WILLIAMSON EJ     Active
WINTONS JR BA     Active
WOOD CO           Active
WOOD TE           Active
WOOD WJ           Active
WOODHAM GT        Active
WOODHAM WH        Active
WORLEY CA         Active
YOUNG RD          Active
WILLIAMS MD       Retired
ALSOBROOK CW      Retired
FRAZIER WF        Retired
KENT JM           Retired
RICH SR JE        Retired
YOUNG JE          Retired
MCKNIGHT TR       Retired
  Status                                     Termination
01/01/2000   Sex   Birth Date   Hire Date       Date         Life Annuity   And   Or
Terminated    M    5/17/1944    9/21/1970       1/14/2000   $      162.96
  Retired     M     5/4/1935    3/11/1960       1/31/2000   $      234.84
Terminated    M    9/25/1940    5/27/1968       8/31/1998   $      144.13
Terminated    M     5/6/1946     1/6/1970       8/31/1998   $      139.38
  Retired    M      8/17/1943    3/16/1967      1/14/2000   $     205.79
  Retired    M       1/2/1947     8/4/1966      1/14/2000   $     208.46
  Retired    M      12/8/1935   12/13/1965      1/14/2000   $     211.13
 Transfer    M     11/23/1975     4/6/1998      8/14/1998   $       2.83
Terminated   M     12/22/1952    1/12/1981      8/14/1998   $     105.67
Terminated   M      1/14/1961     1/1/1980      8/14/1998   $     108.67
Terminated   M      4/19/1969    8/30/1993      8/14/1998   $      67.67
Terminated   M      6/25/1962    5/25/1988      8/16/1998   $      84.13
Terminated   M       4/8/1950    5/20/1972      8/31/1998   $     132.13
 Cash Out    M      8/24/1967    7/26/1989      2/19/1999   $        -
Terminated   M       9/5/1948     4/8/1969      8/14/1998   $     140.92
Terminated   M      9/18/1960    5/14/1979      8/14/1998   $     110.67
 Transfer    M       3/8/1977     4/6/1998      8/15/1998   $       3.54
Terminated   M       3/7/1953    9/20/1976      8/21/1998   $     119.13
 Transfer    M      3/14/1962   10/28/1997      5/15/1998   $       4.96
 Retired     M      6/24/1938    3/28/1960      8/14/1998   $     167.92
Terminated   M       3/1/1964    2/13/1989      8/16/1998   $      81.88
Terminated   M      5/31/1949   10/13/1975      8/21/1998   $     122.13
 Transfer    M       5/2/1956   10/28/1997      8/17/1998   $       7.08
Terminated   M      9/16/1965     4/1/1991      8/14/1998   $      74.92
Terminated   M      9/14/1967    8/24/1987      8/21/1998   $      86.38
Terminated   M       7/1/1962     1/1/1981      8/16/1998   $     106.38
Terminated   M      9/10/1960     3/3/1986      8/24/1998   $      90.88
Terminated   M      7/23/1943     8/2/1965      8/14/1998   $     151.92
Terminated   M       1/7/1953     9/4/1985      8/17/1998   $      92.38
Terminated   M      6/26/1963     5/9/1983      8/14/1998   $      98.67
Terminated   M      9/21/1966     9/9/1987      8/14/1998   $      85.67
Terminated   M       5/5/1954    8/19/1974      8/24/1998   $     125.38
 Transfer    M      3/12/1966    5/16/1995      8/14/1998   $      62.42
 Transfer    M      7/23/1968    5/10/1994      8/24/1998   $      66.38
Terminated   M     10/19/1959    8/21/1978      8/14/1998   $     112.67
Terminated   F       2/7/1955    11/9/1981      8/17/1998   $     103.88
Terminated   M      7/21/1957     3/4/1981      8/21/1998   $     105.88
Terminated   M      5/22/1963    4/26/1989      8/14/1998   $      80.67
Terminated   M      11/2/1957    8/22/1983      8/14/1998   $      97.67
Terminated   M     10/29/1954    4/11/1979      8/14/1998   $     110.92
Terminated   M       6/1/1960    6/20/1978      8/17/1998   $     113.88
 Retired     M      8/19/1937     2/8/1965      8/17/1998   $     154.13
Terminated   M      9/21/1965    3/26/1986      8/16/1998   $      90.63
 Transfer    M      1/17/1976    5/16/1995      8/14/1998   $      62.42
Terminated   M       5/1/1963   10/21/1992      8/14/1998   $      70.17
Terminated   M       2/7/1961     6/6/1979      8/21/1998   $     111.13
Terminated   F     12/28/1958    9/25/1991      8/14/1998   $      73.42
Terminated   M      5/14/1972    10/4/1993      8/14/1998   $      67.42
 Transfer    M       9/7/1957   10/28/1997      8/16/1998   $       7.08
Terminated   M     10/22/1963    8/22/1988      8/21/1998   $      83.38
Terminated   M       4/2/1965    8/12/1988      8/16/1998   $      83.63
Terminated   M     12/23/1966    8/10/1988      8/14/1998   $      82.92
Terminated   M   12/12/1943    2/26/1973   8/14/1998   $   129.17
Terminated   M    5/14/1971     3/6/1991   8/14/1998   $    75.17
Terminated   M    5/19/1962   11/16/1981   8/14/1998   $   102.92
Terminated   M     8/4/1965    7/27/1987   8/21/1998   $    86.63
Terminated   M     5/1/1969    12/6/1989   8/17/1998   $    79.63
Terminated   M    9/24/1961    11/9/1981   8/14/1998   $   103.17
  Retired    M    12/1/1941    1/25/1967   8/21/1998   $   148.13
Terminated   M    11/2/1959    12/3/1990   8/14/1998   $    75.92
Terminated   M   12/10/1940    7/17/1963   8/14/1998   $   157.92
Terminated   M    6/23/1950     6/8/1970   8/24/1998   $   138.13
 Transfer    F     5/4/1966    11/3/1997   8/15/1998   $     7.08
Terminated   M   11/14/1959    9/29/1981   8/14/1998   $   103.42
Terminated   F   10/22/1969   10/17/1990   8/14/1998   $    76.17
Terminated   M    2/25/1955    1/12/1981   8/24/1998   $   106.38
Terminated   M    7/18/1950    7/18/1972    9/5/1998   $   131.63
Terminated   M     4/3/1945    10/7/1968   8/14/1998   $   142.42
Terminated   M    9/11/1941    8/23/1972   8/14/1998   $   130.67
Terminated   M     9/7/1949    1/22/1968   8/14/1998   $   144.42
Terminated   M    7/23/1935    7/15/1974   2/28/1999   $   130.13
Terminated   M     3/5/1964     5/9/1983   8/14/1998   $    98.67
Terminated   M     3/9/1954    7/26/1976   8/14/1998   $   118.92
 Cash Out    M   11/13/1962    7/24/1990   8/14/1998   $      -
Terminated   M    6/27/1962    3/28/1988   8/17/1998   $    84.63
Terminated   M   10/24/1959    9/21/1985   8/14/1998   $    91.42
Terminated   M    2/13/1941     4/1/1967   8/17/1998   $   147.63
Terminated   M   12/29/1960   11/10/1980   8/14/1998   $   106.17
Terminated   M    10/1/1957     6/1/1992   8/17/1998   $    72.13
Terminated   M    3/18/1966     9/4/1984   8/24/1998   $    95.38
Terminated   M     9/3/1972     8/3/1993   8/14/1998   $    67.92
Terminated   M     1/9/1965    3/17/1993   8/14/1998   $    68.92
Terminated   M    1/24/1945   10/21/1975   9/12/1998   $   121.88
Terminated   M    8/13/1956    8/10/1977   8/14/1998   $   115.92
Terminated   M   10/23/1965    9/16/1987   8/14/1998   $    85.42
 Transfer    M    11/5/1967   10/28/1997    8/9/1998   $     6.38
Terminated   M     3/8/1941     9/3/1963   8/21/1998   $   158.38
Terminated   M    8/11/1941     4/3/1967   8/24/1998   $   147.63
Terminated   M   11/14/1958    3/14/1980   8/21/1998   $   108.88
Terminated   M   11/16/1957    2/14/1983   8/14/1998   $    99.17
Terminated   M     8/4/1965    9/27/1993    9/1/1998   $    68.13
Terminated   M     3/9/1960     6/2/1978   8/21/1998   $   114.13
Terminated   M    6/26/1948    5/18/1972   8/31/1998   $   132.13
Terminated   M    8/21/1957    11/9/1981   8/14/1998   $   103.17
Terminated   M   12/10/1964     1/1/1980   8/16/1998   $   109.38
Terminated   M    9/10/1960    8/10/1993   8/17/1998   $    68.63
Terminated   M    9/10/1952    9/23/1976   8/14/1998   $   118.42
Terminated   M    9/16/1943    6/17/1968   8/24/1998   $   143.88
Terminated   M    9/20/1954    1/16/1978   8/10/1998   $   114.42
Terminated   M    9/25/1948    2/24/1969   8/21/1998   $   141.88
Terminated   M     6/9/1954    4/30/1974   8/14/1998   $   125.67
Terminated   F    3/11/1943    6/19/1978   8/14/1998   $   113.17
 Transfer    M    8/23/1976    7/20/1998   8/14/1998   $      -
Terminated   M   11/14/1943    5/20/1972   8/14/1998   $   131.42
Terminated   M    7/20/1962    2/15/1989   8/21/1998   $    81.88
Terminated   M    7/21/1974    9/27/1993   8/17/1998   $    68.13
Terminated   M    10/9/1949   10/29/1979   8/21/1998   $   109.88
Terminated   M    10/3/1944    1/19/1970   8/24/1998   $   139.13
 Transfer    M    2/27/1960    2/10/1997   8/17/1998   $    27.96
 Transfer    M     3/9/1971     2/3/1998   8/14/1998   $     4.25
Terminated   M    3/18/1961     3/3/1982   8/14/1998   $   102.17
Terminated   M    4/21/1966     1/1/1978   8/21/1998   $   115.38
Terminated   M    2/28/1954    6/10/1974   8/21/1998   $   126.13
 Disabled    M     3/4/1948     1/5/1973   8/14/1998   $   129.67
  Retired    M    9/15/1934    9/11/1962   2/28/1999   $   165.63
Terminated   M    3/11/1939    9/20/1968   8/21/1998   $   143.13
 Transfer    M    12/3/1962    7/13/1998   8/12/1998   $     0.71
Terminated   M     9/8/1953    9/29/1981   8/14/1998   $   103.42
Terminated   M   10/14/1947   11/24/1969   8/14/1998   $   138.92
Terminated   M    7/23/1969    1/22/1990   8/14/1998   $    78.42
Terminated   M    10/3/1942    9/23/1968   10/5/1998   $   143.84
 Disabled    M   11/28/1938    1/22/1962   2/28/1999   $   167.38
Terminated   M    7/15/1955    7/23/1980   8/16/1998   $   107.63
Terminated   M    9/19/1969    2/13/1989   8/16/1998   $    81.88
Terminated   M    9/23/1959    7/18/1978   8/31/1998   $   113.63
Terminated   M    7/24/1967    5/18/1988   8/21/1998   $    84.13
Terminated   M    6/30/1945     5/3/1972   8/14/1998   $   131.67
Terminated   M    4/27/1950   12/15/1969   8/24/1998   $   139.63
Terminated   M    8/16/1939    2/27/1967   8/24/1998   $   147.88
Terminated   M    10/2/1938    5/30/1972   8/31/1998   $   132.13
Terminated   M    3/18/1946    3/26/1970   8/21/1998   $   138.63
Terminated   M     4/6/1971   11/29/1989   8/14/1998   $    78.92
  Retired    M   11/18/1937    1/10/1968   2/28/1999   $   149.63
Terminated   M     7/7/1969     8/3/1987   8/21/1998   $    86.63
Terminated   M    8/19/1963    5/11/1992   8/14/1998   $    71.67
Terminated   M   11/18/1953     1/1/1982   8/16/1998   $   103.38
Terminated   M   12/24/1954     8/6/1974   8/14/1998   $   124.92
Terminated   M    9/17/1963   11/30/1983   8/14/1998   $    96.92
 Transfer    M    8/31/1965     2/3/1998   8/14/1998   $     4.25
Terminated   M    2/15/1962    2/24/1992   8/14/1998   $    72.17
Terminated   M   12/28/1964    4/30/1984   8/14/1998   $    95.67
 Cash Out    M    9/19/1956     9/4/1984   3/22/1999   $      -
Terminated   M    2/16/1963    8/26/1985   8/17/1998   $    92.38
Terminated   M    6/10/1947    11/4/1992   8/14/1998   $    70.17
Terminated   M    1/24/1961    4/22/1981   8/21/1998   $   105.38
Terminated   M   12/24/1948    9/20/1976   8/31/1998   $   119.13
Terminated   M    6/11/1954     1/1/1979   8/21/1998   $   112.38
Terminated   M    1/16/1969    3/29/1988   8/14/1998   $    83.92
Terminated   M    5/15/1959   11/19/1980   8/21/1998   $   106.63
Terminated   M    4/15/1963    2/12/1986   8/17/1998   $    91.13
Terminated   M   10/19/1960    1/27/1988   8/14/1998   $    84.42
Terminated   M   11/14/1974    9/27/1993   8/17/1998   $    68.13
Terminated   M    2/18/1960    8/21/1978   8/14/1998   $   112.67
 Disabled    M   11/27/1939    9/25/1972    3/1/1999   $   135.38
Terminated   M     7/8/1970    8/23/1993   8/14/1998   $    67.67
 Cash Out    M    9/15/1941   10/17/1963   5/31/1999   $      -
Terminated   M     5/3/1947    1/21/1977   8/17/1998   $   118.13
Terminated   M    6/13/1973    8/10/1993   8/14/1998   $    67.92
Terminated   M    12/5/1948     9/7/1984   8/17/1998   $    95.38
Terminated   M     8/1/1974    8/23/1993   8/17/1998   $    68.38
Terminated   M    5/16/1970    2/23/1993   8/14/1998   $    69.17
Terminated   M    9/19/1952    9/19/1973   8/14/1998   $   127.42
Terminated   M   12/24/1956    1/19/1979   8/14/1998   $   111.42
Terminated   M    8/19/1963    8/26/1985   8/24/1998   $    92.38
 Retired     M     1/6/1938    3/26/1956   8/17/1998   $   180.63
Terminated   M    6/11/1944    9/23/1968   8/14/1998   $   142.42
Terminated   M   10/24/1959   11/15/1980   8/14/1998   $   105.92
 Retired     M   10/17/1934    5/25/1954   2/28/1999   $   190.38
Terminated   M     4/7/1960    8/26/1985   8/16/1998   $    92.38
Terminated   M    6/29/1954   10/24/1972   8/14/1998   $   130.17
Terminated   M    12/1/1942     5/3/1969   8/21/1998   $   141.38
Terminated   M    2/22/1958    7/11/1978   8/16/1998   $   113.88
Terminated   M    1/15/1972    2/14/1994   8/14/1998   $    66.17
 Transfer    M    7/20/1969    1/16/1995   8/14/1998   $    63.42
Terminated   M    6/15/1959    4/15/1981   8/21/1998   $   105.38
 Retired     M   12/14/1936    1/20/1967   3/31/1999   $   153.09
Terminated   M    7/26/1945    4/29/1970   8/14/1998   $   137.67
Terminated   M    1/11/1938    3/29/1960   8/21/1998   $   168.63
Terminated   M    11/8/1963    8/31/1984   8/17/1998   $    95.38
Terminated   M   12/25/1964    10/1/1992   8/14/1998   $    70.42
Terminated   F    1/23/1953     1/1/1982   8/14/1998   $   102.67
Terminated   M    5/31/1947     4/1/1968   8/14/1998   $   143.92
 Disabled    M    10/5/1947     1/2/1970   8/17/1998   $   139.38
Terminated   M     3/4/1966    8/29/1984   8/14/1998   $    94.67
Terminated   M    9/17/1964    5/10/1989   8/14/1998   $    80.67
Terminated   M    6/25/1964    8/29/1990   8/14/1998   $    76.67
Terminated   M     7/6/1937    3/20/1973   8/14/1998   $   128.92
Terminated   F     5/9/1952    6/19/1978   8/14/1998   $   113.17
Terminated   M    11/6/1962    2/13/1989   8/21/1998   $    81.88
Terminated   M     5/3/1962    3/24/1981   8/14/1998   $   104.92
Terminated   M    2/23/1967    1/31/1990   8/17/1998   $    79.13
Terminated   M    10/4/1956    1/17/1979   10/5/1998   $   112.84
 Transfer    M    4/25/1966    2/10/1997   8/16/1998   $    27.96
 Transfer    M   11/26/1966    2/14/1994   8/17/1998   $    66.88
Terminated   M     8/1/1950    10/8/1990   8/14/1998   $    76.42
Terminated   M     1/7/1946    12/3/1968   12/4/1998   $   144.75
Terminated   M    3/19/1966    9/10/1988   8/14/1998   $    82.67
 Transfer    M    7/25/1967    2/17/1997   8/17/1998   $    27.25
 Transfer    M    2/17/1961    1/12/1998   8/14/1998   $     4.96
Terminated   M     9/2/1956    10/5/1981   8/14/1998   $   103.42
Terminated   M     6/7/1944   10/16/1968   8/21/1998   $   142.88
Terminated   F    6/15/1964    8/21/1987   8/17/1998   $    86.38
Terminated   M    9/13/1948     6/2/1976   8/21/1998   $   120.13
Terminated   M    12/5/1957    9/20/1976   8/14/1998   $   118.42
Terminated   M    11/6/1937    1/29/1962   8/17/1998   $   163.13
Terminated   M    10/3/1945    7/18/1973   8/24/1998   $   128.63
Terminated   M   10/19/1963    7/26/1989   8/17/1998   $    80.63
Terminated   M    9/24/1938    1/10/1962   8/21/1998   $   163.38
 Retired     M    7/20/1943    1/25/1967   8/24/1998   $   148.13
Terminated   M    2/28/1938    7/10/1956   8/22/1998   $   179.88
Terminated   M     4/1/1957     6/1/1977   8/21/1998   $   117.13
Terminated   M    7/19/1952     2/8/1973   8/24/1998   $   130.13
 Transfer    M    9/11/1966     7/9/1998   8/14/1998   $     0.71
 Disabled    M    11/3/1945     5/3/1972   8/14/1998   $   131.67
Terminated   M    8/13/1957    4/22/1976   8/17/1998   $   120.38
Terminated   M    4/14/1958     1/1/1982   8/17/1998   $   103.38
Terminated   M    3/20/1944    9/14/1967   8/16/1998   $   146.38
Terminated   M    6/23/1962     8/5/1992   8/14/1998   $    70.92
 Cash Out    M    9/23/1964     8/7/1992    5/3/1999   $      -
Terminated   M    3/23/1960    8/21/1991   8/17/1998   $    74.38
Terminated   M    2/18/1942    4/20/1972   8/22/1998   $   132.38
Terminated   M    3/20/1954    1/17/1976   8/24/1998   $   121.13
Terminated   M    9/15/1951     3/3/1981   8/16/1998   $   105.88
Terminated   M   11/25/1966    7/17/1990   8/14/1998   $    76.92
Terminated   M    11/1/1971   11/23/1992   8/10/1998   $    69.92
Terminated   M    6/28/1942     7/1/1968   8/14/1998   $   143.17
Terminated   M   10/10/1966    1/16/1990   8/14/1998   $    78.42
Terminated   M    8/24/1953    5/12/1975   8/31/1998   $   123.38
Terminated   M    4/20/1967     3/5/1986   8/16/1998   $    90.88
Terminated   M    6/27/1955    7/26/1989   8/17/1998   $    80.63
Terminated   M   11/26/1938    1/22/1969   8/16/1998   $   142.13
Terminated   M    4/20/1963    9/17/1990   8/14/1998   $    76.42
 Cash Out    M    8/31/1939     8/7/1974   8/24/1998   $      -
Terminated   M   10/14/1961     9/2/1987   8/14/1998   $    85.67
Terminated   M   11/30/1946   10/22/1969   8/14/1998   $   139.17
Terminated   M    3/14/1943    5/30/1974   8/14/1998   $   101.26
Terminated   M    5/24/1952    7/31/1972   8/14/1998   $   130.92
Terminated   M   10/18/1946    3/12/1979   8/21/1998   $   111.88
Terminated   M   11/19/1956     6/1/1976   8/17/1998   $   120.13
Terminated   M    1/29/1939    6/18/1973   8/21/1998   $   128.88
Terminated   M    3/14/1939     4/4/1988   8/14/1998   $    83.92
Terminated   M    5/11/1955     1/1/1984   8/14/1998   $    96.67
Terminated   M    3/18/1958     9/2/1987   8/15/1998   $    86.38
Terminated   M   11/11/1948    1/18/1967   8/14/1998   $   147.42
 Transfer    M     6/5/1961   10/28/1997   8/14/1998   $     6.38
Terminated   M   12/28/1946    8/13/1965   8/14/1998   $   151.92
Terminated   M   11/14/1952    5/12/1979   8/24/1998   $   111.38
Terminated   M     4/2/1957     1/1/1979   8/21/1998   $   112.38
Terminated   M    6/27/1954    9/17/1973   8/21/1998   $   128.13
Terminated   M    9/29/1951    1/13/1975   8/21/1998   $   124.38
Terminated   M     8/5/1940   10/16/1978   8/14/1998   $   112.17
Terminated   M    10/3/1958    9/21/1981   8/14/1998   $   103.42
 Disabled    M     8/5/1944    7/28/1969   8/14/1998   $   139.92
Terminated   F    1/20/1964    12/3/1990   8/14/1998   $    75.92
Terminated   M    5/13/1944    9/20/1976   8/21/1998   $   119.13
Terminated   M    9/29/1963    3/19/1986   8/21/1998   $    90.63
Terminated   M    7/25/1961    7/14/1980   8/21/1998   $   107.88
Terminated   M    1/14/1943    12/8/1967   8/14/1998   $   144.92
 Transfer    M    8/19/1950   10/28/1997   8/14/1998   $     6.38
Terminated   M    6/13/1966    9/27/1993   8/17/1998   $    68.13
Terminated   M    3/25/1942    3/12/1973   8/24/1998   $   129.88
Terminated   M     5/7/1947    6/10/1966   8/14/1998   $   149.42
Terminated   M    8/18/1974     8/3/1993   8/15/1998   $    68.63
Terminated   M   10/25/1960    5/25/1979   8/14/1998   $   110.42
Terminated   M    4/13/1941   10/14/1991   8/17/1998   $    74.13
Terminated   M    8/28/1963     6/6/1983   8/16/1998   $    99.13
Terminated   M   10/19/1956    8/28/1985   8/17/1998   $    92.38
Terminated   M    2/20/1954    5/31/1979   8/31/1998   $   111.13
Terminated   M   12/10/1973    5/10/1994   8/14/1998   $    65.67
Terminated   M     6/8/1965   11/29/1983   8/14/1998   $    96.92
Terminated   M   11/18/1968    8/17/1987   8/16/1998   $    86.38
Terminated   M     5/2/1950    6/18/1974   8/16/1998   $   125.88
Terminated   M    4/15/1958   12/23/1991    8/17/1998   $    73.38
Terminated   M    3/29/1951    9/27/1976    8/14/1998   $   118.42
Terminated   M     2/7/1962    1/12/1981    8/14/1998   $   105.67
Terminated   M    4/26/1954   11/30/1983    8/17/1998   $    97.63
Terminated   M    7/17/1968    1/18/1995    8/14/1998   $    63.42
Terminated   M    1/10/1954    4/30/1984    8/17/1998   $    96.38
Terminated   M    8/14/1941    12/3/1965    8/24/1998   $   151.63
  Retired    M    7/14/1932     2/2/1957    8/21/1998   $   178.13
Terminated   M     8/9/1968     8/7/1989    8/14/1998   $    79.92
Terminated   M    3/25/1940     3/1/1963    8/21/1998   $   159.88
Terminated   M    2/22/1965     1/3/1990    8/14/1998   $    78.67
Terminated   M     9/5/1967   10/12/1987    8/16/1998   $    86.13
Terminated   M    1/30/1964     1/1/1983    8/14/1998   $    99.67
Terminated   M     7/5/1952    7/29/1987    8/14/1998   $    85.92
Terminated   M   12/18/1959    3/17/1980    8/16/1998   $   108.63
Terminated   M     9/8/1956     6/2/1976    8/21/1998   $   120.13
 Transfer    M    5/29/1966    4/13/1998    8/15/1998   $     3.54
Terminated   F   11/17/1956   10/16/1991    8/17/1998   $    73.88
Terminated   M     4/4/1943    2/27/1967    8/24/1998   $   147.88
 Transfer    M     6/5/1965    5/10/1994    8/16/1998   $    66.38
Terminated   M    1/28/1955     2/5/1986    8/21/1998   $    91.13
 Transfer    M    4/15/1980     7/9/1998    8/15/1998   $     1.42
Terminated   M   10/29/1952     5/5/1972    8/14/1998   $   105.42
  Retired    M     3/7/1934    3/27/1956    2/28/1999   $   184.88
Terminated   M    1/28/1962   10/28/1988    8/14/1998   $    82.17
 Transfer    M   10/12/1960    2/10/1997    8/14/1998   $    27.25
 Transfer    M    9/27/1957     7/9/1998    8/14/1998   $     0.71
Terminated   M    7/25/1958    8/20/1987    8/14/1998   $    85.67
Terminated   M   11/29/1968    4/12/1993   10/12/1998   $    70.34
Terminated   M   10/14/1944    10/3/1977    8/21/1998   $   116.13
Terminated   M   11/30/1953    8/21/1972    8/31/1998   $   131.38
Terminated   M     3/8/1936    8/13/1968    9/28/1998   $   144.34
Terminated   M    5/12/1955    1/13/1977    9/12/1998   $   118.38
Terminated   M    7/10/1955    7/23/1993    8/17/1998   $    68.63
Terminated   M    1/28/1958     9/2/1987    8/17/1998   $    86.38
Terminated   M    1/30/1939    1/11/1962    8/16/1998   $   163.38
Terminated   M   11/18/1939    7/29/1974    8/14/1998   $   124.92
Terminated   M     2/8/1952   11/20/1972    8/21/1998   $   130.63
Terminated   M    8/15/1954    8/15/1973    8/14/1998   $   127.92
Terminated   M    2/24/1969     8/2/1989     8/3/1998   $    79.92
Terminated   M    5/20/1961    6/20/1979    8/16/1998   $   110.88
Terminated   M    8/11/1955   10/22/1976    8/21/1998   $   118.88
 Cash Out    M    3/31/1970    2/14/1994     4/1/1999   $      -
 Transfer    M    5/22/1976     7/7/1998    8/14/1998   $     0.71
 Transfer    M    8/17/1954   11/10/1997    8/14/1998   $     6.38
 Transfer    M    5/24/1976    1/20/1995    9/21/1998   $    64.84
Terminated   M    1/26/1969    4/19/1989     9/5/1998   $    81.38
Terminated   M     9/9/1946    1/17/1969    8/21/1998   $   142.13
Terminated   M     5/4/1944    1/28/1970    2/15/1999   $   143.38
Terminated   M    5/31/1963     5/2/1989    8/16/1998   $    81.38
Terminated   M     3/8/1949    7/16/1973    8/14/1998   $   127.92
Terminated   M   10/30/1974    10/1/1992    8/14/1998   $    70.42
Terminated   M    2/22/1961    10/8/1986    8/14/1998   $    88.42
Terminated   M    5/19/1971   10/16/1991    8/15/1998   $    73.88
Terminated   M    11/9/1958    3/13/1979    8/17/1998   $   111.88
Terminated   M    9/27/1974    8/23/1993    8/24/1998   $    68.38
Terminated   F    8/13/1945    6/26/1978   12/31/1998   $   116.71
Terminated   M   11/22/1970    7/31/1989    8/15/1998   $    80.63
Terminated   M    12/4/1948     5/3/1972    8/24/1998   $   132.38
Terminated   M    3/20/1958     8/2/1976    8/21/1998   $   119.63
  Retired    M   10/10/1943   11/22/1966    8/21/1998   $   148.63
Terminated   M   10/26/1970    8/22/1989    8/14/1998   $    79.67
Terminated   M     2/9/1946    2/12/1974    8/14/1998   $   126.42
 Cash Out    M     1/6/1970    9/30/1992    8/14/1998   $      -
 Disabled    M    7/21/1937   11/17/1978    8/14/1998   $   111.92
Terminated   M     7/8/1962    3/17/1981    8/21/1998   $   105.63
Terminated   M     8/1/1947    10/3/1977    9/26/1998   $   116.84
Terminated   M    2/22/1957     6/8/1977    8/15/1998   $   117.13
Terminated   M   12/15/1943    5/23/1968    8/14/1998   $   143.42
Terminated   M     4/1/1956   11/20/1991    8/17/1998   $    73.63
Terminated   M     3/7/1960    3/25/1980    8/17/1998   $   108.63
Terminated   M   10/29/1959    9/27/1978    8/14/1998   $   112.42
Terminated   M   11/25/1965    8/25/1989    8/17/1998   $    80.38
Terminated   M    4/13/1959   11/10/1981    8/31/1998   $   103.88
Terminated   M    6/28/1969    2/13/1989    8/17/1998   $    81.88
 Transfer    M   11/24/1967     6/6/1995    8/14/1998   $    62.42
Terminated   M    11/4/1967     3/1/1989    8/16/1998   $    81.88
Terminated   M   10/15/1953    6/26/1978    8/16/1998   $   113.88
 Transfer    M     9/1/1946   10/28/1997    8/16/1998   $     7.08
Terminated   M    11/9/1963    4/11/1990    8/16/1998   $    78.63
  Retired    M    11/2/1933     2/8/1965    2/28/1999   $   158.38
Terminated   M    3/22/1956   11/13/1990    8/24/1998   $    76.88
 Transfer    M     3/8/1972    2/14/1994    8/17/1998   $    66.88
  Retired    M   10/23/1944    1/16/1967    9/12/1998   $   148.13
Terminated   M     1/2/1957   12/14/1979    8/21/1998   $   109.63
Terminated   M    7/18/1968    3/11/1992    8/17/1998   $    72.88
Terminated   M    11/1/1960     4/1/1981    8/21/1998   $   105.63
Terminated   M    6/29/1963    4/24/1990    8/15/1998   $    78.38
Terminated   M   12/23/1949    1/14/1974    8/21/1998   $   127.38
  Retired    M   11/26/1937    1/16/1962    8/21/1998   $   163.13
 Cash Out    M     1/2/1955    1/12/1977    1/25/1999   $      -
 Transfer    M   12/18/1975    1/19/1995    8/16/1998   $    64.13
 Transfer    M    4/16/1977    2/10/1997    8/17/1998   $    27.96
Terminated   M    7/16/1955    8/29/1974    8/14/1998   $   124.67
Terminated   M     9/5/1947   11/18/1968    8/21/1998   $   142.63
Terminated   M    1/11/1956     8/3/1993    8/14/1998   $    67.92
  Retired    M   11/29/1936    4/26/1972    8/21/1998   $   132.38
Terminated   M   12/17/1973     8/3/1993    8/16/1998   $    68.63
 Transfer    M     9/5/1969    1/20/1995    8/24/1998   $    64.13
Terminated   M    8/24/1955    6/19/1974    8/24/1998   $   125.88
Terminated   M     5/4/1962    2/19/1986    8/14/1998   $    90.17
Terminated   M    11/6/1968    8/23/1989    8/24/1998   $    80.38
Terminated   M     2/1/1953    4/26/1972    8/24/1998   $   132.38
Terminated   M   12/11/1962    4/25/1984    8/24/1998   $    96.38
Terminated   M   11/11/1966   10/13/1986    8/14/1998   $    88.42
Terminated   M    9/29/1947    5/22/1972    9/28/1999   $   183.01
Terminated   M    6/30/1962    1/19/1981    8/13/1998   $   105.42
  Retired    M     3/6/1938    1/22/1962    1/14/2000   $   226.46
Terminated   M    10/1/1954    3/23/1981    8/17/1998   $   105.63
Terminated   M   10/30/1957    3/10/1986    8/14/1998   $    90.17
Terminated   M   10/17/1961     7/7/1980    8/14/1998   $   107.17
Terminated   M    9/18/1953    8/11/1981    8/14/1998   $   103.92
Terminated   M   11/18/1970    1/15/1990    8/15/1998   $    79.38
Terminated   M    9/22/1962    8/22/1990    8/14/1998   $    76.67
Terminated   M    7/18/1961    4/20/1989    8/14/1998   $    80.67
Terminated   M    6/14/1953    10/2/1974    8/21/1998   $   125.13
 Transfer    M    12/4/1974    4/11/1994    8/17/1998   $    66.63
Terminated   M    7/29/1962    3/11/1992    8/15/1998   $    72.88
Terminated   M     3/2/1958    7/28/1976    8/14/1998   $   118.92
Terminated   M   10/19/1936    6/15/1955   12/31/1996   $   139.71
Terminated   M    5/16/1960    10/8/1979    8/14/1998   $   109.42
Terminated   M    1/15/1955     9/5/1984    8/14/1998   $    94.67
  Retired    M    5/10/1966     9/5/1984    8/14/1998   $    94.67
 Transfer    M   12/31/1963    7/20/1998    8/14/1998   $      -
Terminated   M    2/23/1962    4/19/1989    8/17/1998   $    81.38
Terminated   M     5/1/1942    7/13/1964    8/21/1998   $   155.88
Terminated   M     9/7/1952    3/24/1993    8/17/1998   $    69.63
  Retired    M    8/17/1935    2/27/1967    5/31/1999   $   154.25
Terminated   M    10/4/1962    9/23/1985    8/16/1998   $    92.13
  Retired    M    9/28/1939    1/18/1969    4/30/1999   $   147.80
Terminated   M    2/15/1959     9/6/1978    8/14/1998   $   112.67
Terminated   M   10/11/1957    3/18/1981    8/14/1998   $    87.58
Terminated   M    2/21/1951    8/11/1976    8/21/1998   $   119.63
Terminated   M    4/24/1943   11/19/1966    8/24/1998   $   148.63
 Transfer    M    6/29/1971    2/10/1997    8/17/1998   $    27.96
Terminated   M    4/14/1967    12/2/1991    8/16/1998   $    73.63
Terminated   M     7/7/1958    4/18/1979    8/14/1998   $   110.67
Terminated   M     4/1/1959    8/23/1982    8/31/1998   $   101.38
Terminated   M   11/12/1942    9/19/1963    8/24/1998   $   158.13
Terminated   M    3/21/1958    5/11/1977    8/14/1998   $   116.67
  Retired    M    7/11/1934    1/10/1962    8/21/1998   $   170.14
 Cash Out    M    3/13/1958     9/3/1984    4/13/1999   $      -
             M    8/20/1974    8/23/1993    8/14/1998   $    67.67
Terminated   M    12/6/1954    7/17/1973    8/24/1998   $   128.63
Terminated   M   11/11/1958    1/10/1979    8/21/1998   $   112.38
Terminated   M     8/2/1940   11/15/1967    8/24/1998   $   145.63
Terminated   M    11/3/1957    12/8/1976    8/16/1998   $   118.63
 Cash Out    M   11/19/1950     4/9/1969     5/5/1999   $      -
Terminated   M    3/21/1958     8/3/1977    8/14/1998   $   115.92
Terminated   M   12/27/1951   12/14/1987    8/14/1998   $    84.92
 Cash Out    M     7/8/1944   10/26/1976    8/14/1998   $      -
 Transfer    M     8/2/1974    9/27/1993    8/16/1998   $    68.13
 Transfer    M    5/19/1977    2/17/1997    8/14/1998   $    26.54
 Transfer    M   10/29/1969    9/14/1988    8/21/1998   $    83.38
 Transfer    M    9/11/1972    4/11/1994    8/10/1998   $    65.92
Terminated   M   10/31/1970    8/23/1989    8/16/1998   $    80.38
 Cash Out    M    3/23/1969    2/14/1994     3/1/1999   $      -
Terminated   M    7/24/1942    5/31/1966    8/21/1998   $   150.13
 Cash Out    M    9/12/1943    4/30/1969    8/14/1998   $      -
Terminated   M   12/30/1945    2/23/1970    8/24/1998   $   138.88
Terminated   M    11/8/1942     3/9/1964    1/14/2000   $   218.13
Terminated   M    5/22/1944    1/17/1967    8/24/1998   $   148.13
Terminated   M    4/29/1958    9/20/1976    8/21/1998   $   119.13
Terminated   M    6/15/1958    9/27/1978    8/17/1998   $   113.13
Terminated   M   10/15/1957    8/26/1985    8/14/1998   $    91.67
Terminated   M    7/14/1938    3/28/1977    8/14/1998   $   116.92
Terminated   M     7/7/1955    5/24/1979    8/17/1998   $   111.13
 Disabled    M   10/11/1942    3/26/1965    2/28/1999   $   157.88
Terminated   M    4/11/1966    7/31/1989    8/15/1998   $    80.63
  Retired    M     1/4/1935    1/11/1962    1/31/1999   $   166.92
Terminated   M     2/5/1960    12/8/1992    8/14/1998   $    69.92
Terminated   M     6/4/1951   10/13/1975    8/17/1998   $   122.13
Terminated   M    10/6/1963    9/10/1984     8/3/1998   $    94.67
Terminated   M    2/17/1956     5/2/1984    8/21/1998   $    96.38
Terminated   M     3/6/1965     1/1/1983    8/24/1998   $   100.38
Terminated   M   11/19/1966    9/11/1991    8/15/1998   $    74.38
 Cash Out    M    6/10/1971    8/10/1993     3/1/1999   $      -
Terminated   M    6/12/1969    2/23/1993    8/14/1998   $    69.17
Terminated   M    1/14/1952    3/26/1981    8/14/1998   $   104.92
Terminated   M    12/2/1966    10/1/1986    8/14/1998   $    88.42
Terminated   M    5/10/1964   11/30/1983    8/17/1998   $    97.63
Terminated   M    8/25/1968    2/24/1992    8/21/1998   $    72.88
 Transfer    M    12/7/1971    4/20/1998    8/16/1998   $     2.83
 Transfer    M    11/2/1966    4/11/1994    8/13/1998   $    65.92
Terminated   M    9/28/1967    5/14/1986    8/21/1998   $    90.38
  Retired    M    8/20/1939    9/24/1962    8/14/1998   $   160.42
Terminated   M    6/19/1962    3/19/1981    8/21/1998   $   105.63
Terminated   M    12/2/1965    7/27/1987    8/14/1998   $    85.92
Terminated   M     7/2/1941    7/25/1963    8/31/1998   $   158.63
Terminated   M    7/20/1957   11/19/1980    8/14/1998   $   105.92
Terminated   M     1/9/1946    5/22/1970    8/21/1998   $   138.13
  Retired    M   10/15/1968     3/9/1992     6/5/1998   $    50.84
  Retired    M     8/6/1933    8/18/1955    7/31/1998   $   181.67
  Retired    M   12/12/1933    1/16/1967     8/1/1997   $    93.92
  Retired    M    10/9/1933     3/8/1974    10/4/1998   $   127.59
  Retired    M    5/10/1932    1/23/1973   12/31/1998   $   132.96
  Retired    M    7/30/1932     6/2/1982   12/31/1996   $    30.63
  Retired    M    9/20/1947    9/27/1976     8/1/1997   $    74.58
Vlookup Functions:
You are a marketing manager for a local fast food restaurant. Below are the results for a s
Each student will be placed into one of three groups: "Sparse", "Regular", "Frequent"
Here is the distribution:
To accomplish this task we will need to use the VLOOKUP function. VLOOKUP is used as follows:
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

Where the parameters are:
lookup_value:     The value from your dataset that you want to match with the dataset you are looking in.
table_array:      The cell range that makes up the dataset you are looking in.*
col_index_num:    The column number of the data you want to return (with respect to the table you are looking in).
range_lookup:     A True/False value indicating if you want an exact match or not.

                     # of times eaten in one week Customer Group                    Vlookup matches the "lookup_val
                                 0 to 1                    Sparse                   It then finds the Corresponding col
                                 2 to 5                    Regular                  "Range_lookup" decides whether
                                   6+                     Frequent                  Using the table below, we aren't us
                                                                                    Be sure to "lock" (F4) the outlined t
    Student ID      Response     Group (Vlookup)      Group (Hlookup)
     2021390            4
     6516343            8
     1067997            1                                                                      columns
     1932915             0
     5092119             3
     6067345             5
     6995717            12
     1583929             0
     3181508             3                                                                     Now, try the same thing, e
     7149128             5                                                                     (Hint: Hlookup looks in firs
     5929982             1
     6765872             6
     7913829             7                                                                     rows
     9833003            3                                                                                1
     1249173             9                                                                               2
      130354             2
     1328287             1                                                                     There is also a function c
      142290             5                                                                     You can look this up in Ex
     4575128             6
     3378374             4
      426452            10
     4961118             3
     1235376             2
     3494408             0
w are the results for a survey conducted on campus.
gular", "Frequent"




 you are looking in).


 matches the "lookup_value" to the first column in the "table_array."
nds the Corresponding column using "col_index_num" and returns that value or text.
_lookup" decides whether we want an exact match or the next greatest number below the "lookup_value."
e table below, we aren't using an exact match, so range is "true" (Excel assumes true if omitted).
to "lock" (F4) the outlined table as the "table array."



             Labels for Vlookup
                    0 Sparse
                    2 Regular
                    6 Frequent


 Now, try the same thing, except using the Hlookup function and the table below.
 (Hint: Hlookup looks in first ROW, not column, to match the "lookup_value")


                  Labels for Hlookup
                   0           2     6
             Sparse Regular Frequent

 There is also a function called "lookup" with similar properties.
 You can look this up in Excel help.
okup_value."
Pivot Tables:
Let's say you are an analyst for a company and you are asked to analyze this information
by finding the average salary based on location.
Below you have the set of data that we are going to use a PIVOT TABLE to analyze.
Select the cell I8 and follow the instructions to insert a pivot table.
Have the pivot table display the count of female employees based on status and location.

 EMPLOYEE NAME         LOCATION                     SEX          SALARY          STATUS
ABATE, ADOLPH E. LOS ANGELES                         M             50,000      Part Time
ADAMS, DONNA       DALLAS                            F             65,000      Part Time
AARON, JAMES M.    CHICAGO                           M             27,000      Part Time
ADAMSON, JOSEPH CHICAGO                              M             33,000      Full Time
AFEWORK, ASTER E. DALLAS                             M             42,000      Full Time
ABASCAL, ANTONIO DETROIT                             M             42,000       Hourly
ABER, JEFFREY R.   NEW YORK                          M             87,000      Part Time
ADLER, EILEEN      NEW YORK                          F             92,000      Part Time
ABOUKHEIR, IMAD    DETROIT                           M             22,000      Part Time
ADGER, KIMBERLY N. LOS ANGELES                       F             65,000      Part Time
ABSHER, TAMMY      DALLAS                            F             27,000      Full Time
ADAMS, JOHN W.         DETROIT                        M              33,000    Part Time
ADAMS, WALTER L.       DALLAS                         M              42,000    Part Time
ABRAHAM, BONNIE        LOS ANGELES                    F              42,000    Part Time
ADAMS, ANGELA M.       NEW YORK                       F              87,000    Part Time
ADAMS, GARY A.         CHICAGO                        M              92,000    Part Time
ACCARDI, DEBORAH       CHICAGO                        F              22,000     Hourly
ADAMS, LYNETTA         LOS ANGELES                    F              65,000    Part Time
ABREU, RODNEY M.       NEW YORK                       M              27,000    Part Time
ADACHI, DAVID          LOS ANGELES                    M              33,000    Part Time
ABIGANIA, EDITHA S.    DALLAS                         F              42,000    Full Time
ACOBA, CESAR A.        DETROIT                        M              42,000     Hourly
ABIGANIA, RICHARD      CHICAGO                        M              87,000    Part Time
ADDANTE, LISA A.       DETROIT                        F              92,000    Part Time
ADAMS, SCHEKETAH       NEW YORK                       F              22,000    Part Time
Average of Salary
Solver
Solver is an excellent tool when we have to "backsolve" for things, and it's extremely handy when we have
Let's start with a simple example.
We have a rough income statement below. If we want our net income to be $10,000, what do our sales h

                        Sales                  $     300,000.00 <-----By changing this
50% of sales ---------> Variable Costs         $     150,000.00
                        Fixed Costs            $     200,000.00
                        Income before Tax      $     (50,000.00)
35% of income --------> Taxes                  $     (17,500.00)
                        Net Income             $     (32,500.00) <---In Solver, set this equal to $10,000



Now let's try an interest theory example…
Your parents wanted to help you pay for college. When you were born, they paid $4,000 for a savings bo
We want to figure out the implied interest rate on this bond.

            Initial Price                          Value at 18th Birthday
$            4,000.00                          $       11,417.36 <---In Solver, we want to set this equal to 10,000

          Interest Rate
                6.000% <--- By changing this                       You could try guessing and checking a few differen
                                                                   This process is simplified by Solver.
                                                                   Solver is on the far right of the "Data" tab.


Note: If Solver is not on the "Data" tab, you can add it by doing the following:
                     Click on upper left Windows icon
                     Click on "Excel Options" at the bottom
                     Go to the "Add-Ins" section
                     On the bottom "Manage:" dropdown list, select "Excel Add-Ins"
                     Click "Go…"
                     Check "Solver Add-In" and hit "OK"
mely handy when we have complicated math.

 000, what do our sales have to be?




  $4,000 for a savings bond that would be worth $10,000 on your 18th birthday.



 is equal to 10,000


d checking a few different interest rates. You'll find it's hard to get exactly the right number.

 the "Data" tab.
Now let's try a more complex example.

Let's say you've made your annual trip to the bank to cash in your change jar. The banker, having never h
needs a method to determine how to give you your money using the least number of bills and coins.
DO NOT CHANGE THE FORMULAS IN THE TABLE BELOW. We will not be changing the values at all; Solver wil

                                                               Value of Coins
                                                                     $56.32

                                                               Note: The value of our coins cannot be a

                    Number   Value
         Pennies      422     $ 4.22
         Nickels       1      $ 0.05
         Dimes         8      $ 0.80         This is obviously an easy thing to do in our heads. But it's a
         Quarters      9      $ 2.25         Solver using constraints becomes very handy in certain prob
         Singles      19      $ 19.00
         Fives         2      $ 10.00
         Tens          2      $ 20.00
         Twenty's      0      $    -

         Total         463   $ 56.32
e banker, having never had any math whatsoever,
 of bills and coins.
 values at all; Solver will do that for us.




of our coins cannot be any fraction of cents




o in our heads. But it's a great example of how complex we can make a solver problem.
ery handy in certain probability distributions with many parameters. (Loss Models)

								
To top