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

jakesachs
jakesachs
in Modeling

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. 

Tagged:

Best Answer

  • LipChean_Soh
    LipChean_Soh
    Answer ✓

    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

     

Categories