Stock Inventory Free Template Download2 - Free Download | Page 6
4.7, 3287 votes
Please vote for this template if it helps you.
Stock Inventory Free Template Download2 Page 6
Stock Inventory Free Template Download2
Page 5 of 16
Excel Skills | Inventory Control Template | Usage Based
Another important point to emphasize is that the average cost calculation will only be accurate if the stock quantity
on hand at the time of the purchase is calculated accurately. The quantity on hand could be inaccurate if errors are
made when recording stock purchase, usage or adjustment transactions. In order to identify possible errors, we
have included error codes on the sheet in order to highlight possible stock inaccuracies. If an error code is therefore
reflected in the Error Code column, the error should be investigated and resolved in order to prevent inaccurate
Inventory Usage
All inventory usage transactions should be recorded by entering the appropriate negative quantities on the
Movements sheet. The user input that is required on this sheet includes the transaction date, transaction type, stock
code and quantity used. The other user input columns (supplier name, document number and invoice total) are not
required when entering stock usage transactions and can either be left blank or you can enter a default value in
these columns. The invoice amount should equal a nil value when entering stock usage transactions.
We recommend implementing a system whereby the inventory that is used on a daily basis is recorded
independently and then recorded on this sheet at the end of the day or the beginning of the next day. There are
significant benefits to controlling stock on a daily basis and the recording of daily stock usage forms an integral part
of this process. If there are factors that result in daily stock usage measurement being impractical, this template can
Another (less effective) method of controlling inventory usage is to simply perform a physical stock count and to
record the difference between the theoretical stock on hand and the stock count quantities as usage on the stock
count date. If a system like this is followed, inventory usage is not recorded independently and the theoretical stock
balance will therefore only consist of the stock quantities that have been purchased. The resulting stock variances
therefore represent the inventory that has been used since the previous stock count.
The disadvantage of using a stock count system to record usage is that there is no reference point for identifying
stock items and areas which result in losses because the transaction cycle is in effect incomplete. It will therefore be
extremely difficult to investigate and to explain inventory usage in excess of expectations.
The recording of inventory usage on a regular basis (daily or weekly) enables users to calculate accurate theoretical
stock balances which can be compared to physical count quantities. This system makes it a lot easier to identify and
investigate problem stock items and areas and enables users to limit excess stock usage a lot more effectively.
All the user input and calculated columns on the Movements sheet have been covered in the Inventory Purchases
section of the instructions. We will therefore only point out differences between the recording of stock purchase and
stock usage transactions in this section of the instructions.
Transaction Date: the date on which the appropriate stock item has been used should be entered in column A.
Transaction Type: select the Usage transaction type from the list box in column B.
Supplier Name, Document Number: these columns can be left blank when entering usage type transactions or
you can enter a default value like "None" or "N/A".
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.
Transaction Quantity: enter the quantity of stock that has been used as a negative value in column F. The usage
quantity should be recorded in the unit of measure which is displayed in column J. If a usage transaction type is
selected in column B and a positive value is entered in this column, an error code will be displayed in the Error
Code column. Refer to the Error Codes section of the instructions for guidance on how to resolve user input errors.
Invoice Amount: enter a nil value in this column when recording stock usage transactions.
Note: The previous and new average costs that are calculated for stock usage transactions will be the same. This is
because average unit costs will only change when purchasing stock at a different price from the ruling average cost
at the time. The transaction value for stock usage transactions is therefore calculated by multiplying the transaction
Note: The average cost calculations that are included in this template will only be completely accurate if there are
no error messages in column H on the Movements sheet. It is therefore imperative that users ensure that all the
user input errors that are reflected in this column are resolved before viewing any of the inventory movement or
Stock Inventory Free Template Download2 Previous Page Stock Inventory Free Template Download2 Next Page
Stock Inventory Free Template Download2