Inventory Costing - Date Problem

seckpin

Our company set up the posting groups in such way:
Purch. Account = Inventory A/C
Inventory Adjmt. Account = Accrued Purchases A/C
Invt. Accrual Acc (Interim) = Accrued Purchases A/C
Direct Cost Applied Account = Inventory A/C

Expected Cost Posting is activated (WITHOUT automatic cost posting).

It was set up in such way because the Inventory should be taken into the G/L upon receipt instead of invoice. This also means that in our inventory G/L account, we would have posting from Inventory sub-ledger as well as from Purchases & Payables side.

The matching of Inventory sub-ledger and Inventory G/L account works fine until year end closing (31-Dec-04). We noticed that there were problems with the dates that caused the two ledgers to have different figures.

E.g.

Purchase Receipt (16-Dec-04) = $100
(Run "Adjust Cost" and "Post Invenotry Cost to G/L" on 22-Dec-04, Posting Date = 22-Dec-04)
Value Entries
16-Dec-04 Receipt = $100
G/L
22-Dec-04 Dr Inventory A/C = $100
22-Dec-04 Cr Accrued Purchases A/C = $100

Purchase Invoice (05-Jan-05) = $120
(Run "Adjust Cost" and "Post Invenotry Cost to G/L" on 05-Jan-04, Posting Date = 31-Dec-04)
Value Entries
05-Jan-05 Invoice = $20
G/L
31-Dec-04 Dr Accrued Purchases A/C = $100
31-Dec-04 Cr Inventory A/C = $100
05-Jan-05 Dr Inventory A/C = $120
05-Jan-05 Cr Trade Creditors A/C = $120

(Note: We can't put Jan-05 for the posting date when running the two processes because we do have entries that must be back-dated to Dec-04)

So, it turned out that the Inventory Sub-ledger showed a value of $100 when we printed the Inventory Valuation report, but the G/L amount was actually reversed out and thus was zero.

Is there a way to do filtering for the "Adjust Cost" and "Post Invenotry Cost to G/L" processes that the above situation would not happen? If not, any solution to this problem?

Thanks!

chaswin

What costing method are you using? This has a bearing on postings.

It is extremely dangerous to set filters on the adjust cost/post cost runs as the programs expect to have access to all transactions for a stock item and can produce strange outcomes if this is not the case.

seckpin

We are using costing method FIFO with lot tracking, which effectively becomes specific costing since the cost is traced back to each lot.

So your recommendation is not to do any filtering? Then it means we have to take in these cases as reconciling items between the inventory sub-ledger and G/L?

chaswin

Definitely no filters.

I still want to study your setup in more detail, but one point to bear in mind is that for stock issues the initial posting does not take place at the fifo cost. Rather the initial posting takes place at the average cost and the adjust cost then decides which fifo bucket has been used and posts a value entry for the difference between the average cost used and the fifo cost that should have been used.

This means that any stock valuations or profit reports should only be taken following the adjust cost and post cost to GL periodic runs. It should also be noted that the value entries will be processed with a single date - the date supplied as a parameter to the runs. For these reasons I always recommend that the periodic runs are carried out daily with that days date as the parameter date. (Entered on the option tab only. Leave the filter fields blank where applicable).

One potential problem with your setup is that any stock adjustment (positive or negative item journals) posted will debit or credit the stock account and will then post the contra entry to the purchases account. The Invt. Accrual Acc (Interim) is only used for expected inventory adjustments (positive or negative).

One piece of information extra - have you set up the Inv. Account (Interim) in the Inventory Posting setup table? If so, what account did you specify?

The standard Cronus postings are setup so that

The GRN:

Debits Inventory with expected cost
Credits Inventory Account (Interim) with expected cost

The PI then:

Credits inventory account with expected cost
Debits Inventory Account (Interim) with expected cost

(The above transactions reverse the GRN).

Debits the purchase account with the actual cost
Debits the VAT account
Credits the creditor (actual plus VAT)
Debits stock with actual cost
Credits direct cost applied account.

The GRN by itself will reflect in the inventory valuation as long as the "Include Expected Cost" option is ticked on the report.

seckpin

Our Inv. Account (Interim) was setup to be the same as the Inventory Account.

I do understand that FIFO only takes place AFTER the adjustment process, but since we have lot tracking in place, the costing method has become specific, i.e. tied back to the cost of specific lot.

Thanks for your reply but it seems like our problem can't be resolved at all with how the adjustment process works. All we want is to have the system reverse out the expected cost (i.e. Purchase Receipt cost) and put in the actual cost (i.e. Purch Invoice cost) on the same date, while also not jeopardizing the other back-dated adjustments.

Perhaps the only solution is to ask the staff to refrain from posting in any Purchase Invoice for the subsequent month (e.g. Jan) until the closing for the previous month (e.g. Dec) is completed.

chaswin

In release 4.0, the adjust cost routine has been changed so that the adjustments take the posting date of the original transaction that they are adjusting. It is possible that such an approach might help you. If this is the case then your NSC may be able to do a similar change to 3.7.