In this 2 part article we will give
you a checklist that you can use to check your auditor’s data (and that of
other sources) before you use it for freight RFP’s. Here is part 2 of the
checklist.
Check 5 – Data quality
In the data quality checking
process you need to review your historical shipment data sets against
standardised data tables. It is important to ensure that a number of key data
fields are showing 100% harmonized data. For example, make sure that your origin
& destination countries are all stored in a harmonized way (Full ISO
country name or 2 digit ISO country code). Also ensure that place names are
consistently spelled exactly the same way. There are few things more annoying then running
a query across a data set only to find that the result is incomplete due to spelling inconsistencies in the data.
Inconsistent data (e.g.
misspelled place names) can lead to big errors in RFP’s. Carriers will be able
to tell you how often they have been asked to quote for the same trade lane 8
times because a name was spelled in 8 different ways. If you use a FAP vendor
you need to agree on rules and responsibilities when it comes to data quality.
What do you accept to be imported in your data set and who is responsible for
picking up on the errors? Is your own organisation at fault for providing wrong
data in the booking process, is it the carrier who keys it wrong or should the
FAP vendor be made responsible for cleaning and harmonizing the data? If you do
not figure this out up front it is you (the client) who ends up cleaning a data
set when you want to go out to tender.
Check 6 – Missing data
FAP data sets do not always
need all details for the Freight Audit process. This means that shipment data
might have data gaps. For example, your agreed rate with your carrier may be
based on country to country. This means that the carrier invoice for the
related shipments will most likely not contain details such as zip/postcode,
state or place name. Information that is not relevant to audit the shipment,
but it might be essential data for your next RFP project. When you create your
shipment profiles, this level of detail could make the difference between
getting a competitive quote and a bad quote from your invited carriers.
When you work with a FAP
vendor make sure you perform regular checks on missing data. If essential data
elements are not captured in the process you need to address this with both the
carrier and the FAP vendor. Otherwise you might end up with an incomplete data set. Augmenting the data at the back end of the process is time consuming and at times impossible if you are no longer able to link data sources because of missing data elements.
Check 7 – Oddball data
Oddball data is data that
seems out of place in the historical shipment data. For example zero weight
fields (weight displayed as “0” or null), or excessive weight (e.g. parcel
shipment of 20000 KG). In a data set that comes from a FAP vendor these types
of oddball data elements will be present. In the audit process the related
shipment costs may have been corrected, but the wrong shipment data is not most
of the time. When you download your data to use for a RFP you need build in a validation process or even manually
perform a check on these types of oddball data items. If you don’t you may end
up using this type of data to e.g. calculate your average parcel
weights. Or you end up using the incorrect (weight)data to calculate the shipment costs based on the new carrier quotes leading to incorrect analysis - either overstating the new costs or even more dangerous - understating the new costs. Oddball data can impact your
shipment profile and analysis baseline significantly. It may misrepresent your
shipment profile to the carriers and/or it may impact your ability to analyse
the RFP results.
Check 8 – Cost data split
The more detail the better
is the mantra for Freight RFP’s. This is especially true when it comes to the
cost data part. The more detail that is available the more analysis tricks can
be applied. For a RFP it is preferred to have base freight, discounts, fuel
surcharges, all accessorial charges and taxes and duties split as separate
items per shipment. In most cases FAP vendors are perfectly capable of
delivering data this way. There are a few things to watch out for however.
§
Understand what is included
in “freight” – some carriers have a number of costs backed into the base
freight cost item. You need to understand what is included and excluded to be
able to make an apples for apples comparison.
§
In the rate agreement some
carriers may have split the accessorial charges from the base freight, but on
the invoice they may present the cost as a lump-sum amount. FAP vendors will be
able to audit it this way in most cases, but it will present an issue in the
data set. When this happens you need to work with the carrier to ensure that
the costs are split as separate items on the invoice. It also happens that FAP
vendors log the costs as a lump-sum in the system to lower processing costs. Here
you need to accept lower processing costs and a bad data set or accept higher
processing cost but better data which may result in better quotes from your
carriers.
Check 9 – Calculations
Last but not least, check
the calculations in your data set. If you have downloaded your data to MS-Excel
format it is easy to perform a few quick checks. Especially data coming from a
FAP vendor requires some attention. The data set will most likely contain
billed and paid cost data and these can differ. You need to ensure you use
right data (the paid costs data is mostly the most accurate). In the US paid
data is often the source to use, because of the “short pay” process (deduct the
over-billed amount from the charged amount when paying the carrier) that is in
place. In Europe and APAC however short paying does not apply in most cases.
Here billed and paid may be shown different but the paid data may be offset further
by a credit note for which a separate invoice line is created. In your data set
this can look very confusing and can lead to misrepresentation of data if it is
not interpreted is a correct way.
It is therefore always useful
to perform a sense check on your data (did we really spend this amount for
Parcel this year?). Besides that, quickly do the following checks to ensure the
cost data columns and rows add up correctly:
§ Do base freight plus fuel surcharge plus accessorial charges add up to the total shipment cost?
§ Are you sure that the total shipment cost excludes taxes (VAT)?
§ Do the totals of the individual cost columns add up to the total of the total shipment cost column?
Some conclusions
Freight Audit & Payment
companies are a great source of actual historical shipment & cost data. But
don’t take things at face value. The quality of the data largely depends on
the work you put into it up front. Work with your FAP vendor and your carriers
during the FAP process implementation to get the most out of your data. Give
clear instructions to your carriers on what data elements and what level of
detail you expect to see on the invoice. Also make sure you understand the
limitations of your FAP vendor’s database and reporting system. For example, your data
requirements may exceed the available data fields in your vendor’s database. During the selection of your FAP vendor you need to get a good idea if
your required fields can actually be captured. And even more importantly, if they can be reported on in the way you need.
For FAP vendors checking on
data quality besides checking on financial items will become an every greater
requirement from clients. The promise of data availability on a single platform
is great, but this is only as valuable as the quality of the data in the
database.
Most steps in the checklist
we presented apply for shipment data coming from all sources by the way, not
only data provided by your FAP vendor.
No comments:
Post a Comment