Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Mergers - market concentration - math help
Under Dept of Justice guidelines you have to assess a proposed merger's
impact on market concentration. For banks, that really boils down to deposit market share. The mechanics are that you square the market share (stated in percentage points, not decimals) of each bank PRE-MERGER and sum the squares. Then do the same thing after summing the market shares of the merger candidates. If the sum of squares AFTER the pro forma merger is 200 points or more higher than the sum BEFORE the merger, deposits must be divested to bring the change down to the 200 level. The assumption is that the divested deposits go to a bank new to the market. I can do this with goal seek and it works very well for a single market, but I'm hoping there is a formulaic way of doing this that I can apply to a grid of markets. Example Pre-merger Share Sh^2 Bank A 22 484 Bank B 18 324 Bank C 14 196 Bank D 13 169 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Sum 100 1,466 Banks C & D propose to merge Bank A 22 484 Bank B 18 324 Bank CD 27 729 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 100 1,830 Change in sum of squares is 364. Here's the goal seek solution. Note that the divested/reduction in market share for Bank CD gets assigned to Bank New Bank A 22 484 Bank B 18 324 Bank CD 24 553 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Bank New 3 12 100 1,666 Can somebody help me figure out how to do this, please. Thanks Duke |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More readable tables
Under Dept of Justice guidelines you have to assess a proposed merger's
impact on market concentration. For banks, that really boils down to deposit market share. The mechanics are that you square the market share (stated in percentage points, not decimals) of each bank PRE-MERGER and sum the squares. Then do the same thing after summing the market shares of the merger candidates. If the sum of squares AFTER the pro forma merger is 200 points or more higher than the sum BEFORE the merger, deposits must be divested to bring the change down to the 200 level. The assumption is that the divested deposits go to a bank new to the market. I can do this with goal seek and it works very well for a single market, but I'm hoping there is a formulaic way of doing this that I can apply to a grid of markets. Example Pre-merger Share Sh^2 Bank A 22 484 Bank B 18 324 Bank C 14 196 Bank D 13 169 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Sum 100 1,466 Banks C & D propose to merge Bank A 22 484 Bank B 18 324 Bank CD 27 729 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 100 1,830 Change in sum of squares is 364. Here's the goal seek solution Bank A 22 484 Bank B 18 324 Bank CD 24 553 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Bank New 3 12 100 1,666 Can somebody help me figure out how to do this, please. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More readable tables
If the merging banks' pre-merger shares are in cells b2 and b3, I calculate
the share required to be divested to a new bank as =IF(B2*B3<=100,0,(B2+B3-(B2^2+B3^2-2*B2*B3+400)^0.5)/2). And the merged banks' share would be the new banks' share subtracted from b2+c2. Clear as mud? ;-) --Bruce "Duke Carey" wrote: Under Dept of Justice guidelines you have to assess a proposed merger's impact on market concentration. For banks, that really boils down to deposit market share. The mechanics are that you square the market share (stated in percentage points, not decimals) of each bank PRE-MERGER and sum the squares. Then do the same thing after summing the market shares of the merger candidates. If the sum of squares AFTER the pro forma merger is 200 points or more higher than the sum BEFORE the merger, deposits must be divested to bring the change down to the 200 level. The assumption is that the divested deposits go to a bank new to the market. I can do this with goal seek and it works very well for a single market, but I'm hoping there is a formulaic way of doing this that I can apply to a grid of markets. Example Pre-merger Share Sh^2 Bank A 22 484 Bank B 18 324 Bank C 14 196 Bank D 13 169 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Sum 100 1,466 Banks C & D propose to merge Bank A 22 484 Bank B 18 324 Bank CD 27 729 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 100 1,830 Change in sum of squares is 364. Here's the goal seek solution Bank A 22 484 Bank B 18 324 Bank CD 24 553 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Bank New 3 12 100 1,666 Can somebody help me figure out how to do this, please. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More readable tables
Sorry; that last bit should be that the merged banks' share would be the new
bank's share subtracted from b2+b3. --BP "bpeltzer" wrote: If the merging banks' pre-merger shares are in cells b2 and b3, I calculate the share required to be divested to a new bank as =IF(B2*B3<=100,0,(B2+B3-(B2^2+B3^2-2*B2*B3+400)^0.5)/2). And the merged banks' share would be the new banks' share subtracted from b2+c2. Clear as mud? ;-) --Bruce "Duke Carey" wrote: Under Dept of Justice guidelines you have to assess a proposed merger's impact on market concentration. For banks, that really boils down to deposit market share. The mechanics are that you square the market share (stated in percentage points, not decimals) of each bank PRE-MERGER and sum the squares. Then do the same thing after summing the market shares of the merger candidates. If the sum of squares AFTER the pro forma merger is 200 points or more higher than the sum BEFORE the merger, deposits must be divested to bring the change down to the 200 level. The assumption is that the divested deposits go to a bank new to the market. I can do this with goal seek and it works very well for a single market, but I'm hoping there is a formulaic way of doing this that I can apply to a grid of markets. Example Pre-merger Share Sh^2 Bank A 22 484 Bank B 18 324 Bank C 14 196 Bank D 13 169 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Sum 100 1,466 Banks C & D propose to merge Bank A 22 484 Bank B 18 324 Bank CD 27 729 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 100 1,830 Change in sum of squares is 364. Here's the goal seek solution Bank A 22 484 Bank B 18 324 Bank CD 24 553 Bank E 11 121 Bank F 10 100 Bank G 6 36 Bank H 6 36 Bank New 3 12 100 1,666 Can somebody help me figure out how to do this, please. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More readable tables
You have done well, Grasshopper. Thank you so much!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Reference Math | Excel Worksheet Functions |