Homer J wrote:
I've attached the spreadsheet I've made if its any help.
[....]
+-------------------------------------------------------------------+
|Filename: Planning Test.xls.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3692 |
+-------------------------------------------------------------------+

I was not able to access the spreadsheet. I will
offer my own interpretation and examples. I hope
they are in line with what you are trying to do.
If not, perhaps you could fine-tune my examples by
substituting numbers of your own.

As I understand it, you have a target sales goal (S),
which you nominally distribute to each sales team
based on the proportion of the team size (t[i]) to
the total sales staff (T). Thus, nominally, a team's
sales target is s[i] = S*t[i]/T. As a check, I expect
T = SUM(t[i]).

However, in deference to the strengths and weaknesses
of each team, you apply a weighting factor (w[i]) to
each team's goal. Consequently, the team's actual
sales target is s[i] = S*w[i]*t[i]/T. As a check, I
expect that S = SUM(S*w[i]*t[i]/T).

I suspect your problem is in the choice of weights.
I will explain below. But first ....

Note-1: For the purposes of this problem, it does
not seem to matter that the target sales goal (S) is
actually an "overachieving" goal, for example 12% over
expected sales. That fact might affect some of your
own thinking, for example your choice of individual
weights (w[i]). But it does not seem to have any
bearing on the formulas here.

Note-2: Notation like t[i] is my way of indicating
subscripts. If you are not comfortable with such
formal notation, you can think of t[i] as cell names
T1, T2 or $T$1, $T$2 etc. The values S and T might
be constants; or (better) they might be references to
other cells, for example A1 and B1 or cells named
"Sales" and "Team".

I think the key is: you must choose weights such
that T = SUM(w[i]*t[i]). This is derived from the
"S = SUM(...)" check above.

It can be tricky to ensure T = SUM(w[i]*t[i]). The
easiest way might be to set up a column with
"w[i]*t[i]" in each cell, and experiment with values
of w[i] until the total of the column is T.

Consider the following example:

t[i] = { 2, 3, 4, 5, 6}, T = 20
w[i] = {??, 1, 1, 0.8, 1}

What should w[1] be for t[1] (2 people)?

The answer is 1.50. In this simple case, it can be
computed as w[1] = (t[1] + t[4] - w[4]*t[4]) / t[1].

That is, if you believe that team #4 can achieve only
80% of its goal and teams #2, 3 and 5 can achieve
only 100% of their goals, team #1 must pick up the
slack by achieving 150% of its goal.

More commonly, you might expect more than one team to
cover the slack of one or more teams -- if that is
possible. For example, if the total sales goal S is
100:

t[i] = { 2, 3, 4, 5, 6}, T = 20
w[i] = {1.04, 1.04, 1.05, 0.8, 1.1}
s[i] = {10.4, 15.6, 21, 20, 33}, S = 100

This can be very tedious to do if you have a large
number of teams. You probably cannot set up a formula
in every w[i] cell like the one for w[1] above. You
are likely to get "circular references" errors, unless
you can make some simplifying assumptions in some w[i]
cells.

Good luck! I hope this helps you uncover the source
of your numerical error.