I have twice previously considered modelling inventory, using both a simple averaging method to value the stock sold and then on a First In, First Out (FIFO) basis, which was a little bit trickier. Since then, I have been inundated with people asking me to complete the set: how do you model on a Last In, First Out (LIFO) basis?

There has been a little bit of naval gazing on this one
(Pearl Harbo(u)r is my favo(u)rite) – not because I couldn’t produce an
immediate solution, but that I tried to come up with a *cleverer*method. Well, truth is, dear reader, *I
failed*. Time to come clean.

Before anything, allow me to be clear here. The LIFO method of inventory valuation is prohibited under International Financial Reporting Standards (IFRS), although it is permitted under the United States generally accepted accounting principles (US GAAP).

Why the controversy? Potentially, LIFO may distort both the financial statements and financial analysis. There are several ways this can be allowed to happen. For example:

- assuming inventory costs increase over time, LIFO can understate a company's earnings for the purposes of keeping taxable income low
- the inventory valuation reported may be outdated, spoiled, expired or else obsolete
- in a liquidation scenario, earnings may be inflated artificially by selling off inventory with seemingly low carrying costs.

So do bear this all in mind before you start modelling this way! You might get “LIFO parole” if judged.

Having stepped off my sanctimonious soapbox, let’s consider
an example (available in the attached Excel file__)__:

I shall jump to precisely what the problem is here. Can you provide me with the formula for deriving the costs of the inventory in Period 6? The problem is I can only reduce the requirement by four [4] given the inventory procured in the same period, but I am unsure what my stock levels are remaining for the items purchased in previous periods for different prices.

Algorithmically, I can figure it out:

- In Period 1, I purchase 10, sell eight [8], for $8.40 (8 x $1.05) and have two remaining
- In Period 2, I purchase 20 and sell 14. As I have sold less or equal to the number purchased, the LIFO cost is easy, I just calculate 14 x $1.08 which gives me $15.12. I am left with two remaining still from Period 1 and six in Period 2
- In Period 3, I purchase five [5] but sell seven [7]. Therefore, the first five sold are easy to cost (5 x $1.00) but I have to check the remainder for the previous period, which there are sufficient (six), so the remaining three may be calculated (2 x $1.08), giving me a total cost of $7.16. I am left with two still from Period 1, four from Period 2 and none remaining in Period 3
- In Period 4, I purchase eight [8] but only sell five [5], so the costing is easy again, being (5 x $1.22, which is) $6.10. This leaves remaining inventory of two from Period 1, four from Period 2 and three from Period 4
- Period 5 works along the same lines. Since the purchases (17) exceed the sales (9), my costs are trivial being (9 x $1.16, which equals) $10.44. This leaves remaining inventory of two from Period 1, four from Period 2, three from Period 4 and eight from Period 5
- Finally, we get to Period 6. We only procured four [4], but we sold 18. The first four items are easy to cost (4 x $1.19), but the remaining 14 need to be sourced. Eight will come from Period 5 (8 x $1.16), three will come from Period 4 (3 x $1.22) and the remaining three from Period 2 (3 x $1.08). This will be a total cost of $20.94, leaving two remaining from Period 1 and one from Period 2.

See how awkward this is getting and the fact we are having
to keep a running total of remaining inventory levels by period? It is easy when the number of items used is
less than or equal to the total items bought.
The issue is when we have to raid the cupboard for older supplies. We need to know what is remaining and that
depends upon what was used last period.
However, that period’s data depends upon what was used the period prior
to that. And so on. This is an example of recursion and recursive
formulae require Excel’s latest *wunderkind* **LAMBDA**. That takes me back *(groan – Ed.)*. Given this function is not available in all
versions of Excel, I shall not be exploring that option here.

Therefore, unlike solutions for weighted average costing and
First In, First Out (FIFO) methods, I shall have to fall back on what is known
as a grid calculation. I have been unable
to construct a shortcut approach that will bypass this recursion. For those who fail to see the distinction
between the other methods and the LIFO scenario, whilst it’s true that both the
weighted average and the FIFO methods required data from the previous period,
they only needed aggregated totals; here, we have to keep tabs on what stock
remains and in which period it was purchased, *i.e. *an ever-increasing
array of values. Excel’s in-cell
calculations using “traditional, long served” functions do not appear to assist
in this instance.

