Level 2, Sprint 3- 3.3.6 Activity: Test the Shipping Cost Formulas
Hi,
I am struggling with updating the formulas of Confirmed PO Delivery and Confirmed Purchase order receipt.
Regarding "Confirmed PO Delivery", I understand to replace OFFSET by POST, to push the data to future periods instead of pulling: POST(PO Submitted, -Final Shipping Time Weeks, 0).
But I don't get what is wrong with the formula of "Confirmed Purchase Order Receipt". Why should you replace OFFSET by POST?
In my opinion, the use of OFFSET is correct: when in a certain week the Confirmed PO Delivery is set to true, you have to look back in time which shipping amount was set?
By using POST in the formula you will look at certain point in time (EG week 3 FY20) to the confirmed PO Delivery, when it is "1" (because PO was submitted in week 1), the final shipment amount of week 3 (0) will be pushed to week 5 because there is a final shipping time of 2 weeks in Week 3. But, the purpose is to have the value of 1214 as "Confirmed Purchase Order Receipt" in week 3 as it is the final shipment amount in week 1. However, when using formula
IF Confirmed PO Delivery > 0 THEN POST(Final Shipping Amount, -Final Shipping Time Weeks, 0) ELSE 0
the value of 1214 (final shipment amount) will not be pushed to week 3 as the value of confirmed PO delivery is 0 in week 1.
What is the correct formula for confirmed purchase order receipt? Or am I interpreting the formulas in the wrong way?
Thanks in advance!
Answers
-
That's a great question. In-fact whenever I interview candidates this is one question that I keep on repeating. So here is the main difference between POST and OFFSET.
When you want to add multiple numbers coming from different time periods into one particular time period you use POST. See below example
Post n periods later = 1000 needs to be in the same cell and 2000 needs to be offset by -1 meaning it needs to pulled back by 1 period.
So, 1000 of Feb and 2000 of Mar needs to be added and shown in Feb 15 (1000+2000 = 3000)
OFFSET can't add multiple numbers
Hope that clarifies
Misbah
Miz Logix
5 -
Hi,
Thanks! Clarifies a lot!
0
