Client Query - Why does my Stock Value report not match the stock nominal in the Trial Balance?

Client Query - Why does my Stock Value report not match the stock nominal in the Trial Balance?

Problem

My stock value report does not match my stock nominal in the Trial Balance.

Solution

There are several reasons why your stock nominal might not match your stock value, many of these reasons, detailed below, depend on your business processes. 

Throughout this page each potential issue will have an example with a note displaying how the accounts are affected, the colours represent:

  • Red + or - The nominal or report will be displaying an incorrect figure:
    • + (Plus) Means the figure is larger than it should be.
    • - (Minus) Means the figure is smaller than it should be.
  • Green = The nominal or report will be displaying the correct figure.
  • Orange = The nominal or report will be displaying a figure close but potentially not equal to the correct or final figure.

You have booked the stock in on a delivery note, but not posted the associated purchase invoice

When this happens you will have the items in stock, but the stock nominal is not updated with the value of that stock until the purchase invoice is posted. Areas where this can cause problems are:
  1. The time between stock being sold and despatched and the receipt and posting of the purchase invoice.
  2. If you are using dropship to fulfill orders, the timing of purchase invoice posting and the stock value calculation method.

General Increase in purchase costs

If there is an general Increase in purchase costs the accounts will drift in the following fashion: 
  • Stock Nominal +
  • Stock Cost of Sale -
  • Stock Value Report -

General Decrease in purchase costs

If there is an general Decrease in purchase costs the accounts will drift in the following fashion: 
  • Stock Nominal -
  • Stock Cost of Sale +
  • Stock Value Report +

You have not, or never posting purchase invoices

If you do not or have never posted a Purchase Invoice then no Stock Nominal entries will have been made. The stock value report will return results but will be incorrect if average or last price paid is used as the non-posting of the purchase invoice means that the stock value of the individual stock items is not updated.

  • Stock Nominal -
  • Stock Cost of Sale -
  • Stock Value Report -

Non stock controlled items are showing in the nominal but not in the stock value report

If a user purchases a non-stock controlled item the value of that purchase will be added to the stock nominal when the purchase invoice is posted. This value will NOT be accounted for on the Stock Value Report but will have been added to the Stock Nominal assuming the stock option COS Excluded is OFF (unticked) and no Stock Item Purchase Nominal is specified against the stock item.
  • Stock Nominal +
  • Stock Value Report =

The value of the non-stock controlled item will remain present in the Stock Nominal forever unless the user journals it out. The user is responsible for this! 

Stock controlled items are marked as CoS Excluded

Stock Controlled items that have been purchased with the stock option COS Excluded ON and a stock item Purchase Nominal defined against them will have their value posted to whichever purchase nominal is defined against them.

This item's value will be included in the Stock Value Report.

Purchasing Stock Controlled Items marked as stock COS Excluded will have the following effects:

  • Stock Nominal =
  • Stock Value Report +
  • Stock Item Purchase Nominal +

The stock value of a Stock Controlled Items marked as stock COS Excluded will never be updated and can be considered to be using Standard pricing.

Selling a Stock Controlled Items marked as Stock COS Exclude will have the following effects:

  • Stock Nominal =
  • Stock Value Report -
  • Stock Item Purchase Nominal +

Non-Stock Controlled Items marked as Stock COS Excluded

Stock items that are non-stock controlled and marked as COS Excluded normally should never create any discrepancies in the Stock Nominal or Stock Value Report as they will not be recorded in either. Historically it may be possible for a non-stock controlled item with COS Excluded turned on to NOT have a Purchase Nominal defined against it. 
In that scenario the purchase of that item would go into the Stock Nominal and would need to be dealt with manually. 
  • Stock Value Report -

Journals have been made manually that affect the stock nominal

Manual journals into or out of the stock nominal will NOT be accounted for on the Stock Value Report. This can become an issue if someone has entered a stock value journal incorrectly. This is sometimes done by a user who is trying to get a desired stock value figure and is often carried out by a user who does not realise how the system works.
  • Stock Nominal +/-
  • Stock Value Report +/-

Customer returns where the stock has been booked in but the credit note has not yet been issued

When the Customer Return is processed, the stock value of any returned goods will not be accounted for in the Stock Nominals until the Credit Note is issued. The value will be present in a Stock Value Report at the current stock value. 

Pre-posting of Credit Note: 

  • Stock Nominal =
  • Stock Value Report +

Adjusting stock in without a value and then adding a value later

Generally this is only an issue when users are untrained or new to the system and simply incorrectly set an item up and adjust it in. 
The results of various scenarios are listed below: 

Adjusting an item in with no stock value

Simply adjusting an item in with no stock value will affect the following areas: 
  • Stock Nominal -
  • Stock Value Report - If the stock value is left at zero

Processing an order with the zero stock value item

Processing an order with the zero stock value item will affect: 
  • Stock Cost of Sale - if sales orders are issued with zero stock value

An incorrect fix by simply entering a stock value

An incorrect fix by simply entering a stock value will affect the following areas 
  • Stock Nominal -
  • Stock Adjusted -
  • Stock Value Report -

