In this article I am going to explain the FIFO (first in first out) algorithm for calculating cost of goods sold. This is the real business problem I am working on now.

Different methods of calculating Cost of Goods Sold in the Inventory Calculation

There are many articles on the Internet explaining concepts of Calculating Cost of Goods On Hand and Cost of Goods Sold in the inventory calculation. I will give just a few of them and quote a bit of material from these articles to provide a brief overview. I suggest readers of this article review the mentioned articles or just do a Google search on the terms "FIFO Cost of Goods Inventory calculation".

Inventory Valuation Method - FIFO Vs. Moving Average

How to Calculate Cost of Goods Sold (CoGS)

Inventory and Cost of Goods Sold

Chapter 4 by Hugo Kornelis in the "SQL Server MVP Deep Dives" book (first book) talks a bit about Running Total problem, so it may be useful to read this chapter as well.

There are several valuation methods, but for small businesses it is generally restricted to FIFO and Moving Average. 

In our application we have two methods of calculating inventory:  RWAC (Running Weighted Average Cost) and FIFO. The preferred method of the calculation can be set in the Inventory Preference form.

Implementing FIFO Cost of Goods Sold in our application

After we have briefly discussed the theory, I am going to talk about implementing the FIFO algorithm of calculating the Cost of Goods in our software. Historically, our application had only simpler RWAC method (and not even the true RWAC, but rather just Average cost method). Few years ago the company management team decided that it's time to offer our clients a FIFO method of calculating Cost of Goods On Hand and Cost of Goods Sold. My first task was to identify all places in our software where we may need adjustments and my colleague was tasked with creating necessary T-SQL functions.

I need to describe the Inventory table used in our application.

Here is its DDL: 

Each inventory item was defined by these attributes: department, category, item, invent_id, locatn_id. These 5 columns are used to identify a single inventory item in its current location. Quantity and unit_cost columns are used to identify each inventory movement. In case of Sales or Returns (Trans_Type = 'S') the unit_cost is 0 and has to be calculated. Trans_Type can be one of the following: P - purchase, A - adjustment, T - transfer and S - Sale (negative quantity) or Return (positive quantity). The ref_no column in case of sales / returns provides a reference to the trans_no from transactions table. Also the date_time column is important for our calculations. Other columns in the Inventory table are used for other purposes and not relevant for the calculation of Cost of Goods on Hand or Cost of Goods Sold.

So, as I said, the first implementation of the Cost of Goods on Hand calculation was written by my colleague as a multi-statements table valued function that accepted many parameters (some of them were optional) and the calculation method type (RWAC, FIFO or LIFO) and returned the result as a table. I checked the date of the first implementation in our Source of Safe software and it is August 2010. 

It was quickly determined that using the multi-statements table valued function in that way lead to a very bad performance. Also, somehow the process of developing the functions (or procedures) to do these calculations was turned into my hands. I tried to change these functions to inline table-valued functions for each method separately (one for FIFO and one for RWAC, we decided to drop LIFO method then) but yet the performance on these set based functions was really bad for the clients with substantial inventory movements. 

In addition to discussing the FIFO calculation problem in the forum's threads I also had private e-mail exchange with Peter Larsson who eventually helped me to adapt his solution from the Set-based Speed Phreakery: The FIFO Stock Inventory SQL Problem for our table's structure and Cost of Goods on Hand problem.

I discussed this problem in many threads in Transact-SQL forum in MSDN. Here is one of the earliest threads (from May 2011), where I found that inline CTE based solution when we needed to use the same CTE multiple times, was significantly slower than using temp tables to hold intermediate calculations:

Temp tables vs CTE

I just re-read that long thread. Essentially, I confirmed that using inline UDF to calculate Cost of Goods on Hand for the selected inventory and using CROSS APPLY with that function was very slow compared to getting the inventory to work with into a temporary table first and then apply the calculations as a stored procedure. 

