Allocation

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 kjacokes@impetuscg.com if you have any questions or want to hop on a quick call to chat.

Cheers,

Kevin