How to get the Gross Profit of Sales

selece28

Hi Nav Masters.
I have a problems. My client want me to create a report with column like this:

Item Code | Qty | Unit Price | Line Disc Amt | Inv Disc Amt | Total Amt | Purch Price (Cost) + Item Charge | GROSS PROFIT

I know i can get this from table : Sales Invoice Header and Value Entry to get the <Purch Price (Cost) + Item Charge>

But then my client want me to explain to them how to get those "Cost Amount (Actual)" and "Cost per Unit" from Value Entry
Because they said the value in my report is not correct.

They want to query the data from SQL Server directly to get the Cost for each item Sold including the item charge for each of those item, so they can see the correct GROSS PROFIT (Total Amt - Purch Price (Cost) + Item Charge)

I already try to find the relation but seems that i'm lost
My client using FIFO but without "Lot No." or "Serial No."

So can someone describe me how to link between Sales and Purchase?
Or how can i get the cost value that is in the Value entry table?
Is it possible?
I already try to see the relation between Sales and Purchase from table 339 Item Application Entry, but it seems that its not just relate the Sales and Purchase, but also relates the Transfer and other things.

I thought at first i can get it with logic from Form 38, then click Application - Applied Entry. I can see the Purchase related to that Sales, if i can find the Purchase, its easy to find the Item Charge for that Purchase.
But then i find that some Sales related to Sales or Transfer. ](*,)

Please help me, because i need to know this in details and Microsoft Nav don't have it in their help document.


Thanks in advance

girish.joshi

This doesn't exactly answer your question - but it should help:

Getting the link between the Sales Entry and Purchase Entry can be a bit tricky, but its very doable.

You first need to find the item ledger entries related to your sale.
If you have the value entries, you can look at the Item Ledger Entry No. field to find this.

These item ledger entry's are your final outbound entries. You need to find the original inbound entries these are applied to.

Search the Item Application (IAE) table for all entries where the Outbound Item Ledger Entry No. = Item Ledger Entry No. and where the Outbound item Ledger Entry is your set of final outbound ledger entries

For each IAE, there will be Inbound Item Ledger Entry on the record.
These inbound Item Ledger Entries, may or may not be your Original Inbound Item Ledger Entry.

For each Inbound Item Ledger Entry you find, you have to search the Item Application Entry table again. Search for entries where Inbound Item Ledger Entry No is in the set that you just found and the Item Ledger Entry No = Inbound Item Ledger Entry No.

On the Item Application Entries you find, if the outbound ledger entry is 0, than you have found your original inbound item ledger entry.
If the outbound ledger entry is not zero (in cases where the have been sales credit memos or transfer orders for instance) you have found an intermediate Inbound entry. You must find the outbound entries these apply to, and then look for the inbound entries associated with these.

You repeat until there are no more intermediate item ledger entries.

Once you have found all of the original inbound item ledger entries, you can find all of the value entries that are associated with the item ledger entries to find your cost.

selece28

Wow that is very difficult looping.
I will try it.
Thanks for the comment.

Is there any easier way?

Thanks in advance

jonsan21

First of all, you should establish the definition of the GROSSPROFIT with your customer. You may be surprised, but different people may interpret this differently. Second, you have to understand a little bit on Navision Costing method, and how they adjust the cost, based on the adjust cost batches.

The date filter is the challenge, because you can always manually (hence programatically) find the GROSSPROFIT at current point of time, but you have to consider that the report should take care backdating, etc.

I don't think there's an easy way to do this, but most of the partner would have this kind of report 'ready before hand'.

Maybe they will have Gross Profit as a flow filter in upcoming version? No?

Rgds,

Jon.

selece28

Yes i agree, why NAV don't have this kind of reports.
My Client's definition of GROSS PROFIT is :
SALES TOTAL AMT - (PURCH PRICE + ITEM CHARGE)

Regards,

vanrofi

You can also consider the Sales Analysis reports. these are more flexible and provide some nice Drilldown possibilities.

With the flexibility to add or modify your own formulas...creating grossmargin reports without encoding is right there for you...

selece28

Can i use the Sales Analysis Report?
How to use it?
My client want it to be like this. Example:
I have a sales of 100 item, i can get the cost from Value entry, lets say 900
Then they want to know, details of this 90, where is it come from?

They don't use serial number, and can we create report with detail line for each invoice per item.
Lets say they sale 100 item A in 1 invoice, they want to see details like this, example:
- 10 item A from purchase with price 15
- 50 item A from purchase with price 10
- 40 item A from purchase with price 6,25

from this detail we can see the total Cost is 900.

i'm confuse. If i use the link from Item Application Entry like girish.joshi said, my query will take lots of time, probably hang, because my clients have lots of item and quantity.

Is there any other suggestion?