I am kinda stuck at sprint 3 level 2 INV01 formulas, please check formulas and help me with changes

Hamzashaikk
Hamzashaikk
in Academy Discussions
expected result.jpg

this is expected result

result.jpg

this is my result

formulas used -

Beginning Inventory - IF 'SYS01 Time Settings By Week'.'Not 1st Week of Timescale?' THEN PREVIOUS(Ending Inventory) ELSE 'DAT01 Beginning Inventory'.Beginning Inventory

Forecast Demand - 'DEM03 Demand Forecast'.Final Forecast

Reorder Flag - OFFSET(Ending Inventory, Shipping Time Weeks, 0) < 0

Suggested order amount for month - MONTHVALUE(Forecast Demand)

PO Submitted - IF Submit Purchase Order Request? THEN 1 ELSE 0

Confirmed PO Delivery - OFFSET(PO Submitted, -Shipping Time Weeks, 0)

Shipping Method - 'SYS07 Distribution Center Details'.Default Shipping[LOOKUP: 'SYS08 SKU Details'.Supplied By]

Shipping Time Weeks - 'TRA01 Shipping Metrics by Week'.'Shipping Time (Weeks)'[LOOKUP: Shipping Method, LOOKUP: 'SYS08 SKU Details'.Product]

Confirmed Purchase Order Receipt - IF Confirmed PO Delivery > 0 THEN OFFSET(Suggested Order Amount for the Month, -Shipping Time Weeks, 0) ELSE 0

Ending Inventory - Beginning Inventory + Confirmed Purchase Order Receipt - Forecast Demand

Safety Stock Target - HALFYEARVALUE('DAT04 Distribution Center Parameters'.Weekly Safety Stock)

Safety Stock Exception Count - IF Ending Inventory < Safety Stock Target THEN 1 ELSE 0

Answers

  • Tiffany.Rice
    Tiffany.Rice

    I'd start by digging into the Beginning Inventory, the week 1 balance should be pulling from the data module. Ask your self these questions:

    1. Is my data correct in 'DAT01 Beginning Inventory'.Beginning Inventory?
    2. Are there any difference in the applies to (aka dimensionality) between the inventory and data modules? If yes, consider what formulas you may need to translate/aggregate the data into the inventory calculations (LOOKUP, SUM, etc).

    Once you get the beginning balance sorted you can then apply the same method to each subsequent line item. You may have to revisit past lessons if you find that the source module (beginning inventory, demand, etc.) are not reflecting the expected values.

    Hope this helps you to chart a path for your next steps.

  • Hamzashaikk
    Hamzashaikk

    hey thanks, problem is solved

  • kiaouu
    kiaouu

    @run 3 Applying the same procedure to each successive line item is possible once the beginning balance is sorted.

  • alecsomba
    alecsomba

    thanks for sharing appreciate it terrifier 3 soap2day

Categories