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