To be clear, not for a moment am I stating unequivocally there isn’t some shortcut technique, it’s just as at the time of writing, I haven’t thought of one (and I have even started to get imaginative with some of the lesser-known financial functions to try and concoct some Machiavellian approach). Alas, so far, it has eluded me.

Therefore, let’s consider the grid approach:

Do the values in rows 25:30 of
the graphic look familiar? This is
keeping a running total of what inventory still remains. As explained above, this is essential for
working out costing and also useful for working out the age or remaining
inventory, possible stockouts, *etc.*

In order to derive these numbers, I used a favourite function of mine…

*Refresher on OFFSET*

It seems every other article I bring this old chestnut up! The syntax for **OFFSET** is as follows:

**OFFSET(reference,
rows, columns, [height], [width])**

The arguments in square brackets (**height** and **width**)
may be omitted from the formula, but I will need them here. In its most basic form, **OFFSET(reference, x,
y)** will select a reference **x** rows down (**-x** would be **x**rows up) and **y** columns to the right (**-y** would be **y** columns
to the left) of the reference **reference**.
For example, consider the following grid:

**OFFSET(A1,2,3)** would take us two rows down and
three columns across to cell **D3**.
Therefore, **OFFSET(A1,2,3)** = 16, *viz.*

**OFFSET(D4,-1,-2)** would take us one row up and
two rows to the left to cell **B3**.
Therefore, **OFFSET(D4,-1,-2)** = 14, *viz.*

**OFFSET** may also take advantage of the **height** and **width** arguments. If we extend
the above formula to **OFFSET(D4,-1,-2,-2,3)**, it would again take us to
cell **B3**, but then we would select a range based on the **height** and **width** parameters. The **height** would be two rows going up the sheet, with row 14 as the base (*i.e*. rows
13 and 14), and the **width** would be three columns going from left to
right, with column **B** as the base (*i.e*. columns **B**, **C** and **D**).

Hence, **OFFSET(D4,-1,-2,-2,3)** would select the range **B2:D3**,*viz.*

Note that **OFFSET(D4,-1,-2,-2,3)**= *#VALUE!* where dynamic arrays are not recognised, since Excel cannot
display a matrix in one cell, but it does recognise it. However, if after typing in **OFFSET(D4,-1,-2,-2,3)** we **press CTRL + SHIFT + ENTER**, we turn the formula into an array
formula: **{OFFSET(D4,-1,-2,-2,3)}** (do not type the braces in, they will
appear automatically as part of the Excel syntax). This gives a value of 8, which is the value
in the top left-hand corner of the matrix, *but Excel is storing more than
that*. This can be seen as follows:

**SUM(OFFSET(D4,-1,-2,-2,3))**= 72 (*i.e.***SUM(B2:D3)**)**AVERAGE(OFFSET(D4,-1,-2,-2,3))**= 12 (*i.e*.**AVERAGE(B2:D3)**).

**SUM(OFFSET)** and **OFFSET** will both be useful
here.

*Returning to LIFO*

Let’s revisit the grid:

The formula in cell **J25** is

**=IF(J$24>$E25,"",IF(J$24=$E25,MAX(J$13-J$14,0),MIN(MAX(SUM(OFFSET(J24,,,,$E25-J$24))+OFFSET($I$13,,$E25)-OFFSET($I$14,,$E25),0),J24)))**

Lovely. I think this needs to be broken down!

Note that row 24 and the cells **E25:E34** both denote
the period number, so for eight periods we would have an eight by eight grid,
and here, we have a 10 by 10 matrix.
Clearly, I cannot have any remaining inventory relating to periods later
than the period presently being analysed, *e.g.* for Period 3, I could not
take into account stock to be purchased in Period 4 or subsequently. This rules out half of my matrix. The formula

