If you have ever wondered how to create a LIFO spreadsheet 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.
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 an amount cost that changes according to the lot that has arrived. The LIFO calculation is the inverse of the FIFO and is normally applied in companies or stores that sell non-perishable products.
In case of supplying products that are difficult to store or 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. However, here we will explain a simple one , which can be expanded without problems, on the one hand there is that of the products purchased, 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 the LIFO table , which consists of 4 columns that we must fill depending on the sales made, the first columns show the total units sold, then the sum of the cost assigned to them, this is easy to do and only you 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 evaluate how much the warehouse is worth with the remaining products.
Steps to create a LIFO spreadsheet in Excel
To find the data to enter 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 through packages that bring 20 products or similar We will group like this, each row will have a package, we will place the purchase date, the number of products, the total cost of the package.
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 table full we will have to proceed to make the LIFO table. In which we will have to place the total of the units sold first.
This is obtained 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 sell, 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 the value of the inventory , which is taken out with the products that have not yet been sold.
