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 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 :-
- Total Purchased (758) – shows the total quantity of stock purchased (or introduced, or returned to the supplier etc.)
- Average Cost ($27.05) – the purchase price might change from time to time, so this ‘average cost’ displays the total average of all costs.
- 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.