Inventory Management with Excel

For a logistics company, we prepare in an Excel Book, a series of sheets that allow the control of stocks, using a portable computer, while the user moves around the warehouse, noting the real units of each article.

For this book, we created a sheet containing the entire “List of articles”, with the data required (packaging code, article code, article description, units per pallet and number of pallets). No formulas are entered on this sheet, only the basic data that make up each article:

List of articles

On another sheet, which we call “Inventory”, we create a table with formulas, so that the user can enter the real units, which he finds in the warehouse. The data to be introduced by the user, are: The number of the sheet on which he notes down the data, his name or employee code, the packaging code, and finally the number of boxes he can find “loose” or that do not make up the unit of a pallet. The formulas entered look for the units per pallet and the number of pallets according to the article code. This, together with the number of loose boxes entered by the user, calculates the physical or real total:

Inventory

Finally, on another sheet, we present the totals for printing the data, using lookup and reference formulas, to present the data:

Totals for printing

Application made with Excel. To use this application you need to have the MS Excel program. The application is made in Spanish. Note: The data inserted in the images are fictitious.