Hi,
I am trying since couple of hours to construct a formula. However, unsuccessful. Do you guys have a solution? It’s for my Master Thesis.
My Problem:
I have 1000 firms that engaged in M&A activities. For those firms I need a Benchmark-Firm selected via Size (Market Capitalization) and Book-to-Market Value (BTMV) in the respective month in which the initial company engaged in M&A. All of those 1000 active Companies were active in different months spanning from 1997 to 2012.
In other words, I have 1000 firms and I would like to find another company for each initial company, which best fulfills two criteria in a specified month.
Criterion 1: The size (market capitalization) of the benchmark-firm needs to represent 70% to 130% of the size of the active firm. This criterion leads to a pre-selection, however there are still various potential benchmark-firms left.
Criterion 2: Out of the Benchmark-firms which are 70% to 130% of the size of the active firm, the final single benchmark-firm is found. This final selection is performed by choosing the benchmark-firm with the closest BTMV-ratio to the active firm.
From a datasets a pre-selection regarding Size is performed and in the next step the final selection is performed according to the BTMV ratio from another dataset. Both criterions need to be representing the month of the respective M&A activity. The matching between those two datasets (worksheets) can be performed by company name or a identifying code.
Short example:
Firm A is active in March 2001 and has a size of 100 and a BTMV ratio of 2 in March 2001. The size of the firms B,C,D is in the range of 70% to 130% in March 2001. Of these firms, firm D has the closest BTMV with 2,2 in March 2001.
The sample consists of 1000 active M&A companies and there are 2000 potential benchmark-firms, with values for each month from 1997 to 2012.
The size and the BTMV are given in two different worksheets with two common fields (firm name and firm code). In the end I require for further analysis just the name of the benchmark firm for each of the active firms.
It might be helpful to actually see the data, hence:
dropbox.com/s/h7bwcjrvjys5022/Forum.xlsx
To be fair, I wanted to mention that I have posted this problem in other forums as well. As I am a bit under time pressure and do not really have a lot of expereince with forums in general.
http://www.mrexcel.com/forum/excel-q...e-dataset.html
http://excelexperts.com/search-date-...arch-criterias
http://www.excelguru.ca/forums/showt...0351#post10351
Thank you guys so much!!!!
Max