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.
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 Details is for entering in the name and other important information for each stock item.
- The Inventory Report displays a summary of the totals for all 100 (or 300) stock items.
- 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 has been changed to “Red T-Shirts”.
- Underneath that, the current stock level is displayed (as 244). 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 :-
- Total Purchased (680.00) – shows the total quantity of stock purchased (or introduced, or returned to the supplier etc.)
- Average Cost ($26.75) – the purchase price might change from time to time, so this ‘average cost’ displays the total average of all costs.
- 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 a portion of some stock and manufacture a new stock item with those.
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.
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 Details 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 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 #100 (or 300) in Detail
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).
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
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.
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?
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.
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.