# Modelling inventory in Excel: Fee FIFO fumbles

It’s not pretty but a formulaic solution is possible when using first-in, first-out basis.Last time out, this column considered modelling inventory, using a simple averaging method to value the stock sold. Unfortunately, real life usually makes this approach inappropriate. Often, we have to model inventory on a first-in, first-out (FIFO) basis, which is, well, *trickier*.

You can use your favourite search engine to find methods, which usually culminate in opaque user-defined functions, grid computations similar to depreciation calculations, or else brute force Visual Basic for Applications (VBA) scripts. Indeed, often you will find self-appointed sages decreeing that you have to resort to these methods because a formulaic method is “just not possible”.

Hogwash.

Consider the following example. Imagine you decided to dabble in buying and selling shares:

In the **Excel file**, I have assumed:

- The opening price of shares is $100 per share.
- The share price grows 2% each time (there is no need to assume a uniform rate; this makes no difference to the mechanics of the cost calculation).
- Three shares are purchased in the first period (Jan 21) for $100 each.
- Two shares are purchased in the third period (Mar 21) for $100 × (1 + 2%)
^{2}= $104.04 each. - Two more shares are purchased in the fifth period (May 21) for $108.24, and then one each in July and August for $112.62 and $114.87, respectively.
- The following shares are sold: one in Feb 21, three in April, two in June, and the final three in September.
- Rows 28 and 29 in the screenshot provide cumulative totals of the purchases and disposals, respectively.
- The error check in row 32 merely ensures you don’t dispose of more shares than you own.
- Rows 39:43 in the image create a control account, which shows the shares bought at opening prices, sold at closing prices, and the interim gains (losses) so that once all shares are disposed of, the closing balance is once again nil.
- The
**Excel file**may be reviewed to see how these formulas work. However, that is not the main focus of this article.

This sets the scene and is something that could easily be envisaged in the real world. Row 42 (Disposals) depicts the sales proceeds. However, what are the associated costs of goods sold? We could use the averaging method from last time, but in many tax regimes around this world, this option is not allowed.

Instead, many such regimes require a FIFO basis of attributing the costs to determine the taxable profit. If you think about it, that makes sense — provided you are the tax authority or a beneficiary thereof. If share prices are going up, this approach will maximise the net present value of your profits – ie, more tax to pay in real terms.

This provides not only a tax planning headache but also a computational one. You want to find a way to calculate this fee for your FIFO fumbles.

This is the problem:

- First disposal in February is simply $100, being the cost of one of the initial three purchases the month before.
- However, the second disposal of three shares is $304.04. This is because the other two shares purchased for $100 in January are sold, plus one of the March acquisitions at $104.04, which equals (2 × $100.00) + $104.04 = $304.04.
- The third disposal in June is $212.28. This is the other share acquired in March for $104.04 plus one of the two May purchases of $108.24, and $104.04 + $108.24 = $212.28.
- The final disposal in September is $335.73. This represents the other May purchase ($108.24), combined with the July ($112.62) and August ($114.87) acquisitions. This adds up to $108.24 + $112.62 + $114.87 = $335.73.

Once you get your head around the concept, it makes perfect sense. The question is, how on Earth do you model it? And this is where our internet experts resort to user-defined functions, VBA, and witchcraft.

The computations may not be the most transparent I have ever written, but they can be calculated in just *three* lines, as shown in the screenshot below.

Did you say three calculations, Liam? I clearly see four. Yes, that’s true, but the third formula (row 54) is included only to explain the shorter variant (fourth line, row 55). And be careful, Liam, holding discussions with yourself is the second sign of madness. (The first sign of madness is asking, “What’s the first sign of madness?”)

Let’s go through each of those last four formulas in turn.

Row 52, Payback Period, has inquired about the final period required that acquired shares (sorry, I couldn’t resist that sentence). In English, this denotes the final period where shares were purchased, in order to service the share disposal figure for that period. To be clear:

- First disposal in February is simply period 1 (Jan 21), as only one of the three shares purchased in January is needed to service the disposal.
- The second in April returns period 3. This is because the other two shares purchased in January (period 1) do not fully service the requirement of three shares. Therefore, one of the March (period 3) acquisitions is needed too.
- The third disposal in June returns period 5. This is the because not only is the other share acquired in March (period 3) sold, but also one of the May (period 5) shares is disposed of as well.
- The final disposal in September returns period 8. This is because all the remaining shares are now disposed of, with the last acquisition made in August (period 8).
- The other periods return zero (-) as no “payback” is made in these months.

To find the first period where the cumulative total of acquisitions (row 28) is equal to or exceeds the number of disposals required, we can use the formula:

**=MATCH(TRUE,$J28:J28>=J$29,0)**

in cell J52** **and copy it across.

**MATCH** with type **0** for the third argument simply seeks out the first occasion of **TRUE** in the vector specified by the second argument (a vector is simply a row or column of cells). For example, the formula in cell O52 would be:

