Strategic Alliances, Firm Investment, and Firm Performance in the Software Industry
This was a project that I did for coursework in Advanced Database Management Systems.
Firms often choose to partner with one another in strategic alliances or joint ventures (for more on this topic, see Tafti et al. 2013, Tafti et al. 2014). Through strategic alliances, firms can combine resources, share intellectual property, and arguably, compete more effectively. However, within the software industry it is not clear whether alliances are truly helpful in boosting firm performance. Alliances have drawbacks or hazards. It may be that firms that are already successful attract more alliance partners, even when such partnerships might not really create value. While the maxim “correlation is not causation” is generally true, understanding the key summary statistics is a good place to start.
In this project, I had the following data to help assess the role of alliances in the software industry. The data spans over a decade of time (1999-2009), so one may see some long-term effects of alliance activity during that decade. To conduct the analysis, I needed to join the following datasets:
1) SoftwareAlliancesMasterNodeList.csv: This is the master node list file that identifies the firms to be considered in your analysis.
a) prim_cusip: Identifier code of the firm (5-digit CUSIP code); this is a common firm identifier used by the financial markets: http://en.wikipedia.org/wiki/CUSIP.
b) InNet: Binary classification, value of 1 indicating that the firm is within a software industry (i.e. “in network”, for the purpose of this exercise).
c) ln_sales:
d) Name: Name of the firm.
2) EdgeList_yyyy.csv; where yyyy stands for years 1999 through 2009. These files show the list of alliances formed between firms during each year. Each file corresponds to one of the years from 1999 through 2009. Each row represents an alliance connection formed between firms, listed by their CUSIP identifier. Alliances can join two firms in the software industry (where both have InNet=1), or they can join a firm in the software industry (InNet = 1) with a firm outside the software industry (InNet=0).
3) FirmMetrics2006-2010.csv: This contains firm performance and investment measures for publicly listed firms, not just those listed in the master node list.
- year: Year
- adv: Advertising investments
- rd: Research and development investments (R&D)
- physcap: Physical capital investments
- sales: Annual sales
- total_assets: Total assets
- curassets: Current assets
- operincome: Operating income
- pref_stock: Preferred stock
- sharesout: Shares outstanding
- invent: Inventory
- ltdebt: Long-term debtstock_price: Closing stock price at the end of year
- current_liabilities: Current liabilities
- employees: Number of employees
- NAICS industry classifications (one through five-digit versions), for more information,see: https://www.census.gov/eos/www/naics/
- cusip: Full CUSIP code
- prim_cusip: 5-digit CUSIP code
The tables needed setting up first. The first query creates a database named alliance. The second query is for the creation of a table masternodelist that contains information on cusip – an identifier for a company, innet- asn identifier that recognizes a company as s/w or non s/w. This information is provided as a csv sheet. The table needed to be created and the data loaded into it.
Next creation of the table edgelist which contains information on alliance between two firms or cusips in a particular year. We have been provided a separate csv sheet with separate data for each year. These sheets needed consolidation in to one table. There is also the table firmmetrics which contains all performance related metrics of firms from year 2006-2010.
The firmmetrics table needed to be updated with a new column invest_prop that was a calculated value based on its other metris. It was updated as invest_prop = (rd+adv+physcap)/total_assets. Also to the table firmmetrucs was added the column performance represents the calculated tobinq value for that particular firm.
Tobinsq= performance = (pref_stock + (stock_price* sharesout) + current_liabilities – currassets + invent + ltdebt)/(total_assets).
In addition to this another measure of firm performance roa was also added to this table.
roa = sales/total_assets;
Creation of a New Table updatededgelist containing information on Innets of the firms in alliance from a view was done. Based on this table a new table updatededgelist2 was created to have a new column investprop_avg that contained the average of invest_props of 2 cusips if they were both S/W firms and the invest_prop of the S/W firm if it was a S/w to non-S/W alliance.
Now for year-by-year metrics the table needed to be split by year and alliancetype (within software, outside software). The tables sw2006 through sw2009 report investment proportions for sw-sw alliances in that year. Similarly tables osw2006-osw2009 represent the investment proportions made by sw firms in a sw-non sw alliance.
A new table updatededgelist3 was created to join Edgemetrics with the new metrics performance, roa, sales, operating income and stockprice.
Now since the count of firms making alliances by the year was required, a new table summarizing exactly this information called alliancecounter was created. For the creation of this table the table edgemetrics was union all-ed with its column orders reversed such that the new column cusip1 of the table contained a de-duplicated list of all firms entering into alliances in a particular year.
Similarly the table nswcinves was created which counts the number of alliances of software firms with other than s/w firms along with the invest_prop of the s/w firm.From this table was created the table swcinves which counts the number of alliances within software firms within a year along with investprop_avg of the two s/w firms.
Finally the table sol3 was created that has performance and naics2d information on allied firms.
From this table was derived the table sol3ans that is the table which contains performance measures of specific firms within the mentioned naics2d code categories.
The github link to actual SQL work is here
https://github.com/metasubbu/Projects/blob/master/Strategic%20Alliances%20Analysis%20Project