LBO Data Description

From edegan.com
Jump to navigation Jump to search

Work Scripts

SQL

  • E:\McNair\Projects\LBO\LBO academic paper script pre compustat
  • E:\McNair\Projects\LBO\LBO academic paper script post compustat

Stata

  • E:\McNair\Projects\LBO\STATAdatasetup2
  • E:\McNair\Projects\LBO\STATAanalysis2

Work Description

LBO Data and Patent Data

See script for exact work.

  • Get the source file for the LBOs
E:\McNair\Projects\LBO\LBO_List_and_data.xlsx Original data source
  • Clean it up
E:\McNair\Projects\LBO\LBO_cleaned_data.xlsx Names of LBO's/deal numbers with extraneous symbols and words removed
  • Match it against itself
E:\McNair\Software\Scripts\Matcher\Output\LBO_cleaned_data.txt-LBO_cleaned_data.txt.matched Original matched data
E:\McNair\Projects\LBO\LBO_cleaned_data_duplicates_removed
  • Get the patent data
Draw the distinct assignees
Z:\allpatentsprocessed\DistinctAssignees2
Match them against themselves
Z:\allpatentsprocessed\DistinctAssignees2matched
  • Load the LBO data into a dbase
  • Match the LBO data to the patent data
Z:\allpatentsprocessed\LBO Patent matched
  • Note - all data in allpatentsprocessed database
access it by logging on to researcher@McNair DBServ:/bulk/allpatentsprocessed
  • Create Table firstjoin_cleaned: Join patent data to assignee data, creating firstjoin_cleaned. firstjoin_cleaned shows organization names of assignees from the patent data and their patent numbers
  • Create Table secondjoin_cleaned: join firstjoin_cleaned to matchassignees data, creating secondjoin_cleaned. secondjoin_cleaned shows standardized organization names, organization names, and patent numbers
OLD PROCESS (NOT USED ANYMORE)
join secondjoin_cleaned to lbopatentmatch data, creating thirdjoin_cleaned. thirdjoin_cleaned shows LBO's names, standardized organization names, and their patent numbers in order to show lbo's that had patents
  a text file of the final copy can be found here
  Z:\allpatentsprocessed\LBO Patent Data Joined

create table lbopatentinfo showing lbo names, patent numbers, and grant dates of the patents

use lbopatentinfo to create table lbopatentsummary to show lbo names, amount of patents they have, minimum grant date years, maximum grant date years, and average grant date years

import lbo data matched against itself into table(lbodistinct), then use that table and lbopatentsummary to create to create lbofinal. lbofinal then shows distinct lbonames, number of patents, minimum grant year, maximum grant year, and average grant year

use lbofinal to create lboreallyfinal. lboreallyfinal is the same as lbofinal but with null space in number of patents replaced with 0

FINAL DATA IN THE FOLLOWING FOLDER
  E:\McNair\Projects\LBO

#After getting lboreallyfinal, create a table with all of its columns in addition to the lbo deal numbers, and whether it was public to private or private to private in the following steps
#import LBOcleaneddata.txt into table dealnumbxcleannames. it will have the deal numbers and cleaned original lbo names (duplicates not removed)
#import Originallbomatch.txt into table lbonamesxcleannames. it will have the matched standardized lbo names and the cleaned original lbo names
#join those two tables together to reach the standardized lbo names with the deal numbers in table lbonamesxdealnumb, and create a column that says whether the deal was public to private or private to private in the table lboxpriv
#join lbo_list_and_data and lbopatentmatch together using lboxpriv as a go between. this provides a reference in case we want to join using stdorgname to the patent database
#join lbo_list_and_data and lboreallyfinal together using lboxpriv as a go between. this is the final table it can be found in the following folder under the name lbopatentsreallyfinal
  :E:\McNair\Projects\LBO\Data Sets

Introduction of Compustat and Preparation for Stata

