If you’ve ever taken a basic accounting course, managed inventory, or bought and sold shares, then you will probably know about the first-in first-out (FIFO) method of calculating cost of sales.
The concept of FIFO is easy enough, but have you ever tried to get a spreadsheet to calculate your FIFO cost of sale automatically? It is surprisingly difficult. Let’s look at a simple example. Assume that we have four share transactions: 2 buys and 2 sells. The question is how do we get the spreadsheet to automatically calculate the cost of sale figures in the yellow highlighted cells?
Essentially you have two choices here. The first method is to write a macro or a user defined function (UDF) to run through your prior purchases and sales, and figure out the cost of sale of the items in question. The second way is to write a normal spreadsheet formula to do this i.e., without any background code. The latter approach is better for people who don’t like enabling macros or who aren’t comfortable with UDFs. The downside though is that you need a couple of calculation columns. Oh, and the formula is a monster!
Let’s take a look at how you can do this.
Before writing any complex formula you need to have a picture in your mind as to how you want the formula to work. In this case, we said to ourselves–for each sale row, let’s try and figure out the costs of sale for all sales to date. We thought that if we could figure this out, then we would be able to simply subtract all the previous costs of sale, to get the actual cost of sale for the row in question. With this in mind, we realised that we’d need to keep running totals of the quantity purchased and total purchase costs, so we created two new columns to contain these calculations.
Now comes the fun part. We need to write a formula that does the following things:
- Searches through the cumulative buy quantities to date (column E) and figures out the highest number that is LESS THAN the cumulative quantity sold to date, and return the corresponding cumulative buy cost to date (column F). (We need the full amount of this bucket of costs.)
- Calculates the difference between cumulative quantity sold to date, and the cumulative quantity purchased to date, determined in step 1. (We only take a portion of this bucket.)
- Multiplies this marginal quantity purchased by the cost per unit for this row.
- Subtracts all costs of sale to date, thereby leaving you with the purchase cost of the row in question.
Here’s what we came up with. (Each of the four lines of the formula reflects the corresponding steps outlined above.)
Note that this is an array formula and needs to be entered with Ctrl Shift Enter.
Admittedly, this formula is completely ridiculous–just look at the size and complexity of the thing. It’s around 270 characters long, and uses complex functions like arrays, lookups, and offsets. However, if you want to calculate FIFO in a spreadsheet you don’t have too many choices. We have seen others attempt to do this using a matrix approach, and while this simplifies the formulas required, it also blows out the size of the cells you need to do the calculations. In our view, this is a far worse outcome, as more cells means more chance of an error creeping in.
One thing in favour of the monster-formula approach is that it is very easy to test its accuracy. All you need to do is build a very small example model–like the example above–and run different buy and sell scenarios. Use a calculator or paper and pen to check if you’re getting the right result. When you’re comfortable it is working, just copy the formula and apply it to your real data.
Click here to download a copy of this spreadsheet.