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

jakesachs

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. 

Cost_Flat_data_110446.PNG

client_filter_view_110446.PNG

ideal_cost_view_110446.PNG

LipChean_Soh

Hi @jakesachs 

 

Do see if the below suggestion works.

1. 3 lists are created. 

.  .                 

 

2. A flat module is created.

 

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.

 

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