See script for exact work.

  • Create table sdc with SDC data imported from the initial LBO data.
  • Create table LBOCOMPUSTAT4 with Compustat data imported.
  • Create table dealnumbxgvkey selecting deal numbers and gvkeys from table sdc.
  • Create table compustatnamematch with compustat names matched against themselves imported
  • Create table stdcompnamestdorgnamematched with standardized compustat names matched against stdorgname
  • Create table matchedlbocompnames with standardized original lbo names matched to standardized compustat names
  • Create table compnamematchlbocompnames with standardized compustat names, compustatnames, and standardized original lbo names
  • Create table stdcompnamepatentjoin with variables standardized compustat names and patent numbers
  • Create table compustatpatentinfo with variables standardized compustat names, patent numbers, and grant dates
  • Create table stdcompnamexgvkeycleaned with variables standardized compustat names and gvkeys
  • Create table ALLGVKey with all distinct gvkeys from stdcompnamexgvkeycleaned
  • Create table AllYears with all years from 1970 to 2016
  • Create table KeysXYears with variables gvkey and year. Each gvkey corresponds to 47 years.
  • Create table keysxyearsxstdcompname with variables gvkeys, years, and standardized compustat names
  • Create table compupatentyearsummary with variables standardized compustat names, gvkeys, years, and number of patents per year
  • Create table dealnumbxgvkeyxlboyeartemp with variables deal numbers, gvkeys, LBO years, and exit years
  • Create table dealnumbxgvkeyxlboyear with variables deal numbers, gvkeys, lbo years, exit years, entry year 1, entry year 2, exit year 1, and exit year 2
  • Create table compupatentlboyearsummary with variables standard compustat names, gvkeys, years, number of patents per year, and lbo entries/exits in each year
  • Create table statastaging with all variables from compupatentlboyearsummary in addition to all the pulled compustat variables
  • Create table sicnaicsconversion with sic codes and naics codes as a conversion table for adding naics that correspond to the sic codes in blank rows
  • Create table statastagingnew with two extra columns, concordsic and concordnaics, then export to stata

Stata Work

See script for exact work.

Setup

  • Label Compustat/patent variables imported from SQL
  • Generate compound variables and label them
  • Set the data as panel data and a time series
  • Set the data for survival analysis
  • Generate variable of first two numbers in naics code/lagged variable of first two numbers
  • Generate log of non-ratio variables
  • Generate lagged variables
  • Find first fiscal year in which data appears for each company
  • Find last fiscal year in which data appears for each company, or set it to 2016
  • Generate additional lagged variables

Analysis

  • Summary of variables intended for use in hazard model (mean, count)
  • Run Cox Proportional Hazard Model multiple times to determine statistically significant variables
  • Regress on variables that are questionable to determine which may be correlated
  • Run Cox Hazard again with final list of variables
  • Run diff-and-diff

Regressions & Tables

The following is a summary of our variables intended for use in a Cox Proportional Hazards Regression as well as one regression.

  • E:\McNair\Projects\LBO\Saved STATA Tables.txt

Compustat Variables

Company Name

Ticker Symbol

CUSIP

Stock Exchange Code

Fiscal Year-End

ADDZIP -- Postal Code

CITY -- City

IPODATE -- Company Initial Public Offering Date

LOC -- Current ISO Country Code - Headquarters

NAICS -- North American Industry Classification Code

SIC -- Standard Industry Classification Code

STATE -- State/Province

FYEAR -- Data Year - Fiscal

ACT -- Current Assets - Total

ARTFS -- Accounts Receivable/Debtors - Total

AT -- Assets - Total

CEQ -- Common/Ordinary Equity - Total

CHE -- Cash and Short-Term Investments

DLC -- Debt in Current Liabilities - Total

DLTT -- Long-Term Debt - Total

LCT -- Current Liabilities - Total

LT -- Liabilities - Total

TXDITC -- Deferred Taxes and Investment Tax Credit

EBITDA -- Earnings Before Interest

NI -- Net Income (Loss)

OIBDP -- Operating Income Before Depreciation

OPITI -- Operating Income - Total

REVT -- Revenue - Total

SALE -- Sales/Turnover (Net)

TXT -- Income Taxes - Total

XINT -- Interest and Related Expense - Total

XRD -- Research and Development Expense

CDVC -- Cash Dividends on Common Stock (Cash Flow)

ESUBC -- Equity in Net Loss Earnings

IBC -- Income Before Extraordinary Items (Cash Flow)

PDVC -- Cash Dividends on Preferred/Preference Stock (Cash Flow)

XIDOC -- Extraordinary Items and Discontinued Operations (Cash Flow)

DT -- Total Debt Including Current

PRCC_F -- Price Close - Annual - Fiscal

XSTFWS -- Staff Expense - Wages and Salaries

EMP -- Employees

CSHO -- Common Shares Outstanding

XSGA -- Selling, General and Administrative Expense

INTAN -- Intangible Assets - Total

RE -- Retained Earnings

Resources

WRDS USER: mcnair WRDS PASS: 9Mil2015

http://www.axial.net/forum/how-private-equity-screens-lbo-candidates/ - Variables that private equity firms examine to decide whether or not to lbo

Retrieved from "http://www.edegan.com/mediawiki/index.php?title=LBO_Data_Description&oldid=7908"