Estimated GM: Proof Of Concept

From UG

(Difference between revisions)
Jump to: navigation, search
(SOW 0 Proof Of Concept)
Line 1: Line 1:
[[Category:Estimated P and L]]
[[Category:Estimated P and L]]
 +
 +
== Info ==
 +
* parent:
 +
* related Requirements:
== SOW 0 Proof Of Concept ==
== SOW 0 Proof Of Concept ==
 +
 +
The purpose of this phase is to estimate margin of error for most basic rates estimation formula.
 +
 +
We start from calculating estimated Air sales rates based on existing data for the 1st half of the 2011.
 +
 +
We use these rates to estimate sales for CTs departed during the 2nd half of 2011.
 +
 +
Finally, we calculate and present margin of error.
=== Estimated Average Sales Rates Report ===
=== Estimated Average Sales Rates Report ===
Line 12: Line 24:
'''Report Conditions:'''
'''Report Conditions:'''
-
Aggregate for each unique (Client / Origin / Destination) Triple with existing data from CTs that satisfy:
+
Aggregate data from DB for each unique (Client / Origin / Destination) Triple from existing CTs that satisfy:
* MOT = Air
* MOT = Air
* Jan 1, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
* Jan 1, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
'''Parameters''':
'''Parameters''':
-
* Time frame for calculating estimated rates (RTF)
+
* 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 (1st half of the 2011)
-
* Time frame for calculating estimated sales CTs  and error margin(ETF)
+
 
-
** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
+
* Time frame for calculating estimated '''sales''' for CTs  and '''error margin'''(ETF)
 +
** June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011 (2st half of the 2011)
'''Fields and Mappings''':
'''Fields and Mappings''':
Line 29: Line 42:
* C] Destination = Country from Airport of Arrival
* C] Destination = Country from Airport of Arrival
-
part 2 - defining est rate for triple
+
part 2 - defining '''est rate''' for triple
* D] RTF Sales = sum of all sales invoices for RTF (in USD) - actual
* 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
* E] RTF ttl ChW = total chargeable weight for all CTs during RTF
* F] Rate = estimated rate = D divided by E
* 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)
+
* Z] ETF ttl ChW = total chargeable weight for all CTs during ETF
 +
* H] Est Sales = estimated sales (for ESTF) = Z*F
* I] Error, USD = G minus H
* I] Error, USD = G minus H
* J] Error, % =  error margin in % related to Actual sales = (I/G)*100%
* J] Error, % =  error margin in % related to Actual sales = (I/G)*100%

Revision as of 21:00, 18 April 2012


Contents

Info

  • parent:
  • related Requirements:

SOW 0 Proof Of Concept

The purpose of this phase is to estimate margin of error for most basic rates estimation formula.

We start from calculating estimated Air sales rates based on existing data for the 1st half of the 2011.

We use these rates to estimate sales for CTs departed during the 2nd half of 2011.

Finally, we calculate and present margin of error.

Estimated Average Sales Rates Report

This estimation is based on:

  • Client / Origin / Destination unique Triples
  • Chargeable Kg amounts
  • 2011 Air Shipments

Report Conditions:

Aggregate data from DB for each unique (Client / Origin / Destination) Triple from existing 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 (1st half of the 2011)
  • Time frame for calculating estimated sales for CTs and error margin(ETF)
    • June 2, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011 (2st half of the 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)
  • Z] ETF ttl ChW = total chargeable weight for all CTs during ETF
  • H] Est Sales = estimated sales (for ESTF) = Z*F
  • 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

SOW 2

TBD

SOW 3

TBD

Personal tools