Stock Inventory Free Template Download2 - Free Download | Page 5
4.7, 3287 votes
Please vote for this template if it helps you.
Page 4 of 16
Excel Skills | Inventory Control Template | Usage Based
The previous and new average cost calculations are based on all the stock purchase transactions for the particular
stock code as well as the quantity on hand at the time of purchasing stock. These calculations are only affected by
stock purchase transactions because stock unit costs can only change when the purchase price of new stock items
differ from the current average cost. The previous and new average costs will therefore be the same when recording
The transaction value for stock purchase transactions will equal the invoice total for the particular stock item in
column G. Stock usage and adjustment type transactions are valued based on the current average cost on the date
The purchase price per unit is calculated by dividing the invoice total in column G by the transaction quantity in
column F and this price is deducted from the previous average cost in column M in order to calculate the purchase
price variance in column Q and the purchase price variance percentage in column R. The price variance values in
these two columns can be used to analyse buying trends and movements in the prices that are charged by
Note: Purchase price variances are only applicable to stock purchase transactions. These columns will therefore all
contain nil values when recording stock usage or adjustment type transactions.
The movement date in column S is determined based on the transaction date in column A and the relative row
position of the transaction which is applied to the transaction date as a time value. This means that where two
transactions are entered with the same transaction date, the transaction which is entered first on the Movements
sheet will be deemed to have occurred before any subsequent transactions.
It is therefore important to record transactions in the correct sequence when the transaction dates are the same. For
example, if a stock purchase transaction occurs at the beginning of the day and a usage or adjustment transaction
needs to be recorded on the same day, the inventory purchase transaction should be recorded on the Movements
sheet before recording the usage or adjustment transactions otherwise the average cost which will be calculated for
Average Cost Calculations
All the inventory valuation calculations in this template are based on an average cost calculation methodology.
When you use the average cost basis for inventory valuation purposes, a revaluation of the appropriate stock item is
required after each stock purchase. The revaluation is based on the stock quantity on hand at the time of the
purchase, the average cost of stock on hand, the stock quantity that is purchased and the purchase price per unit
The stock on hand at the time of the purchase is valued at the average cost that was calculated after the previous
stock purchase and the stock quantity that is purchased is valued at the supplier invoice value (excluding sales tax if
applicable) for the particular stock code. This principle is best illustrated through an example.
A company purchases 100 units of a product at a cost of $10.00 per unit. The supplier invoice value is therefore
$1,000. The company then uses 50 units before purchasing another 100 units, this time at $20.00 per unit.
The initial average cost is therefore $10.00 per unit and the stock on hand at the time of the next purchase is valued
at $500.00 (50 units x $10.00 per unit). After purchasing additional stock, there are 150 units in stock at a cost of
$2,500 (the remaining $500.00 plus the new supplier invoice total of $2,000.00). The new average cost is therefore
$16.67 per unit - the stock item in our example should therefore be valued at $16.67 per unit and all stock usage or
adjustment transactions should be recorded at an average cost of $16.67 per unit.
Note: A common misconception of average cost is that it is calculated based on historic purchase prices and that it
is therefore not a very accurate method of inventory valuation. As we illustrated in the above example, this is
actually not the case. Previous stock purchases and the prices at which these stock purchase transactions occurred
only influence the average cost calculation to the extent of the inventory quantities that are still on hand at the time
of the next purchase. Under most circumstances, the inventory on hand is valued at a cost that consists mainly of
the most recent purchase price resulting in a reasonably accurate valuation of inventory and also therefore accurate