TMS Monthly Stats Report

From UG

(Difference between revisions)

Revision as of 21:14, 9 August 2011

Info

Mantis: 0003003: [TMS optimization] TMS Monthly Stats (make it art of CT2) 3003

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.

Business Requirements & Current Business Process

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. These are the report columns set from the TM:

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.

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.

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.

Carriers:

Landstar
Lawrence Transportation
Daybreak Express
Albert Farms
Dick Harris & Son

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.

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

Separate Exceptional FTL

From the monthly stats, report on the exceptional FTL lanes which are defined as

Separate Tri-State

Separate Tri-State Potential Savings

Separate Northbound

Separate LTL Northbound Savings

Separate LTL

Separate LTL Savings

Separate Small Package

Figures

TMS Report Menu

TMS Run Supplier Detail Report

Supplier Detail Report Results

Results in HTML (excel ss)

@@ Line 76: / Line 76: @@
 === Separate Exceptional FTL ===
+From the monthly stats, report on the exceptional FTL lanes which are defined as
 === Separate Tri-State ===