How to map/allocate product cost over time based on flat data?

jakesachs
Contributor

How to map/allocate product cost over time based on flat data?

We are doing a project around Margin by SKU and I am having trouble mapping/allocating product cost to a dynamic Time dimension. 

 

Attached is what our flat cost data looks like. DL is the location, 14190 is the item/SKU, and the 11/12/W1 are different cost types (variable, variable + labor, etc). We've got the cost and then the Effective date. As you can see, this item has had 3 cost changes, 1/21, 2/24, and 4/2/21. The IEEDT column is sort of an expiration date, but really the 1/21 costs expire on 2/24 when the cost is updated. Although these are updated on specific days, we are doing analysis on month/quarterly basis. I am fine with the 1/21 cost being the cost for January, the 2/24 cost being the cost for Feb/March, and the 4/2 cost being April-forward. 

 

I am having trouble figuring out how to assign these costs to each product given time. For example, the margin and costs for this product should be different when looking at January vs April. 

 

I've thought about having a module that is dimensionalized by Time, SKU, and cost type to house the costs by sku over time, but not sure how I would populate this module. I've attached a snippet of ideally what that looks like, just not sure how to map the costs to the time periods. 

1 ACCEPTED SOLUTION

Accepted Solutions
LipChean_Soh
Certified Master Anaplanner

Re: How to map/allocate product cost over time based on flat data?

Hi @jakesachs 

 

Do see if the below suggestion works.

1. 3 lists are created. 

LipChean_Soh_0-1622854242840.png.  LipChean_Soh_1-1622854252049.png.                 LipChean_Soh_2-1622854260219.png

 

2. A flat module is created.

LipChean_Soh_3-1622854330346.png

LipChean_Soh_4-1622854348144.png

 

3. A calculation module is created. Note that i just realized that 'DL' is not SKU, but you can just as easily add another list in the flat module (item 2), and apply MAX in 'CALC SKU Cost.Cost' below.

LipChean_Soh_5-1622854435642.png

LipChean_Soh_6-1622854484522.png

 

Please note that this is just one possible way of accomplishing this. The con of this approach is the TEXT comparison in 'CALC SKU Cost.Final Cost', i.e. IF TEXT(Cost) <> "-Infinity". Another approach i can think of which doesn't use TEXT comparison however uses 'Day' as timescale, which has its own con, i.e. large workspace.

 

Thanks,

LipChean

 

View solution in original post

1 REPLY 1
LipChean_Soh
Certified Master Anaplanner

Re: How to map/allocate product cost over time based on flat data?

Hi @jakesachs 

 

Do see if the below suggestion works.

1. 3 lists are created. 

LipChean_Soh_0-1622854242840.png.  LipChean_Soh_1-1622854252049.png.                 LipChean_Soh_2-1622854260219.png

 

2. A flat module is created.

LipChean_Soh_3-1622854330346.png

LipChean_Soh_4-1622854348144.png

 

3. A calculation module is created. Note that i just realized that 'DL' is not SKU, but you can just as easily add another list in the flat module (item 2), and apply MAX in 'CALC SKU Cost.Cost' below.

LipChean_Soh_5-1622854435642.png

LipChean_Soh_6-1622854484522.png

 

Please note that this is just one possible way of accomplishing this. The con of this approach is the TEXT comparison in 'CALC SKU Cost.Final Cost', i.e. IF TEXT(Cost) <> "-Infinity". Another approach i can think of which doesn't use TEXT comparison however uses 'Day' as timescale, which has its own con, i.e. large workspace.

 

Thanks,

LipChean

 

View solution in original post