Creative Solution for shipping broker operations in D365FO

Standard Landed Cost Module:

Below you can see the standard landed cost module structure by design. This is not working for a shipping broker scenario where an additional 4th party is coming into play - the shipping broker. The shipping broker is the middlemen between the ship owner, the shipper as well as the shipment receiver.

Creative workaround:

Below you can see a creative workaround to make the landed cost module a fit for shipping broker (middlemen) operations that want to track voyages but do not maintain inventory per se as just facilitating the negotiations/contracts between shipper, ship owner and shipment receiver.

Visio flow:

See below a full detailed visio flow explaining the workaround step by step. In the section further below, each step is broken down in detail. (in case the screenshot is too small, see further down a break down of the visio flow)

Configuration:

• Set up standard landed cost module - See details in a previous blog post under: https://www.dynamicsduoacademy.com/post/d365fo-new-landed-cost-module-release-10-0-18-auto-costing

• Set up Shipment receiver as customer. (party that receives the shipment and pays the freight plus broker charges)

• Set up Shipment receiver as dummy vendor record. We will later create from the source SO a PO as voyages and landed costs drive of POs only at this point. (not of SOs) The PO transaction will not create any payables as we set the item purchase price of the commodity to be shipped to 0. We simply use the PO later to tie a voyage to a PO and track the voyage along the way.

• Set up Shipowner as vendor records. (party that facilitates the shipment and gets paid a flus through charge by the broker means us)

• Set up 1099 Broker as vendor account (only if outsourced broker payments are handled via POs, if handled via vendor invoice journal and payment than neglect this step.) (party that handles the contract negotiations between Shipowner and Shipment receiver and gets paid a percentage of the contract as commission)

• Allow negative inventory at warehouse level/released product level via item model group

• Set up charge codes for e.g. initial freight and broker commission (means commission share we will get) and other charges that may apply (AR side) - In this scenario we simply go with those 2 to keep things simple.

  • Initial freight debiting AR - Freight account and crediting the customer/vendor account.

• Broker Commission debiting Accrued Revenue and crediting AR Freight. That means we are taking a cut of the initial freight charge and accrue it to our revenue. (our cut) The remaining part of the initial freight will be flushed through to the ship owner. Also, later a percentage of our revenue cut will get paid to the outsourced 1099 broker.

• Set up cost type codes for e.g. freight due to the shipowner and other service set cost types required (AP side)

  • Initial freight due to owner debiting Freight - Outbound and crediting the vendor account.

• Set up released product for commodities that will be shipped with 0 purchase/sales price (we later create payables/receivables primarily via charges/cost types as we do not physically purchase/stock the product to be shipped anyways)

• Set up AR auto charges for charges sets on AR side (Sales and Marketing). We set such up to make things more efficient - means every time we create a SO for a contract those charges will default in at the header level and we only need to enter the final data points for initial freight and broker commission. This comes in handy if there are repetitive charges for certain shipments.

• Set up AP auto cost types for service sets on AP side (Landed cost) We set such up to make things more efficient - means every time we create a PO/Voyage for a contract those charges will default in at the header level and we only need to enter the final data points for flush through charge to be paid to the ship owner.

• Set up broker commission service item as released product (this is only needed in case we use POs to pay our outsourced 1099 brokers, if we only use invoice journals as well as standard payment proposal we can skip this step)

Process flow - Demo:

See below the first part of the above visio flow.

1. Cargo negotiation outside of D365FO.

2. Manual sales order creation for customer.

As soon as the contract is finalized a SO is created. We use the above configured customer account. We enter the commodity product record to be shipped on the SO lines. This product record has all necessary commodity and shipping codes associated/maintained at the product level. If needed we can attach any contractual terms as attachments and add additional information if needed such as fin dim defaults. For now, we ignore such cherries on the cake. You can see that the SO has a 0 unit price as we set the default sales price for the commodity released product record to 0. That means, we do not create any receivables at this point here.

3. Auto charges (AR Service sets) to be populated with actual initial freight and broker commission (our cut) values:

As explained above via auto charges setup at the header level for customer "Broker customer", the initial freight as well as broker commission (our cut) charge codes default in.

Such default in however with a 0 charge value. We now update such depending on the negotiated contract.

Let's assume the initial freight to be received is 100 USD and our cut is 25 USD.

