Estimated P and L based on history
From UG
(→Requirements Analysis) |
(→Phase Two: In system functionality for Air) |
||
| (13 intermediate revisions not shown) | |||
| Line 30: | Line 30: | ||
4. Destination | 4. Destination | ||
| - | In the absence of a previous record for this client, the shipment would look for an identical country to country pairing in Cybertrax. This estimate would get a 80% rating. | + | '''In the absence of a previous record for this client''', the shipment would look for an identical country to country pairing in Cybertrax. This estimate would get a 80% rating. |
'''In the absence of historical data for the specific country to country''' pairing the estimated P&L would not populate. | '''In the absence of historical data for the specific country to country''' pairing the estimated P&L would not populate. | ||
| Line 40: | Line 40: | ||
N/A | N/A | ||
| - | == | + | == Solution == |
=== Concept === | === Concept === | ||
| Line 53: | Line 53: | ||
See example below (this xls contains formulas and attached to parent mantis). | See example below (this xls contains formulas and attached to parent mantis). | ||
| - | Examples provided for Sales (in red). Examples for purchase/cost (in green) are not provided but they have to follow | + | Examples provided are for Sales (in red). Examples for purchase/cost (in green) are not provided but they have to follow similar logic as sales. |
| - | Example is based on one month data. In reality Table B would be based on 12 month of most recent data. And Table A would show any desired time frame. Again most probably up to most recent 12 month. As far as filters - most typical would be - per client, per time frame. | + | Example is based on one month of data. In reality Table B would be based on 12 month of most recent data. And Table A would show any desired time frame. Again most probably up to most recent 12 month. As far as filters - most typical would be - per client, per time frame. |
| Line 62: | Line 62: | ||
==== Mapping for Country ==== | ==== Mapping for Country ==== | ||
| - | Country From, Country To should be mapped to | + | Country From, Country To should be mapped to: |
| + | * [[CT#Origin Country]] | ||
| + | * [[CT#Destination Country]] | ||
| + | ==== When to populate estimations into P/L ==== | ||
| + | |||
| + | Upon confirmation of an actual departure date. | ||
| + | |||
| + | ==== Rating Note ==== | ||
| + | |||
| + | It is a special note indicating reliability of rate, etc. See more below. | ||
| + | |||
| + | By default: Rating Note = "100% rating" | ||
| + | |||
| + | Display it on all related P/L reports. | ||
| + | |||
| + | ==== Absence of a previous record for this client ==== | ||
| + | |||
| + | '''In the absence of a previous record for this client''', the shipment would look for an identical country to country pairing in Cybertrax. This estimate would get a 80% rating. | ||
| + | |||
| + | This means that in this special case System should take into account and calculate average based on ALL shipments in the system for ALL Clients for this (MOT/Country From/Country To) triple. | ||
| + | |||
| + | Rating Note = "80% rating" | ||
| + | |||
| + | ==== Absence of historical data for the specific country to country ==== | ||
| + | |||
| + | '''In the absence of historical data for the specific country to country''' pairing the estimated P&L would not populate. | ||
| + | |||
| + | Rating Note = "Not rated. Absence of historical data for country to country". | ||
| + | |||
| + | ==== When to compile Table B ==== | ||
| + | |||
| + | For performance reasons Table B could be kept as a reference table in the system and be updated say once a day between 6 and 8 pm when we have very few active users in the system. | ||
=== Phases === | === Phases === | ||
| Line 70: | Line 101: | ||
Also before incorporating functionality into the system do [[#Feasibility study]] that assumes generating "ad hoc" reports. | Also before incorporating functionality into the system do [[#Feasibility study]] that assumes generating "ad hoc" reports. | ||
| - | === Feasibility study === | + | === Phase One: Feasibility study Air === |
Under this phase we would: | Under this phase we would: | ||
* produce Tables A and B for say all shipments last year and show this to MO | * produce Tables A and B for say all shipments last year and show this to MO | ||
| - | * if margins of error are satisfactory then he might request additional instances of such reports based on specific time frames/client companies | + | * if margins of error are satisfactory then he might request additional instances of such reports based on specific time frames/client companies |
| - | === In system functionality === | + | === Phase Two: In system functionality for Air === |
If [[#Feasibility study]] is successful then we would embed it with in the system. See below. | If [[#Feasibility study]] is successful then we would embed it with in the system. See below. | ||
| + | |||
| + | ==== Maintain Estimated Rates Table in DB ==== | ||
| + | |||
| + | ==== Maintain Estimated P and L Table in DB ==== | ||
==== Add option to P n L report ==== | ==== Add option to P n L report ==== | ||
| Line 96: | Line 131: | ||
Design for levels 1,2,3 could be standard (counter - filters - shipment level). | Design for levels 1,2,3 could be standard (counter - filters - shipment level). | ||
| + | |||
| + | Call it "Not estimated. Missing rates." | ||
| + | |||
| + | === Phase Three: other modes of transport === | ||
== SOW 1 == | == SOW 1 == | ||
TBD | TBD | ||
Current revision as of 15:11, 25 February 2012
Contents |
[edit] Info
- parent: 0002515 [Estim P&L / Accruals] .......<parent>
[edit] Glossary
[edit] Requirements
[edit] Requirements Submitted by MO
Objective: Gain some visibility into the organization’s financial result, while we wait for an automated, exact and integrated solution (feed from cost and vendor rate database)
The proposal is to estimate costs and revenues on a per shipment basis, utilizing historical information from Cybertrax.
The requirement would be for Cybertrax to generate daily “statistics” compiling a total cost and revenue figure, per container (FCL), cubic meter (LCL), or chargeable KG (air).
The above for shipments ‘closed’ in Cybertrax according to the new 2012 GM Split and related file closing guidelines.
New shipments, upon confirmation of an actual departure date, would populate an estimated P&L based on historical information, based on the following:
1. Client 2. Mode 3. Origin 4. Destination
In the absence of a previous record for this client, the shipment would look for an identical country to country pairing in Cybertrax. This estimate would get a 80% rating.
In the absence of historical data for the specific country to country pairing the estimated P&L would not populate.
A dashboard report showing records with an actual departure date, and no estimated P&L, can also be implemented.
[edit] Requirements Analysis
N/A
[edit] Solution
[edit] Concept
One can imagine that at the center of this module are two tables:
- one contains average rates for specific unique (MOT, Client, From, To) combination calculated based on records:
- that are closed; see definition here: Profit_Sharing_Module#Closing_time_frames
- comparatively recent (let's say closed within last 12 months). Including earlier records would not be feasible due outdated rates. (table A)
- another table contains shipments with estimated rates (based on table B), and margin of error (for records that are already closed (table B)
See example below (this xls contains formulas and attached to parent mantis).
Examples provided are for Sales (in red). Examples for purchase/cost (in green) are not provided but they have to follow similar logic as sales.
Example is based on one month of data. In reality Table B would be based on 12 month of most recent data. And Table A would show any desired time frame. Again most probably up to most recent 12 month. As far as filters - most typical would be - per client, per time frame.
[edit] Mapping for Country
Country From, Country To should be mapped to:
[edit] When to populate estimations into P/L
Upon confirmation of an actual departure date.
[edit] Rating Note
It is a special note indicating reliability of rate, etc. See more below.
By default: Rating Note = "100% rating"
Display it on all related P/L reports.
[edit] Absence of a previous record for this client
In the absence of a previous record for this client, the shipment would look for an identical country to country pairing in Cybertrax. This estimate would get a 80% rating.
This means that in this special case System should take into account and calculate average based on ALL shipments in the system for ALL Clients for this (MOT/Country From/Country To) triple.
Rating Note = "80% rating"
[edit] Absence of historical data for the specific country to country
In the absence of historical data for the specific country to country pairing the estimated P&L would not populate.
Rating Note = "Not rated. Absence of historical data for country to country".
[edit] When to compile Table B
For performance reasons Table B could be kept as a reference table in the system and be updated say once a day between 6 and 8 pm when we have very few active users in the system.
[edit] Phases
I suggest to create solution for MOT Air first.
Also before incorporating functionality into the system do #Feasibility study that assumes generating "ad hoc" reports.
[edit] Phase One: Feasibility study Air
Under this phase we would:
- produce Tables A and B for say all shipments last year and show this to MO
- if margins of error are satisfactory then he might request additional instances of such reports based on specific time frames/client companies
[edit] Phase Two: In system functionality for Air
If #Feasibility study is successful then we would embed it with in the system. See below.
[edit] Maintain Estimated Rates Table in DB
[edit] Maintain Estimated P and L Table in DB
[edit] Add option to P n L report
See Table A on Figure under #Concept.
[edit] Add option to P n L tab (CT Editor)
Possible layout:
[edit] Add dashboard report
Report CTs that have no estimation due to absence of appropriate (MOT, Client, Country From, Country To) combination in the system.
Report it as a DB/KPI report.
Design for levels 1,2,3 could be standard (counter - filters - shipment level).
Call it "Not estimated. Missing rates."
[edit] Phase Three: other modes of transport
[edit] SOW 1
TBD
