Rates DB Air (Requirements)
From UG
Info
- mantis# 0002894: [Rates DB] Implement phase1 for MOT Air
See also: http://ct.jaguarfreight.com/wiki/Rates_DB
Glossary
Actual Gross Weight (GW) is the actual weight of the shipment
Volume Weight (VW) is calculated based on a cargo's size, measurement or dimensions
formula for calculating Using the English System: L x W x H in inches (in.) / 165 = vw (in lbs.) Using the Metric System: L x W x H in centimeters (cm.) / 6,000 = vw (in kgs.)
Chargeable Weight of cargo intended for air shipment is the actual gross weight (gw) or the volume weight (vw), whichever is greater.
Unit load device (ULD) is a pallet or container used to load freight on an aircraft. It allows a large quantity of cargo to be bundled into a single unit.
- ULD pallets are rugged sheets of aluminum with rims designed to lock onto cargo net lugs
- ULD containers, also known as cans and pods, are closed containers, which, depending on the nature of the goods to be transported, may have built-in refrigeration units
Drum is a cylindrical container used for shipping bulk cargo (a liquid or as a mass of relatively small solids like grains). Drums can be made of steel, dense paperboard (commonly called a fiber drum), or plastics, and are generally used for the transportation of certain dangerous materials or modest quantities of bulk goods.
Known Shipper A shipper that is qualified by specific regulations as a known shipper. A known shipper has less restrictions for shipping and as a result will usually have lower rates.
Unknown Shipper A shipper that has not yet been qualified according to the Transportation Security Administrations regulations as a known shipper. An Unknown shipper's cargo has more restrictions when shipping and therefore may have to pay higher rates to ship cargo.
Hazardous Freight is freight that is capable of posing risk to people, health, property, or environment when transported in quantity. AKA dangerous goods
Weight Break is the point at which the weight or chargeable weight of a shipment is large enough to be assessed the lower freight rates which are applicable to larger shipments, this is as per the carrier's tariff.
Volume Cut is discount offered for voluminous cargo. It is the same concept as weight break, but has its own formula to calculate.
If you have freight that is 100 Kilos and the chargeable weight is 200 Kilos, the co-loader or airline will offer 10.00 per Kilo with a 50/50 volume cut This means that you will subtract the actual weight from the chargeable weight and divide the sum by 2. This is the amount you will be paying for. (200-100)/2 = 150 Kilo's Next you will need to break this down into an actual per kilo cost. You will do this by multiplying the 150 Kilos by the rate offered of 10.00. 150 X 10.00 = 1,500.00 Then dividing the sum of this by the chargeable Kilo's to get the actual per Kilo cost. 1,500.00 / 200 = 7.50 per Kilo *This volume cut can vary and may not always be 50/50, it could be 25/75, 40/60, etc...
Minimum (Min) is the lowest amount that can be charged for the service.
Maximum (Max) is the highest amount that can be charged for the service.
Flat Fee is a single fixed fee for a service
Spot Rate is the rate that is quoted for an immediate shipment, as per the shipment specifications, and is not part of a contract.
Summary
What are we trying to achieve? What are the main goals of this module?
Core need
See Rates DB.
Core functionality
See Rates DB.
Detailed Requirements and Related Information
Charge codes related to Rates DB Air
Question
- For each existing charge code:
- define meaning
- provide formula
- give example
- If CC list will be different after Rates DB is added go over all changes in details.
Answer
File below is cc air.xls attached to 0002894: [Rates DB] Implement phase1 for MOT Air
Current business process
Questions
- What is the current business process - How Jaguar deals with Contracts and Tariffs (in each office) now?
- Who is in charge for this in each office?
- Please describe "A Day in the life of Inside Sales Dept NOW"
- In what form existing Air contracts received and maintained ?
- How requests for rates handled (receive, search, answer)?
- What is the typical Request format?
- What is typical Response format?
- Sounds like most of these are "spot rates" received by phone call? Is that right? Do we ever in a response to a phone call ever receive "entire contract" by email instead of just rate from A to B for specific cargo and dates?
Answers
Since Air freight shipments move quickly, when you are contacted for rates, you must provide the rates as quickly as possible. Also, Jaguar Freight does not handle a large volume of air freight shipments and are not currently IATA certified. These are a couple of the reasons, that we do not have many tariff's in place
NY office
NY office has a special group called "Inside Sales".
- Steve - manager
- Sylvia - handles mostly ocean, gets involved into ocean contracts, main user of Rate Explorer?
- Mary - handles ocean, air
They are responsible for providing cost's to our Outside Sales team, operations team, and overseas offices. In addition to obtaining costs for internal employees, they also quote out cost's the customers.
Currently, requests are usually received verbally (in person), via email, or via telephone.
Once the request is received, the inside sales team will begin to research the cost. They begin by determining if the specifications of this shipment are covered within already existing airline tariffs.
This is done by checking 1st that the airline has flight from and to the desired airports. Once this is confirmed, they still need to make sure that the airlines has the room required (over sized freight can only shipper on freighter not a passenger aircraft), accepts the specified commodity type (certain kinds of hazardous freight cannot ship on a passenger aircraft), and accepts freight from this shipper type (Unknown shippers cannot ship on a passenger aircraft).
If they find multiple airlines fitting these specifications, they will determine the best rate and service level. This is the information they will use to quote out the air portion of this rate.
If it does not, they will then research all of this information again using an existing co-loaders tariff. Once again, if multiple rates are found they will select one based on price and service comparison and use this to quote out the air portion of this rate.
If the shipment specifications are unusual, large, or are not covered under any existing tariff, they will email or call the airline or co-loader to find someone that can give them a cost. They will complete the total cost's of the shipment by calculating the rates for any additional services required. (pickup, delivery, Customs, etc...)
In addition to the inside sales team, operators, sales, and management are also involved in obtaining cost's.
Everyone seems to be handling the management of the rates that they obtain in their own way. Some keep them saved in their emails, some keep them saved on their desktop, and some have the rates printed out and keep paper copies.
....
NY office has tariffs in place with 4 airlines and 3 co-loaders. Steven the inside sales manager intends to increase this in the future.
London office
The London office has three main contacts for Cost/Sell rates.
Vikesh - Sales
Ginny - Account manager
Anshu - Inside Sales
The London office currently does not book freight directly with any airlines, they only book with co-loaders. They have no airline tariff's in place at this time.
When they need to cost out an airfreight shipment, they always handle this on a spot rate (case by case)basis. This is because they do not handle a high enough volume of air freight shipments. Also, they are afraid that they will be locked into using these co-loaders for the additional services (delivery, Customs, special handling, etc....). They are using two co-loaders Hook and LS Express. Every time they receive a request for rates, they call or email the co-loaders for a rate.
EXAMPLE OF THE TYPICAL EMAIL EXCHANGE FOR OBTAINING RATES TO BE ADDED.
Paris office
Currently in the Paris office we have three main contacts handling rates Sanja - Account Manager Wassila - Account Manager Inma - Inside Sales
The Paris office request's that rates are received as an attachment to an email. They are received in various forms such as word docs, PDF's, and excel sheets. When the rates are received Inma saves the file in a shared drive, so the whole office can access. All operators in the Paris office are able to access this and quote out cost's according to these sell rates. In this shared drive they have general contract rates as well as specific contract rates. The general contract rates are rates that are provided to everyone by the airline. These rates are usually very high and not usually used. When you begin to offer the airline more volume to specific destinations, they will give you contracted rates for these specific moves. If they need to quote a shipment, that they only have general contract rates for, they will call the airline to negotiate a better rate. Once this rate is agreed on, they will request the airline to email these rates to them and add it to the shared folder.
HK office
They do not currently have an inside sales team, but will have at least one person to handle this in the future.
Right now the main contact for rates in Hong Kong is
Canny - Account Manager
all operators are able to quote costs and sell rates.
Currently Jaguar Hong Kong does not book shipments directly with any airlines. So they do not have any airline tariffs. This is because you must be able to guarantee the airline a large volume of freight, in order to secure a good rate.
They are using the following 3 co-loaders. These co-loaderd are building weekly pallets (ULD's) and are able to obtain much better rates with the airlines.
- AIF for Taiwan rates
- Topfor for China rates
- A-sonic for Hong Kong rates
The rates are being managed and maintained by a spreadsheet that was created by the Hong Kong office and is updated by the co-loaders. The last version of the spreadsheet is emailed to the co-loaders, the co-loaders will update any necessary information and email the spreadsheet back.
Although they are receiving monthly versions of this air freight tariff, they continue to email the co-loaders on a case by case basis for costs. The spreadsheet is maintained only as a reference tool for the other Jaguar offices, when an estimate is needed and they are unable to contact the Hong Kong office.
This is because the rates change frequently. The individual rate requests are handled mostly via email exchange.
Future business process
Questions
- What is the vision for the biz proc in the future?
- A Day in the life of Inside Sales ONCE SYSTEM IS IN PLACE.
- Explain vision of every office.
- This should be formulated by person responsible for this area(like Steven in NY)
- How will we manage tariffs/cpntracts in the system (how to populate? how to update? expiration management)?
- What is a suggested Format of request (for rates) and response?
Answers
NY office
TBD
London office
TBD
Paris office
Philippe's vision for the rate database
I would say , as far as airfreight is concerned that we have already a database in Paris : we compile all rates given by air carriers , carrier per carrier , and put all in a common file ( everyone in the office could have access to ) . So , as soon as we have an access we can start to enter all air rates to the CT database . If every office is able to provide airfreight export rates then ,we should be able to get something consistent in quite short time .I think we just need to enter carrier’s name , carrier’ prefix ( 057 for AF as eg. ) , reference of the tariff if any and airport of departure and arrival ( with already existing airport codes ? ).
London is dealing with an air agent so I have to check with Anshu if she has compiling some rates ( at the opposite, in Paris , we are dealing directly with carriers as you in NY ) but she is off ‘til next week . Anyway , I think this is a Isales job and I would like Anshu leading this process for all Europe . I need to talk to her when she is back .
One idea could be to have access ( as rate explorer for eg. ) : enter an airport of departure and airport of destination and see all carriers they propose rates for such trade .
We have to deal with all taxes ( fuel , IRC ,sec ) because they are not all the same per carrier , per country and this is the main topic , I think . I would suggest to deal directly with Sanja for that .
This is for our buying costs . For the selling rates , I think we have to enter it into the database as per client’s name . And if possible link it with CT operational system . As an example , you open and air file for EA from Europe to USA and depends of the trade concerned , you already have the agreed tariff directly on the billing page . CT file has already name of client , departure and arrival airport , gross and chargeable weight .
This is the first step , please feel free to give me your comment and if needed we can talk on Skype for a meeting .
Thank you
HK office
Chikei's vision for the rate database:
In the short term: As same as we are currently doing for rates ex Asia to Europe and USA, we exchange rates information through an password protected excel spreadsheet. The objective would be to have a quick access to buying rates information and provide quick quotation to customers. However the limit of this is that airfreight rates are very volatile in some period of time (e.g. peak season) and rates can change from a day to another. Handling a spreadsheet is not flexible enough to provide daily update.
In the long term: Indubitably, it will be through a platform (can be Cybertrax?) where we can maintain and share a database and where the data can be extracted for other purposes such as billing, selling rates, quotation,….etc. The objective is the same as above with some other purposes or applications but having a platform would increase the update frequency for certain main trade lanes and possibly integrate the assessment of ‘net/net rates’(net of volume cut*).
- I would explain the volume cut principle in due course
Various types of Contracts and Tariffs And How to calculate rate from point A to point B
Questions
- Provide examples of contracts/tariffs used by Jaguar.
- At least one example for each type.
- Provide example of how to manually calculate rate for specific request.
- Attach actual contract/tariff into mantis. Post screen shot into wiki and explain.
- If possible classify all types of existing Air tariffs.
New York office Examples
Singapore Air contract
It comes as an xls file with two tabs. See below. (Original file Singapore Air NYC Rates Apr 1 - Sep 30 2011 example rate sheet.xlsx attached to 0002894: [Rates DB] Implement phase1 for MOT Air ).
The attached spreadsheet is an example of one of the airlines tariffs.
The top of the page shows you the airline, the commodity (general cargo), any exclusion or notes and the date range these rates are valid for.
Table:
Column A is the origin of the shipment.
Column B is the name of the destination.
Column C is the Country of destination.
Column D is the 3 letter destination airport code.
Column E is the commodity type line.
Column F is the 3 letter commodity type
Column G is the minimum air freight charge for the origin and destination pairing (column A and Column D)
Column H TBD - (The – 45 kilo rate. This is the rate per Kilo from 0 K to the next weight break, in this example it is + 45 Kilo’s.
So this rate is for 1 Kilo – 44 Kilo’s, as long as the total is higher than the minimum rate.
Column I - is the + 45 Kilo rate. This is the rate per Kilo over 45 K and under the next weight break (in this example the next weight break is 100 +, so this rate is for 45K – 99 Kilo’s, as long as the rate is higher than the minimum.
Column J – Is the + 100 Kilo rate – following the same rules as Column I.
Column K – Is the + 300 Kilo rate – following the same rules as Column I.
Column L – Is the + 500 Kilo rate – following the same rules as Column I.
Column M – Is the + 1000 Kilo rate – following the same rules as Column I.
Using this drop downs on the spreadsheet, you can set filters to view multiple selections or narrow it down to just one line.
Tab 2
Example 1:
(Setting column A to NYC and Column D to AKL for a shipment from NY to New Zealand)
If you have a shipment of boxes that is a total of 5 cartons, total 20 K, and total 45 K chargeable weight going from NY to New Zealand.
You would use the dropdown to set as much information as possible.
I set A to NYC, D to AKL, and E to general. This narrowed it down to one line where I can see my rate for this shipment.
To calculate my rate I take my total chargeable Kilo's (45) and check the tariff according to this weight break to find which rate would apply to this shipment ($3.85). Next I compare this total with the minimum ($125.00) and take the higher rate as my cost.
45 x 3.85 = $173.25 (which is higher then the minimum of $125.00, so $173.25 is my cost)
Solent co-loader tariff
- 1- This is the company name of the co loader
- 2- Indicates that you can call for shipments not covered in this tariff.
- 3- Indicates what is covered by this tariff and what charges will be additional. ( Ex. AWB fee is included – Fuel and Security is additional )
- 4- This is the bottom tab of the excel sheet, indicating that these rates cover shipment departing from NYC
- 5- Rates
- a. Destination Column – arrival City
- b. Code – Airport Code
- c. Column C is hidden
- d. The rate per K from 0-99 for Known shippers only
- e. The rate per K from 100-299 for Known shippers only
- f. The rate per K from 300-499 for Known shipper only
- g. Column G is hidden
- h. Column H is a space holder
- i. The rate per K for 0-99 K for Unknown shippers
- j. The rate per K for 100-299 for Unknown shippers
- k. The rate per K for 300-599 for Unknown shippers
Email exchange for rates
- 1- The bottom portion of the email, you will see that Gabrielle (NY Export Operator) is requesting an air tariff from Aeronet (Co-Loader).
- a. You will see that she indicates she needs the rate going to TPE (this is the airlines code for Taipei).
- b. She also indicates that this is ID8000. This is because she is looking for a rate for cosmetics. Cosmetics can be considered Hazardous, but this commodity does not need a hazardous UN number. She is advising the co-loader that the freight is ID8000 because some airlines will charge a hazardous fee for this and some will waive the hazardous fee.
- 2- This is the reply – the rate tariff for this specific lane,commodity, with weight breaks and minimum indicated.
Air France rates
- 1. Airline company logo
- 2. Agreement Ref. #
- 3. Agreement Ref.#
- 4. Customer (our) name
- 5. Shipper (only will be filled in if the rate applies only to a specific shipper
- 6. Airline contact name
- 7. Two digit airline code
- 8. The date in which the rate becomes valid
- 9. The date in which the rate expires
- 10. The three digit airline code for the departure airport
- 11. The currency the rates are in.
- 12. Rate information
- a. Indicates the three letter airport of arrival.
- b. TBA
- c. Direct flight or multiple stop flight, if filled in, would indicate if the flight was going to stop in any other airports before the final airport of arrival or not.
- d. Indicates that these rates are for passenger aircrafts. It is important to note that because some freight cannot be shipped on a passenger aircraft.
- a. Unknown shippers
- b. Hazardous freight
- e. Would note the flight number if this was for a specific flight.
- f. Would indicate how frequently this airline moves passenger aircrafts from the departure airport to this airport of arrival. Usually noted per week.
- g. This indicates if any minimum weight is required. If a minimum weight is required, it would work the same as a minimum charge, this means you would have to pay for the minimum kilo’s indicated here.
- h. Minimum charge per shipment
- i. The per kilo rate from 100 – 299 kilos
- j. 1st weights break at 300 plus kilos. This rate is valid from 300 – 499 Kilos
- k. 2nd weights break at 500 plus kilos. This rate is valid from 500 – 999 Kilos
- l. 3rd weights break at 1000 plus kilos. This rate is valid from 1000 – 2999 kilos
- m. Last weights break at 3000 plus kilos. This rate is valid for 3000 kilos and higher
- n. This is for a high volume of freight, for the same lane, moving weekly or multiple times in a week. It would indicate the percent or amount of freight we would be guaranteeing per week in order to obtain a better rate.
- a. For example: You could agree that you would provide the carrier with 10,000 Kilos per week. The carrier would give you a special rate for this and indicate the 10,000 kilo amount here.
- o. This is also for a high volume of freight, for the same lane, moving weekly or multiple times in a week. This would be space allocated for your freight. If you agreed on an allocated space, it would benefit you during peak shipping times by guaranteeing the room. The disadvantage to allocated space, is that you will always pay for that space, even if it is not used. If you allocated space, the amount allocated would be listed here.
- a. You could allocate space for 4 airline pallets on three flights a week from JFK to CDG. This means that on each of the three flights from JFK to CDG per week, the airline will reserve that space for you and will not book that space with anyone else. You in turn must pay for that space even when you are not using it.
Increased fuel surcharge notification
These are two types of notifications sent by the airlines advising increased fuel surcharges. They list the increase and effective dates.
These are received as attachments to emails.
London office Examples
Exchange Hawk Freight Services Email Exchange
- 1- The bottom portion of the email, you will see that Ginny (UK Account Manager) is requesting a spot rate from Hawk Freight Services (Co-Loader).
- a. You will see that she indicates she needs a rate from the pick up location to Tunis airport.
- b. She advises this shipment is considered Non-hazardous.
- c. She includes the commodity description.
- d. This is the details of this specific shipments handling units.
- e. She advises the pickup location's zip code, so they can include the trucking from the pickup location to the airport of departure in the quote.
- 2- This is the reply – the spot rate for this specific shipment.
Paris office Examples
TBA
Hong Kong office Examples
Hong Kong Air Rates
This excel spreadsheet is emailed to our co-loaders monthly for updates.
- 1 Each departure airport has its own tab. HKG is the three letter airport code the Hong Kong, so this tab includes the rates for shipments that are departing from Hong Kong airport.
- 2 This sheet is divided into sections; each arrival airport has its own section. The first section is for shipments arriving into CDG. CDG is the three letter airport code for Charles De Gaulle airport in France.
- 3 This is the able of rates.
- Column a is the two digit airline code, AF is Air France
- Column b is the currency the rates are. These are in US dollars.
- Column c is the rate per Kilo starting a 45 K and up to the next weight break, which is 100 K. Since this is blank, they are not offering a rate for shipments under the next weight break of 100
- Column d is the rate per Kilo from 100 K – 299 K.
- Column e is the rate per Kilo from 300 K – 499 K.
- Column f is the rate per Kilo from 500 K – 999 K.
- Column g is the rate per Kilo from 1000 K and above.
- Column h is the per Kilo rate for security surcharge
- Column I is the per Kilo rate for the fuel surcharge
- Column j indicates if these rates are booked on a direct or deferred (with trans-shipment points) flights.
- Column K indicates the transit time from departure airport to arrival airport.
- Column L indicates the last time these rates were updated.
Misc
Types and Examples of Air contracts
Examples attached to Mantis http://ct.jaguarfreight.com/mantis/view.php?id=2894 . Sample XLS Quote form. This is what the ISales department uses to create quotes. Sample PDF Quote form. This is a copy of the quote generated from the above. Sample XLS Quote Report. Steven Inside Sales Manager has been assigning a quote number to each quote he creates and logging these into an excel sheet to keep track. Sample of Co-loaders tariff - This is an example of one of the tariffs inside sales uses to search rates offered by one of our co-loaders. Singapore Rates - This is an example of one of the airlines rate tariffs used by inside sales to search rates offered by an airline.
Tariff management systems (known and in use by Jaguar)
Q
Please find and describe in details any tariff management systems that:
- a) Jaguar is using
- b) know but not used in Jaguar
A
APPENDIX Bits and pieces
- Other CT2 components related to Rates DB Air
- Please explain what do we already have in CT2 that is related to Rates DB.
