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

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

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
>
>
>

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
> >
> >
> >

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