Retrospective stock valuation report

About this report

If you keep a history of your stock transactions, you can produce a retrospective valuation report. This displays your stock levels and value at the end of a specified date in the past. You specify a Valuation date when you run the report.

This can be useful when checking the value of stock against the nominal ledger.

Note: You must have selected certain options to use a retrospective stock valuation report. Please see the Before you begin section for more information.

How the stock levels and value are calculated

In the following examples, the following stock item and stock transactions are used to explain how the stock is valued retrospectively.

  • You have a stock level of 10 items on 30/07.
  • You run a retrospective stock valuation report for the 10/07.

  • The following stock transactions are dated between the 10/07 and 30/07:

    Date Transaction Type Quantity Cost price Value

    15/07

    In - POP

    2

    8

    		 16

    13/07

    Out - SOP

    5

    12

    		 60

Quantity

This starts with the confirmed stock item 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. level. The retrospective quantity is calculated by reversing the transactions entered for the stock item from the current date back to the specified valuation date.

This shows how the stock quantity is calculated on 30/07 for a retrospective stock valuation report with a valuation date of 10/07:

Date Transaction Type Stock Movement Retrospective calculation Stock Level
30/07       10

15/07

In - POP

2

-2

 8

13/07

Out - SOP

5

+5

 13

The retrospective stock level for 10/07 is 13

Note: Unconfirmed stock item 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. levels and value (where the stock item has been recorded as received but not the purchase invoice) are not included on a retrospective stock valuation report.

Value

This is the value of the confirmed Items from purchase orders or returns where the purchase invoice or credit note has been entered in Sage 200. Or items that have been added at a specified cost. Or items that have been manufactured (using Sage 200 Manufacturing) where the manufacturing cost is known. stock level (Quantity). The retrospective stock value is calculated differently depending on the Costing Method used for each stock item.

  • Standard costing method.

    (Quantity) x (Standard cost price of the item on the Valuation date specified for the report)

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

    Each time the standard cost price of the item is changed on the stock record, the new price and the date of the change are recorded by Sage 200 using the current date on your PC.

    For this example, the standard cost of the item on 10/07 was £8. This was changed on 15/07 to £10. When running the report with a valuation date of 10/07, the retrospective value is the retrospective quantity x the standard cost on the 10/07.

    Date Transaction Type Stock Movement Retrospective calculation Stock Level Stock value
    30/07       10  

    15/07

    In - POP

    2

    -2

    		 8  

    13/07

    Out - SOP

    5

    +5

    		 13 13 x 8

  • Average costing method.

    (Retrospective Quantity) x (Average buying price of the item on the Valuation date specified for the report).

    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.

    For the example item above, the average buying price on 10/07 was £10. On 15/07 this was changed when 2 new items were received for £11.

    When running the report with a valuation date of 10/07, the retrospective value is the retrospective quantity (13) x the average buying price on the 10/07 (£10). The stock valuation is £130.

    Date Transaction Type Stock Movement Retrospective calculation Stock Level Stock value
    30/07       10  

    15/07

    In - POP

    2

    -2

    		 8  

    13/07

    Out - SOP

    5

    +5

    		 13 13 x 10

  • FIFO costing method.

    The retrospective value by taking the starting value of the stock and reversing the transactions entered for the stock item since the specified Valuation date up to the current date.

    In this example, the following transactions are used to explain how the stock value is calculated retrospectively:

    • You have a stock level of 10 items on 30/07, the stock value is £100.

    • The following stock transactions are dated between the 10/07 and 30/07:

      Date Transaction Type Quantity Cost price Value

      15/07

      In - POP

      2

      8

      		 16

      13/07

      Out - SOP

      5

      12

      		 60
    • You run a retrospective stock valuation report for the 10/07.

    • The retrospective value is calculated in the following way:

      Date Transaction Type Stock Movement Cost price Retrospective calculation quantity Retrospective calculation value Stock Level Stock value
      30/07           10 100

      15/07

      In - POP

      2

      		 8

      -2

      		 -16 8

      84

      (100 - 16)

      13/07

      Out - SOP

      5

      		12

      +5

      		 + 60 13

      144

      (84 + 60)

  • Actual costing method.

    The retrospective value by taking the starting value of the stock and reversing the transactions entered for the stock item since the specified Valuation date up to the current date.

    In this example, the following transactions are used to explain how the stock value is calculated retrospectively:

    • On 30/07, you have stock level of 10 items, the stock value is £100.

    • The fallowing stock transactions are dated between 10/07 and 30/07.

      Date Transaction Type Quantity Cost price Value

      15/07

      In - POP

      2

      8

      		 16

      13/07

      Out - SOP

      5

      12

      		 60
    • You run a retrospective stock valuation report for the 10/07.

    • The retrospective value is calculated in the following way:

      Date Transaction Type Stock Movement Cost price Retrospective calculation quantity Retrospective calculation value Stock Level Stock value
      30/07           10 100

      15/07

      In - POP

      2

      		 8

      -2

      		 -16 8

      84

      (100 - 16)

      13/07

      Out - SOP

      5

      		12

      +5

      		 + 60 13

      144

      (84 +60)

Before you begin

  • You must have selected Keep movement history on the Product Group Details tab for all the stock items you want to include on the report.
  • You must make sure your have not deleted your stock transaction history for the date you want to run the report.

Run the retrospective stock 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. Enter the date in the past that you want to run the report for, as the Valuation date.

    The stock levels and value are calculated as at the end of the day specified.

  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?

Current stock valuation report

Reference

Product group details

Delete archived stock history