Using Google Sheets
Last in, First out methodology
The LIFO methodology assumes that items bought last will be sold first. LIFO holds true when inventory are kept in stacks; thus, the first one sold should be the last one acquired. Using this method, causes that last prices become your cost of goods sold, and older costs your ending inventory balance. In the image, calculated ending inventory represent the older costs. Under normal conditions, LIFO does not follow a normal flow of inventory movement, other than stacks.
Solution:
Using a spreadsheet such as Google Sheets or Microsoft Excel you can achieve the results presented on the image. I used Google Sheets for this calculation. Keep in mind that I use formulas and functions to automate and ease the calculation process. Let the spreadsheet work for you. The following steps show how I calculated the solution:
Since LIFO shows inventory based on the assumption that the oldest inventory stays as compared to the newest; then, I ordered my table in the same way. I sorted my data based on acquisiton date (column A). Then, included the data segregated in columns for ease of analysis and presentation. On column G, applied the following formula:
=if(F55<$C$59,0,if(and(F55>$C$59,$C$59>F54), (F55-$C$59)*D55,C55*D55))
This nested formula test three conditions for our calculation.
First test – =if(F55<$C$59,0, if the cumulative inventory level is less than the calculated sold units (TRUE) then will place $0, that line represent a sold unit.
Second test – if(and(F55>$C$59,$C$59>F54), (F55-$C$59)*D55 if the sold units falls between the beginning and ending cumulative inventory levels then it must substract the cumulative inventory from unit sales and apply the unit cost.
Third test – C55*D55 if both first and second test false, then multiply quantities and unit cost.
I have included a manual calculation to test both the ending inventory and the cost of goods sold. As a matter of a remainder, inventory balances use costs not selling prices; therefore, keep in mind that any schedule prespared must be in terms of costs. Results using LIFO and FIFO, do differ; thus if you need to review further check my FIFO post.
Conclusion:
The Last in First out method of inventory costing, calculates inventory ending balance on the assumption that items bought last will sale first. This seldom approach to the normal inventory movement. Using a spreadsheet such as Google Sheets helps to calculate and present better the data. The same calculation should allow us to present a better report either detailed or in a summary.
I understand that sometimes, you do not have the time to explore and put together spreadsheet solutions. To ease up things, I have included a spreadsheet with this methodology, it is only $3 to cover the costs of running this website and keep adding new material. You can modify, update or change the spreadsheet to accommodate your needs.
Please add some comments, requests of new material or spreadsheets. I want to improve your site experience and keep you as a regular visitor.
You can also subscribe to our webpage, to be notify when new blogs are available!