Hey there! I am trying to allocate monthly expenses across our business units (BUs) based on pre determined splitting criteria, some of those criteria change month over month, such as the percentage of revenue each business unit is producing. What I'm trying to do right now (but failed at it) is linking four different tables:
1. Transfers --> All the payments we have done in a month
2. Suppliers --> A list of all suppliers we currently have
3. Splitting criteria --> A list of all the possible splitting criteria (Equal splitting across BUs , splitting by revenue%, etc)
4. Splitting % --> Given each splitting criteria, how would the percentages be assigned to each BU each month. For example if the split is by revenue %, what percentage of revenue each BU produced every month, or if it is equal splitting all BUs get 33% every month. This table has the following columns: month, splitting criteria, pctg to BU1, pctg to BU2, pctg to BU3.
What I've been trying to do is:
1. To each supplier in the Suppliers table (2) assign a splitting criteria from the splitting criteria table (3). This I done by adding a field and manually assigning the splitting criteria one by one
2. To each bank transfer on the transfers table (1), which contains the supplier name, lookup the splitting criteria from the suppliers table (2)
3. Given the splitting criteria, and the payment date, on the transfers table (1) I would bring from the splitting % table (4) the following columns: pctg to BU1, pctg to BU2, pctg to BU3. This is where I'm stuck, when I try to do a lookup to the splitting % table (4) it does not appear as a lookup option.
4. Create three new columns on the transfers table (1):
* Total to BU1$ : pctg to BU1 x Transfer Amount
* Total to BU2$: pctg to BU2 x Transfer Amount
* Total to BU3$: pctg to BU3 x Transfer Amount
This way by summing each of this three columns every month I know how much was spent per business unit. I would really appreciate any help
