FIFO

  • Tagged with:
  • Arrays,
  • Excel,
  • FIFO,
  • INDEX,
  • Inventory,
  • MATCH,
  • MIN,
  • OFFSET,
  • Spreadsheets,

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:

  1. 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.)
  2. 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.)
  3. Multiplies this marginal quantity purchased by the cost per unit for this row.
  4. 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.

17 Comments so far Join the Discussion

  1. Brian

    1st December 2010 at about 7am

    Thanks for the example, the formula is a little beyond my capability so I could never create this myself. I think though that it needs to be modified slightly to take account of a sale that exceeds the origianl purchase. e.g when the sale value in B4 is changed to (110) the result in D4 should be ($136) (100×1.20 plus 10×1.60) but the model produces a result of ($132) ?

  2. Darren

    1st December 2010 at about 9am

    Brian, thanks for your comment. I know the formula is extremely complicated — you’d think doing a FIFO calculation would be easier, but so far I have not found any simple spreadsheet solution to this problem.

    Regarding the second part of your comment: the problem is (probably) that you’ve edited the calculation cell and then hit Enter, or even just exited the cell, without typing Control Shift Enter. This is what is required to have the formula recognised as an array formula (see italicised text under the second image). Entering a formula as an array formula puts curly brackets { } around the formula (note, you cannot manually put these curlies in, they have to be done with Control Shift Enter). I have tried this myself, and the non-array version shows ($132), whereas the array version shows the correct figure of ($136). I suggest fixing this by either starting from scratch with the model I linked to in the post, or fixing the non-array formula by either the Control Shift Enter method, or just by copying a ‘good’ formula and pasting to the cells that are not correct.

    For more on array formulas, check out this site, or just do a google search for “array formulas excel”.

  3. Brian

    1st December 2010 at about 10pm

    You are correct I had just pressed enter, when I press ctrl,shift, enter it works perfectly. Thanks Darren

  4. Maria

    17th December 2010 at about 4am

    Hi Darren,

    Your formula has been a life saver as I’ve incorporated it with my trade blotter. I have a query which I haven’t been able to solve – I have a summary sheet which shows the aggregate shares for numerous securities and is feeding off my trade blotter which has pricing and security name information. I’m looking to obtain the value in D6 above to appear in one cell. That is, I’m looking to show the following from above:

    Security Shares Total Cost Cum. Buy QTY Cum. Buy Cost
    XYX 10 $16 150 200

    I’ve created a summary sheet which has all of the data other than Total Cost (the value in D6 above). I’d like the total cost to be calculated by going through the different values in my trade blotter for XYX and calculating it by the FIFO method.

    I look forward to hearing from you – let me know how I can post my spreadsheet if that would be of more assistance.

  5. Darren

    17th December 2010 at about 10am

    Maria, please email me a sample of your spreadsheet to darren@sumwise.com and I will take a look.

  6. John Smith

    22nd December 2010 at about 9am

    Thank you for putting forth the effort into the formula. Can I use your spreadsheet in some way to calculate the profit and loss ? I have a FIFO spreadsheet that I could send but it is not working properly, yet. Pls let me know.

  7. Darren

    22nd December 2010 at about 12pm

    Email your spreadsheet to me at info@sumwise.com and I’ll take a look.

  8. okili

    31st May 2011 at about 5pm

    wow, that was exactly what I was looking for, I was trying the whole day to create something like this before I found that, I was on the right way but could not find the final solution to generalize the formulas. Thanks a lot.

  9. Martin

    18th November 2011 at about 3am

    Hi Darren,

    thanks a lot for creating this FIFO sheet. I have found several approaches to this problem, and your solution is the most elegant one. Also thanks for explaining the formula in detail. As I needed to generalize the sheet to reflect not only one item type, but different items, I needed to understand the formula. Basically I introduced an additional “item” column and extended each term of the monster with an “IF(((item array)=(current item)) …” – making Godzilla out of the smaller monster it was before!

    When doing this, I found two questions I want to ask here. Both of them are non-critical, but I am curious.

    1. Excel 2002 keeps complaining about circular references, even in your small example sheet. Excel 2010 seems to be fine with this, but complains when I let it calculate my Godzilla. When switching on iterations, both Excel versions give me correct results. Could you please give me some background information on how your monster creates circular references (or not)?

    2. While I see the purpose of the last line of your monster, I am curious why you used the OFFSET function. To me, a more intuitive statement would have been
    +SUMIF($D$2:$D2;”<0")))
    in the last line of D3 (and copied to all cells below). I know that this does not work for the first row, but the first row must be a purchase anyway. Is there a specific reason to use the OFFSET function?

  10. Darren

    18th November 2011 at about 4am

    Martin,

    Glad to see my little monster has grown up ;) . To answer your questions:

    1. I really don’t know why you’re getting circular reference problems. I have tested it in Excel 2003 and 2010 (I didn’t know there was a 2002 version) and neither give me any circular references. Perhaps you introduced a circularity into it with your Godzilla formula. Feel free to email me your model to info@sumwise.com so that I can check it.

    2. I used OFFSET so that you could insert new rows anywhere in the range and not have to worry about the usual problem of formulae skipping the new row — this happens because you’re trying add up everything above the row in question and if you insert a row, the row below the new row essentially skips the new row just inserted. (Does this make sense?) Using OFFSET turns this physical reference into a logical one and allows new rows to be inserted without impacting the logic of the formulae.

  11. Martin

    18th November 2011 at about 7am

    ad 1. I guess you are right; it might have to do with my replacing a SUMIF with an array multiplication. The sheet is on the way for you to have a quick look. Thanks for offering your help. BTW, my older Excel version is “Excel 2002 (10.6501.6626) SP 3″.

    ad 2. Good point, makes perfectly sense!

  12. Charlie

    9th February 2012 at about 6am

    Thanks for this spreadsheet! I hope to use it to determine the cost basis for stock transactions and have a question about entering stock splits. The IRS seems to request using FIFO to determine the cost basis of stock transactions. Lets say I purchased 100 shares for $1.00 each. Next transaction is a 2:1 stock split. If I enter that as receiving another 100 shares at $0.00 cost the line at the bottom shows a quantity of 200 with a price of $0.50 and a cost of $100.00 which is correct. Now if I sell 150 shares a cost of $100.00 is shown for that sale with 50 shares remaining in the account at no cost. If I sell those 50 shares with a cost of $0.00 my gain on this transaction would be the net proceeds of these 50 shares. Is this correct accounting for the IRS?

  13. Darren

    16th February 2012 at about 4pm

    Carlie – not sure about the IRS treatment although logic would tell you that a stock split should change the cost base in the shares you own in the same proportion. You are right that the current FIFO model the blog post links to does not deal with stock splits in the way you suggest it should.

  14. Peter

    25th March 2012 at about 10pm

    Might be worth noting that the OFFSET function with a negative height argument is undocumented, unsupported and in fact described by Microsoft as a bug. So it could stop working at some point.
    OFFSET(D2,ROW($D$1)-ROW($D2),0,ROW($D2)-ROW($D$1),1) seems to produce the same result with a positive height.

  15. Terence

    10th July 2012 at about 7pm

    I wonder if you have a solution for a list of different items having different costs and that you can tell using a generalized equation or a combination of columns to know the FIFO cost everytime you sell something from that list?

  16. Terence

    27th July 2012 at about 8pm

    Excellent, I am looking for ways to expand your excel so that it can handle different tickers at the same time. I wonder if you have any suggestions?

  17. EDB

    16th October 2013 at about 5am

    How do I create a Google spreadsheet that calculates the cost basis for stock sales using FIFO methodology? I have the following columns for data entry: TRANSACTION_ID, TRANSACTION (BUY/SELL), TICKER, QUANTITY, PRICE, COST BASIS, PROFIT, DATE. For each unique TRANSACTION_ID where TRANSACTION=SELL the COST BASIS would be FIFO cost based on previous buy and sell transactions (for that TICKER and QUANTITY sold).

Join the discussion

Popup

Register your Interest

Please register to keep up to date with our developments.

Register your Interest

Please register to keep up to date with our developments.

custom essays cheap | custom paper writing | Visual Rhetoric buy custom term papers .