TMS Monthly Stats Report

From UG

(Difference between revisions)
Jump to: navigation, search
(→Activity Type)
(→SOW 4)
 
(187 intermediate revisions not shown)
Line 1: Line 1:
-
[[Category:EDI to TMS]]
+
[[Category:Analytics Reports]]
-
== Info ==
+
=== Parent Mantis ===
-
Mantis: 0003003: [TMS optimization] TMS Monthly Stats (make it art of CT2) [http://ct.jaguarfreight.com/mantis/view.php?id=3003 3003]
+
* mantis [http://ct.jaguarfreight.com/mantis/view.php?id=2896 2896]: [TMS (OSDS)] [Optimize] ...... <subproj>
-
Related Mantis: 0002915: [TMS optimization] Marc's transportation strategies and calculations (FTL vs LTL) (ongoing monthly task)  [http://ct.jaguarfreight.com/mantis/view.php?id=2915 2915]
+
=== Scope of this wiki ===
-
 
+
-
== Summary ==
+
Elizabeth Arden's supply chain management requires a monthly statistical report of their domestic trucking activities.  In addition to their actual figures and numbers, they also require an idea of how or where they can save money within their supply chain.  The concept here is to add functionality, which is currently a manual process, into CT2, to automate these high level reports of the monthly domestic trucking statistics.
Elizabeth Arden's supply chain management requires a monthly statistical report of their domestic trucking activities.  In addition to their actual figures and numbers, they also require an idea of how or where they can save money within their supply chain.  The concept here is to add functionality, which is currently a manual process, into CT2, to automate these high level reports of the monthly domestic trucking statistics.
-
== Business Requirements & Current Business Process ==
+
'''!!! See example of this report attached to mantis # 0003003'''
 +
 
 +
== SOW 1 ==
 +
<!--
 +
Create a module/methodology to produce this type of analytical reporting.
 +
-->
 +
'''Mantis:''' [http://ct.jaguarfreight.com/mantis/view.php?id=2915 2915] [TMS optimization] Marc's transportation strategies and calculations: create non charts macros (part A)
 +
 
 +
Core requirements:
=== Generate report from the TMS ===
=== Generate report from the TMS ===
Line 17: Line 23:
This report is called "Supplier Detail Previous Month".  It was built by Descartes according to Marc's/Elizabeth Arden's requirements.  The report contains the number of lanes (Origin to destination) covered per month with the totals of their loads, broken down per carrier.  
This report is called "Supplier Detail Previous Month".  It was built by Descartes according to Marc's/Elizabeth Arden's requirements.  The report contains the number of lanes (Origin to destination) covered per month with the totals of their loads, broken down per carrier.  
-
See [[#Figure_1]], [[#Figure_2]], [[#Figure_3]]
+
See [[#Go into TMS to run report]], [[#Refresh Data Cache for current results]] and [[#Run Report]]
==== Columns set in the TMS ====
==== Columns set in the TMS ====
Line 45: Line 51:
''These columns and their values are used to make up the entire report (workbook) that is given to Elizabeth Arden monthly.''
''These columns and their values are used to make up the entire report (workbook) that is given to Elizabeth Arden monthly.''
-
 
-
See [[#Figure_4]]
 
==== Clean Up Results ====
==== Clean Up Results ====
Line 54: Line 58:
2. As ALL lanes must be unique, currently we double check the Suppliers Name, Origin Zip, Consignees Name, Destination Zip AND the carrier, as it is possible that the company city name passed from CyberTrax has a either a typo or an abbreviation to the name.  For IE: CEI H Holmdel, NJ 07733 & CEI Holmdel Holmdel, NJ 07733 are the same company and the 2 will be listed as separate lanes that would have to be combined.
2. As ALL lanes must be unique, currently we double check the Suppliers Name, Origin Zip, Consignees Name, Destination Zip AND the carrier, as it is possible that the company city name passed from CyberTrax has a either a typo or an abbreviation to the name.  For IE: CEI H Holmdel, NJ 07733 & CEI Holmdel Holmdel, NJ 07733 are the same company and the 2 will be listed as separate lanes that would have to be combined.
-
See [[#Figure_5]] for IE, but to see actual report for Jul, look at Mantis [http://ct.jaguarfreight.com/mantis/view.php?id=2915 2915]
+
For IE, see actual report for Jul, look at Mantis [http://ct.jaguarfreight.com/mantis/view.php?id=2915 2915] & Mantis: [http://ct.jaguarfreight.com/mantis/view.php?id=3003 3003]
=== Separate FTL ===
=== Separate FTL ===
Line 76: Line 80:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 93: Line 99:
=== Separate FTL Savings ===
=== Separate FTL Savings ===
-
Look for ways to improve on the actual cost per lb for the loads that moved with these FTL carrier, for every lane that has 4 or more loads, use the following algorithm to determine if any cost savings.
+
Look for ways to improve on the actual cost per lb for the loads that moved with these FTL carrier, for every lane that has more than 4 loads, use the following algorithm to determine if any cost savings.  Do not apply the cost savings formulas for lanes that do not have any savings, these lanes should still be listed, but should be shifted down with no formulas applied.
==== FTL Savings Columns ====
==== FTL Savings Columns ====
Line 120: Line 126:
*Cost Per Lb
*Cost Per Lb
*Preliminary Best possible Load Count  
*Preliminary Best possible Load Count  
-
*Preliminary Best possible Load Count 1 (rounded up)
+
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
-
*(NEW) Avg Weight per Load with 52 pallets
+
*Preliminary Best possible Load Count 1 (rounded up)  
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
 +
*(NEW) Avg Weight per Load with 52 pallets  
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
*(NEW) Avg Weight per Load (re-calc for 45000 lb)
*(NEW) Avg Weight per Load (re-calc for 45000 lb)
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
*Best possible Load Count
*Best possible Load Count
-
*Best possible Load Count (rounded up)  
+
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
-
*Avg Cost/Load
+
*Best possible Load Count (rounded up)
-
*Load Count Saved  
+
**'''This column is added by Andrei and macros is applied to achieve the results according to the below formula'''  
-
*Revised Total Charges  
+
*Avg Cost/Load
-
*USD amount savings
+
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
 +
*Load Count Saved
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''  
 +
*Revised Total Charges
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''  
 +
*USD amount savings
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
*Best Possible Cost per LB
*Best Possible Cost per LB
 +
**'''This column is added by Andrei and macros is applied to achieve the results according to the [[#FTL_Savings_Formula]] below'''
==== FTL Savings Formula ====
==== FTL Savings Formula ====
Line 182: Line 199:
*Cost Per Lb
*Cost Per Lb
*Best Possible Cost per LB
*Best Possible Cost per LB
-
 
+
**'''This column is added by MO and since there are no ways to apply savings here, the Best Possible Cost per LB is = the actual cost per lb'''
-
==== Exceptional FTL Formula ====
+
-
 
+
-
* Use [[#FTL_Savings_Formula]]
+
=== Separate LTL ===
=== Separate LTL ===
Line 204: Line 218:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 221: Line 237:
=== Separate LTL Savings ===
=== Separate LTL Savings ===
-
For the LTL moves, there's a "Target Cost" of $ 0.15 per lb applied to each lane; that if they can achieve this avg price of $0.15, they can save this about of money per lane.   
+
For the LTL moves, there's a "Target Cost" of $ 0.15 per lb applied to each lane that have cost per lb $ 0.16 or more.  This $ 0.15 is an arbitrary number used to say that if EA can achieve this avg price of $0.15 with their LTL carriers, they can save this about of money per lane.  The savings tab should only include results from [[#Separate LTL]] results and only include the lanes for the [[#LTL Carriers]].  Do not apply the cost savings formulas for lanes that do not have any savings, these lanes should still be listed, but should be shifted down with no formulas applied.
==== LTL Savings Columns ====
==== LTL Savings Columns ====
Line 245: Line 261:
*Avg Weight Per Load
*Avg Weight Per Load
*Cost Per Lb  
*Cost Per Lb  
-
*Best Possible Cost per LB
+
*Best Possible Cost per LB
 +
**'''This column is added by MO and the [[#LTL_Savings_Formula]] is applied to achieve the results'''
*Savings
*Savings
 +
**'''This column is added by MO and the [[#LTL_Savings_Formula]] is applied to achieve the results'''
==== LTL Savings Formula ====
==== LTL Savings Formula ====
Line 253: Line 271:
** IE Total charges of $ 935.926 - (total weight of 5959 x 0.15 target cost) = $ 42.08 ltl savings
** IE Total charges of $ 935.926 - (total weight of 5959 x 0.15 target cost) = $ 42.08 ltl savings
-
=== Separate LTL Northbound Savings ===
+
=== Separate LTL Northbound ===
 +
 
 +
From the LTL lanes/carriers, look for all moves that are going from the Southeast (NC, TN, VA, GA.) going to the Northeast (NJ, NY, ME, CT, PA) to find ways to improve on these actual cost per lb. The lanes to be included are also based upon the [[#LTL Carriers]] and [[#Tri-State LTL Carriers]] that are moving from the SE to the NE.  Their results are NOT to include the [[#FTL Carriers]] and/or the [[#Small Package Carriers]] lanes.
-
From the LTL lanes/carriers, look for all moves that are going from the Southeast (NC, TN, VA, GA.) going to the Northeast (NJ, NY, ME, CT, PA) to find ways to improve on these actual cost per lb by applying a target cost.
+
The columns are the same as the [[#LTL Columns]] but data is only for the Northbound lanes.
==== Northbound Savings Columns ====
==== Northbound Savings Columns ====
Line 265: Line 285:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 277: Line 299:
*Highest Weight Per Load
*Highest Weight Per Load
*Lowest Pallet Per Load
*Lowest Pallet Per Load
-
*Highest Pallet Per Load
+
*Highest Pallet Per Load
-
*Cost Per Lb
+
*Cost Per Lb
-
*Target Cost Per LB
+
*Best Cost Per Lb
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
 +
*Revised Load Count
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
 +
*Revised Weight Per Load
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
 +
*Revised Pallets per Load
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
 +
*Revised Total Charge
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
*Savings
*Savings
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
==== Northbound Savings Formula ====
==== Northbound Savings Formula ====
-
Use [[#Separate_LTL_Savings]]
+
Use [[#Tri-State Savings Formula]]
-
=== Separate Tri-State ===
+
=== Separate Tri-State LTL ===
From the monthly stats, report on the "Tri-State" LTL lanes; within the Arden supply chain, there are two carriers that handle the majority of the Tri-State pickups and deliveries.  These carriers can handle both LTL & FTL moves, depending upon the amount of freight moving from one destination to another.
From the monthly stats, report on the "Tri-State" LTL lanes; within the Arden supply chain, there are two carriers that handle the majority of the Tri-State pickups and deliveries.  These carriers can handle both LTL & FTL moves, depending upon the amount of freight moving from one destination to another.
-
==== Tri-State Carriers ====
+
==== Tri-State LTL Carriers ====
*Jewels Transportation
*Jewels Transportation
*Express Air
*Express Air
-
==== Tri-State Columns ====
+
==== Tri-State LTL Columns ====
*Supplier
*Supplier
Line 303: Line 335:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 330: Line 364:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 344: Line 380:
*Highest Pallet Per Load
*Highest Pallet Per Load
*Cost Per Lb  
*Cost Per Lb  
-
*Best Cost Per Lb
+
*Best Cost Per Lb
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
*Revised Load Count
*Revised Load Count
-
*Revised Weight Per Load  
+
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
-
*Revised Total Charge
+
*Revised Weight Per Load
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''  
 +
*Revised Pallets per Load
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
 +
*Revised Total Charge
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
*Savings
*Savings
 +
**'''This column is added by MO and the [[#Tri-State_Savings_Formula]] are applied to achieve the results'''
==== Tri-State Savings Formula ====
==== Tri-State Savings Formula ====
-
'''Denise's Notes:  This is under construction, it is not correct & am awaiting Marc's confirmation'''
+
This is currently how MO is currently reducing load counts & figuring savings:
*'''Calculate Best Cost Per Lb:'''
*'''Calculate Best Cost Per Lb:'''
-
** Take the REVISED TOTAL CHARGES & divide by Total Weight
+
**Take the revised total charges & divide by the actual total weight
 +
 
*'''Calculate Revised Load Count:'''
*'''Calculate Revised Load Count:'''
-
** Take Total Weight & divide by the Revised Weight Per Load
+
**Take the actual # of loads, divide by 4 and round up to the next whole #, then subtract from the actual # of loads.
 +
***IE Actual # of loads 27 -/- 4 = 6.75 then subtract 6.75 - 27 = 20.25 & round up to next whole # = 21 and that's your revised load count
 +
 
*'''Calculate Revised Weight per Load:'''
*'''Calculate Revised Weight per Load:'''
-
** Take Total Weight & divide by the Revised Load Count
+
**Take total weight and divide by revised load count
 +
 
*'''Calculate Revised Pallets per Load:'''
*'''Calculate Revised Pallets per Load:'''
-
** Take the Total Pallets & divided by Revised Load Count
+
**Take the Total Pallets & divide by Revised Load Count
 +
 
*'''Calculate Revised Total Charges:'''
*'''Calculate Revised Total Charges:'''
-
**
+
**Look at original amount of loads and reduce their total costs as follows
-
*'''Calculate Savings:'''
+
***5 to 7 loads, reduce costs by 10%
-
** Take the Total Charges & subtract the Revised Total Charges
+
***8 to 10 loads, reduce costs by 15 %
 +
***11 + loads, reduce costs by 20%
 +
 
 +
*'''Calculate Savings:'''  
 +
**Take the Total Charges & subtract the Revised Total Charges
=== Separate Small Package ===
=== Separate Small Package ===
Line 383: Line 435:
*Destination State
*Destination State
*Destination Zip
*Destination Zip
-
*Destination Key
+
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Carrier
*Carrier
-
*Activity Type
+
*Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
<font color="red" size="4"> This column is no longer needed as MO does not want to provide graphics on this report. </font>
*Load Count  
*Load Count  
*Total Charge
*Total Charge
Line 398: Line 452:
*Cost Per Lb
*Cost Per Lb
-
== Charts ==
+
=== Solution ===
-
=== Consignee Location Chart ===
+
== SOW 2 ==
-
=== Total Weight ===
+
'''Mantis:''' [http://ct.jaguarfreight.com/mantis/view.php?id=3222 3222] [TMS optimization] Marc's transportation strategies and calculations: create charts macros (part B)
-
=== Load Count Moves ===
+
Core requirements:
-
=== Activity Type ===
+
Create macros for Savings Overview tab without adding the graphical representation.
-
An "Activity Type" is the type of truck move; within the Arden supply chain, there 4 types of trucking moves:
+
=== Savings Overview ===
-
*LTL
+
The savings overview displays dollar savings per type of truck moves:
-
*Regular FTL
+
-
*Tri-State LTL
+
-
*Small Package
+
-
This chart displays the results of the "Activity Type" for all of the trucking moves that occurred within the month.
+
* Tri-State
 +
* FTL
 +
* LTL - No Savings
 +
* LTL - Northbound
 +
* Exceptional FTL
 +
* Small Package
-
=== Load Counts ===
+
Just display the total dollar savings amount, per activity type, for all of the trucking moves that occurred within the month. It should include the sum of the total sales and total savings for each of the activity types including a percentage from that month.
-
=== Consignee Location ===
+
=== Examples of charts ===
-
=== Origin to Destination Total ===
+
==== Savings overview tab IE ====
-
=== Savings Overview ===
+
[[File:Savings Overview.JPG]]
-
== Figures ==
+
=== Impact on DB performance and speed ===
-
=== Figure 1 ===
+
N/A as this is currently being preformed outside of CT
 +
 
 +
== SOW 3 ==
 +
 
 +
'''Mantis:''' [http://ct.jaguarfreight.com/mantis/view.php?id=3351 3351] [TMS optimization] Change: Marc's transportation strategies and calculations: create non charts macros (part A)
 +
 
 +
Core requirements:
 +
 
 +
2 change requests were received for macros as follows: 
 +
 
 +
1. Change '''LTL Northbound Savings''' formula
 +
* Use the same [[#Tri-State Savings Formula]] for LTL Northbound Savings Tab
 +
   
 +
2. Change for parameters on control tab, list both LTL Northbound Savings & TriState LTL Savings as "Load count optimization" 
 +
 
 +
*LTL Northbound Parameters & TriState LTL Parameters:
 +
**Load Count: Divide by 4
 +
***If 5 to 7 loads, reduce costs by 10%
 +
***If 8 to 10 loads reduce costs by 15%
 +
***If 11+ loads reduce costs by 20%
 +
 
 +
== SOW 3a ==
 +
 
 +
'''Mantis:''' [http://ct.jaguarfreight.com/mantis/view.php?id=3351 3351] [TMS optimization] Change: Marc's transportation strategies and calculations: create non charts macros (part A)
 +
 
 +
Remove 2 columns from report:
 +
 
 +
Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 +
 
 +
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 +
 
 +
<font color="red" size="4"> These 2 columns are no longer needed as MO does not want to provide graphics on this report. </font>
 +
 
 +
== History ==
 +
 
 +
=== Links to Archived / Old specs ===
 +
 
 +
=== Mantis ===
 +
 
 +
* See [[#SOW 1]]
 +
* See [[#SOW 2]]
 +
* See related mantis [http://ct.jaguarfreight.com/mantis/view.php?id=3003 3003]
 +
 
 +
== User Guide ==
 +
 
 +
=== SOW covered ===
 +
 
 +
[[#SOW 1]]
 +
 
 +
==== Go into TMS to run report ====
'''TMS Reports Menu'''
'''TMS Reports Menu'''
 +
[[File:TMS Reports Menu.JPG]]
[[File:TMS Reports Menu.JPG]]
-
=== Figure 2 ===
 
-
'''TMS Report Menu Filters'''
+
==== Refresh Data Cache for current results ====
 +
 
[[File:Run Supplier Report.JPG]]
[[File:Run Supplier Report.JPG]]
-
=== Figure 3 ===
 
-
'''Report Results'''
+
==== Run Report ====
 +
 
[[File:Results.JPG]]
[[File:Results.JPG]]
-
=== Figure 4 ===
 
-
'''HTML Report Results'''
+
'''Report Results'''
 +
 
[[File:Results in HTML.JPG]]
[[File:Results in HTML.JPG]]
-
=== Figure 5 ===
 
-
'''Cleaned Up/Unique Lanes'''
+
==== Open excel & enable the macros ====
-
[[File:Jul Stats Clean.JPG]]
+
 
 +
'''Select “Options…” at the top of the sheet, then once the popup appears, select “Enable this content” and press OK'''
 +
 
 +
[[File:Open enable macros.JPG]]
 +
 
 +
===== Notice Series of options available =====
 +
 
 +
1. Get Source File Button
 +
2. FTL Savings – Add Tab button & Calculate Savings button – with parameters settings
 +
3. Separate LTL Savings – Add Tab button & Calculate Savings button – with parameters settings
 +
4. Tri-State LTL Savings – Add Tab button & Calculate Savings button
 +
5. Northbound LTL Savings – Add Tab button & Calculate Savings button
 +
6. Separate Small Package
 +
 
 +
Note, these are all used for macros to create the tabs and savings formula’s according to previously defined business criteria/requirements.
 +
 
 +
[[File:All Options.JPG]]
 +
 
 +
==== First start with the “Get Source File” option ====
 +
 
 +
'''Popup appears asking you to select a file in which to use, you’ll use would the results of the TMS Supplier Detail Previous Month Report already generated and saved'''
 +
 
 +
[[File:Source File IE.JPG]]
 +
 
 +
'''Add file, press “Open”, a new tab will appear on your excel sheet and it’ll be called “Global Source” which are the results of the report that you ran from the TMS'''
 +
 
 +
[[File:Global Source Tab.JPG]]
 +
 
 +
==== Next select the FTL Savings button, “Add Tab" ====
 +
 
 +
'''Macros working in the background to create the FTL tab, once macros is finished, the FTL tab will now be listed on the bottom of the sheet.'''
 +
 
 +
[[File:FTL Tab.JPG]]
 +
 
 +
==== Select the FTL Savings button, “Calculate Savings” ====
 +
 
 +
'''Popup appears asking you to continue (as below), say OK, then another popup will appear saying “Continue Working” and say OK again.'''
 +
 
 +
[[File:FTL Savings Macros.JPG]]
 +
 
 +
'''FTL Savings Tab now Added'''
 +
 
 +
[[File:FTL Savings Tab.JPG]]

Current revision as of 16:40, 12 March 2012


Contents

[edit] Parent Mantis

  • mantis 2896: [TMS (OSDS)] [Optimize] ...... <subproj>

[edit] Scope of this wiki

Elizabeth Arden's supply chain management requires a monthly statistical report of their domestic trucking activities. In addition to their actual figures and numbers, they also require an idea of how or where they can save money within their supply chain. The concept here is to add functionality, which is currently a manual process, into CT2, to automate these high level reports of the monthly domestic trucking statistics.

!!! See example of this report attached to mantis # 0003003

[edit] SOW 1

Mantis: 2915 [TMS optimization] Marc's transportation strategies and calculations: create non charts macros (part A)

Core requirements:

[edit] Generate report from the TMS

This report is called "Supplier Detail Previous Month". It was built by Descartes according to Marc's/Elizabeth Arden's requirements. The report contains the number of lanes (Origin to destination) covered per month with the totals of their loads, broken down per carrier.

See #Go into TMS to run report, #Refresh Data Cache for current results and #Run Report

[edit] Columns set in the TMS

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip
  • Carrier
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Total Loose Package
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Avg Loose Package Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Lowest Loose Package Per Load
  • Highest Loose Package Per Load
  • Cost Per Lb

These columns and their values are used to make up the entire report (workbook) that is given to Elizabeth Arden monthly.

[edit] Clean Up Results

1. As this report contains ALL lanes and their totals of loads, it will contain some moves that have total charges of $ 1.00. These need to be removed from the results as ALL $ 1.00 charges are for lanes that the business team does not have rates for when they tender a load to a carrier, as the TM will not allow a load to be tendered unless it is rated.

2. As ALL lanes must be unique, currently we double check the Suppliers Name, Origin Zip, Consignees Name, Destination Zip AND the carrier, as it is possible that the company city name passed from CyberTrax has a either a typo or an abbreviation to the name. For IE: CEI H Holmdel, NJ 07733 & CEI Holmdel Holmdel, NJ 07733 are the same company and the 2 will be listed as separate lanes that would have to be combined.

For IE, see actual report for Jul, look at Mantis 2915 & Mantis: 3003

[edit] Separate FTL

From the monthly stats, report on the FTL lanes and find ways to improve on the actual cost per lb by reducing the number of loads per lane. Within the Arden supply chain, the following 5 carriers are full trailer load carriers. These carrier do not handle/move only a few pallets at a time, they actually move full truck loads at a time.

[edit] FTL Carriers

  • Landstar
  • Lawrence Transportation
  • Daybreak Express
  • Albert Farms
  • Dick Harris & Son

[edit] FTL Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb

[edit] Separate FTL Savings

Look for ways to improve on the actual cost per lb for the loads that moved with these FTL carrier, for every lane that has more than 4 loads, use the following algorithm to determine if any cost savings. Do not apply the cost savings formulas for lanes that do not have any savings, these lanes should still be listed, but should be shifted down with no formulas applied.

[edit] FTL Savings Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip
  • Carrier
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Total Loose Package
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Avg Loose Package Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Lowest Loose Package Per Load
  • Highest Loose Package Per Load
  • Cost Per Lb
  • Preliminary Best possible Load Count
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Preliminary Best possible Load Count 1 (rounded up)
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • (NEW) Avg Weight per Load with 52 pallets
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • (NEW) Avg Weight per Load (re-calc for 45000 lb)
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Best possible Load Count
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Best possible Load Count (rounded up)
    • This column is added by Andrei and macros is applied to achieve the results according to the below formula
  • Avg Cost/Load
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Load Count Saved
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Revised Total Charges
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • USD amount savings
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below
  • Best Possible Cost per LB
    • This column is added by Andrei and macros is applied to achieve the results according to the #FTL_Savings_Formula below

[edit] FTL Savings Formula

  • Calculate Best Possible Load Count:
    • Take Total Pallets & divide by 52
    • Take Total Weight & divide by the rounded Best Possible Load Count – if greater than 45,000, we have to recalculate as Total Weight divided by 45000 and then use this number, rounded up
  • Calculate New Avg Weight:
    • Take Total Weight & divide by the Best Possible Load rounded up
  • Calculate Avg Cost/Load:
    • Take Total Cost & divide by Load Count
  • Calculate Load Count Saved:
    • Take Load Count & subtract the Best Possible Load rounded up
  • Calculate Revised Total Charges:
    • Take Avg Cost/Load & multiple by Best Possible Load Count rounded up
  • Calculate USD Savings Amount:
    • Take Total Charges & subtract the Revised Total Charges
  • Calculate Best Possible Cost Per Lb:
    • Take the REVISED TOTAL CHARGES & divide by Total Weight

[edit] Separate Exceptional FTL

From the monthly stats, look to see if there are any exceptional FTL moves. There are no true definitions used to determine this... These are full truck moves confirmed by Elizabeth Arden without having enough freight inside of the truck to make it a full truck load. IE 3 plts at 1500 lbs can be considered an exception if EA needs this freight ASAP and it is moved on a truck with no other freight; they will also pay the price of a full truck for that move. Although Arden does have a standard for what they consider as FTL, that is followed by the domestic team and when something is out of this range it needs approval to move as FTL from Arden.

[edit] FTL Standard

33 pallets or 33,000 lbs

[edit] FTL Exceptional Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip
  • Carrier
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Total Loose Package
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Avg Loose Package Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Lowest Loose Package Per Load
  • Highest Loose Package Per Load
  • Cost Per Lb
  • Best Possible Cost per LB
    • This column is added by MO and since there are no ways to apply savings here, the Best Possible Cost per LB is = the actual cost per lb

[edit] Separate LTL

From the monthly stats, report on the LTL lanes and find ways to improve on the actual cost per lb by applying a target cost of $ 0.15. Within the Arden supply chain, the following 2 carriers are less than trailer load carriers. These carrier do not handle/move full truck loads worth of freight at a time from 1 origin to 1 destination, they actually collect pallets and cartons from other locations through out their day and deliver to many other locations as well.

[edit] LTL Carriers

  • Fed-Ex National
  • YRC

[edit] LTL Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb

[edit] Separate LTL Savings

For the LTL moves, there's a "Target Cost" of $ 0.15 per lb applied to each lane that have cost per lb $ 0.16 or more. This $ 0.15 is an arbitrary number used to say that if EA can achieve this avg price of $0.15 with their LTL carriers, they can save this about of money per lane. The savings tab should only include results from #Separate LTL results and only include the lanes for the #LTL Carriers. Do not apply the cost savings formulas for lanes that do not have any savings, these lanes should still be listed, but should be shifted down with no formulas applied.

[edit] LTL Savings Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip
  • Carrier
  • Total Pallets
  • Total Loose Package
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Lowest Loose Package Per Load
  • Highest Loose Package Per Load
  • Load Count
  • Total Charge
  • Total Weight
  • Avg Weight Per Load
  • Cost Per Lb
  • Best Possible Cost per LB
  • Savings

[edit] LTL Savings Formula

  • Total Charges - (Total Weight x Target Cost)
    • IE Total charges of $ 935.926 - (total weight of 5959 x 0.15 target cost) = $ 42.08 ltl savings

[edit] Separate LTL Northbound

From the LTL lanes/carriers, look for all moves that are going from the Southeast (NC, TN, VA, GA.) going to the Northeast (NJ, NY, ME, CT, PA) to find ways to improve on these actual cost per lb. The lanes to be included are also based upon the #LTL Carriers and #Tri-State LTL Carriers that are moving from the SE to the NE. Their results are NOT to include the #FTL Carriers and/or the #Small Package Carriers lanes.

The columns are the same as the #LTL Columns but data is only for the Northbound lanes.

[edit] Northbound Savings Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb
  • Best Cost Per Lb
  • Revised Load Count
  • Revised Weight Per Load
  • Revised Pallets per Load
  • Revised Total Charge
  • Savings

[edit] Northbound Savings Formula

Use #Tri-State Savings Formula

[edit] Separate Tri-State LTL

From the monthly stats, report on the "Tri-State" LTL lanes; within the Arden supply chain, there are two carriers that handle the majority of the Tri-State pickups and deliveries. These carriers can handle both LTL & FTL moves, depending upon the amount of freight moving from one destination to another.

[edit] Tri-State LTL Carriers

  • Jewels Transportation
  • Express Air

[edit] Tri-State LTL Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb

[edit] Separate Tri-State Potential Savings

Look for ways to improve on the actual cost per lb for the loads that moved with these Tri-State carriers, for every lane that has 4 or more loads, use the following algorithm to determine if any cost savings.

[edit] Tri-State Savings Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb
  • Best Cost Per Lb
  • Revised Load Count
  • Revised Weight Per Load
  • Revised Pallets per Load
  • Revised Total Charge
  • Savings

[edit] Tri-State Savings Formula

This is currently how MO is currently reducing load counts & figuring savings:

  • Calculate Best Cost Per Lb:
    • Take the revised total charges & divide by the actual total weight
  • Calculate Revised Load Count:
    • Take the actual # of loads, divide by 4 and round up to the next whole #, then subtract from the actual # of loads.
      • IE Actual # of loads 27 -/- 4 = 6.75 then subtract 6.75 - 27 = 20.25 & round up to next whole # = 21 and that's your revised load count
  • Calculate Revised Weight per Load:
    • Take total weight and divide by revised load count
  • Calculate Revised Pallets per Load:
    • Take the Total Pallets & divide by Revised Load Count
  • Calculate Revised Total Charges:
    • Look at original amount of loads and reduce their total costs as follows
      • 5 to 7 loads, reduce costs by 10%
      • 8 to 10 loads, reduce costs by 15 %
      • 11 + loads, reduce costs by 20%
  • Calculate Savings:
    • Take the Total Charges & subtract the Revised Total Charges

[edit] Separate Small Package

From the stats report on all of the small package shipments; within the Arden supply chain there is only one small package carrier.

[edit] Small Package Carriers

  • Fed-Ex Small Package

[edit] Small Package Columns

  • Supplier
  • Origin State
  • Origin Zip
  • Consignee
  • Destination State
  • Destination Zip 
*Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Carrier 
*Activity Type	(this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)
 This column is no longer needed as MO does not want to provide graphics on this report.
  • Load Count
  • Total Charge
  • Total Weight
  • Total Pallets
  • Avg Weight Per Load
  • Avg Pallet Per Load
  • Lowest Weight Per Load
  • Highest Weight Per Load
  • Lowest Pallet Per Load
  • Highest Pallet Per Load
  • Cost Per Lb

[edit] Solution

[edit] SOW 2

Mantis: 3222 [TMS optimization] Marc's transportation strategies and calculations: create charts macros (part B)

Core requirements:

Create macros for Savings Overview tab without adding the graphical representation.

[edit] Savings Overview

The savings overview displays dollar savings per type of truck moves:

  • Tri-State
  • FTL
  • LTL - No Savings
  • LTL - Northbound
  • Exceptional FTL
  • Small Package

Just display the total dollar savings amount, per activity type, for all of the trucking moves that occurred within the month. It should include the sum of the total sales and total savings for each of the activity types including a percentage from that month.

[edit] Examples of charts

[edit] Savings overview tab IE

File:Savings Overview.JPG

[edit] Impact on DB performance and speed

N/A as this is currently being preformed outside of CT

[edit] SOW 3

Mantis: 3351 [TMS optimization] Change: Marc's transportation strategies and calculations: create non charts macros (part A)

Core requirements:

2 change requests were received for macros as follows:

1. Change LTL Northbound Savings formula

2. Change for parameters on control tab, list both LTL Northbound Savings & TriState LTL Savings as "Load count optimization"

  • LTL Northbound Parameters & TriState LTL Parameters:
    • Load Count: Divide by 4
      • If 5 to 7 loads, reduce costs by 10%
      • If 8 to 10 loads reduce costs by 15%
      • If 11+ loads reduce costs by 20%

[edit] SOW 3a

Mantis: 3351 [TMS optimization] Change: Marc's transportation strategies and calculations: create non charts macros (part A)

Remove 2 columns from report:

Destination Key (this column is added to the report manually and is used for the Consignee Location Tab's Pivot Table Report)

Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart Report)

These 2 columns are no longer needed as MO does not want to provide graphics on this report.

[edit] History

[edit] Links to Archived / Old specs

[edit] Mantis

[edit] User Guide

[edit] SOW covered

#SOW 1

[edit] Go into TMS to run report

TMS Reports Menu

File:TMS Reports Menu.JPG


[edit] Refresh Data Cache for current results

File:Run Supplier Report.JPG


[edit] Run Report

File:Results.JPG


Report Results

File:Results in HTML.JPG


[edit] Open excel & enable the macros

Select “Options…” at the top of the sheet, then once the popup appears, select “Enable this content” and press OK

File:Open enable macros.JPG

[edit] Notice Series of options available

1. Get Source File Button 2. FTL Savings – Add Tab button & Calculate Savings button – with parameters settings 3. Separate LTL Savings – Add Tab button & Calculate Savings button – with parameters settings 4. Tri-State LTL Savings – Add Tab button & Calculate Savings button 5. Northbound LTL Savings – Add Tab button & Calculate Savings button 6. Separate Small Package

Note, these are all used for macros to create the tabs and savings formula’s according to previously defined business criteria/requirements.

File:All Options.JPG

[edit] First start with the “Get Source File” option

Popup appears asking you to select a file in which to use, you’ll use would the results of the TMS Supplier Detail Previous Month Report already generated and saved

File:Source File IE.JPG

Add file, press “Open”, a new tab will appear on your excel sheet and it’ll be called “Global Source” which are the results of the report that you ran from the TMS

File:Global Source Tab.JPG

[edit] Next select the FTL Savings button, “Add Tab"

Macros working in the background to create the FTL tab, once macros is finished, the FTL tab will now be listed on the bottom of the sheet.

File:FTL Tab.JPG

[edit] Select the FTL Savings button, “Calculate Savings”

Popup appears asking you to continue (as below), say OK, then another popup will appear saying “Continue Working” and say OK again.

File:FTL Savings Macros.JPG

FTL Savings Tab now Added

File:FTL Savings Tab.JPG

Retrieved from "https://wiki.jaguarfreight.com/TMS_Monthly_Stats_Report"
Personal tools