Estimated GM: Proof Of Concept

From UG

(Difference between revisions)
Jump to: navigation, search
(→SOW 0 Proof Of Concept)
(→Estimated Average Sales Rates Report)
 
(20 intermediate revisions not shown)
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 ==
 +
 +
'''0003452''': [Estim P&L / Accruals] Model based on history. Phase 1: Feasibility study
 +
 +
The purpose of this phase is to estimate margin of error for most basic formula that is trying to estimate current rates/tariffs.
 +
 +
First we consider MOT Air.
 +
 +
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 figures for CTs departed during the 2nd half of 2011.
 +
 +
We also calculate and present margin of error.
 +
 +
Results are delivered as one time xls report.
=== Estimated Average Sales Rates Report ===
=== Estimated Average Sales Rates Report ===
Line 7: Line 25:
This estimation is based on:
This estimation is based on:
* Client / Origin / Destination unique Triples  
* Client / Origin / Destination unique Triples  
-
** NOTE: Origin = country from Airport of Dept
 
-
** NOTE: Dest = country from Airport of Dest
 
* Chargeable Kg amounts
* Chargeable Kg amounts
* 2011 Air Shipments
* 2011 Air Shipments
Line 14: Line 30:
'''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.
-
* MOT = Air
+
-
* Jan 1, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011
+
-
Report output:
+
'''Parameters''':
-
* xls form
+
* Time frame for calculating '''estimated rates''' ('''TF1''')
-
* each line corresponds to one (Client / Origin / Destination) Triple
+
** TF1: Jan 1, 2011 < CTs with Actual Date of Dept. < June 30, 2011 (1st half of the 2011)
-
* 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)
+
-
=== Estimated vs Actual Sales Report ===
+
* Time frame for calculating '''estimated sales''' for CTs  and '''estimated error margin'''('''TF2''')
 +
** TF2: July 1, 2011 < CTs with Actual Date of Dept. < Dec 31, 2011 (2st half of the 2011)
-
This estimation is based on:
+
'''Fields and Mappings''':
-
* Client / Origin / Destination Triple
+
-
* Chargeable Kg
+
-
* 2011 Air Shipments
+
-
Report Conditions:
+
part 1 - defining triple
-
* MOT = Air
+
* A] Client = E0
-
* Jan 1, 2011 < Actual Date of Dept. < Dec 31, 2011
+
* B] Origin = Country from Airport of Departure
 +
* C] Destination = Country from Airport of Arrival
-
Report output:
+
part 2 - defining '''est rate''' for triple
 +
* D] TF1 Sales = sum of all sales invoices for TF1 (in USD) - actual
 +
* E] TF1 ttl ChW = total chargeable weight for all CTs during TF1
 +
* F] Rate = estimated rate = D divided by E
 +
 
 +
part 3 - calculating '''error margin''' for triple
 +
* G] Act Sales = actual sales (for TF2)
 +
* Z] TF2 ttl ChW = total chargeable weight for all CTs during TF2
 +
* H] Est Sales = estimated sales (for TF2) = Z*F
 +
* I] Error, USD = error margin in USD related to Actual sales = G minus H
 +
* J] Error, % =  error margin in % related to Actual sales = (I/G)*100%
 +
 
 +
 
 +
'''Report output''':
* xls form
* xls form
-
* each line corresponds to one CT for CTs that are not part of a group
+
* each line corresponds to one (Client / Origin / Destination) Triple
-
* for CTs that are part of a group we need one line per group
+
* columns: A, B, ..., J as defined above
-
* columns:
+
 
-
** A Client E0
+
== SOW 2 ==
-
** B CT# (if part of a group then this is lowest CT# in a group)
+
TBD
-
** 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)
+
== SOW 3 ==
-
** E TTl Sales ()
+
TBD
-
** Avg S/ChgW (average sales per kg: total sales in USD for CT or GRP divided by TTl ChgW)
+

Current revision as of 15:35, 26 April 2012


Contents

[edit] Info

  • parent:
  • related Requirements:

[edit] SOW 0 Proof Of Concept

0003452: [Estim P&L / Accruals] Model based on history. Phase 1: Feasibility study

The purpose of this phase is to estimate margin of error for most basic formula that is trying to estimate current rates/tariffs.

First we consider MOT Air.

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 figures for CTs departed during the 2nd half of 2011.

We also calculate and present margin of error.

Results are delivered as one time xls report.

[edit] 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.

Parameters:

  • Time frame for calculating estimated rates (TF1)
    • TF1: Jan 1, 2011 < CTs with Actual Date of Dept. < June 30, 2011 (1st half of the 2011)
  • Time frame for calculating estimated sales for CTs and estimated error margin(TF2)
    • TF2: July 1, 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] TF1 Sales = sum of all sales invoices for TF1 (in USD) - actual
  • E] TF1 ttl ChW = total chargeable weight for all CTs during TF1
  • F] Rate = estimated rate = D divided by E

part 3 - calculating error margin for triple

  • G] Act Sales = actual sales (for TF2)
  • Z] TF2 ttl ChW = total chargeable weight for all CTs during TF2
  • H] Est Sales = estimated sales (for TF2) = Z*F
  • I] Error, USD = error margin in USD related to Actual sales = 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

[edit] SOW 2

TBD

[edit] SOW 3

TBD

Retrieved from "https://wiki.jaguarfreight.com/Estimated_GM:_Proof_Of_Concept"