Hi, I have a problem I've been working on and I'm not quite satisfied with my solution.
I have a given sales quota and I need to allocate it to customers based on their past sales, but if less than 10 then no budget. For eg my budget is 50 and last years sales for customers were:
Customer Sales LY
cust A 10
cust B 12
cust C 5
cust D 15
TOTAL 42
So I won't budget to cust C, and then need to spread the 50 out amongst the other 3. I've looked a straight % increase but because it is a product, I can't use a fractional part and rounding becomes an issue. It leaves a few products unallocated which then have to be manually allocated which I'm trying to avoid.
What is the best way to do this please?
I considered using a VBA loop but I'm not sure how that would work. I have also looked at solver and goal seek but again I don't think they are quite right for my needs. I feel like this is something that must come up a lot, but I haven't been able to find a way to do it.
Any help would be much appreciated please!
Bookmarks