Sales and COGS by Location

LibertyMountain

We are beginning to do business with a 2nd location. We are a wholesale distribution company. We will stock most of our items in both locations. We use 3.7 and will upgrade shortly, but not before taking on a 2nd location.

We want the ability to sell items, on one sales order, from both locations. Furthermore, we want our G/L to show Sales and COGS by location.

I know how to setup so that G/L inventory gets credited on a sales invoice from the correct account by location. (Inventory Posting Setup)

A/R will be according to Customer Posting Group setup (I have no problem here either as I have no need to track A/R by location, nor do I see why anybody would want that need).

But Sales/COGS accounts are setup according to the combination of Gen. Bus. Posting Group and Gen. Prod. Posting Group. This combination will be determined by the customer and item, NOT AT ALL BY THE ITEM'S LOCATION.

Therefore, suppose a customers orders 100 of item A from location A, and 100 of item A from location B (same item, two locations). According to General Posting Setup, all Sales will be booked to a single Sales account; all COGS will be booked to a single COGS account; whereas we would like two Sales accounts and two COGS accounts, one for each location.

How does Navision handle this? Basically if Location code were a dimension passed to the G/L Entry table, this would solve our needs perfectly, as we could filter our income statement by location. But Location is not a dimension on the G/L table. (or is it? and I just didn't pay for that module?)

Do other accountants out there who use Navision have this same requirement (i.e. to track Sales and COGS by location)? How are you using Navision to do this? I don't think we should have to customize Navision at all to handle this business need. So I am hoping there is a way I can solve this need without a customization. (Also, we currently do not use Responsibility Centers or Business Units. I don't think either of these will solve our need, but I admit I am not well-versed in either.)

Thank you in advance for your replies.

nunomaia

I don’t know if you need specifically to obtain COGS from G/L accounts, there are other ways to obtain that.

For example if you make item -> statistics, remove item filter and insert location filter. COGS will be getting from value entries and not from G/L entries.

AdamRoue

You can set up responsibility centres with dimensions linked to each location, in this way you can pass location based dimensions through to the general ledger and design the account schedules to show the costs and sales split by dimension/location. You cannot split this solely by location due to the posting group structures as you have discovered.

nunomaia

When it’s used Responsibilities Centers in sales lines has to be taken some precautions.
Default Responsibilities centers will be get from Sales Header, even if user changes location in sales line RC will remain the same that is in sales header. Changing Location code doesn’t change RC and dimensions witch can lead to incorrect analysis.

When it’s used RC I recommended making a few lines of custom code to avoid errors in inserting lines by users.

LibertyMountain

We solved this by adding a custom fields to the Location Code table (for Global Dim 1). When Location Code is added to a sales line, we edited the "sales line"."Location Code" on validate logic so that NAV will copy Global Dim 1 from the Location Code and paste it to the Sales Line's Global Dim 1.

We do not use Global Dim 1 on any other type of record (item, customer, vendor, etc.). So this has worked nicely for us for several years.

I wish however that Microsoft would allow Location Codes to have Dimensions. I have heard from our NSC that they get this request often.

ara3n

I had suggested it to MS, but they didn't accept it.