GMS

From UG

(Difference between revisions)

Jump to: navigation, search

Revision as of 02:54, 20 February 2012

Info

Mantis

0003321 [Profit Sharing Module]

Scope

Covers this one module - Profit Sharing Module.

Glossary

Business Requirements 1

Intro

This is as received from Module Owner.

General Requirements

A “Gross Margin split” is to be implemented between all offices in 2012.

For 2012, the Gross Margin split will follow the suggested rules listed below.

Please keep in mind, every client will be assigned an single “Owner Office”. This office is responsible for selling to and maintaining the relationship with the client and will receive benefits from the related sales through the Gross Margin split.

It is expected that CyberTrax will automatically generate the needed internal invoices which will distribute the Gross Margin to the correct office.

Files will automatically be closed down daily with set dates, after the actual date of departure, according to the mode of shipment as outlined in the table below:

However, functionality will also be provided so CT records can be closed down manually ahead of the ‘automated closing date’ and this is strongly encouraged.

Files can be opened after their system close date for changes by and approved group of users (management or super users), but it is expected this will be the exception.

If a file is “re-opened” after “closing”, it will need to be “closed” again – at which point a recalculation of the GM Margin Split will need to be processed again.

Ability to report on II’s issued automatically will be required, average time it takes for files to be closed, how many files are “re-opened” after closing etc.

Systems Requirements

Admin Section

1. Client Company to JFS Office link “Owner Office”

2. GM Split Models and Percentages table

3. Automated File Closing table (mode, days)

4. Manual file “lock” and “unlock” subject to user type access rights

Accounting Section