**=MATCH(TRUE,$J28:O28>=O$29,0)**

The argument **$J28:O28>=O$29 **would return:

**{J28>=O$29, K28>=O$29, L28>=O$29, M28>=O$29, N28>=O$29, O28>=O$29}**

which would equate to:

={FALSE, FALSE, FALSE, FALSE, **TRUE**, TRUE}

The first TRUE occurs in position 5, hence O52 returns the value 5. However, the formula is more complex than this. That’s because life is never that simple.

For a start, the formula in cell J52 is:

**=MATCH(TRUE,$J28:J28>=J$29,0)**

This is the equivalent of:

**=MATCH(TRUE,$J28>=J$29,0)**

The second argument is not a vector; it is merely one cell compared with another. **MATCH** doesn’t like this. Therefore, whether the result is TRUE or FALSE, this formula will always return **#N/A** — the formula is not **applicable**. We need to account for this:

**=****IF(J$9=1,(J$28>=J$29)*1****,MATCH(TRUE,$J28:J28>=J$29,0)****)**

This “wraps up” our formula by checking if it is the first month (row 9 is a period counter, so **J$9=1 **is checking whether it is the first period).

The next problem is that if we have an instance where there is no payback (eg, assume the checks in row 32 are breached), the formula will again return **#N/A** — this time to indicate that the payback period is not **available**. This can be countered easily:

**=****IFNA(****IF(J$9=1,(J$28>=J$29)*1,MATCH(TRUE,$J28:J28>=J$29,0))****,)**

This will just replace **#N/A** with zero (0), as nothing is specified after the final comma. (**IFNA** simply checks whether a formula specified in the first argument returns **#N/A** and specifies what to do in this instance in the second argument.)

Finally, to ensure that the formula only calculates in periods where there are disposals, one further modification is made:

**=****IF(J26,****IFNA(IF(J$9=1,(J$28>=J$29)*1,MATCH(TRUE,$J28:J28>=J$29,0)),)****,)**

This just checks that J26 is non-zero. This provides us with a hideous nested **IF** formula, but one that does indeed specify the appropriate payback period:

**=IF(J26,IFNA(IF(J$9=1,(J$28>=J$29)*1,MATCH(TRUE,$J28:J28>=J$29,0)),),)**

This then raises the question, so why did we need this? It’s because the next step is to work out the total costs for all to disposals up to this point:

- First disposal in February needs to be serviced by acquisitions in January.
- Disposals in February and April are serviced by acquisitions in January and March.
- Disposals in February, April, and June are serviced by acquisitions in January, March, and May.
- All disposals (up to September) are serviced by all the acquisitions up to and including August.

Consider the second and subsequent sales of shares:

- The disposals in February and April use all of January’s acquisitions and a proportion of March’s.
- The disposals in February, April, and June use all of January’s and March’s acquisitions and a proportion of May’s.
- The disposals up to and including September use all of the purchases made up to July and a proportion of August’s (which just so happens to be 100%).

Therefore, we have a plan of action. To work out the costs up to and including a particular sale:

- Sum all the acquisition costs for the period
*prior*to the payback period (all of these costs will have been consumed, as explained above); and - For the breakeven period, calculate the
*proportion*used.

This is why I have computed the breakeven period. Now, I need to work out the proportion used in this identified cut-off (payback) period. To do this, I need to use two of my most frequently used functions, namely **MOD** and **OFFSET**.

**Refresher on MOD**

The **MOD** function, **MOD(number, divisor)**, returns the remainder after the **number** (the first argument) is divided by the **divisor **(the second argument). The result will have the same sign as the **divisor**.

For example, **MOD(47,7)** equals 5, since 47 divided by seven (7) is 6 *remainder 5*. Remainders can vary from zero to any number strictly less than **divisor**. The trick I will employ uses **MOD(calculation, 1)**, which will calculate any noninteger component of a calculation (ie, a value greater than or equal to zero [0] and strictly less than one [1] for a positive **divisor**). This forms the crux of calculating the payback period proportion.

**Quick refresher on OFFSET**

Regular readers will recognise I use the **OFFSET** function all the time. 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 (they both have a default value of 1 which is explained further, below). 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**.

**Returning to FIFO**

In cell J53, consider the formula:

**=MOD((J$29-OFFSET($I$28,,J$52))/OFFSET($I$25,,J$52),1)**

**OFFSET($I$28,,J$52) **uses the **OFFSET **function to find the payback period’s cumulative number of acquisitions. This is why the **reference **cell is I28 — moving one column to the right provides January’s details (period 1), moving two columns to the right provides February’s details (period 2), and so on. The number of columns to the right (**J$52**) is given by the identified payback period number.