The charges code for initial freight is set up to debit the customer/vendor type and credit the freight ledger account. The charge code for broker commission is set up to debit freight ledger account and credit revenue ledger account.

4. SO confirmation:

As soon as all data elements are entered, the Sales order is to be confirmed.

See below the SO confirmation process.

The operation processed successfully.

5. SO packing slip posting (batch or manual):

As a next step we post the packing slip that decrements the inventory. As we allowed negative inventory, we are fine to do so here. The packing slip will be posted manually. (but this can also be set up as batch job if needed).

See below the SO packing slip posting process.

After processing the SO changes its status to delivered.

6. Creation of purchase order for shipper at 0 cost via “Create Purchase order from Sales order function”:

As voyages run of POs in D365FO by design (designed for an organization handling inbound shipments via broker – so, not directly designed for broker operations as “middlemen”), we will create a dummy PO as workaround. The PO will be created directly from the SO via “Create Purchase order from Sales order" function. We will have a separate vendor record for our/the smae customer from the original SO to facilitate this process. (Workaround) As soon as the function is triggered, we specify the dummy “customer” vendor record and confirm. Now, a PO is created for our “customer” vendor record with the same commodity product, quantities, warehouse/site information, etc. on the PO lines. The item will again have 0 item cost and no charges are applied here – that means no vendor balance will be accrued at all. All transactions will be posting with 0 financial impact. SO and PO numbers are tied together on the line details via reference field – you can view such on the line details under Product/Item reference and reference type and number.

See below that a PO for the SO was created successfully.

7. PO confirmation

As a next step, wee confirm the dummy PO as required by design on the PO side.

8. Voyage creation for the just confirmed PO:

On the “all voyages” form, we create a new voyage and enter information such as vessel, journey template (point to point, or multi leg cargo), mode of delivery, shipping company (means Shipowner) and BOL information if we are already aware of such. We can add such also at a later point if not available at this point in time. We can alternatively also create a voyage straight from the all POS form via "create new voyage" function.

We went the route via "create new voyage" function on the PO form. See below.

On the next form, the ”voyage editor form”, we will search for the above confirmed PO via query. We select the lines on the PO and add such to the staging list and afterwards to a shipping container. Those steps are required by design. This feature can be very helpful in the scenario of a cargo that contains multiple shipments (e.g. when rented a fill container/ship)

Adding the voyage to the staging list was successful.

We are adding the voyage on staging list to a new container.

We enter details on container information and confirm.

Note, just as a recap: The voyage is tied to the originating PO. You can view such on the PO on the line details under Landed Cost/Voyage.

The original SO is also tied to PO - See under line details.

The above references are important for reporting and tracking the contract end to end. I would usually recommend going with a PowerBI reporting solution here to ties such together and have them visible on one grid with status accordingly. But that is a topic for another blog.

9.Auto cost types (AP Service sets) to be populated with actual flush through freight due to owner:

Cost types for freight due to owner is set up. The cost type is debiting the freight ledger account and crediting the vendor account. Short, is creating an invoice balance to be paid later to the vendor. Auto cost set up will default such AP cost types automatically in at voyage creation at the header level. This can be set up based on ports, shipping company, mode of delivery, etc. That said, Now on the voyage itself, we navigate to “Voyage costs” on the Actionpane and populate the cost that is to due to the shipowner manually as it will default in with 0 cost.

See the cost type defaulted in with 0 cost.

We populate the cost manually/accordingly. Here: 75 USD as flush through as we had a 100 USD initial freight receivable prior on the SO side wherefrom 25 USD are our commission/cut. So 75 USD will be fleshed through to the ship owner for shipping the voyage from A to B.

See below the second part of the visio flow.

10. Voyage processing (up to “ready for costing” status):

As a next step, we can maintain information such as Bill of lading or Master Bill of lading, Measurements and Measurement units (e.g. weight), Departure time, Estimated delivery date, Actual delivery date, ETA at shipping port, Date when original documents are received, Broker advised date field, Goods released date, Verification date, etc. on the voyage itself and track the voyage with dates for each of the logistical steps (legs) it goes through. In our case it is simply point to point to keep things simple. Additionally, there is a remarks field what can be used free text. (if required) A user can use document handling to attach emails or documents to the voyage if needed.