1. Ability to view / search automated invoices (report to see II’s only? Multiple lines on reports to see CT level details?

2. Scope requirements for a Gross Margin Split report (I believe we ALREADY HAVE a Gross Margin Split Report)

Automated Invoices

1. Generate automated “detail sheet” showing CT/Record transactional info

Ideally One Invoice between two offices, per day. Attached to that invoice is a ‘detailed report’ for what CT’s are included in the report.

Ops

1. Manual “lock file” feature, “unlock file” feature for selected users

2. Show II in P&L

KPI type reporting

Could we include a KPI type reporting in the Admin Tables?

For example:

Average time to lock files (per mode, office, team, client) with trending information
Average files ‘unlocked’ after ‘locked’ – weekly (per mode, office, team, client) w/trending

The idea is to keep KPI’s where they belong rather than clustering them (where they are not “critical”)

Calculation Models: Definitions

OO = Owner Office (Link/Table to be created)

EO = Export Office (identified by “export reference number”)

IO = Import Office (identified by “import reference number”

TO = Third party office (identified by “third party reference number”)

There is a Minimum of 1 “reference number” in a record, with a Maximum of 3 “reference numbers” in Cybertrax.

Reference Numbers: Found on General Tab of CT record (A, B, C)

The OO almost always plays one of the other roles (EO, IO, TO).\

Reference numbers are being automated (Tira is the SA for this task)

Calculation Models: RULES

“Handled by OO” means the OO is either the EO or IO If the “OO” is the “Third Party Reference” the OO did not “handle” the shipment.

60/40 rule

If the shipment is handled between two offices and one is the OO;

60% going to the OO (IO, or EO) and

40% going to the other (non OO) office (IO, or EO)

No TO office under this rule

45/45/10 rule

If a shipment is handled by an OO and a sister office is noted as the import or export office on the record with a third office handling other details,

45% of GM to the OO (IO, or EO);

45% to the handling office listed on the record and (IO, or EO – non OO)

10% to the 3rd office involved (TO, non OO)

TO cannot be = to OO under this rule

20/40/40 rule

If a shipment is not handled by an OO and two other office are listed as the export and import offices,

20% of GM to the OO (TO)

40% to each handling office (IO and EO)

TO must be = to OO under this rule

20/80 rule

If a shipment is handled by only one office but is not owned by that office,

20% of GM is attributed to the OO

80% to the handling office (IO, EO)

No TO Office under this rule

Only 1 office on the record (cannot be the OO)\

100% rule

– If a shipment is handled and owned by only one office, the only office listed in the ‘ref’ numbers is also the OO

100% GM goes to the OO

Only 1 office on the record (must be the OO)

Closing dates

Files will automatically be closed down daily with set dates, after the actual date of departure, according to the mode of shipment as outlined in the table below:

Air - 35 days

FCL - 60 days

LCL - 70 days

Truck - 35 days

The above need to be editable by ‘management’ with a record of updates.

Files can be opened after their system close date for changes by and approved group of users, but it is expected this will be the exception.

If a file is “closed” and then “opened” again – it needs to be “closed” (manually -> Action required e-mail? Something else? How do we identify a file that was “opened” and not “closed” again??).

When a file is “closed again” the file needs to be included in the next automated “GM Margin” calculation and invoicing.

Questions: File “closing” -> What updates should be allowed? Comments, Internal Comments? What else?

All in one place

Marc: Can we have all related functionality in one place in the system - admin, reports, etc.

SOW 1

0003403: [Profit Sharing Module] SOW 1 - part B - Misha

Closing Time Frame

Represents pairs: MOT/Days.

Meaning: it takes this number of days since departure to receive and issue all related to particular CT invoices. Therefore only after CT is "closed" actual P/L and internal invoices can be calculated. It is used in calculating #OC status.

Example:

Air - 35 days
FCL - 60 days
LCL - 70 days
Truck (all 3 sub-modes) - 35 days

Number of days is editable field.

Set default as 1000.

Add this to User Access table.

OC status

OC (Open/Closed) status.

Values:

closed before 2012
open
manually open
closed

There are three ways to set this status: automatically, manually and by script.

Setting OC status automatically

When new shipment is created its status is automatically "open".

When OC status is "open" AND the number of days between Actual Date Of Departure and today's day becomes equal to #Closing Time Frame OC status would change automatically to "closed".

OC status change trigger GMS status change

CTs that changed OC status to "closed" should trigger change of GMS Status to "pending GMS".

OC process

Tech Note
There will be "OC process" checking the condition above and performing actions defined above. It will wake up every one hour.

Setting OC status by script

All CTs that would close prior to Jan 1, 2012 (if this functionality would be available since the beginning) should have status "closed before 2012".

All CTs that would close from Jan 1, 2012 to Release day (if this functionality would be available since the beginning) should have status "closed before 2012" should have status "closed".

Tech Note
--------
To achieve above we need to write and run appropriate scripts prior Release !

Setting OC status manually

We need to provide functionality to manually:

set status to "closed" on open CTs
set status to "mannually open" on closed CTs (user who opened CT manually must close CT manually)

This is subject to user access permissions.

Suggested implementation: add "Close/Open file" button to P/L Tab.

Closed CT have different permissions

CT (file) that has "closed" status should limit what operators can do with record. Operations such as invoicing should be blocked.

Implement in this SOW:

generate sales invoice/credit note
map PI invoice

Future SOWs:

edit any/some CT fields
posting into Com
generating docs
upload files

Add section to Admin to manage list above.

GMS status

GMS (Global Marging Split) Statuses:

n/a: closed before 2012 - GMS status for CTs that have OC status "closed before 2012". Need script - see below.
not ready for GMS - CTs that are "open" have this status
pending GMS - CTs that changed status from open to closed should trigger change to "pending GMS" status
GMS completed - after system runs Global Margin Split on this CT system successfully must change status to "completed"
error - after system runs Global Margin Split on this CT system unsuccessfully (example: CT does not fall under any of GMS case)

Tech Note
---------
Need to run script that will set  n/a: closed before 2012  for all CTs that have OC status "closed before 2012".

Owners Office

Owners Office (OO) is a field (attribute) that identify what Jaguar office is the OO for particular Client Company - see Client Companies.

Relationship is one to one.

Add this to "Part A." section on UI.

Import, Export and Third Offices

EO, IO, TO are identified by File Reference Numbers

Identify and Display Offices, Statuses and GMS Case on Gen Tab

See mock up below.

Implementation Note 
Red Box A will be done under File Reference Numbers (by AK) and Red Box B will be done under this SOW by Sasha/Misha.

Add GM Split section to Admin

Add number of configuration options - see below.

Add under Acc > GM Split.

See table below.

%-ages must be stored in the system.

Need a view of current %s.

User could edit %s.

New edit of the table should have associated time frame (from date to date) affecting GM Split re-calculations.

Add this as a property into User Access table

GM Split rules

GM ratio

More than one "GM ratio" (“percentage model”) could be applicable - more than one table as in Fig above could be defined.

Each case should have a name (optional, need textbox for that) and id generated by the system.

We should have one default "GM ratio", and then a number of alternative "GM ratio" (same scenarios, just different percentages). By default a Client Company would be subject to the ‘default’ model.

It means that "GM ratio" is a new property for Client Company entity.

Schedule for GM Split calculation

For example it would be possible to say that GM Split calculation will happen once a day at 17:00 EST

Credit to the office

Ability to post some credit from one office to another office.

This amount will be taken into account when calculating GM split.

Initially this list will be empty. Later someone could come and add lines similar to below:

Move 100,000.00 USD from NY to Paris office on February 15, 2012.

Table should have the following columns:

from office
to office
amount
currency

We should have a log of who posted and when.

Line can be edited only until it is used by GMS Runner.

Log for Admin Changes

Post all changes into Log.

SOW 1 Implementation Notes

GM Split Admin

SOW 2

0003322: [Profit Sharing Module] SOW 1 - part A - Sasha

Run GM Split and Generate Internal Invoices

System has to generate internal invoices between offices to re-distribute profit.

System would Run GM Split and Generate Internal Invoices periodically for example once a day.

Exact schedule would be defined here: #Schedule.

One invoice would cover many many CTs.

Algorithm for scheduled GM Split Run triggered by Scheduler

0) Identify CTs that are subject to GMS calculation this time (CTs with GMS status = "pending GMS" )

1) For every CT in the pool above calculate GM Split and post results into DB

