Current stock valuation report

About this report

The current stock valuation report displays your stock item levels and value at the time the report is generated.

The stock levels and value for the current stock valuation report are calculated for both confirmed stock items Confirmed stock items are where the cost of an item is already known and recorded. This occurs in the following circumstances: stock items have been bought and the supplier's invoice had been recorded in Sage 200; stock items have been returned to a supplier and the credit note has been processed in Sage 200; stock items have been added in Sage 200 at a specified cost; a work order has been entered (using Sage 200 Manufacturing) and the manufacturing cost is known. and unconfirmed stock items Unconfirmed stock items are where goods have been received, but the actual cost is not known. This occurs when: stock items from purchase orders have been entered as received, but the supplier's invoice has not been processed in Sage 200. Stock items from purchase returns have been entered as despatched, but the credit note has not been processed in Sage 200, or where the manufacturing process is finished (using Sage 200 Manufacturing) but not all the costs are known..

The stock levels and value are displayed on the report as follows:

  • Quantity.

    This the confirmed stock level for the item.

  • Quantity Including Unconfirmed.

    This is the confirmed stock level (Quantity) for the item, plus the unconfirmed stock level for the item.

  • Value.

    This is the value of the confirmed stock level (Quantity).

  • Value Including Unconfirmed.

    This is the value of the confirmed and unconfirmed stock level (Quantity Including Unconfirmed).

How the stock values are calculated