**=IF(J$24>$E25,"",
…**

considers this. If
the period number to be analysed is if the period of usage the formula returns
empty data (**“”**). So far, so
good. The next period to consider is the
actual period items are purchased and used (*i.e.* the leading diagonal of
the grid), when the period number in the row and column are equal:

**IF(J$24=$E25,MAX(J$13-J$14,0),
…**

In this situation, the remaining stock would be given by any
excess of purchases over COGS usage, so for cell **J25** this would equate
to **MAX(J$13-J$14,0)**. The
restriction is so that this number may not go negative. It’s a simple calculation. The issue is what follows!

**MIN(MAX(SUM(OFFSET(J24,,,,$E25-J$24))+OFFSET($I$13,,$E25)-OFFSET($I$14,,$E25),0),J24)**

Here, I have constructed a formula that is both replicable
and scalable (this is why I have needed the **OFFSET **function).

Given the recursive nature of the calculation, perhaps starting in Period 1 is not ideal as this situation cannot occur (this calculation only comes into force when we are looking to use prior period inventory to service current year usage – in the first period, by definition, we will have no prior inventory in this example).

Therefore, it is probably more appropriate to consider the
copied down formula in cell **J26** instead:

**MIN(MAX(SUM(OFFSET(J25,,,,$E26-J$24))+OFFSET($I$13,,$E26)-OFFSET($I$14,,$E26),0),J25)**

Let’s first consider the difference between the purchases
made in Period 2 (cell **K13**, 20) and the COGS usage (cell **K14**,
14). This would be given by the formula

**K13-K14**

Now the problem here is I want to copy this formula across the row so that it always references these cells:

**$K13-$K14**

That’s OK, but when I copy the formula down a row I also need it to anchor to rows 13 and 14 respectively:

**$K$13-$K$14**

However, on the next row down I will want to reference rows **L13-L14**,*etc. *I would have to write a
different formula each row, which would become cumbersome: imagine coding a 20-year
monthly model. No thank you. This is why I use

**OFFSET($I$13,,$E25)-OFFSET($I$14,,$E25)**

This uses the cell before the first purchased amount and the
first used amount and then moves one column across for every row of the table
(so Period 1 will use column **J**, Period 2 will use column **K**, *etc.*). This now makes sense.

Whilst this formula seems long this is now keeping track of the surplus or deficit of the purchases with regard to the COGS usage for the current period. I write it this way round so that if it is in deficit it will be negative. Let me park this part of the calculation for a second.

Allow me to now consider

**SUM(OFFSET(J25,,,,$E26-J$24))**

This formula considers the cell directly above the cell in
question (so here it references **J25** as the formula is in cell **J26**). It doesn’t move any rows or columns and the
height / depth is not defined (so it will just be the current row) but the
width is given by the formula **$E26-J$24**, *i.e. *the current period
number less the period number of the inventory being considered. Therefore, for cell **J26**, the width
would be the current period (2) less the period the inventory in question was
acquired (1), which would be 2 – 1 = 1.
Thus, **SUM(OFFSET(J25,,,,$E26-J$24)) **is equivalent to **=SUM(J25)**.

That seems convoluted.

But let’s take the formula in cell **K30** instead (the
formula relating to inventory acquired in Period 2 but considered in Period 6):

**=IF(K$24>$E30,"",IF(K$24=$E30,MAX(K$13-K$14,0),MIN(MAX(SUM(OFFSET(K29,,,,$E30-K$24))+OFFSET($I$13,,$E30)-OFFSET($I$14,,$E30),0),K29)))**

The relevant part of the formula here would be

**SUM(OFFSET(K29,,,,$E30-K$24))**

Here, the width would be the current period (6) less the period
the inventory in question was acquired (2), which would be 6 – 2 = 4. Thus, **SUM(OFFSET(K29,,,,$E30-K$24)) **is
equivalent to **=SUM(K29:N29)**.
Similarly, the relevant part of the formula in cell **L30 **would be **=SUM(L29:N29)**and the formula in cell **M30 **would be **=SUM(M29:N29)**.

This is a cumulative sum in reverse and has the same effect as putting the formula

**=SUM(N29:$N29)**

in cell **N30 **and copying it*right to left*. The advantage of
using the **SUM(OFFSET) **formula is you may copy it down and across the
entire matrix: it is repeatable and scalable, even if it is unintelligible!

Thus, returning to our original formula in cell **J25**,

**=SUM(OFFSET(J24,,,,$E25-J$24))+OFFSET($I$13,,$E25)-OFFSET($I$14,,$E25)**

this formula sums all the past inventory available acquired from that inventory period onwards that is still available (this is why it refers to the previous row) and adds any current period surplus / deducts any current period deficit.

If there is a deficit, the sum of the prior periods’ inventory will be reduced (and indeed, could become negative). This is not possible, so we need to restrict the sum:

**=MAX(SUM(OFFSET(K29,,,,$E30-K$24))+OFFSET($I$13,,$E30)-OFFSET($I$14,,$E30),0)**

However, if this value is
non-zero, it is possible the surplus could exceed the inventory for this period
previously calculated. That is clearly
incorrect, so we must restrict the value to be no greater than the prior period
balance for the period in question, *viz.*

**=MIN(MAX(SUM(OFFSET(K29,,,,$E30-K$24))+OFFSET($I$13,,$E30)-OFFSET($I$14,,$E30),0),K29)**

This effectively calculates which inventory is utilised on a LIFO basis to cover any deficit and is clearly demonstrated in the grid. Putting it all together, we obtain our original monster calculation:

**=IF(J$24>$E25,"",IF(J$24=$E25,MAX(J$13-J$14,0),MIN(MAX(SUM(OFFSET(J24,,,,$E25-J$24))+OFFSET($I$13,,$E25)-OFFSET($I$14,,$E25),0),J24)))**

This wouldn’t be as horrible if we modified the formula for each row, but written this way, the matrix may be increased seamlessly.

Now we have the remaining inventory profile, calculating the
COGS for the period is relatively simple with the assistance of the **SUMPRODUCT **function. And yes, I know, it must
be at least four minutes since I last mentioned this function…

*Refresher on SUMPRODUCT*

Consider the following example:

Here, I have various pricing points and the corresponding
quantities sold. To calculate my total
sales, I can compute my sales by taking the __product__ of **Unit Price **multiplied
by **Quantity** on a line by line level and then __summing__ them. As you can see, **SUMPRODUCT** does it all
in one go:

**=SUMPRODUCT(B3:B11,C3:C11)**

Here, I will combine it with **OFFSET**…

*Calculating COGS*

The formula in cell **F25** calculates the Total Costs
and is given by

**=SUMPRODUCT(OFFSET($J$13,,,,$E25)*OFFSET($J$16,,,,$E25))**

You are probably starting to understand these formulae
now. This formula takes the Purchases
(row 13) and multiplies them by the Purchase Price (row 16), before summing the
total. The width of the range extends by
one period each time, *e.g.*

**=SUMPRODUCT(OFFSET($J$13,,,,$E25)*OFFSET($J$16,,,,$E25))
= SUMPRODUCT(J13,J16) **

**=SUMPRODUCT(OFFSET($J$13,,,,$E25)*OFFSET($J$16,,,,$E25))
= J13*J16**

**=SUMPRODUCT(OFFSET($J$13,,,,$E26)*OFFSET($J$16,,,,$E26))
= SUMPRODUCT(J13:K13,J16:K16) **

**=SUMPRODUCT(OFFSET($J$13,,,,$E26)*OFFSET($J$16,,,,$E26))
= (J13*J16) + (K13*K16)**

**=SUMPRODUCT(OFFSET($J$13,,,,$E27)*OFFSET($J$16,,,,$E27))
= SUMPRODUCT(J13:L13,J16:L16)**

**=SUMPRODUCT(OFFSET($J$13,,,,$E27)*OFFSET($J$16,,,,$E27))
= (J13*J16) + (K13*K16) +(L13*L16)**

*etc.*

This clearly gives the total purchase cost up to and including the period in question.

Similarly, the formula for Costs Remaining in cell **G25** is given by

**=SUMPRODUCT(OFFSET($J25,,,,$E25)*OFFSET($J$16,,,,$E25))**

This is very clearly a similar idea, except it cross-multiplies the relevant purchase prices by the inventory remaining at that point in time.

The difference between these two amounts is the Total COGS
for __all__ periods. Therefore, to
determine the costs for the current period only the formula for COGS (in cell **H25**)
is given by

**=F25-G25-SUM(H$24:H24)**

At last! A simple
formula! This calculation subtracts the
Costs Remaining from the Total Costs (**F25-G25**) and then deducts the
total COSG for previous periods (**SUM(H$24:H24)** – referring to text is
merely treated as zero courtesy of the **SUM** formula) to leave us with the
COGS for the current period.

The COGS are then cited in column **H**:

As I explained earlier, I would have liked to have found a
simpler non-grid approach, but the LIFO method requires a recursive
algorithm. Nevertheless, I am happy to
challenge my readers to construct an alternative approach that __doesn’t__ involve **LAMBDA**.

*Word to the Wise*

Apologies, it’s not the simplest concept we have ever discussed, but this is one of the most commonly requested problems I receive in financial modelling these days. Do watch out though: it is prohibited in many accounting jurisdictions.

In the attached Excel file, I also include an alternative approach, which calculates the COGS costs directly in the matrix instead. I do not go through the methodology here, but it employs a similar technique. Have fun reviewing it!