Support Sales Orders with Multiple Fulfillments In Reports
I'm looking for a report that properly outputs the shipped sales profitability for a given time period. My company counts a sale when it has shipped out the door because in our industry customers frequently change their order, cancel their (already paid) order, and/or preorder products months in advance.
I can view shipped sales profitability with the Sales Profit By Sales Rep report (but others probably work similarly) and it's great until advanced sales are in the mix. The whole sales order is counted even if most of the items on the order have yet to be fulfilled, and there isn't a way to filter them out on that report. In addition the sales order's 'Shipment Date' is set to the most recent date of a shipment. That means that if an advanced sales order has a shipment on Day 1 and on Day 3, when reporting is run on Day 1, it will show as being shipped Day 1. When reporting on Day 2, it shows as being shipped on Day 1. When reporting on Day 3, it shows as being shipped on Day 3! Ideally the report is able to break down partially shipped sales orders into fulfillments and allow for proper filtering.
Alternatively the Fulfillment Details report does provide the appropriate fulfillment information, but I cannot add the costs, COGS, journals, etc columns that would be needed to display profitability - that would be a nice addition!
A workaround that I'm considering is to break down sales orders into simple/advanced orders that I know will always ship the same day so that reporting can be done through DEAR and not need to be exported and manipulated in Excel or the like. Really not ideal and I feel as though a new report or upgrades to existing reports wouldn't be too onerous.
4 people like this idea
The crux of the issue here, I believe, is that because DEAR does not mandate/force users (in an Advanced Sale) to link an Invoice or Invoices to a Fulfilment or Fulfilments, DEAR has no way of providing you with a report that shows the Revenue/COGS/Profit for a single invoice within an Advanced Sale.
Sure, if you use a date range, you can get all the Revenue and COGS for that particular order, which will likely cover most instances, but if you want an explicit Revenue/COGS/Profit for an Invoice, then that cannot happen.
Even with custom reporting that I've had developers provide for my clients, we cannot match COGS to Revenue for an Invoice unless the Invoice has Fulfilments that are linked. That appears to be the only way you can, in your coding, link the two together to do such reporting.
I have brought this up with them many times over the years but it all seems too hard for them to fix.
DEAR just needs to force users to link Invoices to Fulfilments and this issue should be able to be resolved within their current reporting engine. Failing that, they probably need to write a whole new reporting engine to deal with the Advanced Purchase/Advanced Sale functionality.
I have to export 2 reports to Excel, match up the sales then sort them to find the advanced sales that I need to double check. Then I have to go into each sale's transaction history and manually enter the COGS for the fulfilments from that month. Takes me a few hours each month to work out the exact COGS for each invoice. Please help to make this process easier.
I would urge you to Combine Report Module Requests here to maybe get some attention from Dear.