Forum Discussion
FIFO Inventory Formula Challenge
To revel my second anniversary as a member of this EXCELlent Community, I hereby proffer this challenge to season your array manipulation skills. Perhaps, keenadvice mathetes Riny_van_Eekelen PeterBa...
How do you generate these exercises? Amazing!
Here I again didn't understand the logic behind (From Last Purchase Qty), just imitate your formula. First attempt
=LET(
type, -(EVEN(TrnType="Sale")-1),
prod, (ProductName=J18),
table, CHOOSE({1,2,3,4},TrnNum,TrnQty*type, TrnPrice, type*prod),
total, SUM(INDEX(FILTER(table,prod),0,2)),
two, INDEX(SORT(FILTER(table, prod*type=1),,-1),{1;2},{2,3}),
price, INDEX(two,,2),
lastQty, MIN(total, INDEX(two,1,1)),
QTYs, CHOOSE({1,2}, lastQty, total-lastQty),
MMULT(QTYs,price) )If logic will be more clear perhaps will do another attempt in few days.
Let me clarify the logic of the challenge, as follows:
1. Calculate the unsold quantity of the product by subtracting the sold quantity from the purchased quantity. For "Courage", the purchased quantity of 2,611 units minus the sold quantity of 2,430 units equals the unsold quantity of 181 units. Absent any contrary evidence of loss, the unsold quantity must be equal to the physical count.
2. Determine the quantity of the product from the last purchase. For "Courage", such quantity is 101 units from Transaction 31 at 76.00 per unit.
3. Determine the lower value between the physical count and the quantity from the last purchase. For "Courage", the lower value between the physical count of 181 units and the quantity from the last purchase of 101 units is obviously the latter. Thus, of the 181 units counted physically, 101 units originate from the last purchase of 101 units from Transaction 31 at 76.00 per unit while the remaining 80 units originate from the second to the last purchase, which is 760 units from Transaction 22 at 65.00 per unit.
For "Integrity", the lower value between the physical count of 170 units and the quantity from the last purchase of 550 units is obviously the former. Thus, the entire 170 units originate from only the last purchase of 550 units from Transaction 40 at 20.00 per unit.
4. Determine the value of the physically counted inventory under the FIFO method by multiplying the dissected units by their corresponding purchase prices. For "Courage", (101 x 76.00) + (80 x 65.00) = 12,876.00.
For "Integrity", (170 x 20.00) = 3,400.00.
Amazed by how I generate these formula exercises? These exercises are real business circumstances included in my textbooks on EXCELlent Financial Accounting and Reporting!
