Inventory Calculations in SQL Server
May
10
Written by:
Charles Flock
5/10/2017 2:34 PM
In 2013 we introduced 3 XLeratorDB/windowing functions for inventory calculations: FIFO; LIFO; and WAC. In March of 2017 we added new table-valued functions to XLeratorDB/fin08 to do the same calculations as well as a few new functions to provide more detail about the ending inventory balances. In this article we go through some examples to explore the details of inventory valuation techniques and discuss the perils of triangular joins and why you probably don't want to try these calculations in native SQL.
Inventory calculations do not particularly lend themselves to set-based solutions in TSQL. These calculations require that data be ordered within an inventory item and that inventory items be partitioned, which are easy enough to do in SQL Server. But the real challenge arises in calculating the appropriate values for the ending inventory and of the cost-of-goods and gross margin on sales when items are removed from inventory. Since these calculation are always backward-looking and since the size of the backward-looking window is data-dependent it is impossible to write a set-based solution without joining that data to itself, which is known as a self-join or a triangular join.
Triangular joins increase the number of internal reads in a SQL Statement exponentially. For very small row counts the overhead for this type of operation is small. But the nature of exponential growth is such that query performance degenerates very quickly as volume increases.
Here's a very simple example. Using the XLeratorDB SeriesInt function, we generate the integers from 1 to 100 using a self-join to calculate a running sum (and yes, I know that starting in SQL Server 2012 you wouldn't do it this way).
SET NOCOUNT ON
SET STATISTICS IO ON
;with m as (
SELECT SeriesValue as x
FROM wct.SeriesInt(1,100,NULL,NULL,NULL)
)
SELECT m1.x, SUM(m2.x)
FROM m m1
INNER JOIN m m2
ON m2.x <= m1.x
GROUP BY m1.x
SET STATISTICS IO OFF
Let's look at the statistics
Even though we returned 100 rows of data, we actually performed 10,098 logical reads and the CPU time was 15 milliseconds. If we generate the integers from 1 to 1,000 (returning 1,000 rows) there are 1,004,609 logical reads the CPU time is 1,716 milliseconds. At 10,000 there are 100,624,759 logical reads and the CPU time is 172,007 milliseconds. Every 10-fold increase in rows resulted in a 100-fold increase in work, logical reads and CPU time.
Starting in SQL Server 2012, though, you could use the following syntax to create a 'window' (in this case from the beginning of the dataset) to calculate the running total without a self-join.
SELECT
SeriesValue as x
,SUM(SeriesValue) OVER (ORDER BY SeriesValue ASC)
FROM
wct.SeriesInt(1,10000,NULL,NULL,NULL)
This produces the following statistics.
As you can see, 60,119 logical reads and CPU time of 125 milliseconds as compared with 100,624,759 logical reads and CPU time of 172,007 milliseconds.
Do the windowing capabilities introduced in SQL Server 2012 help with inventory calculations? Not really. While it is now pretty easy to keep track of the inventory on-hand or to even generate a report which shows a running total of the inventory on-hand, that's not the type of calculation that is at the heart of standard inventory calculations: cost-of-goods sold (COGS) and gross margin (GM). The actual math is pretty simple:
GM=Sales-COGS
GOGS=Beginning Inventory+Purchases-Ending Inventory
The calculation of the ending inventory affects the calculation of COGS which in turn affects the calculation of GM. FIFO (First In, First Out), LIFO (Last In, First Out), and WAC (Weighted Average Cost) are techniques for calculating the ending inventory. These techniques do not necessarily have anything to do with the physical movement of items in a warehouse but are simply accounting conventions for coming up with inventory values.
We originally introduced the SQL Server scalar function FIFO, LIFO, and WAC as windowing functions to perform these calculations. However, the implementation of these function required PERMISSION = UNSAFE. We have now introduced table-valued version of these functions which run with PERMISSION = SAFE so that you can have this capability in Amazon RDS which requires SAFE CLR.
These new functions might require a little more SQL but run just as fast as or even faster than the scalar functions. We also added some new functions to keep track of the ending balances which can then become the beginning balances for the next time that you run that function and which show the details of the shipments that make up the inventory.
FIFO Balances
The logic behind calculating FIFO balances is pretty easy: whatever is left in inventory is the last thing to be added to the inventory. Thus, in an ordered set of data, simply go backwards from the end of the set to the beginning until the cumulative quantity is equal to the ending quantity. The cost of those items makes up the ending inventory value. Let's look at this with a very simple example consisting of one day's activity for one product.
SELECT
*
INTO
#inv
FROM (VALUES
(1,'widget 1',50,150,7500)
,(2,'widget 1',75,152.5,11437.5)
,(3,'widget 1',-100,250,-25000)
,(4,'widget 1',150,148,22200)
,(5,'widget 1',175,149,26075)
,(6,'widget 1',-25,250,-6250)
,(7,'widget 1',-25,250,-6250)
,(8,'widget 1',200,149.5,29900)
,(9,'widget 1',50,154,7700)
,(10,'widget 1',-100,250,-25000)
)n(trn,descr,qty,price_unit,price_extended)
All of our calculations will assume that inventory movements should be ordered by trn. Positive movements (qty > 0) will be classified as purchases; negative movements (qty < 0) will be classified as sales. For purchases, the price_extended column indicates the cost of the items when they were added to the inventory. For sales, the price_extended column is the revenue generated for the sold items; it is not the cost-of-goods sold.
We can run the following SQL to get some basic information about these inventory movements:
SELECT
SUM(qty) as [Ending Inventory]
,SUM(CASE WHEN qty > 0 THEN qty ELSE 0 END) as [Purchase Quantity]
,SUM(CASE WHEN qty < 0 THEN qty ELSE 0 END) as [Sold Quantity]
,SUM(CASE WHEN qty > 0 THEN price_extended ELSE 0 END) as [Purchase Amount]
,SUM(CASE WHEN qty < 0 THEN price_extended ELSE 0 END) as [Sale Amount]
FROM
#inv
This produces the following result.
In the scalar functions that are part of the XLeratorDB/windowing package we can just run the following SQL to see the inventory balances, cost-of-goods sold, and gross margin after each inventory movement.
SELECT
trn
,descr
,qty
,price_unit
,price_extended
,wct.FIFO(qty,price_extended,'QTY',4,trn,0) as [Ending Quantity]
,wct.FIFO(qty,price_extended,'EV',4,trn,1) as [Ending Value]
,wct.FIFO(qty,price_extended,'COGS',4,trn,2) as [Cost of Goods Sold]
,wct.FIFO(qty,price_extended,'GM',4,trn,3) as [Gross Margin]
,wct.FIFO(qty,price_extended,'COGSC',4,trn,4) as [Cumulative Cost of Goods Sold]
,wct.FIFO(qty,price_extended,'GMC',4,trn,5) as [Cumulative Gross Margin]
FROM
#inv
This produces the following result.
The XLeratorDB FIFO function is very easy to follow and very flexible. However, because it is a SQL Server scalar function it is invoked for each return value. While the function is very efficient it does more or less the same amount of work for each invocation; it just returns a different value.
Using the same data, let's perform the same calculation using the FIFOtvf table-valued function.
This produce the following result.
This is a very different approach than the windowing function. First, the FIFOtvf function uses dynamic SQL to SELECT data. While there many ways to construct the SQL for the inventory calculation, I have elected to execute the function (using a CROSS APPLY) for each unique inventory item. I could have actually run the table-valued function without the CROSS APPLY for all products in inventory. You can read the documentation for more about how to do that.
As with the scalar implementation the inventory values are affected by the order of the data so you need to make sure that you specify an ORDER BY in your dynamic SQL. The table-valued function goes through the resultant table generated by the dynamic SQL calculating all the return values and then you can simply choose which ones are of interest.
The major difference in this approach is that it requires you to JOIN the output of the table-valued function to the function input to add other details to the output which are important to your reporting requirement. In this example I have joined to #inv table to get the product description, invoice quantity, price-per-unit, and extended price. While this requires a bit more SQL, our experience is that the table-valued function runs in about the same amount time, or even faster than the scalar function. In our environment, it processes about 22,000 inventory movements per second.
Both the table-valued function and the scalar function produce the same results, indicating that the value of the ending inventory is 67,375.00. Plugging that number into our 2 equations:
COGS = 0 + 104,812.50 – 67,375.00
COGS = 37,437.50
GM = 62,500 – 37,437.50
GM = 25,062.50
It would be interesting to know what makes up that ending balance of 67,375.00. The table-valued function FIFOend produces that information.
SELECT
#inv.trn
,#inv.qty as [Invoice Quantity]
,#inv.price_extended as [Invoice Amount]
,k.qty as [Inventory Quantity]
,k.amt as [Inventory Amount]
FROM wct.FIFOend('SELECT trn, NULL, qty, price_extended FROM #inv ORDER BY trn')k
INNER JOIN
#inv
ON
convert(int,k.UID) = #inv.trn
This produces the following result.
What we can see is that the full value of the last 3 items added to the inventory are still in inventory and that 25 units of the 150 units from trn 4 are still in inventory. Since these are the last 4 items added to inventory, this is exactly what we expect.
So how well does the table-valued function perform? In this example we will create 100,000 inventory movements in the month of May, 2017 across 25 difference products. The ending inventory balances could be long or short. Since we are just evaluating performance, the query does not return any results; it just populates some temporary table which could then be used for reporting.
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT
SeriesValue as id,
'widget ' + cast(seriesValue as varchar(2)) as descr
INTO
#p
FROM
wct.SeriesInt(1,25,NULL,NULL,NULL)
SELECT
IDENTITY(int,1,1) as trn,
invqty,
invdate,
invitem,
unitprice,
invqty * unitprice as extprice
INTO
#t
FROM (
SELECT
invqty,
invdate,
invitem,
CASE
WHEN invqty <= 0 THEN 25.50
ELSE ROUND(12 + wct.RAND(),2)
END as unitprice
FROM (
SELECT
SeriesValue as invqty
,DATEFROMPARTS(2017,05,wct.RANDBETWEEN(1,31)) as invdate
,wct.RANDBETWEEN(1,25) as invitem
FROM
wct.SeriesInt(-45,55,NULL,100000,'R')
)n
)p
ORDER BY
invItem ASC,
invdate ASC,
invqty DESC
SELECT
#t.trn
,#p.descr
,#t.invdate as [Invoice Date]
,#t.invqty as [Invoice Quantity]
,#t.extprice as [Invoice Amount]
,k.qty as [Inventory Quantity]
,k.amt as [Inventory Amount]
INTO
#end
FROM (SELECT DISTINCT invItem FROM #t)i
CROSS APPLY wct.FIFOend('SELECT trn, NULL, invqty, extprice FROM #t WHERE invItem = ' + cast(i.invitem as nvarchar(max)) + ' ORDER BY trn')k
INNER JOIN
#t
ON
convert(int,k.UID) = #t.trn
INNER JOIN
#p
ON
i.invitem = #p.id
SELECT
#t.trn
,i.invitem
,#p.descr
,#t.invdate as [Invoice Date]
,#t.invqty as [Invoice Quantity]
,#t.extprice as [Invoice Amount]
,k.QTY as [Ending Quantity]
,k.EB as [Ending Value]
,k.COGS as [Cost of Goods Sold]
,k.GM as [Gross Margin]
,k.COGSC as [Cumulative Cost of Goods Sold]
,k.GMC as [Cumulative Gross Margin]
INTO
#det
FROM (SELECT DISTINCT invItem FROM #t)i
CROSS APPLY wct.FIFOtvf('SELECT trn, NULL, invqty, extprice FROM #t WHERE invItem = ' + cast(i.invitem as nvarchar(max)) + ' ORDER BY trn')k
INNER JOIN
#t
ON
convert(int,k.ID) = #t.trn
INNER JOIN
#p
ON
i.invitem = #p.id
Here are the query statistics.
As you can see, the total CPU time (which includes creating the data as well calculating the values for each inventory movement and the detail of the ending balances) 6,512 milliseconds. The SQL puts 17,744 rows into the #end table where components of the ending inventory are stored and has 100,543 logical reads for the FIFOtvf execution and 101,214 read for the FIFOend execution. This example created 100,000 rows of input data for 25 different products across 31 days. Thus performance overhead increases linearly; a ten-fold increase in data would result in a 10-fold increase CPU time and logical reads. We didn't create any indexes on the underlying data, so it's probably possible to tweak the performance a little bit by doing that.
LIFO Balances
LIFO makes the opposite assumption from FIFO; whatever is left in inventory was the first thing added to the inventory requiring that you traverse the set from the beginning. There are, however, basically two types of LIFO. The first of these is perpetual LIFO. This is what they teach you in a basic accounting course and is useful as a pedagogical tool but it is not used much in the real world. In the perpetual LIFO approach you could simply traverse the ordered data from the beginning and accumulate the quantity until it is equal to the ending quantity and the associated costs will be the ending inventory value.
However, in periodic LIFO calculations (which is what happens in the real world), the LIFO balances are effectively recalculated every time an item is withdrawn from inventory which makes the calculation much harder. In other words, every time a sale is made, the cost of goods sold is calculated for that sale and that cost is removed from the inventory and once it is removed it can never be added back to the inventory.
Using the same data as in the FIFO Balances section, let's see what happens with the LIFO functions. Obviously, the first SQL statement returns exactly the same result.
SELECT
SUM(qty) as [Ending Inventory]
,SUM(CASE WHEN qty > 0 THEN qty ELSE 0 END) as [Purchase Quantity]
,SUM(CASE WHEN qty < 0 THEN qty ELSE 0 END) as [Sold Quantity]
,SUM(CASE WHEN qty > 0 THEN price_extended ELSE 0 END) as [Purchase Amount]
,SUM(CASE WHEN qty < 0 THEN price_extended ELSE 0 END) as [Sale Amount]
FROM
#inv
We can use the LIFO scalar (windowing) function in exactly the same way that the FIFO scalar function was used.
SELECT
trn
,descr
,qty
,price_unit
,price_extended
,wct.LIFO(qty,price_extended,'QTY',4,trn,0) as [Ending Quantity]
,wct.LIFO(qty,price_extended,'EV',4,trn,1) as [Ending Value]
,wct.LIFO(qty,price_extended,'COGS',4,trn,2) as [Cost of Goods Sold]
,wct.LIFO(qty,price_extended,'GM',4,trn,3) as [Gross Margin]
,wct.LIFO(qty,price_extended,'COGSC',4,trn,4) as [Cumulative Cost of Goods Sold]
,wct.LIFO(qty,price_extended,'GMC',4,trn,5) as [Cumulative Gross Margin]
FROM
#inv
This produces the following result.
Same calculation, but this time we use the table-valued function FIFOtvf.
This produces the following result.
The LIFO calculation, as expected, yields a different value for the ending inventory than the FIFO calculation: 67,000 instead of 67,375. This has the effect of increasing the cost-of-goods sold which decreases the gross margin.
COGS = 0 + 104,812.50 – 67,000.00
COGS = 37,812.50
GM = 62,500 – 37,812.50
GM = 24,687.50
We can use the table-valued function LIFOend to see which inventory movements make up the ending balances.
SELECT
#inv.trn
,#inv.qty as [Invoice Quantity]
,#inv.price_extended as [Invoice Amount]
,k.qty as [Inventory Quantity]
,k.amt as [Inventory Amount]
FROM wct.LIFOend('SELECT trn, NULL, qty, price_extended FROM #inv ORDER BY trn')k
INNER JOIN
#inv
ON
convert(int,k.UID) = #inv.trn
This produces the following result.
In the FIFO calculation, we just went backwards through the ordered set until the running sum of the positive quantities equaled the ending inventory quantity, which is the sum of movements. In the FIFOend resultant table, the first row of the ending balances may be less than the original amount added to inventory, but all the subsequent rows in the resultant table will be equal to the original invoice amounts added to the inventory.
This is not the case with periodic inventory calculations. As you can see in the resultant table, 25 of the 50 units from transaction 1 are in inventory, 125 of the 175 units from transaction 5 are in inventory, and 150 of the 200 units from trn 8 are in inventory. How did that happen?
In transaction 3, we sold 100 units and we had 125 units in inventory. Subtracting 100 units from inventory at that time leaves us with 25 units and using the LIFO rules, those units came from trn 1. We then add 150 units (trn 4) and 175 units (trn 5). Trn 6 and Trn 7 each subtract 25 units from trn 5 using the LIFO rules. Trn 8 adds 200 units and trn 9 adds 50 units. The last transaction, 10 removes 100 units from inventory, taking 50 from 9 and 50 from trn 8.
We can actually use the LIFOtvf function to see the detail of the inventory balance after each movement.
This produces the following result.
That actually can be little confusing, so we can use the following PIVOT as a pedagogical tool.
SELECT
[Invoice Number]
,[Invoice Quantity]
,SUM([Invoice Quantity]) OVER (ORDER BY [Invoice Number] ASC) as [Ending Inventory]
,CAST(ISNULL([1],0) as int) as [1]
,CAST(ISNULL([2],0) as int) as [2]
,CAST(ISNULL([3],0) as int) as [3]
,CAST(ISNULL([4],0) as int) as [4]
,CAST(ISNULL([5],0) as int) as [5]
,CAST(ISNULL([6],0) as int) as [6]
,CAST(ISNULL([7],0) as int) as [7]
,CAST(ISNULL([8],0) as int) as [8]
,CAST(ISNULL([9],0) as int) as [9]
,CAST(ISNULL([10],0) as int) as [10]
FROM (
SELECT
#inv.trn as [Invoice Number]
,#inv.qty as [Invoice Quantity]
,i.trn as [Inventory Invoice Number]
,k.qty as [Inventory Quantity]
FROM
#inv
CROSS APPLY
wct.LIFOend('SELECT trn, NULL, qty, price_extended FROM #inv WHERE trn < = ' + cast(#inv.trn as varchar(max)) + ' ORDER BY trn')k
INNER JOIN
#inv i
ON
convert(int,k.UID) = i.trn)P
PIVOT (SUM([Inventory Quantity]) FOR [Inventory Invoice Number] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10]))D
ORDER BY 1
This produces the following result.
The previous SQL simply shows which invoices are contributing to the inventory balance on a LIFO basis
WAC Balances
For both the LIFO and FIFO inventory methods, it is possible to trace the ending balance back to specific shipments and their associated invoices. Not so with the weighted-average cost method.
As the name suggests, the value of the inventory in the weighted average cost method is the weighted average of the items in the inventory. This is a little more complicated than just calculating the weighted average of the purchases added to the inventory, because the weights need to adjusted for the items taken out of inventory
Let's see what happens with the WAC functions. At this point, it should be obvious that the summary information is the same since it does not included a calculation of the ending balances.
As with FIFO and LIFO we can use the WAC scalar (windowing) function.
SELECT
trn
,descr
,qty
,price_unit
,price_extended
,wct.WAC(qty,price_extended,'QTY',4,trn,0) as [Ending Quantity]
,wct.WAC(qty,price_extended,'EV',4,trn,1) as [Ending Value]
,wct.WAC(qty,price_extended,'UP',4,trn,6) as [Unit Price]
,wct.WAC(qty,price_extended,'COGS',4,trn,2) as [Cost of Goods Sold]
,wct.WAC(qty,price_extended,'GM',4,trn,3) as [Gross Margin]
,wct.WAC(qty,price_extended,'COGSC',4,trn,4) as [Cumulative Cost of Goods Sold]
,wct.WAC(qty,price_extended,'GMC',4,trn,5) as [Cumulative Gross Margin]
FROM
#inv
This produces the following result.
Same calculation, but this time we use the table-valued function WACtvf.
This produces the following result.
The WAC calculations yields an ending inventory value of 67,275, which is between the LIFO and FIFO calculations.
COGS = 0 + 104,812.50 – 67,275.00
COGS = 37,537.50
GM = 62,500 – 37,537.50
GM = 24,962.50
As you can see from the 2 queries, the cost of goods sold is calculated as the sales quantity multiplied by the unit price from the previous row which does not change the unit price. You can also see that the unit price in the resultant table for trn 2 and trn 3 are the same because removing items from inventory does not change the weighted average price. When an item is added to the inventory, the unit price is the weighted average of the current row and the previous row.
Because of the way that the weighted-average calculation works it is impractical to have a function that returns the details of the specific inventory items that make up the balance as, generally, apart from the last row, the balances will not be in whole units.
Conclusions
The XLeratorDB inventory functions are really fast, reliable, easy to use, and scalable. Whether you are running on in-house processors or in an RDS environment these functions will save you time, money, and effort. Before letting your developers try to build these calculations from scratch, download the 15-day trial of XLeratorDB and try our functions first. All the documentation is available on the site, and there lots of examples which you can simply copy and paste into SSMS.
If you are not a SQL Server user, you can find these same functions in XLeratorDLL, which also has a 15-day trial.
If you have questions, please send us an e-mail at support@westclintech.com. If there are other functions that you think you might need check out the statistics, engineering, math, strings, and financial options libraries. If you can't find what you need, just send us an e-mail.