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.
- Service 1 = $2MM
- Service 2 = $3MM
- Service 3 = $1MM
- Service 4 = $4MM
- 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!
