Estimated GM: Proof Of Concept

From UG

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)