How Sage Accounts calculates its Cost Price

Every stock transaction in Sage Accounts has a cost price, even if it is zero. When you enter an Adjustment In (AI) or Goods In (GI) the cost price used is the last purchase price, this can be amended manually. However, when you enter an AO or GO transaction the cost price is calculated using FIFO.
For example, if the following AI transactions were entered:

And the following AO transactions were entered:

The cost price for the first AO transaction is £/€100.00 as it has taken five items out of stock from the first AI transaction. The cost price for the second AO transaction is £/€75, as it has taken five items out of stock from the first AI transaction at £/€100 per item, and then five items out of stock from the second AI transaction at £/€50 per item. Sage Accounts then averages the total cost by the quantity to give the price per unit for the AO transaction.

The same example applies to GI and GO transactions.

Reasons why the report may show incorrect figures, negative figures or a value with zero quantity

There are several reasons why the figures on the Retrospective Product Valuation may appear to be incorrect. These are as follows:

• The report has been run for an incorrect date range.
• Rounding of small unit prices.
• Allow Negative Stock is selected and the product has gone into negative stock.
• The Clear Stock option has been run.
• One or more products have no product category.
• The report excludes Damages In transactions but not Damages Out transactions.
• Incorrect or no cost price on a stock transaction.
• Stock has been transferred in, then stock has been transferred out with a transaction date before the transfer in.

The report has been run for an incorrect date range

The Retrospective Product Valuation calculates its figures based on the stock transactions between the dates entered into the criteria when the report is run. To allow the report to calculate correctly you must enter the Stock Tran Date from as 01/01/1980. This is because the report needs all of the transactions prior to the to Stock Tran Date to so that it can calculate the correct values. The following example shows how running the date for an incorrect range can cause the figures to appear incorrectly:

AI dated 01/01/07

AI dated 02/01/07

After these transactions are entered, the Product Activity appears as follows:

If the Retrospective Product Valuation is run from Stock Tran Date from 02/01/07 to 31/12/2050 it shows:

This is incorrect as the actual value based on the activity is 2000.00, the report has missed the first Adjustment In as it is excluded by the criteria. To allow the report to calculate correctly you must enter the Stock Tran Date from as 01/01/1980 to the relevant date.

Rounding of small unit prices

Rounding of small unit prices can make the figures on the Retrospective Product Valuation incorrect. The following example shows how rounding can affect the Retrospective Product Valuation report:

For the purpose of this example the report should be run for Stock Tran Date from 01/01/1980 to 31/12/2050.

AI dated 01/01/07

AI dated 02/01/07

AO dated 03/01/07

If the Retrospective Product Valuation is run at this point it shows the following:

This is because as default Sage Accounts uses 2 decimal places (D.P.) for the unit price. For this AO Sage Accounts calculates the cost price as £/€1.26 as it has rounded down to 2 D.P. the real cost price for these items is 1.26365. To avoid this you need to increase the default price decimal precision in the software. To do this:

1. Click Settings, then click Product Defaults.
2. Change the Price D.P. then click OK.
   

Allow Negative Stock is selected and the product has gone into negative stock

In Sage Accounts you can choose to allow your quantity in stock to become negative. If you allow negative stock this can cause the figures on the Retrospective Product Valuation to appear incorrectly, this is due to the cost prices used for GO transactions.

When the product goes into negative stock Sage Accounts uses the last purchase price as the cost price for a GO. This is because FIFO cannot be used as there are no items in stock. If you then enter an AI to bring the item back into stock with a different cost price, this causes the report to show a negative value when there is a quantity on the report. The following example demonstrates the figures the Retrospective Product Valuation would show after certain transactions are entered:

For the purpose of this example the report should be run for Stock Tran Date from 01/01/1980 to 31/12/2050.

AI dated 01/01/07

If the Retrospective Product Valuation is run at this point it shows the following:

GO dated 02/01/07

If the Retrospective Product Valuation is run at this point it shows the following:

GO dated 03/01/07

If the Retrospective Product Valuation is run at this point it shows the following:

AI dated 04/01/07

If the Retrospective Product Valuation is run at this point it shows the following:

This shows how if negative stock has been allowed the Retrospective Product Valuation can show incorrect figures due to the Product Activity.

The Clear Stock option has been run

When you run the Clear Stock option it removes stock transactions from the Product Activity providing they meet the correct criteria. The Clear Stock option. However, if you clear stock this can result in the figures on the Retrospective Product Valuation becoming incorrect. The following example demonstrates the figures the Retrospective Product Valuation would show after certain transactions are entered and after the Clear Stock option has been run:

For the purpose of this example the report should be run for Stock Tran Date from 01/01/1980 to 31/12/2050.

AI dated 01/01/07

AO dated 02/01/07

For this AO the Cost Price calculated is £/€100.

AI dated 30/01/07

AO dated 04/01/07

For this AO the Cost Price calculated is £/€75.

After these transactions are entered the Product Activity is as follows:

If the Retrospective Product Valuation is run at this point it shows the following:

If the Clear Stock option was run until 03/01/07 the first two transactions are removed from the Product Activity as they meet the criteria. The Product Activity after clearing stock is as follows:

If you run the Retrospective Product Valuation after clearing stock it shows the following information:

This shows that if the clear stock is run the Retrospective Product Valuation can show incorrect figures due to the Product Activity.

One or more products have no product category

The report runs for all product categories between 1 and 999 unless you specify another range on the criteria window. If a product record contains no category at all, for example if it was created using File Import, this product does not appear on the report.

To correct this, enter a product category in the relevant product record. To check for product records with no category:

1. Click Products and services then click Filter.
2. Click the Join drop-down and click Where.
3. Click the Field drop-down and click Stock category number.
4. Click the Condition drop-down and click Is Equal To.
5. Leave the Value column blank and click Apply.

The Product list displays any products with no category. To show all records, click Search, click Discard, click Apply then click Close.

The report excludes Damages In transactions but not Damages Out transactions

When you post Damages In (DI) or Damages Out (DO) transactions they don't affect the quantity in stock. The Retrospective Product Valuation report should exclude these transaction types, however, the report filter is incorrect so it includes DO transactions. This can make the valuation incorrect.

For the purpose of this example the report should be run for stock tran date from 01/01/1980 to 31/12/2050.

Enter the following transactions:

The correct quantity in stock is 90 with a value of £/€450.00, however, the Retrospective Product Valuation report shows the following information:

This is because the report is subtracting the DO transaction from the total.

To correct this you must amend the report filter to exclude DO transactions.

Incorrect or no cost price on a stock transaction

If the incorrect cost price has been entered on a transaction, for example if a zero cost price was entered, this causes the Retrospective Product Valuation report to be incorrect.

To correct this going forward you can print the Product Activity report for the relevant product, adjust any remaining stock out then run the Clear Stock option for just the affected product to clear all stock transactions. Finally, adjust the remaining stock back in with the correct cost price.

Stock has been transferred in, then stock has been transferred out with a transaction date before the transfer in

If you transfer stock in and then post a transaction that moves stock out, dated before the adjustment in, it is possible to have a legitimate negative stock value on the report.

For example, the following transactions would give a stock value of -5.00 if the report is run on 01/02/2014 from 01/01/1980 to a date between 01/01/2014 and 10/01/2014:

To confirm this, open the product record and click Activity.