Sales Analysis report does not match Cost of Sales Account

[Former Member]

My client is running SAP B1 8.81 PL 6. He has set all items to standard costing method. He has discovered that the difference between the Sales Amount and the Gross Profit amount on the Sales Analysis report, which should equal the Cost of Sales for the Items, does not match the Cost of Sales G/L account on the P&L report. If you look at a sample of rows, the above difference divided by the quantity equals the standard cost of the item. There are no manual journal entries that affect the COS account for the posting period that is being analyzed. I cannot see any mathmatical difference or problem with the above analysis, nor can I find the problem. There is about an $85K difference between the reports. The Sales Amount on the Sales Analysis report matches the revenue account on the P&L to the penny. Has anyone ever compared these 2 reports? Thank you for your help.

Bill Turek

[Former Member]

If you right click on an Invoice and choose "Gross Profit" it will give you a setting on how you want to calculate the Gross Profit for that invoice.
In the accounting point of view the "Base Price" should be "Item Cost" so that the Gross Profit will be (Net Selling Price - Item Cost = Gross Profit). This will give you the same Gross Profit amount between P and L and Sales Analysis Report.

However, SAP B1 gives the user the ability to select a different base price other than the Item Cost.
This gives the user the flexibility for the Sales Report. Example (Net Sales Price - Vendor Sales Price = Gross Profit). People do this sometimes because  the item's cost fluctuates if you use FIFO or average cost and they want a uniform cost to calculate their Gross Profit.

In summary if the Base Price is not set to "Item Cost" then there will be difference in the Gross Profit between the P and L and Sales Analysis Report.

See screenshot:

-Eric Alo-

B1 Financial Consultant

[Former Member]

Marcia,

Thank you for the response. I checked the database and all the Deliveries were invoiced by the end of the month. If you create an invoice from a sales order, the invoice will create a journal entry that also charges COGS. I have both conditions in this issue. I think the problem is with back dated Inventory Revaluations. There were 10 Inventory Revaluation transactions entered in August but were back dated to July.

Bill Turek

[Former Member]

Jitin,

I used the Items tab on the Sales Analysis report and I have the Invoices radio button checked. I ran the report for the month of July, 2011.

Bill

[Former Member]

There are several factors to consider. If you are using Deliveries before invoicing, the COGS posting takes place at the Delivery, not the Invoice. It may be that the invoice is not created until the next day - in this case, there would be a discrepancy at month-end between the COGS posting and the gross profit on invoices, since the items delivered on the last day of the month would not be invoiced until the first of the next month. Also, it is possible to change the gross profit on a document at the time of invoicing, either manually or by means of a change in the standard cost in the Item Master. Those are just a few ideas - there may be other differences as well.

[Former Member]

Gordon,

I did a database search and found the Item Cost (OITM, AvgPrice) for each Item that was in the Sales Analysis report. There was no item with a zero cost. So, this must be a bug.

Bill

[Former Member]

No. You have to search within sales document. Current costs correct can not guarantee all history costs are correct.

[jitin_chawla]

Hi,

Please let us know which tab you are using to run the Report and what documents are included.

You can check for any service type document which are displayed in the sales analysis report. If running on Orders check for the grossprofit field in ORDR and RDR1 table and compare if the value is appearing correct.

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Jitin,

The Gross Profit column appears to be correct. Since it is based on the difference between the Sales Price of Item and the Item Cost, it is controlled by the Sales Price entered in the Sales Order for each Item. Is this assumption correct?

Bill

[Former Member]

Hi Bill Turek,

Cost of Sales Account may not be hit by all sales process. When the cost is wrong if the sales happened, it may have 0 cost.

Check the variance to item level. Find out what the standard cost setup. You may pin point the difference this way. If nothing is wrong in the detail, it could be by bug.

Thanks,

Gordon

[jitin_chawla]

Hi,

Can you confirm whether the gross profit figure/balance is appearing correct?

Kind Regards,

Jitin

SAP Business One Forum Team