Hello All,
I am stuck on this ask for DCA and I can't figure out what is holding me up. I have seen a number of people try to tell people the answer but after being stuck for over an hour would love some help understanding why my answer isn't working.
I am trying to recreate the DCA seen below with the description for the INV01 Inventory Ordering module:
Week 1 & 2 (blue highlight)
- Week 1 shows the result when Submit Purchase Order Request? is NOT checked.
- Week 2 shows the result when Submit Purchase Order Request? is checked (however, in the example nothing is writable).
Week 3 (purple highlight)
- Week 3 shows the result when Override Suggested Order Amount? is checked - Override Amount is writable.
Week 4 (green highlight)
- Week 4 shows the result when Override Shipping Method? is checked - Override Method is writable.
I believe my formulas are good for the AD02 Inventory Ordering Overrides module but open to suggestions:
AD02 Inventory Ordering Overrides | Formula |
|---|
Submit Purchase Order - Write? | 'INV01 Inventory Ordering'.Submit Purchase Order Request? |
Override Amount – Write? | 'Submit Purchase Order - Write?' AND 'INV01 Inventory Ordering'.Override Suggested Order Amount? |
Override Shipping – Write? | 'Submit Purchase Order - Write?' AND 'INV01 Inventory Ordering'.Override Shipping Method? |
I have adjusted the read and write access in the INV01 Inventory Ordering module as seen below:
I think I am very close, but for some reason the Final Shipment Amount for week 2 isn't show and thus throwing off a few numbers as seen below:
I am guessing this could be tied to one of my other formulas, but truly not sure at this point.
INV01 Inventory Ordering | Formula |
|---|
Beginning Inventory | IF NOT 'SYS01 Time Settings By Week'.Current Period? THEN PREVIOUS(Ending Inventory) ELSE 'DAT01 Beginning Inventory'.Beginning Inventory |
Forecast Demand | 'DEM03 Demand Forecast'.Final Forecast |
PO Submitted | IF Submit Purchase Order Request? THEN 1 ELSE 0 |
Confirmed PO Delivery | POST(PO Submitted, Final Shipping Time Weeks) |
Confirmed Purchase Order Receipt | IF Confirmed PO Delivery > 0 THEN OFFSET(Final Shipment Amount, Final 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 |
Production Orders | |
Reorder Flag | OFFSET(Ending Inventory, Shipping Time Weeks, 0) < 0 |
Submit Purchase Order Request? | |
Suggested Order Amount for the Month | MONTHVALUE(Forecast Demand) |
Override Suggested Order Amount? | |
Override Amount | |
Final Shipment Amount | IF NOT Submit Purchase Order Request? THEN 0 ELSE IF NOT Override Suggested Order Amount? THEN 0 ELSE Override Amount |
Shipping Method | 'SYS07 Distribution Center Details'.Default Shipping[LOOKUP: 'SYS08 SKU Details'.Distribution Center] |
Shipping Time Weeks | 'TRA01 Shipping Metrics by Week'.'Shipping Time (Weeks)'[LOOKUP: Shipping Method, LOOKUP: 'SYS08 SKU Details'.Product] |
Shipping Costs | Final Shipment Amount / 1000 * 'TRA01 Shipping Metrics by Week'.'Cost per 1000 Units'[LOOKUP: Shipping Method, LOOKUP: 'SYS08 SKU Details'.Product] |
Override Shipping Method? | |
Override Method | |
Final Shipping Method | IF NOT Override Shipping Method? OR ISBLANK(Override Method) THEN Shipping Method ELSE Override Method |
Final Shipping Time Weeks | 'TRA01 Shipping Metrics by Week'.'Shipping Time (Weeks)'[LOOKUP: Final Shipping Method, LOOKUP: 'SYS08 SKU Details'.Product] |
Final Shipping Costs | Final Shipment Amount / 1000 * 'TRA01 Shipping Metrics by Week'.'Cost per 1000 Units'[LOOKUP: Final Shipping Method, LOOKUP: 'SYS08 SKU Details'.Product] |
Shipping Cost Variance | IF NOT Override Shipping Method? THEN 0 ELSE Shipping Costs - Final Shipping Costs |
Please help and thank you
