Historical Stock Valuation Report

Use this report to show the value of stock items as at a date in the past. You can produce the report in product, warehouse and category sequence. The report includes information about the in-stock quantity, cost price selling price and value. Options for the report determine whether negative or zero quantity stocks are included. Stock items that have been issued for a works order in the Bill of Materials application, and landed costs can also be included on the report. More about Landed Costs

If a stock item is subject to landed costs, when a stock receipt is posted both the unit cost and the unit landed cost are used to calculate the total cost of the receipt. This applies to all types of stock: Non FIFO, FIFO, and batch and serial traceable. The landed costs that are relevant for the stock item are set up on the Stock Control - Processing - Landed Costs form.

When the stock item is issued to a customer, the total cost (including both the unit cost price and the unit landed cost) is taken into account when margins are calculated. Where these costs are displayed in Opera, for example in these Sales Ledger forms: View, Sales Analysis View, Sales History View and these Sales Order Processing reports: Orders Listing and Quotes and Proformas report, they include both the unit cost and the unit landed cost.

The profit and margin values on the report take the landed costs into account.

See the To Set Up Stock Control for Landed Costs Help topic for details on the steps that need to be followed to set up landed costs.

Valuation Methods

This report calculates the stock value using the First In First Out (FIFO) method where possible. This method uses the transaction prices to calculate a valuation. The FIFO valuation on the report will match the valuation held in the Nominal Ledger for the relevant period in most situations. However, in those situations where an item has slipped into negative stock the valuation on this report may be different.

For items that are linked to Stock Profiles that are not marked to use the FIFO method, the valuation is calculated using the cost prices on the stock item or relevant warehouse. The non FIFO valuation should also match the valuation held in the Nominal Ledger for the relevant period. However, if the cost price on the stock item or relevant warehouse has changed the valuations will be different. This is because the Nominal Ledger is updated using the cost price at the time of receipt, whereas this report uses the cost price on the stock item or relevant warehouse on the Produce Report As At date.

You own a stock item that is valued as FIFO with average costing. The average cost is £10 per item.

On 01/10/2019 you receive 100 into stock at £10 each. The average cost remains at £10. During October you sell and issue 90 of the items. On 01/11/2019 you receive a further 100 items at £11 each. You now have 110 items in stock and the average cost changes automatically to £10.91.

The Nominal Ledger was updated for each receipt and the issues to customers.

Date	Quantity	Cost Price	Value
01/10/2019	100	10	1000.00
01/10/2019 - 31/10/2019	- 90	10	-900.00
01/11/2019	100	11	1100.00
Nominal Ledger valuation			£1,200.00

At the beginning of January 2020 you run the Historical Stock Valuation report as at 31/10/2019. At that date the average cost is £10 each and you had 10 in stock. The report therefore values the stock at 10 * £10 = £100.00.

You also run the report as at 01/11/2019. At that date the you have 10 remaining from the first receipt at a value of £100.00 and 100 from the second receipt at a value of £1,100. The report therefore values the stock at £100.00 + £1,100 = £1,200.00 (the same value as in the Nominal Ledger as at 01/11/2019).

You own a stock item that is valued as Non FIFO with standard costing. The standard cost is £10 per item.

On 01/10/2019 you receive 100 into stock at £10 each. During October you sell and issue 90 of the items. On 01/11/2019 you receive a further 100 items into stock at £11 per item. You now have 110 items in stock.

The Nominal Ledger was updated for each receipt and the issues to customers.

Date	Quantity	Cost Price	Value
01/10/2019	100	10	1000.00
01/10/2019 - 31/10/2019	-90	10	-900.00
01/11/2019	100	11	1100.00
Nominal Ledger valuation			£1,200.00

On 01/12/2019 you change the standard cost of the item to £11.

At the beginning of January 2020 you run the Historical Stock Valuation report as at 01/11/2019. At that date the standard cost is still £10 per item and you had 1100 in stock. The report therefore values the stock at 110 * £10 = £1,100.

You also run the report as at 01/12/2019. At that date the standard cost is £11 per item and you still had 110 in stock. The report therefore values the stock at 110 * £11 = £1,210.00, which is different to the Nominal Ledger.

You own a stock item that is valued as Non FIFO with average costing. The average cost is £10 per item.

On 01/10/2019 you receive 100 of the item into stock at £10 each. The average cost remains at £10. During October you sell and issue 90 of the items. On 01/11/2019 you receive a further 100 items into stock at £11 each. You now have 110 items in stock and the average cost changes automatically to £10.91.

The Nominal Ledger was updated for each receipt and the issues to customers.

Date	Quantity	Cost Price	Value
01/10/2019	100	10	1000.00
01/10/2019 - 31/10/2019	- 90	10	-900.00
01/11/2019	100	11	1100.00
Nominal Ledger valuation			£1,200.00

At the beginning of January 2020 you run the Historical Stock Valuation report as at 31/10/2019. At that date the average cost is £10 per item and you had 10 in stock. The report therefore values the stock at 10 * £10 = £100.00.

You also run the report as at 01/11/2019. At that date the average cost is £10.91 per item and the quantity had increased to 110. The report therefore values the stock at 110 * £10.91 = £1,200.10, which is different to the Nominal Ledger.

This report can be viewed on screen, saved as a PDF document or printed. It can also be saved as an Excel spreadsheet.

Initialising the Historical Stock Valuation

This report is available after the Stock Control - Utilities - Initialise Historical Stock Valuation command has been used, which sets up the necessary database that this report uses.

To Print the Historical Stock Valuation Report

Managing the Historical Stock Valuation Database

A single daily record is added to the historical stock valuation database for each stock item/warehouse. For example, if you hold an item in two warehouses the database will include two records, one for each warehouse. The record is updated for both inward movements (for example, receipts from suppliers) and outward movements (for example, issues to customers).

You can use the Stock Control - Utilities - Period End command to manage the size of the database used for this report. Over time the database will grow so housekeeping is important. The number of records in this database is displayed on the System - Utilities - Data Status Check form.