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.
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!
Solved! Go to Solution.
Could you please post data for such a mock up scenario that shows you input data and desired output data.
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.
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!
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?