Home > Inventory > Stock Control > Concepts > Stock Movements

Stock Movements

When inventory is sold or received or transferred, it has an impact on average cost and stock levels. This document will explain the impact of the various transactions. First of all, we need to understand the important numbers in the system.

For each item at each location, the system keeps a set of stock numbers. (These numbers can be seen in the Range tab in Items.)

Stock On Hand

The current stock on hand of the item.

On Order

The number of units that are currently on order. An item can be ordered with a purchase order for a supplier or a transfer request for another store.

Allocated

The quantity of stock that has been allocated for customer orders, laybys and cash on delivery transactions. These units are also counted in the Stock On Hand because you still have the actual stock until final payment is received and the item is released to the customer.

Returned

The number of units that are to be returned to a supplier (damaged goods, and so on). These units are also counted in the Stock On Hand because you still have the actual stock until the goods have been delivered to the supplier.

In Transit

The number of units in transit to another store as a result of a transfer. These units are also counted in the Stock On Hand because you still have the actual stock until the goods have been confirmed as received .

Requested

The quantity of stock that has been requested from this location. If Location A requests 5 units of stock from Location B, Location B will have a Requested quantity of 5.

Average Cost

The average cost of the items currently in stock. If you are using the FIFO method, the average cost is still maintained and it will be the average of those items. So for valuation purposes you can multiple the stock on hand by average cost. This is valid for Average Cost or FIFO.

Average Rebates

The average of the expected rebates (as defined with supplier and item trading terms). This is normal updated when an invoice is accepted and we can calculate the expected rebates. As stock is transferred and adjusted, the average rebates is adjusted just like average cost is.

Like Average Cost, the Average Rebates also works for FIFO.

Average Cost

Average Cost is the average of the items currently in stock. As stock is adjusted, the average is calculated using this formula:

((Current SOH x Current Average Cost) + (New Stock x New Cost)) / (Current SOH + New Stock)

When stock goes below zero or the stock goes from negative to positive, the average cost is simply set to the new cost. Although this is not strictly following the average cost formula, it does provide a number which makes more sense to the average retailer. If documents are entered as soon as they are received, this situation should not occur.

The average cost is always recalculated, even if stock is decremented. When stock is decremented the 'new cost' of the above formula is usually the same as the 'current average cost' so there is no change to the actual average cost. However, to keep everything in sync internally, the average cost is still recalculated.

FIFO

FIFO (First In, First Out) is another costing method used by the system. In this case, the system records a batch each time stock is increased. This batch will contain the total number of units, the units currently available and the cost of the item. As stock is decreased, the system will look at the available batches, oldest to newest.

Example:

I receive 5 units on Monday and a further 8 units on Tuesday. This will give me two batches:

Original

Current

Cost

5  

5  

$50

8  

8  

$55

If I sell 6 units my batches will change as follow:

Original

Current

Cost

5  

0  

$50

8  

7  

$55

FIFO also allows serial number tracking to be used. You can choose various options for each item. You might choose to not track serial numbers at all (in which case FIFO is simply used to track costs) or you might choose to track individual products. In all cases, the system will create these batch records. If I am tracking serial numbers for an item and I receive five units, the system still only creates one batch record, as above, and attaches the serial numbers to that batch.

Transactions

We will now examine the impact of various transactions on these stock numbers. The tables below show examples of the transactions. The Average Cost column shows the average cost recorded in the system when using the Average Cost method. The Average Cost (FIFO) shows the average cost recorded in the system when using the FIFO method. When stock is being increased the two columns will generally show the same number. But when the stock is decreased you can see the subtle difference in the calculation.

When stock is being decreased we can use the average cost or the FIFO as the value of the item. However, when we increase stock then the cost we use will depend on the transaction. If we are receiving the goods via a goods receipt, we cost the item at the expected cost, as per the original purchase order. However, if we are doing a stock adjustment, the item has no definite cost associated with it. All we have said is we have an extra two units. In this case we do a best cost calculation for the transaction date and use that cost. We cost the item based on what we would have to pay for a new item.

Purchase Order/Receiving

When a purchase order is accepted, the on order quantity is increased.

If a purchase order is cancelled, the on order quantity is decreased.

When stock is received (with a goods receipt or an invoice), the on order is decreased and the stock is increased. The cost used for the item is the cost from the original purchase order.

Action  

SOH

On Order

Allocated

In Transit

Requested

Returned

Average Cost  

Average Cost (FIFO)

FIFO

Comment

Current

10

0

0

0

0

0

$12

$12

1 batch of 10 units @ $12.

 

Order 10 units @ $15

10

10  (+10)

0

0

0

0

$12

$12

 

 

Received 3 units with status of 'More To Come'

13 (+3)

7 (-3)

0

0

0

0

$12.69

$12.69

Create one new batch for 3 units @ $15

The average cost is changed by the expected cost on the purchase order.

Receive 6 units with status 'Received'. We are 1 unit short on the original order

19 (+6)

0 (-7)

0

0

0

0

