CK model for Rates DB Air (requirements)
From UG
(→Example of spreadsheet) |
(→Example of spreadsheet) |
||
| Line 75: | Line 75: | ||
'''Example of pivot table output''' | '''Example of pivot table output''' | ||
| + | |||
| + | *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 the table is generated with the following output | ||
[[File:Air freight spreadsheet Pivot table.JPG]] | [[File:Air freight spreadsheet Pivot table.JPG]] | ||
Revision as of 14:38, 21 July 2011
Contents |
Info
0002690: [Rates DB] ..... <project>
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
Summary
We are rewriting the rate db air - Phase one according to new simplified requirements, provided by Chikei Ho (Module Owner)
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 to be added to the same spreadsheet, it is shared).
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.
Example 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
- 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 the table is generated with the following output
Business Requirements
Core need
To upload excel spreadsheet into a database.
To be able to export the excel spreadsheet from the database.
Uploading the spreadsheet will replace any rates currently in the database.
