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!

Accepted answers

All comments

Wendy

Hi David and Rob

@DavidSmith , @rob_marshall

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

kjacokes

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

Quick Links

Can you help?

Unanswered questions

Best way to Architect Financial Statements in Anaplan for Scalability
I have seen two methods used to build financial statements and I am wondering what others have experienced in scalability/maintenance between the two or if there is a better approach. Assume that we have a flat data source for our general ledger within Anaplan. Our line item we are focused on within the source data is the…
Curious Comparison: Forecast Modeling for Unusual Products
I've been experimenting with some forecast modeling techniques in Anaplan recently and decided to test them on a few quirky, offbeat product categories just for fun. One of the categories I played around with was love dolls—yes, you read that right. It actually turned out to be more interesting than expected. Unlike…
OData Rest API for
Hi, Does anyone know how to get an outbound OData Rest API, (OAuth2) to establish a connection between SAP and Anaplan. Thanks Khaled