The value of a stock item is calculated differently depending on the Costing Method used for each stock item.

  • Standard costing method.

    (Stock level) x (Standard cost price)

    This uses the Standard cost price, on the stock item's record, to calculate the value of both confirmed and unconfirmed goods.

    The Standard cost price is displayed on the report as the Valuation price.

    In this example, the standard cost price of an item is £10. You start with five of this in stock on 01/07. The purchase invoice for them has been entered.

    Date   Stock level Standard Cost Stock valuation
    01/07 5 confirmed 5

    10

    		 50

    You purchase a further three items on the 15/07 and record the invoice.

    Date   Stock level Standard Cost Stock valuation
    15/07 Add 3 confirmed 8

    10

    		 80

    You order and receive a further four items on the 20/07. This time you have not yet received the invoice and the standard cost has been reduced to £8.

    When you run the stock valuation report now the new standard cost price (£8) is used to value all the items.

    Date   Stock level Standard Cost Stock valuation
    20/07 Confirmed 8

    8

    		 64
      Including Unconfirmed 12 8 96

  • Average costing method

    (Stock level) x (Average buying price)

    This uses the average buying price on the stock item's record to calculate the value of both confirmed and unconfirmed goods.

    Each time a purchase invoice or an addition of stock is recorded for an item, the average buying price is updated and recorded by Sage 200 using the current system date on your PC.

    The Average buying price is displayed on the report as the Valuation price.

    In this example, you start with 5 items in stock with an average buying price of £10.

    Date   Stock level Average Buying Price Stock valuation
    01/07 Confirmed 5

    10

    		 50

    You then order and receive an additional 5 items on 15/07 at a price of £12.

    The new average buying price is calculated as £11 ((5 x 10) +(5 x 12) / 10). The invoice is entered.

    Date   Stock level Average Buying Price Stock valuation
    15/07 Confirmed 10

    11

    		 110

    You then order and receive a further five items at a price of £12 on 20/07, but the invoice has not been entered. The average buying price has not been updated. Their value is calculated using the last Average buying price(£11).

    Date   Stock level Average Buying Price Stock valuation
    20/07 Confirmed 10

    11

    		 110
      Including Unconfirmed 15 11 165

  • FIFO costing method

    This uses a different cost price depending on whether the goods are confirmed or unconfirmed.

    Confirmed goods

    (Confirmed stock level per bin) x (the cost price of each item)

    Unconfirmed goods

    (Unconfirmed stock level per bin) x (the last cost price of the item, per bin)

    The Last cost price is the cost price that was recorded the last time a purchase invoice or an addition of stock was entered for the item in that bin.

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

    In this example, the last cost price of an item in Bin 1 is different to the last cost price of the same item Bin 2.

    You start with five items in Bin 1 and three items in Bin 2.

    Date   Bin Stock level Cost Price Stock valuation
    01/07 Confirmed 1 5

    10

    		 50
      Confirmed 2 3 8 24
      Total Confirmed   8   74

    On 15/07 you order and receive five items for Bin 1. The cost price is £11 and the purchase invoice is entered.

    Date   Bin Stock level Cost Price Stock valuation
    15/07 Confirmed 1 5

    10

    		 50
      Confirmed 2 3 8 24
      Confirmed 1 5 11 55
      Total Confirmed   13   129

    On 20/07, you order and receive three items for Bin 2. The cost price is £9 but the invoice has not been entered. The last cost price (£8) is used to value these items.

    Date   Bin Stock level Cost Price Stock valuation
    20/07 Confirmed 1 5

    10

    		 50
      Confirmed 2 3 8 24
      Confirmed 1 5 11 55
      Unconfirmed 2 3 8 (last cost price) 24
      Total Confirmed   13   129
      Total including Unconfirmed   16   153

  • Actual costing method (traceable stock items only).

    This uses a different cost price depending on whether the goods are confirmed or unconfirmed.

    For confirmed items the actual cost price is recorded against each batch or serial number.

    Confirmed items

    		(Confirmed stock level per bin) x (the cost price of each item)

    Unconfirmed items

    (Unconfirmed stock level per bin) x (the last cost price of the item per bin)

    The Last cost price is the cost price that was recorded the last time a purchase invoice or an addition of stock was entered for the item in that bin.

    In this example, a serial numbered item is being valued. The actual cost of each item is recorded. On the 01/07 you start with five items in Bin 1 and 3 items in Bin 2.

    Date   Bin Serial number Stock level Cost Price Stock valuation
    01/07 Confirmed 1 1 - 5 5

    10

    		 50
      Confirmed 2 6 - 8 3 8 24
      Total Confirmed     8   74

    By the 15/07 you have sold 2 items, one from Bin 1 and one from Bin 2.

    Date   Bin Serial number Stock level Cost Price Stock valuation
    15/07 Confirmed 1 2 - 5 4

    10

    		 40
      Confirmed 2 7 - 8 2 8 16
      Total Confirmed     8   56

    By the 20/07, you ordered and received five items for Bin 2 at a cost price of £11. You have entered the invoice for these.

    You have received an additional three items for bin 2 at £10, but have not entered the invoice. The last cost price of £11 is used to value these items.

    Date   Bin Serial number Stock level Cost Price Stock valuation
    20/07 Confirmed 1 2 - 5 4

    10

    		 40
      Confirmed 2 7 - 8 2 8 16
      Confirmed 2 9 -13 5 11 55
      Unconfirmed 2 14-16 3 11 (last price) 33
      Total Confirmed     11   111
      Total including Unconfirmed     14   143

Run the current valuation report

Open: Stock Control > Reports > Status > Valuation.

  1. Use the criteria to select the stock items to include on the report.

    You can select by:

    • Stock Item
    • Stock items in a warehouse
    • Product Group
    • Stock item search value
    • Stock item costing method
  2. To run the current stock valuation report, accept the current date as the Valuation date.
  3. To produce the report, click OK.

Common questions

Current stock valuation is different to retrospective valuation

If I run a current stock valuation report and then run the retrospective report for the same day several days later, why are the values different?