2) after #1 is completed set GMS status = "GMS completed" or "error" for these CTs

3) create global internal interoffice invoices for above CTs that has become "GMS completed"

4) update Gen, Inv, P/L tabs to reflect that info

5) send e-mail (or post into Dashboard) simple report of CTs that have "GMS status"= error

Invoices generated by GM Split Runner

Number of invoices

There should be limited number of invoices issued each time Runner runs. It corresponds to num of unique pairs from the list of offices. In 2012 it would be:

NY/HK
NY/Lon
NY/Par
HK/Lon
HK/Par
Lon/Par

Layout

See mock up in Example below.

Apply new GMS split rules to GMS completed CTs

'!!! Spec changed !!!

This is the case when we want to change GM Split %ages and apply them not only in the future GMS runs but also recalculate for CTs that have GMS status completed.

This can be achieved by changing OC status for such group of CTs from closed to open to closed. This will force GMS status to be "pending" and next time GMS Runner runs it will re-adjust GMS for these CTs.

Example 1 (Non groupped CTs)

Let's look at the simple case.

Assume that GM Split Run found only one CT with GMS Status = pending.

Assume that this CT is CT#325563, AIR, JACKEL COSMETICS (HK) LTD from live site.

Gen Tab

Inv Tab

P n L Tab

Pdf mock up

List of Internal Invoices or Search Inv Report

We need some kind of way to see all Internal invoices generated by the System.

One option would be to re-use Search Internal Invoices report. See Mock up below with possible new layout.

This solution implies that you would be able to use various existing filters such as Issued from, etc.

Alternatively you could simply provide a list of invoices with paging sortable by Inv#, Issuing date. Ideally with fiber by Issuing office.

First release of this component could have simplest design possible (fastest to code) because of time pressure.

How other Reports such as PnL for will be affected by this change

We are changing the way we handle Internal Invoices. This could affect or even break some of the existing reports such as PnL Report for Multiple CTs (summary and detailed view).

See screenshots below.

Major difference as far as structure of invoice/values that could affect how data is represented in DB and how reports are affected:

invoice issued by = System (not operator)
one invoice corresponds not just one CT or CT group but to any collection of CTs and groups (any mode, any Client)

Additional DB structure analysis required to answer that. Sasha/Kostya/Alex will discuss that.

Example 2 (Groupped CTs)

Misc

=== How P/L tab is affected ===
=== Removing existing II functionality that conflicts with new GM split functionality ===
* no need to issue II from CT profile
=== Reporting ===
==== Changes to existing reports ====
==== New non KPI DR reports ====
==== New KPI DR reports ====

SOW 3

@@ Line 471: / Line 471: @@
 [[File:GM Split Search Internal Invoices 1.jpg | 700px]]
-==== How other Reports will be affected by this change ====
+==== How other Reports such as PnL for will be affected by this change ====
 We are changing the way we handle Internal Invoices. This could affect or even break some of the existing reports such as PnL Report for Multiple CTs (summary and detailed view).
@@ Line 482: / Line 482: @@
 Additional DB structure analysis required to answer that. Sasha/Kostya/Alex will discuss that.
+[[File:PnL Rep for Multiple CTs Summary.jpg | 700px]]
 === Example 2 (Groupped CTs) ===

GMS