Sage 200 Professional - The Stock Valuation Report Explained in Sage 200

The stock valuation report calculates the value of stock using three different tables from the database, the MovementBalance table, BinItem table and StockItem table. How these tables are used depends on the costing method of the item and the MovementBalance 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 MovementBalance records as described above but in addition it provides an indicative cost for the remaining Unconfirmed goods. (Unconfirmed MovementBalance records will always have 0 in StockLevelIssued.)

How the calculations are carried out.

For each MovementBalance 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. StockLevelIssued is more than the OpeningStockLevel - 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 MovementBalance record.

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

FIFO:
Confirmed movement balance per bin:
MovementBalance.CostPrice x quantity

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

Unconfirmed movement balance per bin where invoices have been raised:
StockItem.LastCostPrice x quantity

 NOTE: When the last cost price of the item is zero, the average buying price is used.

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

[NB – the AverageBuyingPrice is updated each time a POP invoice is recorded, as is the LastCostPrice. If the AverageBuyingPrice 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 1 – FIFO item

Example 1a – Average Costing item

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

Example 3 – FIFO item – Unconfirmed stock has been sold

Example 4 – 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)

Upgrade your licence

Growing business? Add more companies, users, or employees to your licence with ease. Leave your details and we’ll be in touch.