Problem to reconcile Inventory Interim Accounts

Beatrice
Beatrice Member Posts: 346
edited 2007-03-07 in NAV/Navision Classic Client
Hi,

One of our customer is having big problem to reconcile its Inventory Interim Accounts, on which there are balances remainining.... ](*,)

In fact, they've got 3 Inventory Interim Accounts: Resale, Components and Finished Goods.

I guess that this is because of shipments and receipts, both sales and purchase, which have not yet been invoiced.

Big Problem is that they are unable to submit monthly management accounts.... :cry:

Anyone can help plzzzz...

How are they gonna proceed to identify all uninvoiced receipts and shipments?

Thanks in Advance.

Regards,
Beatrice.

Comments

  • ssingla
    ssingla Member Posts: 2,973
    I am trapped in a similar situation at present. I will not say that the solution adopted by me is good but it has served the purpose. As I have found no standard solution or report to solve the problem I have used this method. Before I share the solution with you, let me know the following things which will help me in suggesting:

    1) The total of Invt. Accural Acc. (Interim) , COGS Account (Interim) and Inventory Account (Interim) is zero. If not then you need to have a look on the inventory and general posting setup.
    2) How big is Value entry i.e How many entries are there.
    3) How many Inventory posting Groups has been defined.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • colingbradley
    colingbradley Member Posts: 162
    Have you run reports 10540 and 10541?
    These give the details of GRNI (Goods Received Not Invoiced) and GSNI (Goods Shipped Not Invoiced)
    Experience is what you get when you hoped to get money
  • ssingla
    ssingla Member Posts: 2,973
    I guess the reports are a part of Localization and not standard World wide Version.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • ssingla
    ssingla Member Posts: 2,973
    How is your Inventory Posting Group (IPG) linked with the General Product Posting Group (GPPG). Is it one to One to One Relationship or one to many. In case it is One to Many then are many IPG linked with One GPPG or Vice Versa.

    If you print out the Inventory valuation report (ReportId 1001) for each IPG it will show you the balance of inventoy posting group and the expected cost total. This Expected cost amount is represented in the Interim Accounts.
    Total them as per the relationship of IPG and GPPG and you can derive the balances of GL and reconcile them.

    In case the relationship between IPG & GPPG is one to many in both ways then it will be cumbersome to reconcile. I hope this is not the case.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • colingbradley
    colingbradley Member Posts: 162
    See if you can download them

    http://colin.justfixit.co.uk/R10540 Stock Rec not Invoiced.txt

    http://colin.justfixit.co.uk/R10541 Stock Shipped not Invoiced.txt

    They are in .txt format so just open and change the object number.

    You may need to do some small code changes but it will give you the idea.
    Experience is what you get when you hoped to get money
  • colingbradley
    colingbradley Member Posts: 162
    Sorry, I will rename without spaces...wait before trying the download.
    Experience is what you get when you hoped to get money
  • colingbradley
    colingbradley Member Posts: 162
    Here are the renamed files;

    http://colin.justfixit.co.uk/R10540_Sto ... voiced.txt

    http://colin.justfixit.co.uk/R10541_Sto ... voiced.txt

    Just tested...sorry, the administrator has added security. Will get that removed.
    Experience is what you get when you hoped to get money
  • ssingla
    ssingla Member Posts: 2,973
    On clicking the link it is asking the user name and password for acess to the files.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • colingbradley
    colingbradley Member Posts: 162
    Yes, sorry, I have put in a request to the administrator to change that, it may take a few hours so please send me an email and I will mail them to you.

    colingbradley@gmail.com
    Experience is what you get when you hoped to get money
  • colingbradley
    colingbradley Member Posts: 162
    The admin fixed the problem, an update had changed the permissions.

    try this now

    http://colin.justfixit.co.uk/R10540.txt
    http://colin.justfixit.co.uk/R10541.txt
    Experience is what you get when you hoped to get money
  • Beatrice
    Beatrice Member Posts: 346
    Hi Everyone,

    First of all, many thanks for your kind support.

    Also, I wish to excuse myself for the delay.

    Please give me some time to verify the setups.

    I will refer to you as soon as possible.

    Thanks in Advance.

    Kind Regards,
    Beatrice.
  • Beatrice
    Beatrice Member Posts: 346
    Hi,

    Following is update regarding our setup.

    1)The total of Invt. Accural Acc. (Interim) , COGS Account (Interim) and Inventory Account (Interim) is zero. If not then you need to have a look on the inventory and general posting setup.

    Invt. Accrual Acc. (Interim): 7170, Inventory Adjustment

    COGS Account (Interim): 7190, Cost of Sales (same account is used for COGS Account)

    Inventory Account (Interim):
    This account is classified into three accounts:
    2111, Resale Interim
    2121, Finished Goods Interim
    2131, Components Interim

    All the above-mentioned accounts have a Balance.

    2)How big is Value entry i.e How many entries are there?

    There are many entries. Just to get an idea, the following balances are on the Interim Accounts as at 26 January 2006:
    2111, Resale Interim Rs 5,125,630.89
    2121, Finished Goods Interim -Rs 158,557.59
    2131, Components Interim Rs779,640.83

    3)How many Inventory posting Groups has been defined?

    Three Inventory Posting Groups have been defined:
    Components
    Resale
    Finished Goods

    How is your Inventory Posting Group (IPG) linked with the General Product Posting Group (GPPG). Is it one to One to One Relationship or one to many. In case it is One to Many then are many IPG linked with One GPPG or Vice Versa.
    One to One Relationship

    Total them as per the relationship of IPG and GPPG and you can derive the balances of GL and reconcile them.

    Using the Inventory Valuation Report, can you please explain clearly how I can perform the reconciliation using this report?

    Thanks and Regards,
    Beatrice.
  • ssingla
    ssingla Member Posts: 2,973
    Sorry for the delayed response as I was off for a leave.

    To start with the setup seems to be on the wrong side.

    Expected cost in the case of purchase and sale represent the expected increase/decrease in inventory and the resulting increase/decrease in the payable/receivable.
    Invt. Accrual Acc. (Interim): Represents the expected liability to the vendors.
    COGS Account (Interim): Represents the Expected asset in the form of receivables.

    Both these accounts should be a balance sheet account as the Inventory Account (interim). Though COGS interim seems in the first glance to be Income statement account.
    I will explain the method of reconciling later in the day after I am finished from my work.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • Beatrice
    Beatrice Member Posts: 346
    Hi Singla,

    Thanks for the reply.

    Yes, the COGS Account (Interim) is an Income Statement account.

    As mentioned earlier, in the General Posting Setup, same account is specified for the COGS Account (Interim) and the COGS Account, that is account 7190: Cost of Sales, which is an Income Statement account.

    Many thanks for your kind support. I will await for a response by the end of the day.

    Kind regards,
    Beatrice.
  • ssingla
    ssingla Member Posts: 2,973
    Dear Beatrice,

    Run the Inventory Valuation report Inventory Posting Group Wise. At the end of the report the total of Expected Cost will be available. This will represent the balance of the relevant Inventory Account (Interim). I hope the same tallies.
    Further the setup seems to be errorenous. The Invt. Accrual Acc. (Interim): 7170, Inventory Adjustment and COGS Account (Interim): 7190, Cost of Sales should be Balance sheet accounts.
    If the total of Inventory Account (Interim) is tallied with the Expected Cost in the Inventory Valuation report then pass a journal voucher to transfer the balance fromm account 7170 and 7190 to new accounts which should be Balance Sheet accounts. Further the new account for 7170 should be classified in Sundry Creditors/Vendors/Suppliers group and new account for COGS Interim should be in Sundry Debtors/Reveivable group.
    You have to transfer the balance to the extent of balance in Inventory Account Interim from these accounts so that the total of all interim accounts is equal to zero.
    Then change the setup accordingly.
    If the accounts do not reconcile then make a flow field in the GL Entry Table which should be a boolean field having reference to Value Entry table field "Expected Cost". Value Entry number exist in the GL entry table. Then prepare a report for all the accounts filtered by boolean field and derive the balance of all the accounts. This will show the balances in all the accounts which pertains to Expected Cost in the Valuation report.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • ssingla
    ssingla Member Posts: 2,973
    I hope this will reconcile the accounts if not then revert back, I will look for some other method. I have recently used the method and the method worked to perfection and the after altering the setup every thing is running fine for the last 1 month.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • Beatrice
    Beatrice Member Posts: 346
    Hi Singla,

    Many thanks for your reconciliation solution.

    But one last question though before I proceed with your solution, how does the balance on these new balance sheet accounts get written off?

    Thanks Again.

    Kind Regards,
    Beatrice.
  • ssingla
    ssingla Member Posts: 2,973
    Expected Cost Entry in case of sale shipment is :
    COGS Interim Dr
    Inventory Acount Interim Cr

    The same entry is reversed at the time of Sales Invoicing and the actual sales entry is passed. These accounts are not Income Statement Accounts. The balance of all the interim accounts will be zero after all the shipment/receipts are invoiced.

    Because you have calculated the expected cost total in the g/l accounts and transaferred it to a new account you need to change the setup and define this new account so that invoicing have posting in this account which will ultimatly bring the balance to zero.

    As on the closing date of balance sheet if the balance is not zero, then the accounts and auditors will be more than happy to know the balance of pending receipts/shipments to pass there adjustment entries.
    CA Sandeep Singla
    http://ssdynamics.co.in
  • ecclectic
    ecclectic Member Posts: 176
    ssingla wrote:
    7190, Cost of Sales should be Balance sheet accounts.
    If the total of Inventory Account (Interim) is tallied with the Expected Cost in the Inventory Valuation report then pass a journal voucher to transfer the balance fromm account 7170 and 7190 to new accounts which should be Balance Sheet accounts.

    excuse my ignorance, i really don't understand how changing the setup (from income statement account to balance sheet) will help with the reconciling of the inventory nor wat's wrong with the setup described by beatrice.

    the real difference between a balance sheet and an income statement account is when closing of financial where the bal of income statement ac would be transfered to profit/retained profit account.

    under wat heading (CA/ CL or else ) in balance sheet would u put the ac specified in cogs interim.

    for my part i have successfully reconciled inventory in manufacturing/trading companies using the setup described by beatrice
  • ssingla
    ssingla Member Posts: 2,973
    In addition to reconciling I was helping him create correct setup so the future transaction are properly posted and reconciled.
    Ecclectic wrote
    under wat heading (CA/ CL or else ) in balance sheet would u put the ac specified in cogs interim.

    If the COGS and COGS Interim account are same then there is no standard way to know the balance of COGS Interim and if you do not know the balance how one can reconcile :-k
    CA Sandeep Singla
    http://ssdynamics.co.in
  • ssingla
    ssingla Member Posts: 2,973
    C/AL do know the difference in COGS and COGS Interim thats why they are defined seprately in the setup. How C/AL differentiate them is beyond my technical knowledge (I am a Functional Consultant :mrgreen: ). The value entry of COGS interim will have a boolean marked as true "Expected Cost".
    CA Sandeep Singla
    http://ssdynamics.co.in
  • colingbradley
    colingbradley Member Posts: 162
    COGS is the actual invoiced and adjusted cost of sales. "Cost Of Goods Sold"

    COGS (Interim) is the EXPECTED cost but not yet invoiced and so is a best guess or in accountants jargon the Accrual value. If the invoice has a greater cost than expected, the adjustment will be made and the final COGS will be posted.

    At the end of the year (or other period), an accrual journal is posted as a reversing entry to finish the accounts for the year.
    Experience is what you get when you hoped to get money
  • Beatrice
    Beatrice Member Posts: 346
    Hi Everyone,

    Thanks for your support.

    In fact, we have made a backup of the production database and restored it on a Test database.

    We have posted all uninvoiced receipts and uninvoiced shipments for these three Inventory Posting Groups but a balance is still remaining though on one of the Interim account.

    Can anyone help?

    Many Thanks.

    Kind Regards,
    Beatrice.
  • ecclectic
    ecclectic Member Posts: 176
    ssingla wrote:
    In addition to reconciling I was helping him create correct setup so the future transaction are properly posted and reconciled.
    Ecclectic wrote
    under wat heading (CA/ CL or else ) in balance sheet would u put the ac specified in cogs interim.

    If the COGS and COGS Interim account are same then there is no standard way to know the balance of COGS Interim and if you do not know the balance how one can reconcile :-k

    actually my point was, i don't think it helps the reconciliation that the account specified in cogs interim was an income statement acc or balance sheet.
  • ssingla
    ssingla Member Posts: 2,973
    But if COGS and COGS interim are same account then you can not derive the balance of these accounts seprately.
    CA Sandeep Singla
    http://ssdynamics.co.in

Categories