It also started to become clear that using our current structure of the table and not having anything pre-calculated will lead to bad performance as we need to re-calculate the cost every time from the very beginning. About a year or so ago I proposed a plan to re-design our inventory table by adding few more tables we will be updating at the same time as transaction occur. Unfortunately, we haven't proceed in this direction yet and I don't know if we ever going to look into these ideas in order to make the calculation process easier.

In this article I planned to discuss the Cost of Goods Sold calculations, so I will just give the current code of the Cost of Goods on Hand FIFO procedure without too many explanations.

Current procedure to calculate Cost of Goods on Hand

You can see that this procedure is using #Inventory temporary table and that it also has fifo_rank column which is not present in the i_invent table from the database. I am pre-selecting rows I may be interested in into the temporary #Inventory table and creating fifo_rank column using ROW_NUMBER() function to partition by the 5 columns that determine a single inventory item and order by date_time, po_link columns. You can also see that this procedure references this function siriusfn_LastCostUpToDate. This function calculates last cost of the item to date using iterative approach  - it first tries to calculate it for the specific invent_id (invent_id <> 0 is for the "matrix" items, e.g. items that may come in different sizes or colors). If there are no rows for the specific invent_id it tries to get the last cost for the item itself regardless of invent_id. If it is still unknown, it checks purchase orders table (i_pchord) also first for the invent_id and then for the item itself.

Here is the current code of this function:

FIFO Cost of Goods Sold

Now I am going to discuss the procedure I am using to calculate Cost of Goods Sold using FIFO method. About a year ago I spent a lot of time creating two versions of the procedure - one for SQL Server 2005-2008 and one for SQL Server 2012. I thought I tested these procedures extensively and had them working great. Our testers also tested them in various scenarios (I hope). Turned out I had not tested them well enough and they were failing in a really simple scenario. Also, our client found a more complex scenario and was able to perform analysis of these procedures and showed their faults. Therefore I needed to look at them again and fix the problems.

So, I looked at them recently and I had to admit, I could not really understand what I was doing in them.  I think if I had written this article then rather than now, it may have helped. So, by documenting my line of thoughts now in creating this procedure and also accepting the revisions by other people, it may help me (and others) to perfect this procedure in the future, or re-design it again if needed.

The scenario that my colleague found failing in the last implementation of the procedure was the following:

1. Create a new retail tracking item if necessary
2. Receive 20 units of the item at $10.00 each
3. Receive another 20 units of the item at $5.00 each
4. Sell 30 units of the item
5. Sell 10 units of the item the next day (or set carryover 1 day forward)
6. Make sure the "Closing Cost Calculation Algorithm" under Retail Preferences in SysManager is set to FIFO. Run the Profit and Loss Report against each day. For the second day (sale of 10 units), COGS correctly shows $50.00. For the first day (sale of 30 units), COGS shows $150.00 (should be $250.00) 

So, I decided I am going to try to re-write this procedure again rather than trying to figure out what was that procedure doing and where the bug may be. I also found the following thread in MSDN SQL FIFO Query which I already used in my prior attempts to solve the FIFO Cost of Goods Sold problem. This time I concentrated on the Peter Larsson's solution in that thread (SwePeso).

In the procedure, that is invoked before the FIFO Cost of Goods Sold procedure is called, I am selecting inventory items for the user's selections (say, for the Profit and Loss report the user can select particular department (or department and category), may select specific vendor and also selects a date range interval). So, I select rows into #Inventory temp table up to the end date of the selected dates interval. I again add FIFO_RANK column and also for simplicity I add InvNo numerical column using DENSE_RANK() function with order by Department, Category, Item, Invent_ID, Locatn_ID. This is done in order to use a single integer column to identify each inventory item rather than 5 columns. In my calculations I am also using dbo.Numbers table that has a single number column. In our database that table contains numbers from ~-100K to 100K.

The idea of the new design of this procedure is to calculate the starting point of the inventory in one step (up to Start Date - dtStart parameter) using Peter's idea and then process each individual sale or return (and negative quantity transfers) within the selected date intervals. The final result should have all sales and returns in the selected period (quantity and unit_cost).