We can advance the voyage through multiple stages depending on what documents/information are provided and where the voyage is from a logistics standpoint (e.g. “documents received, in transit, ready for costing)

We process the voyage through the natural cycle - means up to "ready for costing".

11.SO Invoicing:

The sales order that we initially created is still in “Delivered” status. As a next step we will invoice the SO. The SO invoice just shows the commodity item with 0 cost and just shows the charges that hit the customer account (means here Initial freight). It won't show the broker commission charge as we set the debit/credit posting to ledger posting main accounts accordingly. see explained above. Short, the customer will never see our commission percentage/cut on the invoice that he/she will receive.

See below the invoice processing process.

As explained above the invoice amount only the charges for the initial freight - here: 100 USD.

The invoice can now be send out to the customer for payment.

12. Customer Payment journal:

As soon as the payment is received, a customer payment journal is leveraged to record the payment. This credit the invoice balance and debits the bank account.

After creating the journal we add lines for the above invoice, settle the transaction and post.

13. Purchase order receipt:

As a next step we receive the dummy PO via all voyage form. (0 cost) This increments the inventory and we zeroed out the inventory deduction prior processed via SO packing slip posting.

See below the PO receipt process.

14. Purchase order invoice:

As a next step we invoice the dummy PO via all voyage form. (0 cost) As the cost is 0 and the PO is in its final status “Invoiced” and had 0 financial impact, we can consider the PO as completed.

See the net amount is 0.

See the PO invoice posting was successful.

See that 0 payables were created.

15. Vendor invoice journal:

As a next step, we create manually a vendor invoice journal for the voyage and the payable to the ShipOwner.

On the lines we leverage the "select voyage cost" function to allocate the voyage cost accordingly.

16. Allocate actuals via “Voyage cost function”:

On the Lines of the above invoice journal, we add the Shipowner as vendor account, populate the credit and leverage the “Voyage cost function” to first select the voyage and then allocate voyage costs accordingly and tie lastly the voyage to the vendor invoice journal. This function is very helpful if there are multiple cost type lines.

In our case it is just the 75 USD flus through cost to be allocated.

After confirming you can see that the voyage number is now referenced on the vendor invoice journal line. This ties again in what I addressed further above - short a full end to end visibility of inventory and financials with status.

17. Post vendor invoice journal:

As a next step, the vendor payment journal will get created and posted - this debits the freight ledger account and credits the ledger account for accounts payable.

18. Voyage processing to “costed status”:

Now, we flag the voyage to costed status to complete the voyage process and keep things clean.

See below that the status of the voyage changed to costed.

19. Create and process vendor payment journal:

Lastly, we create vendor payment journal as soon as we paid the shipowner (debiting ledger account for accounts payable and crediting the bank account). Pay when paid is enforced through the whole process by design/nature.

On the lines we select and settle the correct transactions for shipping vendor accordingly.

Now, we post.

20. Vendor invoice journal processing for “Broker” vendor:

As an add-on if we have 1099 outsourced broker that get also a cut of the pie we would create either a PO with broker vendor account and with a broker commission service item on the line or we go simple and simply create and process a vendor invoice journal. As soon as the Shipowner is paid, we will create a vendor invoice journal for the broker. The posting profile for the vendor will be set up to credit the broker commission account (our cut) and debit the 1099 broker commission account (outsourced brokers).

Additional information:

• Contract management (means multiple voyages for same client) can be handled via “Copy all” function on the “All sales orders” form – This allows us to copy a SO. Such copied SOs will not be tied together but you can create them ahead of time and flag them as contract by repurposing a header field such as channel if required. Additionally, we can also use sales agreements to create multiple sales orders for contracts.

• For some of the above data entry points we can use OTB excel add-in functionalities and entities. If not OTB available, we can add custom entities/template to support such data entry on certain forms via excel-add in.

• Use of PowerBI to create a dashboard that shows to which PO and SO a voyage belongs an dalso see payment status (support pay when paid). Reference links are created by D36FO automatically by design.

• Food for thought – Use of RPA to process information that is received via email through AI builder and automatically enter it via unattended RPA and PowerAutomate Desktop in D365FO.

• Use of Workspaces and embedded PowerBI reports for Open SO, PO and Voyages as an option.