How to create an inventory management system in Excel, that can deal with multiple products, multiple suppliers, and fluctuating purchase prices? Let me show you step-by-step how to build a simple spreadsheet that can help you track inventory units as well as inventory value.
⏱️TIMESTAMPS⏱️
00:00 Inventory system in Excel
00:20 Excel inventory system: product list
02:00 Opening inventory
03:40 Inventory purchases
07:05 Available for sale inventory
11:24 Average cost inventory method
13:30 Outgoing inventory shipments (COGS)
15:27 Ending inventory calculation
16:29 Correcting stock level mistakes
18:17 Calculating month-end inventory value
20:04 Closing the inventory system for the month
If you are looking for powerful #inventory software that’s easy to use, and provides you visibility to your COGS and margins, then try InFlow inventory management software for free: https://partnerstack.inflowinventory.com/ExcelSystem
Our Excel inventory system is for a small company that sells fitness equipment for home use. The spreadsheet starts with a tab called “Product list”, which I have pre-filled with some sample data: item code, item name, item description, item category, supplier, and item status. Item code is a unique product identifier (a “primary key”) that plays a very important role in our inventory management spreadsheet.
The second tab that I partially pre-filled is the opening inventory. Item code on the left, then item name, number of units in stock, the purchase value per unit, and the total value.
Now we add a tab by clicking the “+” over here, and we name this tab “In”, to account for purchases of new units, or transfers from another warehouse. The columns on this tab are date, delivery note, item code, item name, units, value per unit, and total value. Let’s apply some formatting to the headers (bold, blue background), and to the cells (borders – all borders). As the “In” tab is one of the tabs where transactions are recorded, we identify it with a different header color. Tabs with orange headers need to be set up only once and are for calculation purposes, tabs with blue headers are for transaction input.
In order for the cost accounting side of the #inventorymanagement spreadsheet to work smoothly, our next tab is going to be called “Available for sale”. Units available for sale is the sum of the units in opening inventory plus the incoming units during the period. The value of available for sale inventory is the sum of the opening inventory at the start of the month, plus the value of the incoming inventory that was purchased during the month. What we are doing here is getting the data ready to calculate a new inventory value per unit, using what is called the periodic system average cost inventory method. The average cost inventory method blends all purchases together, and comes up with one number per unit to apply in both Cost Of Good Sold in the income statement, and inventory valuation on the balance sheet.
Now that the inventory available for sale is calculated in our Excel inventory system, we can add a tab for the outgoing shipments: what we have sold, or transferred to other warehouses.
We can now prepare the ending inventory overview. Units in ending inventory equals units available for sale minus units out. Value of the ending inventory equals the number of units times the value per unit.
Philip de Vroe (The Finance Storyteller) aims to make accounting, finance and investing enjoyable and easier to understand. Learn the business and #accounting vocabulary to join the conversation with your CEO at your company. Understand how financial statements work in order to make better investing decisions. Philip delivers finance training in various formats: YouTube videos, livestreams, classroom sessions, and webinars. Connect with me through Linked In!
Want to get access to bonus content, and/or express your gratitude by buying me a cup of tea? Join my channel as a member through https://www.youtube.com/channel/UCQQJnyU8fALcOqqpyyIN4sg/join