**J$29-OFFSET($I$28,,J$52) **thus calculates the payback period’s cumulative acquisitions from the current period’s cumulative disposals. This should always provide a nonpositive number, which is intentional, to get the correct proportion. In absolute terms, this number should also be less than or equal to the number of purchases made in the payback period — as this is the definition of the payback period.

**OFFSET($I$25,,J$52) **is the *total* number of purchases (acquisitions) made in the identified payback period. Therefore, using the trick discussed above when introducing **OFFSET**,

**=MOD((J$29-OFFSET($I$28,,J$52))/OFFSET($I$25,,J$52),1)**

calculates the required proportion.

Since we only want this formula to calculate when there has been a sale (disposal, as shown in row 26) and when a payback period (row 52) has been calculated, the formula is placed inside an **IF** statement:

**=IF(J$26*J$52,MOD((J$29-OFFSET($I$28,,J$52))/OFFSET($I$25,,J$52),1),)**

This is the formula in row 53. Just take note of one important point here: If there is no remainder (ie, the value is zero [0]), this formula will result in zero (0). Strictly speaking, the proportion used in the payback period would be 100% though, not zero, so we will need to make an adjustment in this instance. I will revisit this scenario shortly.

Now comes the fun(!) part.

To calculate the cost base used for all disposals, this will equal:

- The sum of all the acquisition costs for the period *prior* to the payback period (as explained earlier). This will be given by the formula

**=SUM(OFFSET($I$40,,,,J$52))**

- This sum needs to be modified though if 100% of the costs are used in the payback period (ie, the next period needs to be included, too):

**SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))**

- For the breakeven period, we will need to calculate the *proportion* used, which is calculated in parts. Consider:

**= SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))
**which simply derives the acquisition costs for all periods up to and including the payback period less the acquisition costs for all periods prior to the payback period, ie,

*just the payback period’s*

*total*acquisition costs, less

**OFFSET($I$40,,J$52)*(1-J$53)**

which is the unused proportion in the payback period. Adding these two parts together will give us what we need.

Still with me? No, I’m not either. However, if we combine these calculations, we obtain the total costs for all disposals so far:

**=SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))-OFFSET($I$40,,J$52)*(1-J$53)+SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))**

We now need to calculate the costs just for the disposal in that period alone, which is the total costs calculated, less all the costs calculated for earlier periods, ie:

**=SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))-OFFSET($I$40,,J$52)*(1-J$53)+SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))****-SUM($I$54:I$54)**

Since we again only want this formula to calculate when there has been a sale (disposal, as shown in row 26) and when a payback period (row 52) has been calculated, the formula is once more placed inside an **IF** statement, as shown below.

**=****IF(J$26*J$52,****SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))-OFFSET($I$40,,J$52)*(1-J$53)+SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))-SUM($I$54:I$54)****,)**

This is the formula in cell J54:

**=IF(J$26*J$52,SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))-OFFSET($I$40,,J$52)*(1-J$53)+SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))-SUM($I$54:I$54),)**

Eagle-eyed readers will spot this is more complex than it needs to be, which is why this is called the “Long” formula. The expression

**SUM(OFFSET($J$40,,,,J$52))-SUM(OFFSET($I$40,,,,J$52))-OFFSET($I$40,,J$52)*(1-J$53)**

may be simplified to

**OFFSET($I$40,,J$52)*J$53**

which is quite a simplification! Therefore, using this substitute provides us with the “Short” formula in cell J55:

**=IF(J$26*J$52,OFFSET($I$40,,J$52)*J$53+SUM(OFFSET($I$40,,,,J$52+(J$53=0)*1))-SUM($I$55:I$55),)**

Clear as mud? Nonetheless, this provides us with the correct cost base for the period, without fancy footwork using user-defined functions, grid algebra, or VBA. From here, it is simple to work out the profit assessable to tax:

It’s not pretty, but it is FIFO.

**Word to the wise**

Apologies, it’s not the simplest concept we have ever discussed, but this is a common problem in financial modelling. I wanted to show it for this reason — and because so many naysayers you cannot do this formulaically!

Some more advanced readers might consider using **SUMPRODUCT** and **INDEX** instead. This is because **SUMPRODUCT** cross-multiplies costs, and **INDEX** is not a so-called *volatile* function, which means it does not recalculate every time you press **ENTER**.

Given the name of my company is **SUMPRODUCT**, rest assured, dear reader, I have plenty of time for this function, but sometimes it slows down calculations, certainly compared to **SUM**. Similarly, **OFFSET** may be volatile, but it also often calculates more quickly than **INDEX**, especially given you would need multiple **INDEX **expressions. In larger models, you will find my formulas will calculate more quickly than using these alternatives — but it’s not *wrong* to use them instead.

It would be a boring world if we all thought the same.

*— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of *Introduction to Financial Modelling

*. Send ideas for future Excel-related articles to him at*

*liam.bastick@sumproduct.com*

*. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an*FM

*magazine senior editor, at*

*Jeff.Drew@aicpa-cima.com*

*.*