Percentage doesn't add up

[Homer J]

Hi,
I got a problem with a planning tool I've built to show how many extra
sales are needed each month by my teams to hit a target and then an
overachieveing target.

I've also applied a weighting to each team to allow for experience. My
problem occurs when I then try to add the overachieveing target (eg.
12%) and then apply the weighting to each team, every result is only
96.44% of what I expected it to be.

The sum I'm using to calculate each teams stretched target is
=sum(stretched plan number of sales*team size as a percentage of total
staff)*(1+weighting applied to that team, which is also a percentage)

Please help!

 

[Bryan Hessey]

I cannot get the answer that you show.

Assuming 3 groups (to test) of 2 people, 2 people and 6 people in a
total staff of 10, and assuming total plan sales of 20

The first portion "stretched plan number of sales*team size as a
percentage of total staff" would show 4 and 4, and 12 for the larger
group.
The second portion "1+weighting applied to that team, which is also a
percentage" would equate to 1.12 for a 12% increase.

4 * 1.12 = 4.48 (twice) and 6 * 1.12 = 13.44

4.48 + 4.48 + 13.44 = 22.4, the same figure as the original figure of
20 times 1.12

Hope you can spot your error from that.

 

[Homer J]

Thanks for your reply but I have a question

6 * 1.12 = 13.44

Sholdn't this be 6.72 & then the numbers dont add up??

 

[Bryan Hessey]

Sorry, quoted wrong number, 2, 2 and 6 people with 20 sales = 4, 4 and
12

12 * 1.12 = 13.44

 

[joeu2004]

I got a problem with a planning tool I've built to show how many extra
sales are needed each month by my teams to hit a target and then an
overachieveing target.

A specific example with hypothetical values might facilitate the
discussion. And it might be better to provide actual Excel or other
mathematical formulas instead of English descriptions. That latter is
usually not sufficiently precise.

I've also applied a weighting to each team to allow for experience. My
problem occurs when I then try to add the overachieveing target (eg.
12%) and then apply the weighting to each team, every result is only
96.44% of what I expected it to be.

The sum I'm using to calculate each teams stretched target is
=sum(stretched plan number of sales*team size as a percentage of total
staff)*(1+weighting applied to that team, which is also a percentage)

Please clarify ....

Is the "stretched plan number of sales" the same as the "overachieving
target"? That is, it already incorporates (e.g) the 12% factor for
"overachieving". No need to multiply anything by 1.12, as one
respondent
did. Right?

Is the "weighting applied to that team" the same as "the weighting ...
to allow for experience"? And is it "then applied" __after__ the
overachieving factor (12%, e.g)? That is, the weighting factor is
different for each team, and it is unrelated to (e.g) the 12%
overachieving factor. Thus, we would not use 1.12 in place of the
"1 + weighting applied to that team", as one respondent did. Right?

If my first assertion is correct, what are you summing and why? I
would think an individual team's base overachieving target (before
weighting) is simply "stretched plan number of sales * team size /
total sales staff size".

And if you are summing (only) all of the accounts that team is
responsible for, why would you multiple my the team's size as a
proportion of the total sales staff?

Moreover, the description "1 + weighting ... as a percentage" does
make sense to me. I suspect you should to remove "1 +". But that
is based on the ass-u-me-tion that the "weighting ... to allow for
experience" means that a weak team would have a weighting factor
less than one (80%, e.g).

As for an explanation of the 3.56% error (1 - 96.44%), I cannot help
you, since you did not provide sufficient information, even
hypothetically.

 

[Homer J]

Sorry if I made this sound confusing.
This is what I'm trying to do. I have a plan for the whole business an
a number of teams working towards that plan. None of the team have th
same level of experience so I'm trying to add a weighting to each team
Then work out what percentage of the workforce each team is. Then spli
the whole plan by the percentage for each team and then add th
weightings to each team.
The stretched plan & over acheiveing plan are the same thing, that jus
me getting mixed up in my own terminology. It all goes wrong when I ad
the weightings

I've attached the spreadsheet I've made if its any help.
The top part is the basic plan split out between each team. The middl
part adds on the stretched target. The bottom part applys th
weighting. The total of the weighted section should equal the stretche
plan. My problem is it doesn't.

I really appreciate your help with this

+-------------------------------------------------------------------
|Filename: Planning Test.xls.zip
|Download: http://www.excelforum.com/attachment.php?postid=3692
+-------------------------------------------------------------------

 

[joeu2004]

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) to
the total sales staff (T). Thus, nominally, a team's
sales target is s = S*t/T. As a check, I expect
T = SUM(t).

However, in deference to the strengths and weaknesses
of each team, you apply a weighting factor (w) to
each team's goal. Consequently, the team's actual
sales target is s = S*w*t/T. As a check, I
expect that S = SUM(S*w*t/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). But it does not seem to have any
bearing on the formulas here.

Note-2: Notation like t is my way of indicating
subscripts. If you are not comfortable with such
formal notation, you can think of t 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*t). This is derived from the
"S = SUM(...)" check above.

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

Consider the following example:

t = { 2, 3, 4, 5, 6}, T = 20
w = {??, 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 = { 2, 3, 4, 5, 6}, T = 20
w = {1.04, 1.04, 1.05, 0.8, 1.1}
s = {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 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
cells.

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