Processing an item with an incorrectly fixed stock value

Processing an item with an incorrectly fixed stock value will affect: 
  • Stock Nominal -
  • Stock Cost of Sale - If orders are issued with zero stock value 

Changes in the stock value of items over time

The stock value calculation method can account for large and varying stock value discrepancy. Using the correct calculation method for your business process can make a huge difference to keeping discrepancies in line, see Understanding Stock Value and Cost of Sale for more about stock value and how it is calculated.

The stock value calculation method can account for large and varying stock value discrepancy. Using the correct calculation method for your business process can make a huge difference to keeping discrepancies in line.

Average Pricing

Average Pricing in most cases is the most accurate over time for most users. Although the user need to be aware that there are certain purchasing situations that will create immediate discrepancies; for example if they always sell all delivered goods before receiving the Purchase Invoice, which updates the stock value calculation.

Example - Average Pricing

Scenario

  1. A customer places a Back Order for 10 items that have a stock value and buy price of £5.00 NET each.
  2. You create a Purchase Order to fulfil the Back Order requirement.
  3. The goods are delivered and the sales order is immediately fulfilled. They are issued with a Cost of Sale of £50 (10x£5) as that is current average.
  4. The Purchase Invoice finally arrives and the items are now £8.00 each.

Results

Once the Purchase Invoice is posted £80 is booked into stock but the COS was £50 leaving £30 in stock that doesn't exist on the shelf so the accounts will be out in the following areas:

  • Stock Nominal +
  • Stock COS Nominal -
  • Stock Value Report +/-

In the above example the use and manipulation of Standard pricing could have greatly reduced the discrepancy.

Standard Pricing

Standard Pricing is where the user controls the stock value. Unless they recalculate the value of stock items for each purchase there will always be a discrepancy unless the purchase value never changes. This method used globally will inherently have larger discrepancies.

Example - Standard Pricing

Scenario

  1. A customer places a Back Order for 10 items that have a manually set stock value of £5.00.
  2. A Purchase Order is created to fulfil the Back Order requirement.
  3. The goods are delivered and the sales order is immediately fulfilled.
  4. The user adjusts the stock value to £7.50 as they know the bill be around that price. The order issued with a COS of £75 (10x £7.50 per item).
  5. The Purchase Invoice finally arrives and the items are now £8.00 each.

Results

Once the Purchase Invoice is posted £80 is booked into stock but the COS was £75 leaving £5 in stock that doesn't exist on the shelf. The accounts will be out in the following areas:

  • Stock Nominal +
  • Stock COS Nominal -
  • Stock Value Report -

This is the same scenario used in the Average pricing section, In this case Standard pricing has reduced the Stock Nominal discrepancy in comparison. 

Last Price Paid

Last Price Paid updates the stock value to the NET last price paid for that stock item. Discrepancies will simply come down to rate of sale verses purchase price fluctuation i.e. if you buy and sell all your stock regularly the difference will be quite small but if the sales/purchase periods are large then very large difference are possible. This can be compounded by the non-posting of invoices.

Using this stock value calculation method will inevitably require intervention in the accounts.

Example 1 - Last Price Paid

Scenario

  1. A customer places a Back Order for 10 items that have a stock value and buy price of £5.00 NET each from the last purchase.
  2. You create a Purchase Order to fulfil the Back Order requirement.
  3. The goods are delivered and the sales order is immediately fulfilled. They are issued with a Cost of Sale of £5 as that is what you last paid.
  4. The Purchase Invoice finally arrives and the items are now £8.00 each.

Results

Once the Purchase Invoice is posted £80 is booked into stock but the COS was £50 leaving £30 in stock that doesn't exist on the shelf. The accounts will be out in the following areas:

  • Stock Nominal +
  • Stock COS Nominal -
  • Stock Value Report -

Example 2 - Last Price Paid

Scenario

Another purchase and sale scenario that Last Price Paid would not be suited to is:

  1. You have no stock.
  2. You create a Purchase Order, Delivery and Post the Purchase Invoice for 10 items at £1.00 each, add £10 into Stock.
  3. A customer places an order for 20 items which needs to be despatched as a whole order.
  4. You create a Purchase Order for another 10 items to fulfil the Back Order requirement.
  5. The goods are delivered and the sales order is immediately fulfilled. They are issued with a Cost of Sale of £20 (20x£1) as that is what you last paid.
  6. The Purchase Invoice finally arrives and the items are now £2.00 each.
  7. Once the final Purchase Invoice is posted, £20 is booked into stock.

Results - buy price was low then high

The COS was £20 leaving £10 in stock as there was already £10 in from the first Purchase (£10+£20-£20=£10). The accounts will be out in the following areas:

  • Stock Nominal +
  • Stock COS Nominal -
  • Stock Value Report /+

Results - buy price was high then low

The COS was £20 leaving £10 in stock as there was already £10 in from the first Purchase (£10+£20-£20=£10). The accounts will be out in the following areas:

  • Stock Nominal -
  • Stock COS Nominal +
  • Stock Value Report -

This demonstrates the limitations of Last Price Paid.