Rates DB (MAIN WIKI)

From UG

(Difference between revisions)
Jump to: navigation, search
(Business Information)
 
(34 intermediate revisions not shown)
Line 1: Line 1:
-
[[Category:Projects]]
+
[[Category:Rates DB]]
== Info ==
== Info ==
Line 5: Line 5:
0002690: [Rates DB] ..... <project>  
0002690: [Rates DB] ..... <project>  
-
==Business Analysis and Requirements==
+
== Glossary ==  
-
''To be written by BA. This section should cover Needs, Requirements, Background and Design Ideas. Also could include Glossary, existing business workflow, cases, etc''
+
-
"Rates DB" is a code name for tariff and contract management system with additional functionality that will affect multiple modules of CT2.
+
* [[cat#Rates DB | Rates DB]]
-
==Business Information==
 
-
Currently there seems to be no standard in obtaining or maintaining rates.  When a rate is required operators will obtain the rates either by, searching online tariffs, rate management systems, sending an email request, telephone calls, searching tariffs or documents saved on their desktop or manually looking through paper copies of contracts or tariffs.
+
'''''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.
-
It is required to create a data base that would maintain rates for each MOTEach MOT will be handled individually.   
+
'''''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 cargoIt 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. 
 +
Add this total to the actual weight and this is the amount you will be paying for.
 +
  (200-100)/2 = 50 + 100 = 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.
 +
 +
'''''Royalties Tax''''' is a governmental Tax actually only applicable by the Democratic Republic of Congo, Kinshasa. We are charged by Brussels Airlines, The tax code is TXC and it is applicable on actual gross weight
 +
 
 +
'''''Lumpsum charge''''' are applied to taxes applicable per shipment and only one rate exists and is applied by all airlines. It is a flat tax not applicable per weight break
 +
 
 +
'''''Diagnose''''' is a security process which replace the X-Ray one. As soon as a cargo released to the airline cannot be secured by X-Ray, they will apply the Diagnose method which consists in securing the cargo by dogs (Diag = Diagnosis + Nose). for more information http://www.diag-nose.com/
 +
 
 +
'''FE''' - Far East
 +
 
 +
'''EU''' - European Union
 +
 
 +
'''US''' - United States
 +
 
 +
'''TAWB''' - Trans Atlantic west bound
 +
 
 +
'''TAEB''' - Trans Atlantic east bound
 +
 
 +
'''TPWB''' - Trans Pacific west bound
 +
 
 +
'''TPEB''' - Trans Pacific east bound
 +
 
 +
'''TEU''' - Twenty-foot equivalent unit
 +
 
 +
'''FEU''' - Forty-foot equivalent unit
 +
 
 +
== Summary ==
 +
 
 +
''What are we trying to achieve? What are the main goals of this module? ''
 +
 
 +
=== Core need ===
 +
 
 +
Jaguar Global needs Tariff and Contract Management System with additional functionality that will improve/affect other areas such as CT creation, invoices, reports, etc.
 +
 
 +
Currently there seems to be no standard in obtaining or maintaining rates.  When a rate is required, operators will obtain the rates either by, searching online tariffs, searching rate management systems (Rate Explorer), sending an email request, telephone calls, searching previous emails, searching documents saved on their desktop or manually looking through paper copies of contracts or tariffs.  This is currently being done by various employees with little sharing of the information.   
 +
 
 +
=== Core functionality ===
 +
 
 +
Creating such "database" should allow operators throughout the organization (including overseas offices) to share and compare this information. 
 +
 
 +
'''Main Features:'''
 +
 
 +
* management of tariffs and contracts (purchase and sales sides)
 +
** adding, editing, deleting, listing, searching, sorting, querying
 +
*** management of the tariffs should allow operators the ability to update individual tariffs or multiple tariffs at one time.  Updating specific charge codes, origin/destination pairings, country to country pairings, E0. clients, bill to, shippers, consignees, etc...
 +
***  management of tariffs should allow operators to schedule increases/decreases in advance.
 +
***  Possibly notify operators of expiration of rates.
 +
 
 +
NOTE: Purchase side - phase 1 and sales sides - phase 2
 +
 
 +
* "Quotations" web portal and internal (for Jag Sales, Ops, Clients, etc)
 +
** searching, sorting, querying and producing an official Jag doc (with number, etc)
 +
 
 +
 
 +
'''Additional Ops features:'''
 +
* CT could be created based on quote
 +
 
 +
'''Additional Acc features:'''
 +
* Automated population of charge table in "sales invoices"
 +
* Automated calculation of "estimated cost and sale" for a shipment
 +
* Auto approval of PI charges
 +
 
 +
The system should be able to provide quick user friendly access to all current costs.
 +
 
 +
'''MOTs'''
 +
 
 +
This module is to be implemented for all modes of transport. Functionality is expected to be the same across all MOTs.
 +
 
 +
Each MOT has its own wiki and includes MOT specific requirements:
 +
 
 +
* [[Rates DB Air]]
 +
* [[ Rates db Ocean FCL]]
 +
* [[Rates DB Truck]]
 +
 
 +
'''Incoterms or International Commercial terms'''
 +
*Incoterms - TBD in Phase 2
 +
http://en.wikipedia.org/wiki/Incoterm
 +
 
 +
== Some Details ==
 +
 
 +
'''Types of tariffs'''
*Ocean FCL
*Ocean FCL
Line 23: Line 140:
*Air  
*Air  
*Trucking
*Trucking
-
*Customs
+
*Customs
The database would be used for various reasons
The database would be used for various reasons
-
*Generating Sales Quotes
+
'''Rates structures and formulas'''
-
**Searched by Customers for general rates  
+
 
-
**Searched by Customers for customer specific rates
+
Each charge will have its own rates structures and formulas necessary to maintain these rates.  However, it is possible to have some costs that are an all in rate.
-
**Searched by Jaguar employees to generate for Customers and enable overseas office to access rates.
+
 
-
*Searching Costs
+
For Example:
-
**Searched by Jaguar employees to
+
We could receive a rate from Air France for a shipment from JFK to CDG of 100 K showing:
-
***Quote costs
+
Air Freight:  .75
-
***Compare costs
+
Fuel Surcharge .90
-
*Searching Carriers
+
Security      .75
 +
Then we could receive a rate from KLM for the same shipment showing:
 +
Air Freight:  2.40 all in
 +
 +
These rates are equal, they are only shown differently.
 +
 +
 
 +
 
 +
It is possible that each rate may have multiple variables.(minimums, maximums, weight breaks, etc...)
 +
 
 +
The system will also need to capture accessorial charges, which will also be defined per MOT.
 +
 
 +
'''Specific vendor'''
 +
 
 +
Rates will be linked to a specific vendor and description.
 +
 
 +
'''Search and estimation'''
 +
 
 +
*Searched by Jaguar employees to
 +
**Compare costs for different vendors
 +
**Quote costs based on the costs saved in the system
 +
 
 +
*Searching Vendors or Carriers
**Searched by Jaguar employees to compare carriers
**Searched by Jaguar employees to compare carriers
-
**History searched by Jaguar employees to better negotiate future rates
+
 
*Estimation of CT costs
*Estimation of CT costs
*Estimate sales invoice with possible auto generation.
*Estimate sales invoice with possible auto generation.
 +
'''Generating Sales Quotes'''
 +
* Searched by Customers for general rates
 +
* Searched by Customers for customer specific rates
 +
* Searched by Jaguar employees to generate for Customers and enable overseas office to access rates.
 +
* Searching Costs
 +
'''Maintaining Customer specific sell rates'''
-
Main Features:
+
Sell rate is different for every customer.
-
* management of tariffs and contracts (client and vendor sides)
+
 
-
* "Quotations" web portal for sales
+
 
-
* Automated population of charge table in "sales invoices"
+
'''Multiple search filters, Inputs, Outputs'''
-
* Automated calculation of "estimated cost and sale" for a shipment
+
 
-
* Etc
+
The rates database system, should be user friendly with quick results.
 +
 
 +
The system should offer multiple search filters.  The user should be able to select only one filter or multiple filters
 +
*By Vendor
 +
*By Carrier
 +
*By Co-loader
 +
*By origin airport
 +
*By destination airport
 +
*Actual Weight
 +
*Chargeable Weight
 +
*Dimensions
 +
*Known or Unknown
 +
*Hazardous or Non Hazardous
 +
*Commodity
 +
 
 +
The system should show all results based on the filters set, so the operator can narrow done the results by selecting multiple filters.
 +
 
 +
The results should be sortable by each column of output, but should default with the lowest rate to highest rate.
 +
 
 +
Output should include
 +
*Vendor or Carrier
 +
*Co-loader
 +
*Departure or Origin Airport
 +
*Arrival or Destination Airport
 +
*Cost
-
==Pre Implementation Architect Notes==
+
Should list complete tariff and narrow down based on if the below filters are set.  
-
''Written by Architect after previous section is completed.''
+
-
==Implementation Notes==
+
*Actual Weight
-
''To be written by Developer or BA by the time UAT is completed. Briefly defines what was implemented and how.''
+
*Chargeable Weight
 +
*Dimensions
 +
*Known or Unknown
 +
*Hazardous or Non Hazardous
 +
*Commodity
-
==Post Implementation Architect Notes==
+
If none of these filters are set, the system should include weight breaks, known and unknown rates.
-
''Written by Architect after previous section is completed.''
+

Current revision as of 15:58, 11 October 2011


Contents

[edit] Info

0002690: [Rates DB] ..... <project>

[edit] 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.  
Add this total to the actual weight and this is the amount you will be paying for.
(200-100)/2 = 50 + 100 = 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.

Royalties Tax is a governmental Tax actually only applicable by the Democratic Republic of Congo, Kinshasa. We are charged by Brussels Airlines, The tax code is TXC and it is applicable on actual gross weight

Lumpsum charge are applied to taxes applicable per shipment and only one rate exists and is applied by all airlines. It is a flat tax not applicable per weight break

Diagnose is a security process which replace the X-Ray one. As soon as a cargo released to the airline cannot be secured by X-Ray, they will apply the Diagnose method which consists in securing the cargo by dogs (Diag = Diagnosis + Nose). for more information http://www.diag-nose.com/

FE - Far East

EU - European Union

US - United States

TAWB - Trans Atlantic west bound

TAEB - Trans Atlantic east bound

TPWB - Trans Pacific west bound

TPEB - Trans Pacific east bound

TEU - Twenty-foot equivalent unit

FEU - Forty-foot equivalent unit

[edit] Summary

What are we trying to achieve? What are the main goals of this module?

[edit] Core need

Jaguar Global needs Tariff and Contract Management System with additional functionality that will improve/affect other areas such as CT creation, invoices, reports, etc.

Currently there seems to be no standard in obtaining or maintaining rates. When a rate is required, operators will obtain the rates either by, searching online tariffs, searching rate management systems (Rate Explorer), sending an email request, telephone calls, searching previous emails, searching documents saved on their desktop or manually looking through paper copies of contracts or tariffs. This is currently being done by various employees with little sharing of the information.

[edit] Core functionality

Creating such "database" should allow operators throughout the organization (including overseas offices) to share and compare this information.

Main Features:

  • management of tariffs and contracts (purchase and sales sides)
    • adding, editing, deleting, listing, searching, sorting, querying
      • management of the tariffs should allow operators the ability to update individual tariffs or multiple tariffs at one time. Updating specific charge codes, origin/destination pairings, country to country pairings, E0. clients, bill to, shippers, consignees, etc...
      • management of tariffs should allow operators to schedule increases/decreases in advance.
      • Possibly notify operators of expiration of rates.

NOTE: Purchase side - phase 1 and sales sides - phase 2

  • "Quotations" web portal and internal (for Jag Sales, Ops, Clients, etc)
    • searching, sorting, querying and producing an official Jag doc (with number, etc)


Additional Ops features:

  • CT could be created based on quote

Additional Acc features:

  • Automated population of charge table in "sales invoices"
  • Automated calculation of "estimated cost and sale" for a shipment
  • Auto approval of PI charges

The system should be able to provide quick user friendly access to all current costs.

MOTs

This module is to be implemented for all modes of transport. Functionality is expected to be the same across all MOTs.

Each MOT has its own wiki and includes MOT specific requirements:

Incoterms or International Commercial terms

  • Incoterms - TBD in Phase 2

http://en.wikipedia.org/wiki/Incoterm

[edit] Some Details

Types of tariffs

  • Ocean FCL
  • Ocean LCL
  • Air
  • Trucking
  • Customs

The database would be used for various reasons

Rates structures and formulas

Each charge will have its own rates structures and formulas necessary to maintain these rates. However, it is possible to have some costs that are an all in rate.

For Example:
We could receive a rate from Air France for a shipment from JFK to CDG of 100 K showing:
Air Freight:   .75
Fuel Surcharge .90
Security       .75
Then we could receive a rate from KLM for the same shipment showing:
Air Freight:  2.40 all in

These rates are equal, they are only shown differently.


It is possible that each rate may have multiple variables.(minimums, maximums, weight breaks, etc...)

The system will also need to capture accessorial charges, which will also be defined per MOT.

Specific vendor

Rates will be linked to a specific vendor and description.

Search and estimation

  • Searched by Jaguar employees to
    • Compare costs for different vendors
    • Quote costs based on the costs saved in the system
  • Searching Vendors or Carriers
    • Searched by Jaguar employees to compare carriers
  • Estimation of CT costs
  • Estimate sales invoice with possible auto generation.

Generating Sales Quotes

  • Searched by Customers for general rates
  • Searched by Customers for customer specific rates
  • Searched by Jaguar employees to generate for Customers and enable overseas office to access rates.
  • Searching Costs

Maintaining Customer specific sell rates

Sell rate is different for every customer.


Multiple search filters, Inputs, Outputs

The rates database system, should be user friendly with quick results.

The system should offer multiple search filters. The user should be able to select only one filter or multiple filters

  • By Vendor
  • By Carrier
  • By Co-loader
  • By origin airport
  • By destination airport
  • Actual Weight
  • Chargeable Weight
  • Dimensions
  • Known or Unknown
  • Hazardous or Non Hazardous
  • Commodity

The system should show all results based on the filters set, so the operator can narrow done the results by selecting multiple filters.

The results should be sortable by each column of output, but should default with the lowest rate to highest rate.

Output should include

  • Vendor or Carrier
  • Co-loader
  • Departure or Origin Airport
  • Arrival or Destination Airport
  • Cost

Should list complete tariff and narrow down based on if the below filters are set.

  • Actual Weight
  • Chargeable Weight
  • Dimensions
  • Known or Unknown
  • Hazardous or Non Hazardous
  • Commodity

If none of these filters are set, the system should include weight breaks, known and unknown rates.

Personal tools