Excel Inventory

Free Template

Excel Inventory Template

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 three templates to choose from, one with space for 100 products, one for 300 products and the other for 600 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.

download the template

Template for 100 Items

Template for 300 items

Template for 600 Items

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 us.

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.


How to use the excel inventory template


First of all, if you live in a country where you report on, and pay sales tax, to your government, exclude the sales tax amounts from this spreadsheet.  As a random example, if you buy a stock item for $20 which includes $3 sales tax, only enter $17 into the spreadsheet. The value of your stock on hand should not include sales tax.

Second of all, having some Excel knowledge will help you, however, they do have a good knowledge base which you can access from within your Excel program. If you use 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.

TABS

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:-

  • Help Tips gives you quick tips for entering and editing data in this Excel Template
  • The Example page gives you an idea of how a page about an Item looks when it is filled in.
  • The Inventory Report displays a summary of the totals for all 100 stock items.
  • There is another tab that isn't in the above screenshot called Monthly. This is where you record the end of month stock value.
  • Item #1, Item #2 etc. is where you enter information about each item as it is purchased or sold, or stolen etc. There are 100 of these tabs.  The screenshot shows only up to Item #10. You can see the remaining tabs by:-

           1. clicking on the arrow circled in red in the image below which moves the tabs along one by one

           2. or by right clicking between the two arrows (see red arrow below) which brings up the list of all tabs 

Screenshot of where to click to bring up the tabs list.

You can then select which tab you want by clicking on it, and then clicking OK. You can scroll down past Item #17" to see all the other Items with the right-hand scroll bar.

Screenshot of tabs list

THE EXAMPLE TAB IN DETAIL

When you are in the Excel Inventory template looking at the Example page you will see that:-

  • The name of this item has been changed from “Item #1” to “Red T-Shirts”. 

  • Underneath that, the current stock level is displayed (as 328). This will only be accurate on your actual Items pages if you update all purchases (in the blue sections) and all sales (in the green sections). 

  • Along the top in the grey boxes, you will see the totals of each column, namely :-
  1. Total Purchased (758) – shows the total quantity of stock purchased (or introduced, or returned to the supplier etc.) 

  2. Average Cost ($27.05) – the purchase price might change from time to time, so this ‘average cost’ displays the total average of all costs.

  3. Total Cost ($20,390) – this is the total quantity purchased of 758 multiplied by the average cost of $27.05.  
  • This example sheet shows a currency symbol. If you want to display a currency symbol on your pages, you need to select all the cells that you want the symbol to display, right click your mouse button, select Format Cells, select ‘Number’, select ‘Currency’ and make your selection.

THE INVENTORY REPORT TAB IN DETAIL

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.

This report has been ‘locked’ so that you don’t accidentally remove the links, however, if you need to make a change you can unlock it by:

  1. clicking "Review" (selection menu across the top of Excel), and 
  2. selecting "Unprotect Sheet" – there is no password.

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

"ITEM #1 TO "ITEM #100 IN DETAIL

This is where you keep the record 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"

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).

The Drop Down List Transaction Types

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?

Did you return an item to the supplier? 

Did a customer return an item to you? 

Did you make stock (rather than buying it)? 

Did stock get damaged? 

Did you use stock in a promotion (like at a Fair)? 

Is stock missing and you don’t know where it is? .

Has stock been stolen? 

Do you need to make an adjustment for whatever other reason? 

Use "Purchase" or "Sales".

Use "Purchase Return"

Use "Sales Return".

Use "Stock Introduced".

Use "Stock Damaged" to remove it.

Use "Stock Used Promotions".

Use "Stock Missing"

Use "Stock Stolen".

Use "Stock Adjustment".


You can reveal and edit this list 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.


how to record inventory in bookkeeping software

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?

Here’s how.

Every time you make a purchase of stock

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:- 

  • A purchase invoice (bill), entering the purchase price to "Purchases", or

  • Enter the bank transaction of the payment of the purchase to the "Purchases" account (you would usually do this if you don’t keep track of bills and payables in your bookkeeping software, or if you are keeping an excel spreadsheet to track income and expenses and so can only record payments and not invoices).

  • Then, you will record the purchase invoice number (if applicable) and stock details into your Excel inventory template.

Every time you sell stock

You will enter the sale amount into your bookkeeping system against your Income account (which you can call “Sales”), with one of these methods: -

  • A sales invoice entering the sales price to the "Sales" account, or 

  • Enter the bank transaction of the payment received for the sale to the "Sales" account (for people who don’t issue sales invoices from their bookkeeping software or who use an excel spreadsheet).

  • Then, you will record the sale invoice number (if applicable) and details of stock sold into your Excel inventory template.

Updating the Balance Sheet

The value of any stock that is left unsold, sitting in your stock room needs to be shown on your Balance Sheet as "Stock on Hand"

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.

By the way, periodic inventory works very well even if you don’t want to bother keeping track of daily purchases and sales of inventory and if you want to do away with maintaining an Excel inventory template.

Instead, you will do a stock count once a month and calculate the value of Stock on Hand – you, of course, will need to have a record somewhere of the cost of all the items you purchased so that you can calculate the Stock on Hand value by multiplying the quantity of stock in your stock room by the cost price.

A Bit Like Periodic Inventory

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. 

A Bit Like Perpetual Inventory

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.

How to Get the Value of Stock on Hand from the Excel Inventory Template

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). 

journal entries for inventory

To update the Balance Sheet and Income Statement

If you are using bookkeeping software, you will enter two journals to bring the balances in to your accounts - you can learn about journals here or about debits and credits here.

You need to set up three new accounts: -

  • a "Stock on Hand" account on your Chart of Accounts under Assets (Current Asset)
  • a "Closing Stock" account on your Chart of Accounts under Cost of Goods Sold
  • an "Opening Stock" account on your Chart of Accounts under Cost of Goods Sold

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.

Why?

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 our Contact Form.


You are here:

GET FREE UPDATES

Bb_resized_even_smaller

Subscribe to get our latest content by email.

We won't send you spam. Powered by ConvertKit



Back to Top : Excel Inventory