Knowledgebase:
Sage 200 - Stock Valuation Report Explained
Posted by Kathryn Smith, Last modified by Kathryn Smith on 10/11/21 14:58

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:-

Stock Item

 

Average Buying Price

4.00

Bin Item

Bin Name

Last Cost Price

Bin1

3.50

Bin2

0







 

Example – FIFO item

Type

Bin

Cost Price

Opening Stock Level

Stock Level Issued

Value

 

Confirmed

Bin1

3.50

100

20

280

100-20 = 80 x Cost Price

Confirmed

Bin1

3.80

50

0

190

50 x Cost Price

Unconfirmed

Bin1

0

100

0

350

100 x Last Cost Price

Unconfirmed

Bin2

0

200

0

800

200 x Average Buying Price (Last Cost Price is 0)

         

------

 
         

1620

Stock Valuation figure
(Including Unconfirmed)

Example – Average Costing item

Type

Bin

Cost Price

Opening Stock Level

Stock Level Issued

Value

 

Confirmed

Bin1

3.50

100

20

280

100-20 = 80 x Cost Price

Confirmed

Bin1

3.80

50

0

190

50 x Cost Price

Unconfirmed

Bin1

0

100

0

400

100 x Average Buying Price

Unconfirmed

Bin2

0

200

0

800

200 x Average Buying Price

         

------

 
         

1670

Stock Valuation figure
(Including Unconfirmed)

 

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

Type

Bin

Cost Price

Opening Stock Level

Stock Level Issued

Value

 

Unconfirmed

Bin1

0

100

0

350

100 x Last Cost Price

Shortfall*

Bin1

4.00

0

20

-80

-20 x Cost Price (shortfall uses the Average Buying Price to populate Cost Price)

Unconfirmed

Bin2

0

200

0

800

200 x Average Buying Price

Unconfirmed

Bin 2

4.00

20

0

80

Created by the transfer - uses the Cost price on the corresponding Shortfall

         

------

 
         

1200

Stock Valuation figure
(including Unconfirmed)

*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

Type

Bin

Cost Price

Opening Stock Level

Stock Level Issued

Value

 

Unconfirmed

Bin1

0

100

0

350

100 x Last Cost Price

Shortfall*

Bin1

4.00

0

30

-120

-30 x Cost Price (shortfall uses the Average Buying Price to populate Cost Price)

Unconfirmed

Bin2

0

200

0

800

200 x Average Buying Price

             
         

------

 
         

1030

Stock Valuation figure

(including Unconfirmed)

*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.

Type

Bin

Cost Price

Opening Stock Level

Stock Level Issued

Value

 

Shortfall*

Bin1

4.00

0

300

-1200

-300 x Cost Price (shortfall uses the Average Buying Price to populate Cost Price)

Confirmed

Bin2

0

200

0

800

200 x Average Buying Price

             
         

------

 
         

-400

Stock Valuation figure

*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)

(0 vote(s))
Helpful
Not helpful

Comments (0)