CK model for Rates DB Air (requirements)

From UG

Current revision as of 15:15, 11 August 2011

[edit] Info

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

Rates_DB

[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.

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.

For additional definitions please see Rates_DB#Glossary

[edit] Summary

We are rewriting the rate db air - Phase one - according to new simplified requirements, provided by Chikei Ho (Module Owner)

[edit] Current Business Process

Currently, Chikei has created an excel spreadsheet to maintain and search rates. Each office is sharing this spreadsheet using dropbox. http://www.dropbox.com/.

This enables each office to update their own portion of the rate sheet (all are updated on the same spreadsheet, it is shared).

It allows each office to access the other office's rates.

Dropbox is preferred because it is more secure then emailing the rates.

The first page of the spreadsheet is where all of the rates are entered. (Tab is named GCR ED)

The Second page of the spreadsheet is the Pivot table. This is where the results can be searched by selecting airport to airport pairs. (Tab is named Pivot)

[edit] Examples of spreadsheet

Spreadsheet is to large, to copy in one picture.

1st picture columns A through Q

2nd picture columns R through AC

Example of pivot table output

The table is generated with the following output by

• By selecting the Origin Airport from a drop down list in row 2 column B

AND

• By selecting the Destination Airport from a drop down list in row 3 column B

[edit] Business Requirements

Uploading of the shared excel spreadsheet into a database.

Exporting the excel spreadsheet from the database.

Each spreadsheet uploaded will replace any spreadsheet previously uploaded into the database.

Enable searching of these rates.

Output of the rates displaying all information currently shown in the output of the pivot table.

A restriction should be put on who will be able to import and export these rates. I believe this will be a done by SuperOps, but I will confirm.

[edit] Questions and Answer from Chikei

Questions are in black

Answers are in blue

1) What is the biz process for "Air freight buying rates file" upload and maintenance:

• a) Is it one file per office? (value in the first column will always be the same in one file)
• b) Is there only one file per office? (which means we can overwrite old data when uploading new version of this file)

There will be a unique spreadsheet where all the offices will use to add on their rates and share the information with the other offices. We are currently using dropbox to update, maintain and exchange those information.

• c) If operator uploads a new file should the system replace new file with the old or compare and merge line by line

Any new version of the spreadsheet will overwrite the old one. (In our dropbox folder, there will be only ONE spreadsheet)

Do want access to Dropbox and be able to see the available database in place? (including the seafreight ones)

2) Questions about fields/values:

• a) what is "TACT"

TACT stands for The Air Cargo Tariff published by IATA (International Air Transport Associations) and contains comprehensive information regarding air cargo rules, regulations, rates and charges. When the database is referring to the TACT that means the published IATA rates are to be applied.

The pivot table does not handle letters as a data so it is showed as ‘zero’ but we should read TACT which informs the operators that IATA TACT rates applied.

• b) validity from date - how is it defined

The validity date is defined by the carriers or subcontractor. Basically this information will be monitored by the office who enters the rates he is responsible for.

• c) do we need "comment" field for spreadsheet

If possible yes, this would add on flexibility. The database does not cover all the options. Having a comment field would enable to clarify possible grey areas of some rates.

• d) we suggest to add "valid from" field to Output

This suggestion is welcomed

Two points:

1)I believe that the segmentation of the airfreight rates structure will have multiple applications (in connection with Finance/Accounting/Sales). This is something we need to talk and stay in phase.

2)In a practical (or common user) point of view, the way we need the rates sorted is different from my initial expectation. Please find here attached an Excel sheet including a pivot table to share with you how I visualize the sorting function. (The Excel Sheet is uploaded into Mantis 2690)

[edit] Additional Questions and Answer from Chikei

This section added 8/11/12

1) Once the system is in place and spreadsheets can be uploaded, Do the spreadsheets still need to be password protected? At this time, they can only upload spreadsheets that are not protected and need to find a work around if this is required.

The reason we are protecting the spreadsheet is due to the fact that it can fall into third party’s hands when manipulating it:

Saved in Dropbox – the files are hosted by a service provider we never know how secure the system is.

Sent by email as an attachment – Outlook has a useful ‘autofill’ email address but a single moment of inadvertence and you can send the email to a third party (worst to a client).

If we can keep control of the spreadsheet and prevent above possibilities we can omit to password protect the spreadsheet. I am not sure who will be uploading the spreadsheet. If it is done by the one updating the rates in all the offices hence no circulation of spreadsheet then I think we can remove the protection when uploading the file to the system.

NOTE

This would mean that each office will need to be updating its own spreadsheet, so we would have to allow for 4 offices to upload there own information, which should not override information uploaded by other offices.

2) Right now the requirements are to replace the previous information when a new spreadsheet is uploaded. I would like to confirm if you would like these requirements to remain? I am asking this because, keeping the requirements this way, means we will have no records of the prior rates. Also, we will not be able to upload rates with effective dates in the future (GRI’s, ect…).

Understood. As same as we have in place today with the spreadsheet, we indicate a validity date (which means beyond this date, the rates are not valid anymore and operators should update the rates to ensure a certain continuity). However we have some flexibility in the spreadsheet which can indicate the upcoming PSS and/or GRI.

Would it be too complicated to add on an ‘effective date’ functionality (which means the system would not add on the PSS/GRI or any upcoming surcharges to the ‘all in’ rate when the effective date is not reached yet however the information is displayed and available in the system)?

Otherwise, a comment block should offer us the flexibility to ‘alert’ the users of the expected PSS/GRI/Surcharges and all rates are limited in time with a validity date.

3) Should we allow the user to select a display currency (the currency in which the rates should display) for the output of the search results or should the output of the rates search be defaulted to local currency by user?

As an example, Hong Kong colleagues are converting RMB or HKG airfreight rates into USD rates when quoting to our US colleagues. At the moment, we did not encountered difficulties as the selling rates are normally covering the currency’s variation. However, I would prefer that the INPUT of the rates are keyed in with the local currency (as we are buying as such) but we can offer a possibility to convert the OUTPUT into the other end local currency.

This happens for airfreight only (and local charges) as for the ocean freight we are buying rates in USD.

4) Should the above currency be an additional row, so the currency that the rates were originally uploaded in will show also?

As same as in the existing spreadsheet, we have a column for the currency that the rates are expressed into. The currency information has to be displayed in the outputs.

[edit] Additional information

information added 7/26/11

[edit] Importing and Exporting the rate sheet

The ability to import the spreadsheet should only be given to specific user groups (for example SuperOps, Sales, etc...). We need to add a section in Admin -> Users Access Admin -> where we can select which groups should be allowed to handle this.

The user groups selected to import these spreadsheets, should also be able to export the entire spreadsheet. Exporting should be the last version imported and should be in the same format as the spreadsheet that was uploaded.

[edit] Searching rates and downloading results

All internal users should have the ability to search the rates once they are uploaded.

Operators should have the option to download the search results to excel.

[edit] Missing information

This section added 8/1/11

If an airport or currency is listed on the spreadsheet, but does not exist in Cybertrax, the user should receive an error message.

The error message should indicate exactly what information is not in Cybertrax.

The system should not accept the upload until either the currency/airport is added into Cybertrax or the spreadsheet is corrected.