Estimated GM: Proof Of Concept
From UG
(Difference between revisions)
(→SOW 0 Proof Of Concept) |
(→SOW 0 Proof Of Concept) |
||
| Line 19: | Line 19: | ||
* Time frame for calculating estimated rates (ERTF) | * Time frame for calculating estimated rates (ERTF) | ||
** Jan 1, 2011 < CTs with Actual Date of Dept. < June 1, 2011 | ** Jan 1, 2011 < CTs with Actual Date of Dept. < June 1, 2011 | ||
| - | * Time frame for calculating estimated sales CTs (ESTF) | + | * Time frame for calculating estimated sales CTs (ESTF) and error margin |
** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011 | ** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011 | ||
| - | '''Mappings''': | + | '''Fields and Mappings''': |
| - | * Client = E0 | + | |
| - | * Origin = Country from Airport of Departure | + | part 1 - defining triple |
| - | * Destination = Country from Airport of Arrival | + | * A] Client = E0 |
| - | * | + | * B] Origin = Country from Airport of Departure |
| - | + | * C] Destination = Country from Airport of Arrival | |
| + | |||
| + | part 2 - defining est rate for triple | ||
| + | * D] ERTF Sales = sum of all sales invoices for ERTF (in USD) - actual | ||
| + | * E] ERTF ttl ChW = total chargeable weight for all CTs during ERTF | ||
| + | * F] ERate = estimated rate | ||
| + | |||
| + | part 3 - calculating error margin for triple | ||
| + | * G] Act Sales = actual sales (for ESTF) | ||
| + | * H] Est Sales = estimated sales (for ESTF) | ||
| + | * I] Error, USD | ||
| + | * J] Error, % = error margin in % related to Actual sales | ||
| + | |||
'''Report output''': | '''Report output''': | ||
* xls form | * xls form | ||
* each line corresponds to one (Client / Origin / Destination) Triple | * each line corresponds to one (Client / Origin / Destination) Triple | ||
| - | * columns: | + | * columns: A, B, ..., J as defined above |
| + | |||
| + | |||
| + | |||
** A Client | ** A Client | ||
** B Origin | ** B Origin | ||
Revision as of 20:40, 18 April 2012
SOW 0 Proof Of Concept
Estimated Average Sales Rates Report
This estimation is based on:
- Client / Origin / Destination unique Triples
- Chargeable Kg amounts
- 2011 Air Shipments
Report Conditions:
Aggregate for each unique (Client / Origin / Destination) Triple with existing data from CTs that satisfy:
- MOT = Air
- Jan 1, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
Parameters:
- Time frame for calculating estimated rates (ERTF)
- Jan 1, 2011 < CTs with Actual Date of Dept. < June 1, 2011
- Time frame for calculating estimated sales CTs (ESTF) and error margin
- June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
Fields and Mappings:
part 1 - defining triple
- A] Client = E0
- B] Origin = Country from Airport of Departure
- C] Destination = Country from Airport of Arrival
part 2 - defining est rate for triple
- D] ERTF Sales = sum of all sales invoices for ERTF (in USD) - actual
- E] ERTF ttl ChW = total chargeable weight for all CTs during ERTF
- F] ERate = estimated rate
part 3 - calculating error margin for triple
- G] Act Sales = actual sales (for ESTF)
- H] Est Sales = estimated sales (for ESTF)
- I] Error, USD
- J] Error, % = error margin in % related to Actual sales
Report output:
- xls form
- each line corresponds to one (Client / Origin / Destination) Triple
- columns: A, B, ..., J as defined above
- A Client
- B Origin
- C Destination
- D Act Sales ()
- E TTl Sales ()
- Avg S/ChgW (average sales per kg: total sales in USD for CT or GRP divided by TTl ChgW)
Estimated vs Actual Sales Report
This estimation is based on:
- Client / Origin / Destination Triple
- Chargeable Kg
- 2011 Air Shipments
Report Conditions:
- MOT = Air
- Jan 1, 2011 < Actual Date of Dept. < Dec 31, 2011
Report output:
- xls form
- each line corresponds to one CT for CTs that are not part of a group
- for CTs that are part of a group we need one line per group
- columns:
- A Client E0
- B CT# (if part of a group then this is lowest CT# in a group)
- C GRP# (or print "n/a" if CT is not part of a group)
- D TTl ChgW (total chargeable weight of CT or GRP in Kg)
- E TTl Sales ()
- Avg S/ChgW (average sales per kg: total sales in USD for CT or GRP divided by TTl ChgW)
