Portfolios of accounting data based on SIC codes

[emielbrak]

Dear all,

I am doing research on the effect of a merger/acquisition on the industry rivals.
If a company is an industry rival is based on the 4-digit SIC code.
I have 1863 unique M&A-announcements, which are all done in a specific 4-digit SIC code.
These 1863 SIC-codes are in one column. In total 234 unique SIC are in my data. Next to this column is the Year in which the acquisition is made.
These years range between 1900-2015. I want to get the average Cash position per M&A announcement of ALL companies active in that SIC-code at the year of the announcement.
I want to get this data from the Excel-tab in which I have data on the industry rivals downloaded from Compustat which are structured as followed.

Column 1: Year of Cash
Column 2: SIC code of the company
Column 3: Value of Cash

Multiple companies are active within the same SIC-code, so for example there are 4 companies active in SIC 1234 and 98 companies active in SIC 1245.
If an acquisition is done in SIC 1234 in Year 1995 there can be 4 companies active, but an acquisition done in SIC 1234 in 2014 there could be 10 companies active.
Could you help me providing with a Excel-code to match the SIC codes & Year of the M&A announcement with the average value of cash position of all companies active in that SIC code and in the year of announcement?
I tried some indexmatch/vlookup/hlookup functions but I did not succeed.

I would really appreciate your help. My excel is attached.
Kind regards,
Emiel

[mcmahobt]

A lot of values so it will take some time to calculate, but enter this formula into C2 on the Announcement worksheet as an array formula (confirmed with CTRL+SHIFT+ENTER), and drag down to fill.

Please Login or Register  to view this content.

[emielbrak]

Dear,
Although I implemented your given formula, only blanks appear in my excel-sheet.
Could someone check what I did wrong? Many thanks for helping me out!
File is attached.

Emiel

[mcmahobt]

Your Year and SIC code in the workbook you just attached are in a different order than your previously submitted one. Based on the one you most recently submitted, try this in C2 (again, entered as an array formula) and drag down to fill:

Please Login or Register  to view this content.

[emielbrak]

Dear mcmahobt,

I now understand your formula and it's working. The blank I was referring to is the " " implemented in formula.
However, what I want is the following, maybe I was not clear enough.
I want the sum of the cashposition for every match of SIC & Announcement Year.

For example, deal number 30 (row 31, tab Announcement) is SIC 3310 and Year 1995.
Than the formula should sum the cash position (column 3, tab Compusat) of row 1869, 4263, 14386, 15868, 17871, 22518, 29124, 29323, 33733, 34706, 36240 and 37613 because these all have the Year 1995 and SIC 3310. To find these row numbers I filtered on year 1995 and SIC 3310. The filter is still working in this attachment.

Hopefully, my question is more clear to you all.
Would love to see a formula I can use for all my deals.

Thanks

[oeldere]

See the attached file.

[mcmahobt]

Ahhhh this makes much more sense. In C2, enter this:

Please Login or Register  to view this content.

and drag down. This is NOT an array formula and only needs to be confirmed with ENTER. Note, however, that based on the example you provided of SIC 3310 and year 1995, the expected result should be zero since there is no Cash value associated with that year and SIC code.

[oeldere]

Your question in #5 with a pivot table solution.

See the attached file.

You file is to big so I can't show you.

I shortened your file, to show you.

[emielbrak]

Dear Oeldere,

Looking in your file and taking row 26 as example. You give as result cash 167403, however this is the first observation in 'Compustat' with SIC 2200 and Year 1995.
However, I need the sum of ALL cash positions in 'Compustat' with SIC 2200 and Year 1995 as manually showed in the attached file, tab Compustat.

[emielbrak]

This one worked, great. Thanks

[oeldere]

Since you got solutions from several members (in 7 and 8) it is always usefull to add to who you are replying.