The system maintains two cost prices for inventory items:
| Price | How it Works |
| Last Unit Cost | This is the latest purchase price for the item. The system updates this price when you purchase or manufacture an item. |
| Average Cost | The system uses this cost to take items out of inventory, for example, when you sell an item. The system calculates a new average cost each time you put items into inventory. |
When inventory items are purchased into stock Pastel calculates the average cost as follows:
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
The Value in each case is calculated as the quantity of items multiplied by the cost of the items.
Example 1:
For example, assume that you have no inventory items in stock, and purchase 10 new items at R15 each:
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= R0 + 10 X R15
= R0 + R150
= R150
In the example above there will be 10 items on hand after this transaction, each shown an Average Cost of R15 (R150 divided by 10).
Example 2:
Assume that, following the above purchase, another 5 items are purchased but at R13 each:
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= 10 x R 15 + 5 X R13
= R150 + R65
= R215
After the 2 examples above there will be 15 items on hand after this transaction, each shown an Average Cost of R14.33 (R215 divided by 15).
NOTE: Whenever items are removed from the system (for example, they are sold, or written off via an inventory journal) they are removed from inventory at the current average cost and this cost cannot be changed during this removal.
Example 3:
Assume that of the 7 items above are sold:
Value of Total Inventory = Value of Existing Inventory - Value of Sold Items
= 15 x R 14.33 - 7 X R14.33
= R215 - R100.33
= R114.67
After the example above there will be 8 items on hand after this transaction, each showing an Average Cost of R14.33 (R114.67 divided by 8). THE RESULTING AVERAGE COST HAS NOT CHANGED DURING THIS TRANSACTION.
NOTE: Whenever items are added to the system (for example, they are purchased, or added via an inventory journal) they are added to inventory at the cost specified by the user and the average cost is re-calculated.
Example 4:
Assume that, following the above sale, another 5 items are purchased for stock but cost R18 each.
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= 8 x R 14.33 + 5 X R18 = R114.67 + R90.
= R204.67.
After the examples above there will be 13 items on hand after this transaction, each shown an Average Cost of R15.74 (R204.67 divided by 13).
NOTE: If you allow quantities of inventory items to fall below zero then average costs can be adversely affected. This is usually as a result of items being sold before they are purchased.
Example 5:
Assume that, given our 13 items on hand at R15.74 each, we receive an order for 100 items. We therefore decide to invoice the 100 items and collect another 80 items en route to the customer. The purchase of the 80 items will be captured after the invoicing, sale and delivery.
Value of Total Inventory = Value of Existing Inventory - Value of Sold Items
= 13 x R 15.74 - 100 X R15.74 (assumed by Pastel)
= R204.67 - R1574.00
= R-1369.33
After this example the inventory shows -87 items on hand at R15.74 each. If the 80 items purchased cost R15.74 each this situation would be rectified after a standard capture of the purchase. However let's see what would happen if the 100 items were purchased as more than this:
Example 6:
(purchase is more expensive than R15.74 - say R20.00 each because of the rushed order)
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= -87 x R 15.74 + 80 X R20 = R-1369.33 + R1600.
= R 230.67.
Note that after this example the 3 items left in stock would show an average cost of R76.89 (R230.67 divided by 3). The remaining three items have to 'wear' all the variance in actual purchase price compared to the theoretical price that was used during the sale of the items not in stock.
However, let's see what would happen if the 100 items were purchased as less than the average cost:
Example 6A:
(Purchase is less expensive than R15.74 - say R13.75 each)
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= -87 x R 15.74 + 100 X R13.75 = R-1369.33 + R1375.
= R 5.67.
Note that after this example the 3 items left in stock would show an average cost of just R 1.89 each (R5.67 divided by 3).
In some cases a decrease in purchase price can have a serious affect on the average cost:
Example 6B:
(purchase is less expensive than R15.74 - say R12.00 each because of a bulk order)
Value of Total Inventory = Value of Existing Inventory + Value of Purchased Items
= -87 x R 15.74 + 100 X R12 = R-1369.33 + R1200.
= R -169.33.
Note that after this example the 3 items left in stock would show an average cost of ?R56.44 (?R169.33 divided by 3). Clearly an average cost of less than zero is nonsensical.
NOTE: If Pastel detects that a purchase processed will cause inventory costs to become negative it will simply leave the average cost in an unadjusted state. If this happens then the Inventory Valuation will not balance to the GL Balance Sheet account for inventory on hand at the end of the period and the GL account will have to be manually adjusted.
SOLUTIONS:
Option 1: Disallow negative stock and process purchases before sales
If you use the strict discipline of not allowing quantities to fall below zero you are unlikely to have to make sizeable adjustments to you GL inventory on hand accounts.
Option 2: Disallow negative stock and process GRNs, then sales and then purchases
If you are forced through business circumstances to process sales before purchased items are keyed onto your system, then capture estimates of what each item will cost as Goods Received Notes in Pastel. When the actual purchase is keyed differences between the actual purchase cost and the cost entered on the GRN are posted to a Purchase Variance account, rather than possibly affecting average cost.
Option 3: Allow negative stock, process sales before purchases and process frequent GL adjustments.
If you cannot enter items received into the system prior to their sale (for example in very busy retail environments using Pastel Point-of-Sale) then you should check average costs and total inventory value frequently. To adjust for discrepancies, process GL journal adjustments to balance the GL inventory on hand accounts with the Inventory Valuation reports, at least monthly (you can do this more frequently if required).
Example 7 (after example 1-5 and 6B above):
At the end of the month the Inventory Valuation shows 3 items on hand at R15.74 = R 47.22 Dr, but the GL inventory on hand account shows a credit balance of R169.33. To correct this situation process the following adjustment using a GL journal:
Inventory on Hand Dr R 216.55
Contra: Inventory Adjustment Cr R 216.55
The extra R216.55 credit represents an adjustment to Cost of Goods Sold (COGS) which was previously debited with 100 items at an assumed cost of R15.74 each. In fact the average cost and resulting debit to COGS would have been lower, had the actual purchase at the lower price been captured before the items were invoiced out (since the average cost would have been adjusted to a correct value before the updating of that invoice).