Please vote for this template if it helps you.
Page 2 of 16
Excel Skills | Inventory Control Template | Usage Based
Instructions
www.excel-skills.com
UOM - the unit of measure (UOM) refers to the stock measurement that is used when ordering, using and counting
stock. Stock usage and counting units of measure carry more weight than stock ordering units of measure. For
example, if it is easier to record the usage and stock count of an inventory item in kilogram and the product is
purchased from a supplier in bags, the unit of measure should be specified as kilogram. When the product is
purchased from the supplier, the quantity that is purchased then simply needs to be converted to kilogram when
Opening Quantity - enter the opening stock quantity in this column. This is the stock quantity that is on hand on the
date that you start using the template for recording inventory transactions. All subsequent inventory movements
should be recorded on the Movements sheet.
Opening Cost - enter the cost of the opening stock quantity that is recorded in column D. The cost is multiplied by
the opening stock quantity in order to calculate an opening stock valuation.
Note: All the columns on the StockCodes sheet with yellow column headings require user input. The columns with
light blue column headings contain formulas that should be copied from one of the existing rows for all new stock
Inventory Valuation & Movement Reports
The calculated columns on the StockCodes sheet enable users to view a stock valuation and movement report for
each individual stock item which is automatically calculated based on the inventory transactions that are recorded
The inventory valuation report consists of the quantity on hand, average cost and inventory valuation for each
individual stock item (columns G to I). The inventory movement report consists of the opening quantity, purchases
quantity, usages quantity, adjustments quantity, opening value, purchases value, usages value and adjustments
value (columns J to Q). The total of the quantity movements should equal the quantity on hand in column G and the
The inventory valuation and movement calculations are based on the "From" and "To" dates that are specified in
cells H2 and H3 respectively. If both of these cells contain valid dates, the inventory valuations are calculated based
on the "To" date that is specified in cell H3, the opening quantities and values include all transactions that are dated
before the "From" date in cell H2 and the inventory movements include all transactions between the "From" and "To"
If only a "From" date is specified and the "To" date cell is left blank, the opening balances include all transactions
before the "From" date and the inventory valuation and movement calculations include all transactions after the
"From" date and on or before the end of the current month (which is determined by the current system date). If only
a "To" date is specified, the opening balances include only the opening quantities and values that are calculated
based on the user input in column D and E and the inventory valuation and movement calculations include all
If both the "From" and "To" date cells are left blank, the opening balances are calculated based on the user input in
columns D and E and the inventory valuation and movement calculations include all the transactions that are
included on the Movements sheet and dated on or before the end of the current month (which is determined by the
Note: As you can see, the functionality that has been added to the StockCodes sheet enables users to display an
inventory valuation and movement report for any user defined date range. Users are therefore not only able to
calculate a current inventory valuation by stock code, but are also able to view the inventory valuations or
Note: All the amount columns on the StockCodes sheet also include a subtotal above the column heading
(formatted in italic) which displays a total for all the stock codes that are visible on the sheet. If the Filter feature is
used to filter the data on this sheet, these calculations will be based only on the filtered data.
Note: The Error Code column will contain an error code if there is a problem with the data that has been entered in
any of the user input columns. If this column therefore contains any error codes, these user input errors need to be
resolved before viewing the data on this sheet. Refer to the Error Codes section of the instructions for guidance on
the different error codes which may be encountered when using this template.
source: excel-skills.com
Previous

3/17

Next