Logic to remove and reallocate dollars

[cking6178]

Looking for some help on a straightforward problem, but the logic is somewhat complex. I know how to solve it in a "one-off" scenario, but hoping to be able to generalize it for future use.

I'm doing a pricing study and I have the following fields: Cost Pool, Seller, Buyer, Direct Expense, Markup %, Hierarchy (alpha descriptor to identify cost center and other business info), Service Name, Service % Split (some hierarchies are mapped to multiple services).

There are 13 sellers, 90 buyers, and ~850 hierarchies. In certain instances a buyer may only buy certain services. Sounds easy, except our allocation method "sells" at a Cost Pool level, so I need to manually back out the allocated dollars to certain services within specific seller/buyer relationships and reallocate the dollars to keep the Cost Pool and seller/buyer relationship "whole".

For Example:

Seller A sells 5 services in total within Cost Pool 1. Buyer A only buys 4 of the services from Seller A in Cost Pool 1. Seller A sells $10MM annually to Buyer A and our allocation method sells all 5 services.

1. Service 1 = $2MM
2. Service 2 = $3MM
3. Service 3 = $1MM
4. Service 4 = $4MM
5. Service 5 = $1MM

If Buyer A does not buy Service 2, I need to redistribute the $3MM across the remaining 4 services. Logically, I take Service 1 $'s/(Total Pool - Service 2) and get 28.5% and then multiply that with the $10MM pool, to get a new Service 1 of $2.85MM. Then repeat the process to identify the new, correct service$'s.

As mentioned above, I can solve this in a "one off scenario", but I'm hoping to build a repeatable generic formula to use for all the manual adjustments I need to make. The file is currently 68k records, so not ideal if I have to create a unique formula everytime.

Any help is much appreciated!

[DavidP]

Could you please post data for such a mock up scenario that shows you input data and desired output data.

[cking6178]

Hi David,

Thanks for the reply. I've attached a sample data set. The first tab has the sample data, the second tab shows the results I'm looking to achieve. Assuming it's a mental block on my part on how to achieve this via Alteryx and make it generic enough to replicate across the full data set.

[ChrisTX · Accepted solution]

Hi @cking6178

The attached workflow should meet your requirements.

Chris

[cking6178]

Hi @ChrisTX

Appreciate that. Looks like I'm not going to make it as generic as was hoping so I can cascade through the ~68k records. Thanks for your efforts!

[ChrisTX]

Replace the file for "Original Data" - import your ~68k records.  Replace the file for "Service to Remove" - import your full list.  Should get you what you need.  Or am I missing something?