Download this free Microsoft Excel inventory template and use it to keep track of all the products that you buy, make and sell.
There are two templates to choose from, one with space for 100 products and one for 300 products.
The first section of this page explains how to use this template.
The second section explains how to bring the inventory balances off this template into your bookkeeping accounts.
The valuation of the stock is calculated using the Average stock method.
You can learn more about stock calculations here.
The download is in zip format. You will need to unzip it – your computer or device should be able to do this automatically. If you have any issues with getting the document open, contact me.
After opening the document, you should click File, Save As and save it to your selected folder. Go here for more information on digital filing.
Having some Excel knowledge will help you, however, Excel does have a good knowledge base which you can access from within your Excel program.
If you use Microsoft 365 (formerly called Office 365), you can access help from the top menu, next to the light bulb icon where it says "Tell me what you want to do" where you can type in a word or two related to what you want help with and do a search.
When you open up the Excel Inventory template, you will see a line of named tabs across the bottom of the screen as in the picture below:-
This is a quick summary of the tabs. Further down the page is more detail about each one:-
When you are in the Excel Inventory template looking at the Example page you will see that:-
The next three examples show you what to do if you need to take a portion of some stock and manufacture a new stock item with those.
Enter in the names of all your vendors or suppliers, their address and contact details and any other information that you want.
You will then be able to select your vendors or suppliers from whom you buy stock within each Item sheet.
Type in all the names of your customers and their contact details.
You will then be able to select your customers to whom you sell stock to from within each Item sheet.
On this sheet, you will enter in the names of all your stock items, select the name of the vendor or supplier who you buy the item from (you might purchase from more than one vendor) and other important information.
If you manufacture the stock item, you can leave the vendor columns blank.
This sheet is linked to every Stock Item tab in this Excel workbook.
You can click on an item number in the Item Number column and it will automatically take you through to that Item in the workbook.
The Inventory Report in this Excel inventory template is linked to every Item page within the same template.
Whenever a change is made to any Item’s page, this report is automatically updated. You do not need to manually add any information into it.
You will notice all headings have an arrow field to the right. See red-circled arrows in image below.
When you click on these arrows you can filter information to only show what you want by selecting the info you want.
For more on using the data filter, go here.
This is where you keep the inwards or outwards transactions of each individual item of stock – enter your purchases and sales, and other adjustments to record stolen stock or stock used in promotions and so on.
The totals from these pages are all linked to the "Inventory Report".
You only need to enter details of purchases and sales in all cells with a white background.
The cells with color either have text in them, or have formulas (careful not to over-type these formulas by accident.)
When you download the template, each heading will say “Item #1” or “Item #2”, you can over-type these with the actual name of your stock items – like we did on the example page that says “Red T-Shirts", "Green Velvet Fabric", "Zippers" and "Round Cushion".
You can also change the names of all the tabs along the bottom.
The 'data filter' arrows are also available on every Item page (see explanation under Inventory Report above).
If you select a cell in the “Transaction Type” column, a field arrow will appear in the right-hand side of the cell.
See red circled area in screenshot below.
If you select this arrow, a list of phrases appears from which you can select the transaction type.
Is it a normal purchase or sale? Use "Purchase" or "Sales".
Did you return an item to the supplier? Use "Purchase Return".
Did a customer return an item to you? Use "Sales Return".
Did you take portions of stock to manufacture other stock? Use "Stock Used in Manufacture".
Did you make stock (rather than buying it)? Use "Stock Introduced".
Did stock get damaged? Use "Stock Damaged" to remove it.
Did you use stock in a promotion (like at a Fair)? Use "Stock Used Promotions".
Is stock missing and you don’t know where it is? Use "Stock Missing"
Has stock been stolen? Use "Stock Stolen".
Do you need to make an adjustment for whatever other reason? Use "Stock Adjustment".
You can reveal and edit this list - to add more options to select from - by pointing your mouse to the tabs along the bottom, right clicking, and selecting "Unhide", then choose "ListSelections". Edit the wording that you want to change.
The “Example Red T-Shirts” page shows you a series of example transaction types so you get an idea of how to enter your data.
Note how to do the returns and losses or damages – you can update these by entering a negative quantity in the Purchases and Returns section.
Use the Average Cost for stolen, missing, adjusted, damaged or promoted items.
It’s all very well being able to track your inventory in this Excel inventory template, but how should you record all of this in your bookkeeping records so that your Income Statement and Balance Sheet are correct?
You will enter the cost of the purchase into your Cost of Goods Sold account (which you can call “Purchases”), with one of the following methods:-
You will enter the sale amount into your bookkeeping system against your Income account (which you can call “Sales”), with one of these methods: -
You could leave it until the financial year end, and update the Balance Sheet at that time (or let your Tax Accountant do it), but if you like to have more accurate looking monthly figures we recommend updating your Balance Sheet once a month.
This is a bit like Periodic Inventory where you are not recording all inventory into the accounts the minute a purchase or sale happens but are only entering the value of stock on hand into the accounts once a month so it can be viewed on your Balance Sheet.
Keeping the Excel inventory template up-to-date is a bit like Perpetual Inventory because you are keeping a perpetual record of all your stock purchased and sold, the only reason it is not a complete perpetual inventory is because this method of tracking stock in an Excel spreadsheet does not automatically update your Balance Sheet and Income Statement at the same time.
Let’s use the month of May as an example.
When you have updated your inventory for the month of May with all sales and purchases in the Excel inventory template, make a note in the ‘MONTHLY’ summary tab of the stock on hand value for the end of May (get this Stock on Hand value from cell J6 on the Inventory Report but make sure this does not include any sales or purchases dated after May otherwise it will be wrong).
To update the Balance Sheet and Income Statement
You need to set up three new accounts: -
Here are the journals:-
What Does This First Journal Do?
This leaves only the *cost of the stock, that has already been sold, in the Cost of Goods Sold section of the Income Statement, which is what you want.
*the cost being the amount paid to the supplier when purchasing the stock.
Because in a normal inventory double-entry bookkeeping system, when stock is purchased and entered into inventory it is coded directly to the “Stock on Hand” Asset account on the Balance Sheet. When an item is sold out of that inventory, the system automatically transfers (journals) the cost of sold item to the Cost of Goods Sold “Purchases” on the Income Statement.
Why Do We Reverse It?
It is reversed because the closing stock balance is only a valid value of stock on hand at the end of May so we don’t want it to show this value through June. It will be re-updated at the end of June and then reversed 1 July using the same steps as explained in all the info above, and doing this every single month.
If you have any questions about using this Excel inventory template, send your questions through my Contact Form.