Hi all, first time posting, but always checked the site for Excel answers. Hope you can help!
My issue is that I need to come up with a formula that takes the following into consideration:
I have 1000 units of SKU A. I would like to allocate these 1000 units based on a few amount totals that have different, but important, weighted internal values to us.
Value A - Sales Volume of a particular Vendor YTD (first most important #)
Value B - Overall Sales Volume for the YTD (second most important #)
Value C - Total Quantity of orders placed YTD (third most important #)
Value D - Total Quantity of items purchased YTD (fourth most important #)
So, if I wanted to allocate customers product based on these criteria, I would have 4 reports. I would allocate the 1000 units in the following ways:
Value A - Sales Volume ($) of a particular Vendor YTD - 500 units (50% of qty available)
Value B - Total Sales Volume ($) for the YTD - 250 units (25% of qty available)
Value C - Total Quantity (#) of orders placed YTD - 150 units (15% of qty available)
Value D - Total Quantity (#) of items purchased YTD - 100 units (10% of qty available)
The idea is that based on different purchasing habits I would be able to allocate SKU A to a variety of accounts (big and small).
What would be the best way to write a formula? I'm having a hard time wrapping my head around this one. I would prefer to do some sort of VLOOKUP so I could do it all at once instead of 4 separate reports.
And any customer that would receive less than .75 of an item would be rounded down to 0 to hopefully come up with more accurate allocations.
What do you think?
My issue is that I need to come up with a formula that takes the following into consideration:
I have 1000 units of SKU A. I would like to allocate these 1000 units based on a few amount totals that have different, but important, weighted internal values to us.
Value A - Sales Volume of a particular Vendor YTD (first most important #)
Value B - Overall Sales Volume for the YTD (second most important #)
Value C - Total Quantity of orders placed YTD (third most important #)
Value D - Total Quantity of items purchased YTD (fourth most important #)
So, if I wanted to allocate customers product based on these criteria, I would have 4 reports. I would allocate the 1000 units in the following ways:
Value A - Sales Volume ($) of a particular Vendor YTD - 500 units (50% of qty available)
Value B - Total Sales Volume ($) for the YTD - 250 units (25% of qty available)
Value C - Total Quantity (#) of orders placed YTD - 150 units (15% of qty available)
Value D - Total Quantity (#) of items purchased YTD - 100 units (10% of qty available)
The idea is that based on different purchasing habits I would be able to allocate SKU A to a variety of accounts (big and small).
What would be the best way to write a formula? I'm having a hard time wrapping my head around this one. I would prefer to do some sort of VLOOKUP so I could do it all at once instead of 4 separate reports.
And any customer that would receive less than .75 of an item would be rounded down to 0 to hopefully come up with more accurate allocations.
What do you think?