This can be due to the following:

  • If the current stock valuation report includes the value of both confirmed and unconfirmed stock items.

    This is because the retrospective valuation report only contains the value of confirmed stock items.

    This will occur if your current valuation report was produced using a version of Sage 200 prior to v2010. In earlier versions of Sage 200, the quantities of both confirmed and unconfirmed stock items were used to calculate the value of your stock.

  • If you have revalued your stock, for stock items using the FIFO and Actual costing methods.

  • If you have recorded stock items in your warehouse as sold before you have recorded the same stock items as received.

    This is because the current stock valuation report uses the last cost price of the item. If you subsequently record an invoice for received goods at a different cost price, the last cost price can be updated, subject to your POP Order Processing settings. When you produce the report retrospectively, the newer cost price is used to calculate the value of your stock.

    You have 20 of tins of paint in your warehouse, but you have not recorded the receipt. Your records in Sage 200 show you currently have no stock. The last cost price of the stock was £1. The current stock valuation, therefore, is £0.

    1. 15/07 - you sell 10 tins. The current stock valuation report shows you have -10 in stock at a value of -£10. This is because the last cost price was £1.

    2. On 30/08, you produce a stock valuation report retrospectively for the 15/07. This shows your stock level as -10 with a value of -£20.

    3. 18/07 - you record the receipt and the invoice for 20 tins of paint. The cost price is £2. The current stock valuation report shows you have stock level of 10 with a value of £20.

      Date

      Transaction type

      Stock movement

      Cost price

      Stock level

      Stock value

      30/07     1 0 0
      15/07 Out - SOP - 10 1 - 10 -10
      18/07 In - POP +20 2 10 30

      The retrospective stock valuation run on 18/07 with a valuation date of 15/07 would show a stock level of -10 but a stock value of -20, as the last cost price was updated to £2.

  • If the standard cost price on a stock item is changed more than once in the same day, and the current valuation report was produced before the final price change.

    The standard cost of a box of wine is £10. A current stock valuation report is produced. The stock is valued at £10 per box. Later that day the standard cost of the item is changed to £12.

    When a retrospective stock valuation report is produced for the same day, the stock is valued at £12 per box.

  • If transactions have been backdated.

    You have 100 chairs in stock. They use a FIFO costing method.

    1. 30/07 - You produce a current valuation report. This shows a stock level of 100 chairs.
    2. 01/08 - You record the despatch of 10 chairs, dated 28/07.
    3. 01/08 - you produce another current valuation report. This shows a stock level of 90 chairs.

    4. 01/08 - you produce a retrospective report with a valuation date of 30/07. This shows a stock level of 90 chairs.

      The retrospective report includes the 10 chairs that were issued because the transaction date is prior to the Valuation date for the report.

  • If you have chosen to Update stock quantities when the invoice is posted in the SOP settings Processing tab.

    This affects the valuation report in the same way as backdating transactions.

Stock items a have a negative value on the stock valuation report

You'll get a negative value for your stock in two different circumstances:

  • You've chosen to allow negative levels for some stock items (this is set at product group level and is applied by default to all stock items in that group). This allows you to allocate and sell stock on a sales order, even if there isn't enough stock recorded in your warehouse.
  • You have Shortfalls - you've sold stock you've not been invoiced for. This happens when you raise a purchase order and record the goods as received. The stock is classed as Unconfirmed because you haven't received the invoice. You physically have the items so you can still sell them. The items are allocated to sales order(s) and recorded as despatched. This means that your Confirmed stock level is negative, also displaying as negative on the Stock Valuation report.

You have no items in stock. You add 10 to a purchase order and you receive those 10 items. This means that you have 10 items in your warehouse. Your stock valuation will show 10 unconfirmed items and 0 confirmed, as you haven't yet received the invoice from your supplier. The 10 unconfirmed items are valued using an estimated / average price (see how these prices are calculated).

You then sell these 10 items and despatch them to your customer. This is fine because they are physically present in your warehouse. So you now have 0 items in your warehouse. You still haven't received the invoice from the supplier, so you actually have 10 unconfirmed items and -10 confirmed items. When you run the stock valuation report your value including unconfirmed will be 0 but your confirmed value will be negative. When receive the invoice from your supplier the stock becomes confirmed, the shortfall is fulfilled and the values are updated accordingly.

Stock items not showing on the stock valuation report

When I produce a retrospective stock valuation report, why are some items not shown on the report?

