This article is relevant if you are seeking to update actual shipping costs in NetSuite and accrue for freight expense.
Background
In a recent implementation, a client asked us to help them capture actual shipping costs on their dropship orders. In their model, they asked their dropship suppliers to use their United Parcel Service (UPS) account for all shipping costs; this method of tracking shipping costs is generally called “third party account” billing. Thus, in our client’s model, the Sales Order linked Purchase Order which is distributed to the dropship vendor only supplies the product line information — shipping is not specified on the purchase order as a purchased cost because the actual cost will be recorded on our client’s UPS account.
In the native NetSuite dropship flow (note, readers may be interested in the challenge of properly measuring margin in my related 2016 article, Solved: NetSuite Drop Ship Purchase Accruals), when the supplier ships the goods based on the linked Purchase Order, an item fulfillment is generated off the respective Sales Order. A packaging list is updated to record all the tracking numbers which are used to inform the customer that the goods are now in transit.
Our client wished to capture the actual shipping cost on the item fulfillment record to help a) track the cost of shipping with respect to the sales order and b) accrue the freight expense.
NetSuite Automated Shipping Costs Updates
In the original idea of the project, we envisioned automatically going to the UPS website nightly via their API to pick up respective shipping costs. After much back and forth, we discovered that our client’s “net” shipping costs, which is a negotiated rate, are not available in the UPS API feeds. This “net” amount is supplied via UPS invoice records and not the shipping record feeds supplied by the API.
We then reverted to Plan B. The client was able to generate a CSV extract of the invoice costs that referenced the tracking numbers and offered the “net” shipping costs we needed. Under this course of action, we recognized that NetSuite’s native CSV tool will not support the lookup of the tracking number(s) to find the item fulfillment record to update the shipping costs.
Click the image to better understand the CSV file layout.
In my 2022 article, Update Unsupported NetSuite Records with CSV, I demonstrate how we can update NetSuite records using our Prolecto Record Import/Export Manager (RIEM) framework.
Using this license-free tool (no charge to Prolecto clients), the client does not need to manipulate or pre-process the native CSV supplied information by UPS. While the client could have dropped the file into a specified folder so that it could be automatically picked up by a scheduled routine, they instead elected to email the file to a NetSuite-provided email address. In either case, the framework provides the power needed to look up and update records while also offering proper reporting and controls for high-performance updates.
Note, when working with NetSuite’s native shipping costs field on the item fulfillment record, I urge readers to review my 2019 article, Understand NetSuite Shipping Revenue, Costs and Margin Review to respect the implications on invoicing.
Using SuiteGL to Record Freight Expense on Item Fulfillments
Once we had the shipping cost recorded on the item fulfillment, we had an opportunity to book an accrual. In this case, we can use SuiteGL to record the following:
Dr. Freight Expense Cr. Freight Expense Accrual
This will then automatically capture the expense in the right accounting period and record the obligation. Under this method, we must be conscientious to capture our UPS shipping costs before we close the accounting books.
Once the vendor bill from UPS is supplied, which can now be after the books are closed, accountants now have the option to reconcile the bill. The idea is that the UPS vendor bill, when entered, will produce this accounting:
Dr. Freight Expense Accrual Cr. Accounts Payable
Finally, another approach would be to generate a purchase order against each of these shipments — that would set up the accrual and support the vendor bill processing without the need for SuiteGL. We discuss this method in my related 2021 article Drive Automated Estimated NetSuite Landed Costs from Purchase Orders, which can instead capitalize the cost of shipping into the dropshipped goods.
Multiple Ways to Record NetSuite Shipping Costs
While this client’s approach was to work with their dropship supplier on their third-party freight account, there are other methods. Ideally, stock-based shipping costs should be captured at the time of shipping with an integrated shipping system. In my 2021 article, Learn How To Markup NetSuite Shipping Costs by Percentage, I offered how capturing the actual shipping costs can be leveraged to create a profit center.
The key is to capture information as soon as possible to help drive the accounting matching principle (line up costs and revenue in the period incurred) and to build good controls.
If you found this article relevant, feel free to sign up for notifications to new articles as I post them. If you are ready to address the capture and accounting of your NetSuite-driven shipping costs, let’s have a conversation.
Marty Zigman
Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.
More Posts - Website - Twitter - Facebook - LinkedIn - YouTube
