Dear Sage City Member
This blog article will assist you in understanding how the system calculates the adjustment values when there is an alteration in cost while using either the Latest or Manual Costing method.
The Cost is usually adjusted by either processing a direct Stock Adjustment where you can choose between three actions, namely, Set, Add and Subtract or an Inventory document transaction such as the Goods Received Voucher (GRV). There are however other ways in which the cost may be adjusted but for the purposes of this blog we will use a GRV transaction as an example.
The system will increase or decrease the Inventory value by using the current quantity multiplied by the variance between the current and previous cost. The values will then be posted to the Stock Adjustment and Stock Control Account, respectively.
Firstly, you would have identified the enormous “unaccounted” value posted upon enquiring on a recent transaction – Goods Received Voucher (GRV)
The below screen displays a General Ledger Enquiry on the Stock Control Account (7400) drilled down to a specific GRV. The drill down on Audit Trail displays an enormous adjustment of R25 339 600.17.
To get to the Enquiry screen you would have to Navigate to General Ledger | Enquiries | Ledger Enquiries.
Once you have identified the transaction responsible for the Cost Adjustment and have opened the source to identify the necessary Inventory Item. You may then follow the below steps to understand the calculation. Herewith steps:
- Navigate to Inventory | Maintenance | Inventory Items.
Customise the Grid and Add the Item ID field, this would then assist you in determining the Stock link for the next step. Take note of the Item ID for item identified on the GRV Enquiry.
- Navigate to Start | Microsoft SQL Server | SQL Server Management Studio
You would then need to connect to the Server instance, expand the databases folder, select your database, right click, and select New query.
- Copy, Paste and Execute the following query.
select * from _etblInvCostTracking
where istockid= 'Enter the Item ID for the item as identified'.
In the inverted commas section, you would need to enter the Item ID (numeric value) as identified on the Inventory Grid.
The query should return results like below:
4. Scroll to the relevant transaction date reference by ‘dTxDate’ and take note of the following: fQtyOnHand before transaction, fLatestCost before transaction and fLatestCost current/after transaction.
Current Latest Cost (fLatestCost): R1695.6522
Previous Latest Cost (fLatestCost): R16.087
Previous Quantity on Hand (fQtyOnHand): 15087
You should now have sufficient Information to calculate.
Formula: (Previous fQtyOnHand * Current fLatestCost) – (Previous fQtyOnHand * Previous fLatestCost)
= (15087 * 1695.6522) – (15087 * 16.087)
= 25 582 304.7414 – 242 704.569
= 25 339 600.1724
Rounded to two decimal places = 25 339 600.17
This formula calculates the variance of the total value of the inventory.
OR
(Current fLatestCost – Previous fLatestCost) * fQtyOnHand
= (1695.6522 – 16.087) * 15087
= 1679.5652 * 15087
= 25 339 600.1724
Rounded to two decimal places = 25 339 600.17
This formula calculates the variance in the unit cost and then multiplies with the quantity on hand to get the new Inventory Valuation.
Total value of adjustment = R25 339 600.17
The Total Value of adjustment is the value posted to the General Ledger as soon as the GRV is posted.
In conclusion the Adjustment resulted in a higher cost thus the system will adjust the Inventory value positively by debiting the Stock Control Account and crediting the Stock Adjustment Account with the Total value of adjustment as calculated above.
------------------------------------------------------------------------------------------------------------------------------------------------------------------