FIFO stock valuation specifications
02-16-2012, 02:40 PM (This post was last modified: 02-16-2012 06:26 PM by phil.)
FIFO stock valuation specifications
FIFO Stock valuation
FIFO stock valuation is necessary in some accounting jurisdictions and requires that stock is consumed on a first in first out basis. This means that the oldest stock is assumed to be the first to be sold (or adjusted out).
What needs to be done in webERP to Facilitiate FIFO Valuation
To value stock on this basis it is necessary to keep track of the date each quantity arrived and the cost it was landed for. This will require maintaining a new table of costs for each item, the sum of the values and quantities, must always equate to the total stock value/quantity on hand respectively.
Create new table – sql/mysql/upgradexxxxxx.php
Also the stock costing configuration parameter should also allow FIFO as well as Standard Costing and Weighted Average costing - as currently. When the user changes to FIFO costing the FIFO table will need to be populated - initially with a single entry for each item dated today with the quantity on hand (in all locations) with the current standard or WA cost. If the parameter is changed to either Standard or WA (from FIFO) then all entries in the FIFO table should be deleted.
For the moment, it is not proposed to make this specific to each inventory location, so stock that arrived in Seattle if it is the oldest stock in the company, and the New York branch makes a sale the cost will be that of the Seattle purchased stock.
Running reports off the FIFO table will give you the FIFO valuation of stock – which should always agree to the general ledger value of stock in an integrated stock->GL system (Company Preferences)
It is necessary to consider every possible stock movement type and make the necessary inserts and updates to the new table and consider the impact of FIFO valuation of each stock movement on the general ledger journals created and what should actually happen under FIFO valuation schema.
Stock Arrival - Goods Received
Each time stock arrives, a new record will be inserted into this table with the quantity arriving – initially as arrival may well be before the invoice is entered, this cost will be the weighted average cost held in the stockmaster .
Scripts to modify GoodsReceived.php and GoodsReceivedControlled.php also ReverseGRN.php
Whilst there is no movement of stock, this is when the cost of the stock that arrived is actually discovered for sure. When the supplier invoices the goods, then the actual cost (based on the exchange rate entered at the time of the purchase invoice entry) at the price invoiced by the supplier – if the item is not on a shipment - then the FIFO table needs to be updated for those items received and being invoiced by this supplier invoice. Existing general ledger entries are unchanged from weighted average costing as the cost is all allocated to the cost of the stock (to the extent there is still stock on hand), if the stock is sold (i.e. stock is negative before the goods are booked in) then the variance between what the stock was held at and what it was from the supplier invoice is written off to profit and loss as a price variance (cost of sales). Check stock value in GL on WA sh/be same as FIFO in this case. The new WA cost is updated and this should always be the same as the FIFO cost – things only get more complicated when we invoice stock.
Scripts to modify SupplierInvoice.php (impact on SupplierCredit.php)
When a shipment is closed and the actual cost of goods on the shipment are worked out the table needs to be updated with the actual costs – same as SupplierInvoice.php logic . The GL journals will be the same as WA because the variance is all taken to the stock cost (unless the stock is negative – in which case any variance is written off to profit and loss)
Where stock is taken out – the value of the adjustment should be taken from the oldest stock (FIFO) so this needs to be retrieved from the new table and the table quantities updated – if certain deliveries are consumed then these records need to be deleted. The GL journals need to be created based on the costs being consumed – suggest calculate a weighted average of the items adjusted and just do one journal irrespective of how many deliveries of stock were adjusted out. The new WA cost (which will be the FIFO) valuation will need to be updated based on the cost consumed by the adjustment – either by running the total remaining FIFO cost dividing by the new qty left on hand or by applying the difference in cost between the FIFO cost of the adjustment and the WA cost of the adjustment between the remaining items in stock.
For stock being adjusted into stock, we can only use the current weighted average stock cost and create a new record in our new FIFO table for the quantity adjusted in – GL journals at the current WA cost.
Scripts to modify StockAdjustments.php and controlled adjustments.
Historically there has been no GL entries when stock is transferred between locations. FIFO stock as planned is not specific to a location so no changes are required to our FIFO table. We still have the same stock just at different locations – no impact on GL nor stock valuation.
When goods are sold we need to determine the oldest stock from our new table and do the COGS GL at the new WA based on the oldest deliveries – our table needs to decrement the quantities consumed that were sold too. Need to do this for controlled serial numbered items too. It is proposed that a weighted average cost for all the quantity being sold be arrived at and that this cost should form the basis for the general ledger accounting.
Then when the stock quantity sold has been taken out of the locstock the new WA cost of the remaining stock needs to be calculated based on the either the FIFO valuation of the remaining records or the difference between the cost removed and the current weighted average divided over the remaining stock quantity and the WA (standard cost) updated.
In recalculating the cost of assemblies, the FIFO cost of each component needs to be retrieved and the stock values adjusted for each component based on the FIFO cost consumed.
Scripts to modify ConfirmDispatch_Invoice.php, CounterSales.php, api/debtortransactions.php InvoiceOrder function
We can only use the WA cost based on the remaining stock at the time the return is processed. There would be no way of determining when the stock originally arrived into stock. So no difference in processing GL entries, just need to add a record to the FIFO table to show the date the goods received and the cost (Current WA cost).
Credit_Invoice.php SelectCreditItems.php api/DebtorTransactions.php
Where an item has a bill of material, it’s cost will always be re-calculated based on the latest bill of material at the time a work order is released. However, existing stock will not all be revalued based on this cost as it was previously – only new production will reflect this cost, existing production will have the FIFO table as all stock items will with the cost of each run of production (based on the BOM at the time the work order was released)
Work order issues
When component stock is issued to the work order we now need to calculate the cost of that stock from our new table in the same way as we do for invoices. The work order cost needs to be incremented and gl journals done based on the FIFO cost of the items issued. The items issued to the work order need to have our FIFO table updated for those items consumed.
Scripts involved WorkOrderIssues.php and controlled.
Work Order receipts
When new items are received as completed they will be received based on the work order “standard cost” as usual – not proposing any change to the manufacturing with manufacturing variances away from the bill of material cost being written off to usage variances. However, new entries will be required into the FIFO table for each receipt based on the work order "standard" cost at the current date.
Work order closing
This process recalculates the cost of the items received against the work order based on the cost of items issues to the work order, issues of stock greater than the bill of material requirements are assumed to be variances which are written off to work order usage variances and are not costed into the stock value. It is not proposed to alter the logic here.
Contract Issues/receipts – actually uses work orders functionality
Stock valuation report
Since I am considering updating the so called “standard” cost based on the new FIFO valuation after each transaction, then the stock valuation report can remain unchanged.
Logic Works Ltd
07-13-2012, 11:01 AM
RE: FIFO stock valuation specifications
Actually its First in first out inventory usages system which is also a traditional system but good for the small industries.I must say that in this system you can show more profits in your balance sheet.
User(s) browsing this thread: 1 Guest(s)