Stock Valuation Report Explained

The stock valuation report calculates the value of stock using three different tables from the database, the Movement Balance table, Bin Item table and Stock Item table. How these tables are used depends on the costing method of each item and the Movement Balance records in existence at the time of the valuation.

Calculating the “Value” figure on the stock valuation report.

The calculation is made using the Confirmed Movement Balances only (i.e. where the MovementBalance.MovementBalanceTypeID = 0 or 2).

Calculating the “Value (Including Unconfirmed)” figure on the stock valuation report.

Unconfirmed Movement Balances are created when goods are moved in via POP Goods received but the invoice has not yet been received. This calculation takes into account the Confirmed Movement Balance records as described above but in addition it provides an indicative cost for the remaining Unconfirmed goods. (Unconfirmed Movement Balance records will always have 0 in Stock Level Issued.)

How the calculations are carried out.

For each Movement Balance record in existence for the item the system subtracts the MovementBalance.StockLevelIssued from the MovementBalance.OpeningStockLevel to get the quantity to be used in the calculation [NB if this results in a shortfall* - i.e. Stock Level Issued is more than the Opening Stock Level - the result of multiplying by the cost will be a negative figure.] This is then multiplied by the appropriate cost depending on the costing method assigned to the stock item and whether this is a Confirmed or Unconfirmed Movement Balance record.

The actual calculations, by costing method, are as follows:

FIFO:

Confirmed movement balance: MovementBalance.CostPrice x quantity

Unconfirmed movement balance where no invoices have been raised: BinItem.LastCostPrice x quantity

Unconfirmed movement balance where invoices have been raised: StockItem.LastBuyingPrice x quantity

Actual:

Confirmed movement balance: MovementBalance.CostPrice x quantity

Unconfirmed movement balance where no invoices have been raised: BinItem.LastCostPrice x quantity

Unconfirmed movement balance where invoices have been raised: StockItem.LastBuyingPrice x quantity

Standard:

Confirmed movement balance: StockItem.StandardCost x quantity

Unconfirmed movement balance: StockItem.StandardCost x quantity

Average:

Confirmed movement balance: StockItem.AverageBuyingPrice x quantity

Unconfirmed movement balance: StockItem.AverageBuyingPrice x quantity

Please note the Average Buying Price is updated each time a POP invoice is recorded, as is the Last Cost Price. If the Average Buying Price field is 0 then the order price from the POP order is used to populate it, when the goods are received. This field can also be entered manually when the stock item is first set up.

Examples:-

Example – FIFO item

Example – Average Costing item

Example – FIFO item – Unconfirmed stock has been transferred from Bin1 to Bin2

*Shortfalls are created:-

• When unconfirmed stock is issued – e.g. sold through SOP, moved out by stock adjustment.
• When more Confirmed stock is issued than is actually in stock (only applicable if negative stock levels are allowed)

Example – FIFO item – Unconfirmed stock has been sold

*Shortfalls are created:-

• When unconfirmed stock is issued – e.g. sold through SOP, moved out by stock adjustment.
• When more Confirmed stock is issued than is actually in stock (only applicable if negative stock levels are allowed)

Example – FIFO item - Confirmed stock has been sold (negative stock levels are allowed)

This explains why the valuation report can sometimes show a negative amount.

*Shortfalls are created:-

• When unconfirmed stock is issued – e.g. sold through SOP, moved out by stock adjustment.
• When more Confirmed stock is issued than is actually in stock (only applicable if negative stock levels are allowed)