$13.42

$13.42

Create one new batch for 6 units @ $15.

The average cost is changed by the expected cost on the purchase order.

Order 6 units @ $14.

19

6 (+6)

0

0

0

0

$13.42

$13.42

 

 

Cancel the order of 6 units

19

0 (-6)

0

0

0

0

$13.42

$13.42

 

 

When stock needs to be returned to the supplier, a Return Request is raised (basically a negative purchase order). When the supplier authorises the request, an Authorised Return transaction is entered (basically a negative goods receipt).

Action  

SOH

On Order

Allocated

In Transit

Returned

Average Cost

Average Cost (FIFO)

FIFO

Comment

Current

10

0

0

0

0

$12

$12

1 batch of 10 units @ $12.

 

Request to return 3 units

10

0

0

0

3

$12

$12

 

 

Actual send the 3 units back to the supplier

7 (-3)

0

0

0

0 (-3)

$12

$12

1 batch of 7 units @ $12.

The average cost is changed by the expected cost on the purchase order.

Receiving Stock Without A Purchase Order

If a goods receipt or invoice is created and it is not matched to a purchase order, the stock on hand will be increased at the expected cost (using the best cost calculations). However, since it is not matched to a purchase order, the on order quantity is NOT adjusted.

Invoicing

The main purpose of the invoice is to say you were charged $x for the items. Ideally, this should be the same value as the original purpose order. However, if the cost is different the system needs to make an cost adjustment to the received items. If you receive stock using goods receipt, the invoice function will only ever record a cost adjustment (if one is required). If you receive stock using invoicing, the system makes two entries - one for the received stock (equivalent to the one that would have been created in goods receipt) and a cost adjustment.

Action  

SOH

On Order

Allocated

In Transit

Requested

Returned

Average Cost 

Average Cost (FIFO)

FIFO

Comment

Current

10

0

0

0

0

0

$12

$12

1 batch of 10 units @ $12.

 

Order 10 units @ $15

10

10  (+10)

0

0

0

0

$12

$12

 

 

Received 10 units with status of 'Received'

20 (+10)

0(-10)

0

0

0

0

$13.50

$13.50

Create one new batch for 10 units @ $15

The average cost is changed by the expected cost on the purchase order.

Invoiced 10 units @ $16

20

0

0

0

0

0

$14

$14

Update batch cost to be $16

No stock adjustments are made but the average cost is adjusted.

Difficulties arise when you are invoiced a different quantity to what was received or you have already sold stock that needed a cost adjustment.

If you are invoiced a different quantity the cost is adjusted by the value of the

If the total new stock on hand is less than zero or the stock on hand goes from negative to positive, the average cost stays the same as the current average cost. No adjustment is made. If the stock is positive then the average cost is adjusted as follows:

(Current SOH x Current Avg Cost) + (Adjust Quantity x Adjust Cost)) / Current SOH.

Note that we divide only by the Current SOH. We do not include the Adjust Quantity here because the actual units of stock has not changed. Only the value of those units has changed. The value for the Adjust Quantity depends on the invoice itself. If we received stock using the invoice, the adjust quantity is the quantity received. If we received stock using goods receipt, the adjust quantity will be the invoiced quantity.

If we received 10 units on a goods receipt and sold 4 units, when the invoice for the 10 units is made, the value is adjusted for the full 10 units. So the value of those remaining 6 items is increased more than originally required to compensate for the costs that should have been on those 4 units sold.

If we are using FIFO then we need to make an adjustment to an existing batch. We try to find the most appropriate batch. We look for batches in the following order.

  1. Batch created for stock received on this invoice.

  2. Batch created for stock received on a goods receipt for the PO this invoice matches.

  3. Batch created for stock received on an earlier invoice for the PO this invoice matches.

  4. Any batch we can find.

If we have to make an adjustment for 10 units and we can only find batches with a total of 8 units, we have a 2 unit shortage. This means we have not adjusted the stock batches by the full value. At the moment this value is not recorded anywhere. It should be recorded to allow an adjustment to the accounts. This will be addressed in a future modification

POS Transactions

For a standard sale, we need to decrease stock on hand.

When we do a pending transaction, like a customer order or layby, we need to allocate the stock.

If we do a return we need to increase stock on hand and make cost adjustments.

Action  

SOH  

On Order

Allocated

In Transit

Requested

Returned

Average Cost

Average Cost (FIFO)

FIFO

Comment

Current

19

0

0

0

0

0

$13.42

$13.42

1 batch of 10 units @ $12.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

 

Sold 2 units

17 (-2)

0

0

0

0

0

$13.42

$13.58

1 batch of 8 units @ $12.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

Note the change to FIFO average cost.

Customer order for 3 units

17

0

3 (+3)

0

0

0

$13.42

$13.58

 

 

Completed customer order of 3 units

14 (-3)

0

0 (-3)

0

0

0

$13.42

$13.92

1 batch of 5 units @ $12.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

 

Customer order for 2 units

14

0

2 (+2)

0

0

0

