How to Create a LIFO Spreadsheet in Excel to Calculate Inventory Cost


If you have wondered how to create a sheet LIFO calculation in Excel To calculate the cost of inventory? Here we will explain the basic concepts of what a LIFO calculation is and how you can do it through Excel, quickly and without much difficulty.

We will do the process through Excel tools, and once the procedure is finished and we have the results, we can transform it to Google Sheets to later share it with the cloud or with our partners.

What is a LIFO spreadsheet for calculating the cost of inventory?

The LIFO method, which we express through a spreadsheet to calculate inventory costs, is one of the methods used in warehouse and inventory control policies.

You may also be interested in:

Which by its acronym in English indicates that the last products entered the warehouse are the first to be sold, in English it would be last in, first out. With this we can make valuations of our inventory in short periods of time and know how much we have paid and if in any case we have made sales, how much we have earned.

This is done, because in a work month several orders can be made, and each one can have a different lot cost, thus giving a cost of an amount that changes according to the lot that has arrived. The LIFO calculation is the inverse of the FIFO and it is normally applied in companies or places that sell non-perishable products.

create lifo excel spreadsheet calculate inventory cost

In case of supplying products that are difficult to store or that expire quickly, this method is not recommended, therefore it is important that you take into account what products you are selling to track your inventory, before deciding on one method or the other.

Structure to create a LIFO spreadsheet in Excel

The structure may vary depending on the needs we have. Nevertheless here we will explain a simple, which can be expanded without problems, on the one hand there is the number of purchased products, this box has to show the total number of units purchased, how much each package brings, the date of purchase, the cost of each package, and a box for the total.

Once we have this we will proceed to LIFO box, which consists of 4 columns that we must fill depending on the sales made, the first columns carry the total units sold, then the sum of the cost assigned to them, this is easy to do and you just have to have the updated data.

After this we will have to place a box that says the rest of the products in the warehouse, to know how many have not yet been sold, with the latter we can perform the evaluation of how much the warehouse is worth with the remaining products.

Steps to create a LIFO spreadsheet in Excel

To find the data to be entered in the LIFO method, we will first have to make a table with all the products purchased, we will have to group them by packages, normally when products are purchased it will be done by means of packages that bring 20 products or similar, we will group them like this, each row will have a package, we will place the date of purchase, the number of products, the total cost of the package.

lifo spreadsheet for inventory cost

By making the sum of all the products in a row, multiplying it by the price, we will have the total cost of that package or box, we will do this with each of the subsequent rows. Once we have this full box we will have to proceed to perform the LIFO box. In which we will have to place the total of the units sold first.

This is taken out with the sum of everything sold in the period of time elapsed for this method, knowing that the last to enter were the first to be sold, taking their value to set the price. Then in the box for the price of goods sold, the sum of everything sold at the price of the first package will be placed.

Once we have this, we will go to the remaining table, in which we will place the goods that still need to be sold at the unit price and finally we will have to calculate inventory value, which is taken out with products that have not yet been sold.

How useful did you find this content?

Click on a star to rate!

Average score 0 / 5. Counting of votes: 0

So far, no votes. Be the first to rate this content.

I'm sorry this content was not useful for you!

Let me improve this content!

Tell me, how can I improve this content?

You may also be interested in:

Deja un comentario