... ...

Excel Inventory

Free 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 for 300, 700 or 1,000 items.

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.

Excel Inventory Overview Video

download the Excel Inventory Template

Template For 300 Items

Download Template 300

Template For 700 Items

Download Template 700

Template for 1000 Items

Download Template 1000

The download is in zip format. You will need to unzip it – your computer 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. 


How to use the excel inventory template

The goal of this template is to track the quantity and value of stock on hand. So a report is included that lets you see a summary of the quantity and value of each item and an overall stock on hand. 

Excel Knowledge
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.

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 Examples page shows you what an Item's page looks like with transactions entered into it, and also how to move stock around within the workbook if you need to take some stock and make a new stock item.
  • Vendors(Suppliers) and Customers are the sheets where you enter in all the details of your vendors, suppliers and customers. 
  • The Stock Manager is for entering in the name and other important information for each stock item.
  • The Inventory Report displays a summary of the total purchases sales and quantity/value on hand for all stock items (currently not able to provide reports for tracking stock by Customer or Vendor).
  • The Monthly sheet is where you record the end of month stock value and is the value you will enter into your Bookkeeping records on the Balance Sheet as Assets.
  • Item #1, Item #2 etc. is where you enter the transactions for each item as it is purchased or sold, or stolen etc. 

The Examples Tab in Detail

When you are in the Excel Inventory template looking at the Example page you will see that the name of the first item is “Red T-Shirts XL”. Next to that, the current stock level is displayed (as 244) and the overall value on hand of 6,501.88. 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).  There are other cells showing the totals, namely :-

  1. Total Purchased (680.00) – shows the total quantity of stock purchased (or introduced, or returned to the supplier etc.) 
  2. Average Cost ($26.75) – the purchase price might change from time to time, so this ‘average cost’ displays the total average of all costs.
  3. Total Cost ($18,120.00) – this is the total quantity purchased of 680 multiplied by the average cost of $26.75.  

The next three examples show you what to do if you need to take some some stock and manufacture a new stock item with those. 

The Menu

This menu helps make navigation of the workbook easy.

The Vendors or Suppliers Sheet

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.

Customers Tab

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.

Stock Manager

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.

Enter your stock on hand quantity and value if you have them already, they will fill over into their relevant sheet.

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 the arrow next to each item to take you directly to that Item's main sheet.

The Current QTY column comes from the Inventory Report.

Enter a re-order level and the Current QTY cell will turn red when it drops below the re-order level.

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.

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 #300 (or 700 Or 1000) 

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

You can change the names of all the tabs along the bottom to your Item names.

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? 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 Examples 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.

Periodic Inventory - 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 my Contact Form.

Share this page

Facebook Comments

Have your say about what you just read!
Leave me a comment in the box below.