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
[TABLE="width: 234"]
<tbody>[TR]
[TD]Grp Forecast[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC allocation[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD="align: right"]4%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD="align: right"]17%[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD="align: right"]7%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD="align: right"]6%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD="align: right"]8%[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD="align: right"]15%[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Customer10[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

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?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr]
[tr][td]

1​

[/td][td]Grp Forecast[/td][td][/td][td]

5​

[/td][td][/td][/tr]

[tr][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

3​

[/td][td][/td][td]

Allocation​

[/td][td]

Forecast​

[/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Customer 5[/td][td]

17%​

[/td][td]

1​

[/td][td]C4: =--(SUM(C$3:C3) < $C$1)[/td][/tr]

[tr][td]

5​

[/td][td]Customer 4[/td][td]

15%​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Customer 9[/td][td]

15%​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Customer 2[/td][td]

13%​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Customer10[/td][td]

10%​

[/td][td]

1​

[/td][td][/td][/tr]

[tr][td]

9​

[/td][td]Customer 8[/td][td]

8%​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

10​

[/td][td]Customer 6[/td][td]

7%​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

11​

[/td][td]Customer 1[/td][td]

6%​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

12​

[/td][td]Customer 7[/td][td]

6%​

[/td][td]

0​

[/td][td][/td][/tr]

[tr][td]

13​

[/td][td]Customer 3[/td][td]

4%​

[/td][td]

0​

[/td][td][/td][/tr]
[/table]

 

[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?
[TABLE="width: 237"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Grp Forecast
[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC allocation[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]13%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]4%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]15%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD]17%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD]7%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD]8%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD]15%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer10[/TD]
[TD]10%[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[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?
[TABLE="width: 237"]
<tbody>[TR]
[TD]Grp Forecast[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FC allocation[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]13%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]4%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]15%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD]17%[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD]7%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD]8%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD]15%[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Customer10[/TD]
[TD]10%[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 641"]
<tbody>[TR]
[TD]Grp Forecast[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Allocation[/TD]
[TD]Forecast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Customer 5[/TD]
[TD]17%[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]Customer 4[/TD]
[TD]15%[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Customer 9[/TD]
[TD]15%[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Customer 2[/TD]
[TD]13%[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]Customer10[/TD]
[TD]10%[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Customer 8[/TD]
[TD]8%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Customer 6[/TD]
[TD]7%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]Customer 1[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Customer 7[/TD]
[TD]6%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Customer 3[/TD]
[TD]4%[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

</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)>