Stock Inventory Free Template Download2 - Free Download | Page 4
4.7, 3287 votes
Please vote for this template if it helps you.
Page 3 of 16
Excel Skills | Inventory Control Template | Usage Based
All inventory purchase transactions should be recorded on the Movements sheet. The columns with yellow column
headings require user input and the columns with light blue column headings contain formulas that should be copied
for all new inventory movement transactions that are entered on this sheet.
The following columns require user input:
Transaction Date: enter the delivery date that relates to the stock purchase transaction in column A. All dates
should be entered in accordance with the regional date settings that are specified in the System Control Panel.
We've added data validation to column A to ensure that only valid dates are entered in this column. The date on
which the supplier delivers the inventory to your business premises should be recorded in this column because the
inventory is added to the theoretical inventory balance on this date. If the inventory has not been delivered yet, the
Transaction Type: select the Purchase transaction type in column B.
Supplier Name: enter the name of the appropriate supplier in column C.
Document Number: enter the supplier invoice number or other unique reference number in column D. The
reference that you enter in this column should enable you to trace the transaction back to its supporting
Stock Code: select the appropriate stock code from the list box in column E. All stock codes need to be created on
the StockCodes sheet before being available for selection. Refer to the stock description which is displayed in
column I to ensure that the correct stock code has been selected.
Transaction Quantity: enter the quantity of stock that is purchased in column F. The purchase quantity should be
recorded in the unit of measure which is displayed in column J.
Invoice Amount: enter the total invoice amount that relates to the stock code that was selected in column E. This
amount should be entered exclusive of sales tax if the business is registered for sales tax purposes and inclusive of
sales tax if it is not registered. If the business is registered for sales tax purposes, the sales tax can be claimed back
from the appropriate tax authorities and should therefore not form part of the inventory cost.
Note: If a supplier invoice includes multiple stock codes, each stock code needs to be recorded individually on the
Movements sheet. A single supplier invoice could therefore consist of multiple stock purchase transactions on the
Note: If stock is delivered by a supplier that is not the same supplier as the one from which the stock is ordered, the
supplier invoice that relates to the delivery cost will have to be recorded separately in order to include the delivery
cost in the average cost calculation. When you record the information from the delivery supplier invoice, it is
important that the stock quantity should be entered as a nil value, otherwise the quantity may be included twice in
the calculation of the stock quantity on hand. It may also be necessary to allocate the delivery cost to more than one
The calculated columns on the Movements sheet consist of the error code, stock description, unit of measure
(UOM), previous quantity on hand, new quantity on hand, previous average cost, new average cost, transaction
value, purchase price per unit, purchase price variance, purchase price variance percentage and the stock
movement date. The values in all of these calculated columns are calculated automatically based on the values that
are recorded in all the user input columns (columns with yellow column headings).
The stock description is included to enable users to view a description of the stock code which is selected in column
E and the UOM is included in order to indicate which unit of measure should be used when entering transaction
quantities. The previous and new quantity on hand calculations reflect the quantity of stock that is on hand before
Note: The quantity on hand calculations reflect the quantity of stock that is on hand before and after recording a
transaction. These calculations play an important role in calculating the previous and new average costs in columns
M & N and can also be used to identify stock transaction recording errors - if either the quantity on hand before or
after recording a transaction is negative, it means that an error has been made in the recording of previous stock
movement transactions. Should this be the case, an error code will be reflected in column H which indicates that the
previous inventory movement transactions for the affected stock code need to be investigated.