Round issue for small numbers based on % sell thru by customer

[dversloot1]

Hello,

I'm running into a small problem here that I'm hoping someone can help out with.

If I forecast a small number of units for a group of customers, then break it down based on historical customer sales, how do I sort out the rounding so that the break down will equal my group forecast?

Example

Grp Forecast	5
	FC allocation	Forecast
Customer 1	6%	0
Customer 2	13%	1
Customer 3	4%	0
Customer 4	15%	1
Customer 5	17%	1
Customer 6	7%	0
Customer 7	6%	0
Customer 8	8%	0
Customer 9	15%	1
Customer10	10%	0

<tbody>
</tbody>

When I remove all decimals, there are only 4 units. How do I ensure that all 5 get allocation appropriately?

 

[NeonRedSharpie]

In the above scenario, Customer10 should have a 1 since he returns 0.50. So, for this scenario:

=ROUND(B3*$B$1,0)

This assumes $B$1 is your Group Forecast and B3 is the start of Customer 1's percentage (6%).

 

[shg]

Sort by allocation?

Row\Col	A​	B​	C​	D​
1​	Grp Forecast		5​
2​
3​		Allocation​	Forecast​
4​	Customer 5	17%​	1​	C4: =--(SUM(C$3:C3) < $C$1)
5​	Customer 4	15%​	1​
6​	Customer 9	15%​	1​
7​	Customer 2	13%​	1​
8​	Customer10	10%​	1​
9​	Customer 8	8%​	0​
10​	Customer 6	7%​	0​
11​	Customer 1	6%​	0​
12​	Customer 7	6%​	0​
13​	Customer 3	4%​	0​

 

[dversloot1]

I guess to take it to the extreme, if the group forecast was 3 and you used your formula, only Customer 5 would get a 1 unit.
Any workaround for that?

Grp Forecast	3
	FC allocation	Forecast
Customer 1	6%	0
Customer 2	13%	0
Customer 3	4%	0
Customer 4	15%	0
Customer 5	17%	1
Customer 6	7%	0
Customer 7	6%	0
Customer 8	8%	0
Customer 9	15%	0
Customer10	10%	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[NeonRedSharpie]

I guess to take it to the extreme, if the group forecast was 3 and you used your formula, only Customer 5 would get a 1 unit.
Any workaround for that?

Grp Forecast	3
	FC allocation	Forecast
Customer 1	6%	0
Customer 2	13%	0
Customer 3	4%	0
Customer 4	15%	0
Customer 5	17%	1
Customer 6	7%	0
Customer 7	6%	0
Customer 8	8%	0
Customer 9	15%	0
Customer10	10%	0

<tbody>
</tbody>

Nope. There really isn't because they all round down to 0. Well, you can do this in column D:

=RANK(C4,$C$4:$C$13)

and then this in column E:

=IF(D4<=$B$1,1,0)

But that's getting into very very specific territory and will only work through a forecast of 8.

 

[dversloot1]

Nope. There really isn't because they all round down to 0. Well, you can do this in column D:

=RANK(C4,$C$4:$C$13)

and then this in column E:

=IF(D4<=$B$1,1,0)

But that's getting into very very specific territory and will only work through a forecast of 8.

I think I've figured it out.
It isn't pretty but it gets the job done.
I sorted the customers in order of highest to lowest allocation.
In C4 i wrote:
<c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)><c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)><c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)>=IF(SUM(C$3:C3)=C$1,0,IF(AND(SUM(C$3:C3) < C$1,ROUND($B4*C$1,0)=0),1,IF(SUM(ROUND(C$1*$B4,0),C$3:C3) > C$1,C$1-SUM(C$3:C3),ROUND(C$1*$B4,0))))
I dragged this down and across for other group forecasts.

Grp Forecast		3	5	10	15	20	25	100
	Allocation	Forecast
Customer 5	17%	1	1	2	3	3	4	17
Customer 4	15%	1	1	2	2	3	4	15
Customer 9	15%	1	1	2	2	3	4	15
Customer 2	13%	0	1	1	2	3	3	13
Customer10	10%	0	1	1	2	2	3	10
Customer 8	8%	0	0	1	1	2	2	8
Customer 6	7%	0	0	1	1	1	2	7
Customer 1	6%	0	0	0	1	1	2	6
Customer 7	6%	0	0	0	1	1	1	6
Customer 3	4%	0	0	0	0	1	0	3

<tbody>
</tbody>

</c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)></c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)></c$1,round($b4*c$1,0)=0),1,if(sum(round(c$1*$b4,0),c$3:c3)>