Guidelines Executive Stock Option Experience Study
Data Formats: The file formats for the data must use the ASCII character set. The data may be submitted in either comma delimited value format (CSV) or in Excel (XLS). If the comma delimited data format is chosen, there should be a separate file submitted for each of three following data record layouts. If the Excel data format is chosen, the workbook should include at least one worksheet for each of the three following data record layouts. Data File Number 1: Option Grant Records • File naming convention: Since the econometric model to be implemented in this study requires information for each participating firm’s historical stock price path, some form of firm identification will be required. If you are using the comma delimited text format for data submission, please name the Data File Number 1 COMPANY_NAME_Grant.csv. If you are using the Excel spreadsheet format, please name the first worksheet in the Excel Workbook COMPANY_NAME_Grant. COMPANY_NAME should be replaced with your company’s name. Data entry protocol: 1. Number of options granted: The number of shares granted should be recorded as of the grant date. In other words, do not make any adjustments for stock splits that occur after the grant date. 2. Strike Price: The strike prices should be recorded as of the grant date. As above, do not make any adjustments for stock splits that occur after the grant date. 3. Vesting dates and Number of Options Vested: The date for each vest and the number of options vested on that date should be recorded for all vesting periods. In each case, the number of options should be recorded as of the grant date. As above, do not make any adjustments for stock splits that occur after the grant date. Thus, a firm that has a vesting structure of monthly vests over three years would require seventy two columns (thirty six paired entries) to record the date and the number of options that vested on that date over the thirty six month vesting period. Similarly, a grant that allows 100% vesting at the end of the first year would have only two columns (a single date and the number of options vested at that date).
•
Data File Number 2: Option Exercise Records • File naming convention: If you are using the comma delimited text format for data submission, please name the Data File Number 2 COMPANY_NAME_Exercises.csv. If you are using the Excel spreadsheet format, please name the second worksheet in the Excel Workbook COMPANY_NAME_Exercises. COMPANY_NAME should be replaced with your company’s name. Data entry protocol: 1. Number of options exercised: The number of shares exercised should be recorded as of the exercise date. In other words, unlike the values in Data File Number 1, these numbers are adjusted for stock splits. 2. Stock Price at Exercise: The price recorded should be the closing price on the exercise date. Data File Number 3: Option Modification/Cancellation Records • File naming convention: If you are using the comma delimited text format for data submission, please name the Data File Number 3 COMPANY_NAME_Cancellations.csv. If you are using the Excel spreadsheet format, please name the third worksheet in the Excel Workbook COMPANY_NAME_Cancellations. COMPANY_NAME should be replaced with your company’s name. Data entry protocol: 1. Number of Cancelled/Forfeited Options: The number of shares cancelled or modified should be recorded as of the cancellation/modification date. In other words, unlike the values in Data File Number 1, these numbers are adjusted for stock splits. Data Submissions: The preferred data delivery method is on one or more CDs sent by some insured overnight carrier such as FEDEX. The FEDEX should be addressed to: Professor Nancy Wallace Haas School of Business University of California, Berkeley Berkeley, CA 94720-1900 (510) 642-4732 wallace@haas.berkeley.edu
•
•
Please include the Confidentiality Form with your data submission if you send it on CDs. An alternative acceptable submission protocol is a file transfer protocol (FTP) providing the study data on your company secure internet website together with an account ID and password to Professor Nancy E. Wallace via US mail or other secure transmission mechanism. If you use FTP for submitting data, please send the Confidentiality Form separately.
Data Layout Specification Stock Option Experience Study (There should be three separate data files where each row is one data record. There should be a comma entered in the column between each variable field for the CVS format. Excel submissions should have the following column headings for each variable column.)
Data File Number 1: Option Grant Records Character Variable Name columns
1–15 Employee Code Number
Description
Encrypted employee code. The code format can be either character or numeric. Encrypted option grant identification code. The code format can be character or numeric. Date of the option grant. (Format: DDMMYYYY) Number of options that were granted on the grant date. Please refer to the Guidelines if there have been stock splits. Strike price of the option. Price should be reported accurately to the thousandth place. Please refer to the Guidelines if there have been stock splits. (Format: 9999.999) 1=Incentive; 2=Nonqualified Date at which the option expires. (Format: DDMMYYY) Description in words and percentages of the vesting structure. As an example, a grant that vests at the rate of twenty five percent per year over four years would appear as ( 25%/4 Yrs) First vesting date for the grant. (Format: DDMMYYYY)
17–31
Option ID
33–40 42–56
Grant date Number of options granted
58–65
Strike price
67 69–76 78–97
Type of options: Grant Expiration Date Vesting schedule description
99–106
First Vest: Date (1)
Character columns
108–122
Variable Name
First Vest: Number of Options Vested (1)
Description
Number of options that vest on the first vesting date. Please refer to the Guidelines if there have been stock splits. If there is a second vesting date for the grant, then record the vesting date. (Format: DDMMYYYY) If there is a second vesting period, then record the number of options that vested on the second vesting date. Please refer to the Guidelines if there have been stock splits. If there is a third vesting date for the grant, then record the vesting date. (Format: DDMMYYYY) If there is a third vesting period, then record the number of options that vested on the third vesting date. Please refer to the Guidelines if there have been stock splits If there is a fourth vesting date for the grant, then record the vesting date. (Format: DDMMYYYY) If there is a fourth vesting period, then record the number of options that vested on the fourth vesting date. Please refer to the Guidelines if there have been stock splits Continue to record subsequent vesting dates in order. Record subsequent numbers of options vested on each vesting date. If there is a Nth period vesting date for the grant, then record the vesting date. (Format: DDMMYYYY) If there is an Nth vesting period, then record the number of options that vested on the Nth vesting date. Please refer to the Guidelines if there have been stock splits
124–131
Second Vest: Date (2)
133–147
Second Vest: Number of Options Vested (2)
149–156
Third Vest: Date (3)
158–172
Third Vest: Number of Options Vested (3)
174–181
Fourth Vest: Date (4)
183–197
Fourth Vest: Number of Options Vested (4)
…
…
Nth Vest: Date (N) Nth Vest: Number of Options Vested (N)
Data File Number 2: Option Exercise Records Data columns Variable Name
1–15 Employee Code Number Option ID
Description
17–31
33–40 42–56
Exercise Date Number of Options Exercised
58–65
Stock Price on Exercise Date
67–81
Number of Shares Sold Same Day
Encrypted employee code. The code format can be either character or numeric. Encrypted option grant identification code. The code format can be character or numeric. Date that the option was exercised. (Format: DDMMYYYY) Number of options exercised on the recorded exercise date. Please refer to the Guidelines if there have been stock splits. Stock price on the exercise date. Price should be reported accurately to the thousandth place. Please refer to the Guidelines if there have been stock splits. (Format: 9999.999) Total number of shares sold on the same day as the option exercise. Please refer to the Guidelines if there have been stock splits.
Data File Number 3: Option Modification/Cancellation Records Data columns Variable Name Description
1–15 Encrypted employee code. The code format can be either character or numeric. Option ID Encrypted option grant identification code. The code format can be character or numeric. Cancellation Date Date that the option was cancelled or modified. (Format: DDMMYYYY) Number of Please refer to the Cancelled/Forfeited Options Guidelines if there have been stock splits. Reason: 1 = Deceased; 2 = Resigned; 3 = Retired; 4 = Repriced; 5 = Unknown Employee Code Number
17–31
33–40
42–56
58