You might have encountered some formula solutions for FIFO Inventory but none rendered only a single formula. In this article, we will dissect the parts of each of our two formulas in the solution file.
The Circumstances of the Challenge
COVID Corp. buys products only at reorder point. Thus, the cost of its inventories assigned by using the first-in, first-out (FIFO) cost formula may originate from only the last two purchases.
The Rules of the Challenge
The formula solution must comply with all of these rules:
1. Except for TrnNum, TrnType, ProductName, TrnQty, and TrnPrice, no other name may be defined in the Name Manager;
2. Without referring to any cell in the range G1:L15, the formula in L18 must return the cost of the inventory using the FIFO cost formula for the product in J18; and
3. Regardless of the Excel version of the user, the formula must be confirmed only with Enter.
The Objective of the Formula
The formula in L18 aims to simulate the formula in H15, which requires all of these variables:
1. Quantity from the last purchase, which is 101 as shown in H10;
2. Quantity from the second to last purchase, which is 80 as shown in H11;
3. Price of the last purchase, which is 76.00 as shown in H13; and
4. Price of the second to last purchase, which is 65.00 as shown in H14.
The Submitted Legacy Formula
In celebration of my second anniversary therein, I proffered the FIFO Inventory Formula Challenge to the members of Microsoft Excel Tech Community. Besides the modern formulas from other experts, Sergei Baklan submitted the formula below, which Lori null improved and Peter Bartolomew edited:
=SUMPRODUCT(INDEX(TrnPrice,AGGREGATE(14,6,1/(ProductName=J18)/(TrnType<"Q")*TrnNum,IF(1,{1;2})),0),{1;-1}*MIN(INDEX(TrnQty,AGGREGATE(14,6,1/(ProductName=J18)/(TrnType<"Q")*TrnNum,1),0),
SUMPRODUCT(TrnQty*-1^(TrnType>"Q")*(ProductName=J18)))+{0;1}*SUMPRODUCT(TrnQty*-1^(TrnType>"Q")*(ProductName=J18)))When evaluated, the foregoing formula simulates the formula in H15, as shown below:
=SUMPRODUCT({76;65},
{101;80})The Required Variables in our First Formula
Our first formula requires the determination of these variables:
1. Quantity of the physical count;
2. Quantity of the last purchase;
3. Lower quantity between the physical count and the last purchase;
4. Quantity from the second to last purchase; and
5. Prices of the last two purchases.
The Quantity of the Physical Count
To determine the quantity of the physical count, we use the formula below:
=SUMPRODUCT(TrnQty*-1^(TrnType>"r")*(ProductName=J18))In the foregoing formula, array1 argument of SUMPRODUCT refers to:
The transaction quantities multiplied by -1’s (for sale transactions) or 1’s (for purchase transactions) multiplied by 1’s (for Courage products) or 0’s (for non-Courage products), which returns 181.
The Quantity of the Last Purchase
To determine the quantity of the last purchase, we use the formula below:
=LOOKUP(2,1/(TrnType<"r")/(ProductName=J18),TrnQty))In the foregoing formula, lookup_vector argument of LOOKUP refers to:
1 divided by 1’s (for purchase transactions) or 0’s (for sale transactions) divided by 1’s (for Courage products) or 0’s (for non-Courage products), which returns a vertical array of 1’s (for eligible transactions) or #DIV/0!’s (for ineligible transactions). Finally, LOOKUP returns the purchase quantity corresponding to the position of the last 1 in lookup_vector, which is 101.
The Lower Quantity between the Physical Count and the Last Purchase
To determine the lower quantity between the physical count and the last purchase, we use the formula below:
=MIN(SUMPRODUCT(TrnQty,-1^(TrnType>"r")*(ProductName=J18)),
LOOKUP(2,1/(TrnType<"r")/(ProductName=J18),TrnQty))In the foregoing formula, number1 argument of MIN returns 181 while number2 returns 101, which results to 101.
The Quantity from the Second to Last Purchase
To determine the quantity from the second to last purchase, we use the formula below:
=SUMPRODUCT(TrnQty*-1^(TrnType>"r")*(ProductName=J18))*{0,1}+
MIN(SUMPRODUCT(TrnQty,-1^(TrnType>"r")*(ProductName=J18)),
LOOKUP(2,1/(TrnType<"r")/(ProductName=J18),TrnQty))*{1,-1}In the foregoing formula, the physical quantity multiplied by {0,1} plus the lower quantity between the physical count and the last purchase multiplied by {1,-1} returns a horizontal array of {101,80}, which represents such lower quantity and the quantity from the second to last purchase, respectively.
The Prices of the Last Two Purchases
To determine the prices of the last two purchases, we use the formula below:
=LOOKUP(AGGREGATE(14,4,TrnNum*(TrnType<"r")*(ProductName=J18),
{1;2}),
TrnNum,
TrnPrice)In the foregoing formula, array argument of AGGREGATE refers to:
The transaction numbers multiplied by 1’s (for purchase transactions) or 0’s (for sale transactions) multiplied by 1’s (for Courage products) or 0’s (for non-Courage products), which returns an array of 1’s (for eligible transactions) or 0’s (for ineligible transactions). Consequently, AGGREGATE returns a vertical array of {31;22}. Finally, LOOKUP returns the purchase prices corresponding to the 31st and 22nd positions in lookup_vector, which are in a vertical array of {76;65}.
The Construction of our First Formula
To determine the inventory for Courage, we use the FIFO cost formula below:
=MMULT(SUM(TrnQty*-1^(TrnType>"r")*(ProductName=J18))*{0,1}+
MIN(SUMPRODUCT(TrnQty,-1^(TrnType>"r")*(ProductName=J18)),
LOOKUP(2,1/(TrnType<"r")/(ProductName=J18),TrnQty))*{1,-1},
LOOKUP(LARGE(TrnNum*(TrnType<"r")*(ProductName=J18),{1;2}),
TrnNum,TrnPrice))Because array1 argument of MMULT can handle array operations, we used SUM instead of SUMPRODUCT. Similarly, we used LARGE instead of AGGREGATE in array2. Thus, array1 returns a horizontal array of {101,80} and array2 returns a vertical array of {76;65}, which results to 12876.
The Construction of our Second Formula
To avoid the use of MIN and the repetition of the physical quantity variable, we use the triple MMULT formula below for Discipline:
=MMULT(MMULT({1,1},MMULT((SUM(TrnQty*-1^(TrnType>"r")*(ProductName=J22))*{1,1}+
LOOKUP(2,1/(TrnType<"r")/(ProductName=J22),TrnQty)*{1,-1})^
{1,1;1,2}^{1,1;1,0.5}*{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
LOOKUP(LARGE(TrnNum*(TrnType<"r")*(ProductName=J22),{1;2}),
TrnNum,TrnPrice))After evaluating the physical quantity, last purchase quantity, and last two purchase prices, the foregoing formula becomes:
=MMULT(MMULT({1,1},MMULT((34*{1,1}+
380*{1,-1})^
{1,1;1,2}^{1,1;1,0.5}*{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
{13;11})After multiplying the physical quantity by {1,1}, it becomes a horizontal array of {34,34}, as shown below:
=MMULT(MMULT({1,1},MMULT(({34,34}+
380*{1,-1})^
{1,1;1,2}^{1,1;1,0.5}*{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
{13;11})After multiplying the last purchase quantity by {1,-1}, it becomes a horizontal array of {380,-380}, as shown below:
=MMULT(MMULT({1,1},MMULT(({34,34}+
{380,-380})^
{1,1;1,2}^{1,1;1,0.5}*{1,1;1,-1},{1;1})
/2*{0,1;1,-1}),
{13;11})After adding {34,34} and {380,-380}, they become a horizontal array of {414,-346}, as shown below:
=MMULT(MMULT({1,1},MMULT({414,-346}^
{1,1;1,2}^{1,1;1,0.5}*{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
{13;11})To simulate the MIN construction, we manipulate the physical quantity and the last purchase quantity, based on the theory that:
The higher number between two numbers is equal to half of the sum of: (1) the sum of the two numbers; and (2) the absolute difference between such numbers. Conversely, the lower number between two numbers is equal to half of the excess of: (1) the sum of the two numbers; over (2) the absolute difference between such numbers. To convert -346 in {414,-346} to its absolute value of 346, we raise it first to the power of 2, and then to the power of 0.5. After raising {414,-346} to the power of {1,1;1,2}, they become a tetragonal array of {414,-346;414,119716}, as shown below:
=MMULT(MMULT({1,1},MMULT({414,-346;414,119716}^
{1,1;1,0.5}*{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
{13;11})After raising {414,-346;414,119716} to the power of {1,1;1,0.5}, they become a tetragonal array of {414,-346;414,346}, as shown below:
=MMULT(MMULT({1,1},MMULT({414,-346;414,346}*
{1,1;1,-1},{1;1})/
2*{0,1;1,-1}),
{13;11})After raising {414,-346;414,346} to the power of {1,1;1,-1}, they become a tetragonal array of {414,-346;414,-346}, as shown below:
=MMULT(MMULT({1,1},MMULT({414,-346;414,-346},
{1;1})/
2*{0,1;1,-1}),
{13;11})The innermost MMULT evaluates to a vertical array of {68;68}, as shown below:
=MMULT(MMULT({1,1},{68;68}/
2*{0,1;1,-1}),
{13;11})After dividing {68;68} by 2, they become a vertical array of {34;34}, as shown below:
=MMULT(MMULT({1,1},{34;34}*
{0,1;1,-1}),
{13;11})After multiplying {34;34} by {0,1;1,-1}, they become a tetragonal array of {0,34;34,-34}, as shown below:
=MMULT(MMULT({1,1},{0,34;34,-34}),
{13;11})The inner MMULT evaluates to a horizontal array of {34,0}, which represents the quantity from the last purchase and quantity from the second to last purchase, respectively, as shown below:
=MMULT({34,0},
{13;11})Finally, the last MMULT returns 442, as expected.
Copy of the Solution File
Did our formulas challenge your array manipulation skill? Kindly inform me of your thoughts on the solution file through a comment below.