$13.42

$13.92

 

 

Cancel customer order

14

0

0 (-2)

0

0

0

$13.42

$13.92

 

 

Return 1 unit

15 (+1)

0

0

0

0

0

$13.53

$13.99

Create new batch of 1 unit @ $15

The cost of the item will be the expected cost (using best cost calculation). In this case $15.

Stock Adjustments/Stocktakes

Stock adjustments and stocktakes work in a similar way. When we accept a stocktake, we do not simply overwrite the current stock on hand with the actual counted. We actually do an adjustment which is the difference between the expected count and the actual count.

Action  

SOH

On Order

Allocated

In Transit

Requested

Returned

Average Cost  

Average Cost (FIFO)

FIFO

Comment

Current

19

0

0

0

0

0

$13.42

$13.42

1 batch of 10 units @ $12.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

 

Decrease by 2 units

17 (-2)

0

0

0

0

0

$13.42

$13.58

1 batch of 8 units @ $12.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

Note, the change to FIFO average cost.

Increase by 3 units

20 (+3)

0

0

0

0

0

$13.66

$13.79

Create 1 batch of 3 units @ $15

The cost of the item will be the expected cost (using best cost calculation). In this case $15. 

Internal Transfers

An internal transfer moves stock from one location to another. When stock is increased at the 'to' location, the cost of the item is its cost at the 'from' location. for example item 1 has an average cost of $50, so the average cost of item 2 will be changed by $50.

An internal transfer can also move stock from one item to another. It does not have to be the same item but the two items must have a compatible unit of measure. In this case the adjustments are done the same way, but the cost of the 'to' item takes into account the ratio between the two items. So if item 1 is 700ML and item 2 is a NIP, the ratio is 23.33 (700mls/30mls). If we transferred from one unit of item 1 @ $50, we would receive 23.33 units of item 2 @  $2.14. The nett value of item 1 was moved to item 2, just at a different ratio.

The following example shows the transfer between locations A and B.

Action  

SOH

On Order

Allocated

In Transit

Requested

Returned

Average Cost

Average Cost (FIFO)

FIFO

Comment

Current

A.10
B.5

A.0
B.0

A.0
B.0

A.0
B.0

0

A.0
B.0

 

A.$10
B.$13

A.$10
B.$13

A. 1 batch of 10 units @ $10.
B. 1 batch of 5 units @ $13.

 

Transfer 2 units from A to B

A. 8 (-2)
B. 7 (+2)

A.0
B.0

A.0
B.0

A.0
B.0

0

A.0
B.0

A.$10
B.$13.12

A.$10
A.$13.12

A. 1 batch of 8 units @ $10.

B. 1 batch of 5 units @ $13.

Create new batch of 2 units @ $13.42.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

The cost of the new batch for B was the cost from A.

Transfer Request/Sends

The transfer request/send process is a three-step action. The original request will increase the on order quantity for the from location and increase the requested quantity for the sending location. The transfer send will increase the in transit quantity and decrease the requested quantity for the sending location. When the stock is received, the on order at the from location will decrease and the stock on hand will increase.

The in transit at the sending location will decrease and the stock on hand will decrease. Note the in transit for the sending store is not actually changed until the requesting location receives the stock. However, the requested quantity is decreased immediately the sending location sends the stock.

If stock is not fully received at the from location, the system will generate a stock adjustment for the missing stock. So the stock on hand at the sending location will still go down by the full quantity sent.

The following example shows the transfer between locations A and B.

Action  

SOH

On Order

Allocated

In Transit

Requested

Returned

Average Cost

Average Cost (FIFO)

FIFO

Comment

Current

A.10
B.5

A.0
B.0

A.0
B.0

A.0
B.0

A.0
B.0

A.0
B.0

 

A.$10
B.$13

A.$10
B.$13

A. 1 batch of 10 units @ $10.
B. 1 batch of 5 units @ $13.

 

B requests 2 units from A.

A.10
B.5

A.0
B.2 (+2)

A.0
B.0

A.0
B.0

A.2 (+2)
B.0

A.0
B.0

A.$10
B.$13

A.$10
A.$13

 

On order for B has increased

A sends 2 units to B

A.10
B.5

A.0
B.2

A.0
B.0

A.2 (+2)
B.0

A.0 (-2)
B.0

A.0
B.0

A.$10
B.$13

A.$10
A.$13

 

In transit for A has been increased.

B receives 2 units

A.8 (-2)
B.7 (+2)

A.0
B.0 (-2)

A.0
B.0

A.0 (-2)
B.0

A.0
B.0

A.0
B.0

A.$10
B.$13.12

A.$10
B.$13.12

A. 1 batch of 8 units @ $10.

B. 1 batch of 5 units @ $13.

Create new batch of 2 units @ $13.42.
1 batch of 3 units @ $15.
1 batch of 6 units @ $15.

Now stock is actual adjusted at both locations. The cost of the new batch for B was the cost from A.

Converted from CHM to HTML with chm2web Pro 2.85 (unicode)