This can be due to the following:

  • The stock item has stock level of zero for the date specified on the report. Stock items with a level of zero are excluded from the report.

  • The stock item has no cost or transaction history for the valuation date specified for the report.

    If this is the case the following message is printed at the end of the report: Stock items will be excluded from this report if no cost or transaction history is available for the selected date.

    This occurs if you have backdated your transactions or have deleted the archived stock history for that item.

    Sage 200 uses the current system date to record changes made to the standard and average cost prices of stock items. If you run a retrospective stock valuation report with valuation date prior to the current system date, Sage 200 does not have any record of the cost price for the stock item. In the following example the current system date is 30/07.

    • 30/07 - You create a new stock item.
    • 30/07 - You enter a purchase order for the item at dated 01/07.
    • 30/07 - You record the receipt of the goods dated 05/07.
    • 30/07 - You run a current valuation report (As a t 30/07). This shows the quantity and value of the stock.
    • 30/07 - You run a retrospective stock valuation) report with a valuation date of 15/07. The stock item does not appear on the report.

    This is because the cost price of the item was recorded on the 30/07 and Sage 200 has no record of a cost price prior to this.

Back dated transactions and stock valuation

I have entered back dated transactions. How does this affect the retrospective stock valuation) report.

  • The retrospective stock valuation report will be different to a current stock valuation report run for the same date.

    This example shows how the stock level is affected when transactions are backdated:

    You have 100 chairs in stock.

    1. 30/07 - You produce a current valuation report. This shows a stock level of 100 chairs.
    2. 01/08 - You record the despatch of 10 chairs, dated 28/07.
    3. 01/08 - you produce another current valuation report. This shows a stock level of 90 chairs.

    4. 01/08 - you produce a retrospective report with a valuation date of 30/07. This shows a stock level of 90 chairs. This is not the same as the current valuation report run in step 1 because you have added a back dated transaction.

      The retrospective report includes the 10 chairs that were issued because the transaction date is prior to the Valuation date for the report.

  • Stock items may not appear on the report or may be valued using a cost price different to that expected.

    Changes to the average and standard cost price of items are recorded by Sage 200 using the current system date, not the transaction date.

    If you run a retrospective stock valuation report for a date before the change to the cost price was recorded (the system date that the transactions was entered), the previous cost price of the item will be used. If no cost price was recorded, the item will not appear on the report.

    Sage 200 uses the current system date to record changes made to the standard and average cost prices of stock items. If you run a retrospective stock valuation report with valuation date prior to the current system date, Sage 200 does not have any record of the cost price for the stock item:

    • 30/07 - You create a new stock item.
    • 30/07 - You enter a purchase order for the item at dated 01/07.
    • 30/07 - You record the receipt of the goods dated 05/07.
    • 30/07 - You run a current valuation report (as at 30/07). This shows the quantity and value of the stock.
    • 30/07 - You run a retrospective stock valuation report with a valuation date of 15/07. The stock item does not appear on the report.

    This is because the cost price of the item was recorded on the 30/07 and Sage 200 has no record of a cost price prior to this.

How do I ensure accurate stock valuation for projects?

If you use Project Accounting, we recommend using the following settings to ensure accuracy in your stock valuation reports.

  • Use traceable stock items, with batch/serial numbers. The product group should be set to use Items use batch or serial numbers.

  • Use the Actual costing method for the product group (only available if the product group allows batch/serial numbers).

  • Use the setting to Allow prices to be updated when goods are received, in POP Settings on the Order Processing tab.

    Note: This is only recommended if the goods price is not expected to differ from the order price.

Using these settings will ensure that the actual cost of the item will be recorded for the project.

Can I modify the stock valuation report to use different criteria?

We recommend that you do not modify the Stock Valuation report, to include in and between on the Warehouse criteria. Doing so may produce incorrect figures, so you should only use is with the Warehouse criteria.

We also advise not to create a new report that includes these criteria.

See also

What do you want to do?

Retrospective stock valuation report

Reference

Product group details