Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default More readable tables

You have done well, Grasshopper. Thank you so much!


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Reference Math Ralph Howarth Excel Worksheet Functions 0 January 26th 05 07:27 PM


All times are GMT +1. The time now is 08:28 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"