Estimated GM: Proof Of Concept

From UG

(Difference between revisions)
Jump to: navigation, search
(SOW 0 Proof Of Concept)
(SOW 0 Proof Of Concept)
Line 17: Line 17:
'''Parameters''':
'''Parameters''':
-
* Time frame for calculating estimated rates (ERTF)
+
* Time frame for calculating estimated rates (RTF)
** 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) and error margin
+
* Time frame for calculating estimated sales CTs and error margin(ETF)
** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
Line 30: Line 30:
part 2 - defining est rate for triple
part 2 - defining est rate for triple
-
* D] ERTF Sales = sum of all sales invoices for ERTF (in USD) - actual
+
* D] RTF Sales = sum of all sales invoices for RTF (in USD) - actual
-
* E] ERTF ttl ChW = total chargeable weight for all CTs during ERTF
+
* E] RTF ttl ChW = total chargeable weight for all CTs during RTF
-
* F] ERate = estimated rate
+
* F] Rate = estimated rate = D divided by E
part 3 - calculating error margin for triple
part 3 - calculating error margin for triple
* G] Act Sales = actual sales (for ESTF)
* G] Act Sales = actual sales (for ESTF)
* H] Est Sales = estimated sales (for ESTF)
* H] Est Sales = estimated sales (for ESTF)
-
* I] Error, USD
+
* I] Error, USD = G minus H
-
* J] Error, % =  error margin in % related to Actual sales
+
* J] Error, % =  error margin in % related to Actual sales = (I/G)*100%
Line 45: Line 45:
* each line corresponds to one (Client / Origin / Destination) Triple
* each line corresponds to one (Client / Origin / Destination) Triple
* columns: A, B, ..., J as defined above
* 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)
 

Revision as of 20:45, 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 (RTF)
    • Jan 1, 2011 < CTs with Actual Date of Dept. < June 1, 2011
  • Time frame for calculating estimated sales CTs and error margin(ETF)
    • 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] RTF Sales = sum of all sales invoices for RTF (in USD) - actual
  • E] RTF ttl ChW = total chargeable weight for all CTs during RTF
  • F] Rate = estimated rate = D divided by E

part 3 - calculating error margin for triple

  • G] Act Sales = actual sales (for ESTF)
  • H] Est Sales = estimated sales (for ESTF)
  • I] Error, USD = G minus H
  • J] Error, % = error margin in % related to Actual sales = (I/G)*100%


Report output:

  • xls form
  • each line corresponds to one (Client / Origin / Destination) Triple
  • columns: A, B, ..., J as defined above
Personal tools