So, I decided to introduce yet another temporary table I called #MovingInventory. In this table I have InvNo column (this is artificial Item Id for each inventory item in a location I created in the pre-step), fifo_rank column, quantity - the same quantity as in the #Inventory, CurrentQuantity (this column should reflect the current remaining quantity), Removed (quantity removed) and Returned (quantity returned). If we are to change our current Inventory process, we may create this table as a permanent table in the database and update it on each inventory movement. We can also create InventorySales table. Using these tables will significantly simplify the current calculation process.

Therefore, in the beginning of the procedure I now have this code:


So, we start with populating this new #MovingInventory temporary table with all positive additions to the inventory with their unit_cost. I set CurrentQuantity to quantity and Returned and Removed to 0. 

I have two more temporary tables used in this procedure: #Sales - this table will be used to generate our final result and it will contain all sales and returns in the specified date range with the quantity sold (returned) and unit cost used.

I also have #Removed table. I could have used table variable here instead but I recall I had some problems with the table variable before in my prior version of that procedure so I decided to use temporary table again. This table will be used to hold items removed (or returned) on each iteration and it will be cleaned (truncated) on each iteration.

Here is the definition of these 2 temporary tables at the top of the procedure:



Now, I decided to use two cursor loops in my procedure - one to iterate through each inventory item and another inner loop to go through each individual sale for that item. We all know well, that cursor based solutions are generally not recommended as they normally perform much worse than set based solutions. However, for solving this problem I simply don't see a set-based solution that's why I decided to use cursors. I may eventually re-design this procedure into CLR based procedure although I am not sure CLR based procedures may work with the temporary tables to start with.

So, my first step is to calculate prior inventory in one step. Here is the code I use for this:


Here I am attempting to calculate our current working inventory in one step. I get the total sold quantity and last date (fifo_rank) when it was sold prior to dtStart and then distribute that sold quantity among all prior additions into inventory.

Here I am not considering situations when somehow we already sold more than we had in the inventory originally or when we returned more than sold (so total quantity will be greater than 0). To be honest, I am not 100% sure how to treat these situations, so I assume that possibility of them occurring is very low.

Once we got the inventory up to the starting date (dtStart) I am ready to process each individual sale or return. Here is how I do it for Sales and negative transfers:


So, for each sale (or negative transfer) I use the same idea as in calculating starting inventory. I remove the sold quantity distributing it among rows where current quantity > 0 ordering by date_time (fifo_rank) column. I then update the #MovingInventory table (current quantity and Removed columns) and I output results using OUTPUT keyword for UPDATE into #Removed table. In addition, I populate #Sales table if the Trans_Type is 'S' (sales) to be used in the final select statement.

I also try to consider situations when we sold (or moved out) more than we have in the inventory. In this case we're using Last Cost for the item.
Here lies another problem not currently considered - if we have the negative quantity balance, we need to keep decrementing that difference after we receive that item. This is not currently done in my procedure - so we may get incorrect Cost of Goods Sold in such scenarios. I may need to think more how to handle this problem.

For the returns I am using a similar process to what I use for Sales, but I try to return back what I've already removed in the opposite direction (e.g. last removed - first returned). So, this is how I handle returns:



Here again if we returned back more than we originally removed, I am returning using the last known cost for the item.

The Cost of Goods Sold FIFO procedure

Now I will give you the whole procedure code and hopefully you will see my logic. I also will appreciate comments or code corrections as this is still a work in progress and hasn't been tested extensively yet.


At the top of the script code I provided #Inventory table for the original failing scenario in order to confirm that it works correctly with the new code. I also have a scenario I tested originally in the comments after the stored procedure.

Summary

In this article I described the process of working on a complex problem of Calculating Cost of Goods Sold using FIFO method and gave my current procedure code. I also showed potential problems and flaws in that code. I will appreciate comments and ideas of improving this algorithm.

This article participated in the TechNet Guru Competition for July 2013 and won the Silver Prize.