Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Percentage doesn't add up
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! -- Homer J ------------------------------------------------------------------------ Homer J's Profile: http://www.excelforum.com/member.php...o&userid=26166 View this thread: http://www.excelforum.com/showthread...hreadid=395256 |
#2
|
|||
|
|||
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 Wrote: 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 ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=395256 |
#3
|
|||
|
|||
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??? -- Homer J ------------------------------------------------------------------------ Homer J's Profile: http://www.excelforum.com/member.php...o&userid=26166 View this thread: http://www.excelforum.com/showthread...hreadid=395256 |
#4
|
|||
|
|||
Sorry, quoted wrong number, 2, 2 and 6 people with 20 sales = 4, 4 and 12 12 * 1.12 = 13.44 Homer J Wrote: Thanks for your reply but I have a question Sholdn't this be 6.72 & then the numbers dont add up??? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=395256 |
#5
|
|||
|
|||
Homer J wrote:
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. |
#6
|
|||
|
|||
Sorry if I made this sound confusing. :( This is what I'm trying to do. I have a plan for the whole business and a number of teams working towards that plan. None of the team have the 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 split the whole plan by the percentage for each team and then add the weightings to each team. The stretched plan & over acheiveing plan are the same thing, that just me getting mixed up in my own terminology. It all goes wrong when I add 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 middle part adds on the stretched target. The bottom part applys the weighting. The total of the weighted section should equal the stretched 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 | +-------------------------------------------------------------------+ -- Homer J ------------------------------------------------------------------------ Homer J's Profile: http://www.excelforum.com/member.php...o&userid=26166 View this thread: http://www.excelforum.com/showthread...hreadid=395256 |
#7
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage Calculation | Excel Worksheet Functions | |||
Formatting percentage signs in Excel | Excel Discussion (Misc queries) | |||
Help with function to add percentage to cell based on checkbox. | Excel Worksheet Functions | |||
Percentage of overall attendance | Excel Discussion (Misc queries) | |||
display data as a percentage of a subtotal in excel pivot table | Excel Discussion (Misc queries) |