Allocation
Hello,
I have some questions regarding allocations. Hope I can get some advice from you. Thank you so much in advance!
Here is the background information:
- Source Module:
Flat file without time dimension.
| STATE | WSLR | BRAND | LINECODE | PDCN | MONTH | TARGET |
1 | MO |
| BUD | 3 |
| Jan 19 | 1,000 |
2 |
| 1216 | BDL | 3 |
| Feb 19 | 2,000 |
3 |
|
|
|
| 1111260 | Mar 19 | 3,000 |
4 |
| 1216 |
|
|
| Apr 19 | 4,000 |
5 | MO |
| BUD |
|
| May 19 | 5,000 |
6 | MO |
|
|
|
| Jun 19 | 6,000 |
7 |
|
| BUD |
|
| Jul 19 | 7,000 |
8 |
|
| BUD | 3 |
| Aug 19 | 8,000 |
9 |
|
|
|
|
| Sep 19 | 9,000 |
- Calculation Module:
Dimension: by WSLRPDCN, by Month
The target can be adjusted at 5 data level – 1) State, 2)WSLR, 3)Brand, 4)Line Code, 5)PDCN by month.
The main module is dimensioned by WSLRPDCN by time. WSLRPDCN is concatenation of 3 lists(dimensions) 1) WSLR, 2)PD, 3)CN
1)State is Parent of WSLR
2) Brand is Parent of PD
3) Line Code is Parent of CN
Calculate:
- For example, like Line 1 – if the target in certain state (MO), certain brand (BUD) and Line code (3) is $1K, the all WSLR (child) under this state (parent) are going be allocated equally, all PD (child) under this brand (parent) are going to be allocated equally, and all CN (child) under this Line Code (parent) are going to be allocated equally. Then combine with related WSLR, PD, CN to have target by WSLRPDCN
- For example, like Line 6 - if the target in certain State is $6K, then all WSLR under this state is going to be allocated equally with total $6K, then combine with all PD and all CN to have target by WSLRPDCN
Issues: How to manage this type of allocation? Thanks!
Answers
-
Hi David and Rob
May I ask your help on my questions? I'm not sure if it's doable but definitely want to get your opinions on it. Thank you so much in advance!
Wendy
0 -
Hi @Wendy,
Happy to try and help. Keep me honest on my understanding of your question and what piece of the allocation logic we're trying to figure out.
My understanding of the question is: "How one would approach the allocation in such a way that it a) works b) is easy to follow the step-wise logic c) accommodates allocating from parents to children, across multiple dimensions.
Regarding "a" and "b" above, from an approach perspective, whenever I am thrown a new allocation I will:
1. Break it down into as many steps as possible. I find that I start to confuse myself when I try to combine the data movement into a single, big step.
2. Make liberal use of separate dimensions; when proving out the allocation I try not to use concatenated dimensions. I build out each of my lists separately (e.g., State-WLSR as one list, Brand-PD as another list, LineCode-CN as a third list). And I add to those lists only the items I need in order to build out an allocation from a single source data point to several target data points. The idea here isn't to build out the whole thing, but to prove the math can work and to visualize the dimensionality in action.
3. Once I prove out the allocation logic for one source data point, I'll do it for a second, and a third. At that point I'll optimize the allocation for performance and size, rebuilding the allocation, but this time using concatenated lists. At this stage I am still not introducing the full data set, or full set of list items. I don't want to do this yet because I'm still figuring out module sizing and don't want to "**** up" my model. Note that at this stage I have the benefit of being able to referencing the example I build in #2, making this less of a problem-solving exercise and more of a translation exercise (I need to do the same math, just with concatenated lists instead of stand-alone lists)
4. Finally, once we've proven out the optimized, efficient design (from a size and performance perspective), we incorporate all of the items into our lists, to calculate real results we can validate, get an idea of total module size, and gauge performance.
Now to item "c". If I correctly understood you're asking about allocating parents to children, one way to approach this would be to:
1. In a staging module, dimensionalize your flat source data set by whatever the source dimensionality is, let's say it's by State, Brand, and LineCode
2. Create a process that updates a list with WSLRPDCN items rolling up to State-Brand-LineCode items (or whatever your source module dimensionality is); this process should run whenever any of these lists is updated AND whenever you load in a new source data set with allocable expense
3. In a target module, look at each of your WSLRPDCN items. Have a line item of value 1 called "# of Allocation Target Intersections". Sum up that value by State-Brand-LineCode. Then calculate the allocated expense in the target intersections by taking Allocable Expense from the source module (some dimension-traversing functions required; SUM and LOOKUP) and dividing it by the "# of Allocation Target Intersections" in the target module.
Let me know if you have any questions. Don't hesitate to e-mail me at [email protected] if you have any questions or want to hop on a quick call to chat.
Cheers,
Kevin
2
