TMS Monthly Stats Report
From UG
(→Separate FTL) |
(→Separate FTL) |
||
Line 59: | Line 59: | ||
To improve on the actual cost per lb, for the loads that moved with these carriers, the following algorithm is used: | To improve on the actual cost per lb, for the loads that moved with these carriers, the following algorithm is used: | ||
- | *Calculate Best Possible Load Count: | + | *'''Calculate Best Possible Load Count:''' |
**Take Total Pallets & divide by 52 | **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 | **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: | + | *'''Calculate New Avg Weight:''' |
**Take Total Weight & divide by the Best Possible Load rounded up | **Take Total Weight & divide by the Best Possible Load rounded up | ||
- | *Calculate Avg Cost/Load: | + | *'''Calculate Avg Cost/Load:''' |
**Take Total Cost & divide by Load Count | **Take Total Cost & divide by Load Count | ||
- | *Calculate Load Count Saved | + | *'''Calculate Load Count Saved:''' |
**Take Load Count & subtract the Best Possible Load rounded up | **Take Load Count & subtract the Best Possible Load rounded up | ||
- | *Calculate Revised Total Charges: | + | *'''Calculate Revised Total Charges:''' |
**Take Avg Cost/Load & multiple by Best Possible Load Count rounded up | **Take Avg Cost/Load & multiple by Best Possible Load Count rounded up | ||
- | *Calculate USD Savings Amount: | + | *'''Calculate USD Savings Amount:''' |
**Take Total Charges & subtract the Revised Total Charges | **Take Total Charges & subtract the Revised Total Charges | ||
- | *Calculate Best Possible Cost Per Lb: | + | *'''Calculate Best Possible Cost Per Lb:''' |
**Take the REVISED TOTAL CHARGES & divide by Total Weight | **Take the REVISED TOTAL CHARGES & divide by Total Weight | ||
Revision as of 20:48, 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, we double check the Supplier Names & Origin Zip with the Consignees Name & Destination Zip AND the carrier. If necessary I will combine lanes as the TMS considers the suppliers/consignee's companies name to determine if it is a separate lane or not; it does not look at the street address, city name &/or state to determine this, so it is possible to have duplicate lanes on the report.
Separate FTL
From the monthly stats, report on the FTL lanes and find ways to improve on the actual costs by reducing the number of loads per lane. Within the Arden supply chain, the following 5 carriers are full trailer load carriers. This means that they do not handle/move only a few pallets, they move full truck loads at a time.
Carriers:
- Landstar
- Lawrence Transportation
- Daybreak Express
- Albert Farms
- Dick Harris & Son
To improve on the actual cost per lb, for the loads that moved with these carriers, the following algorithm is used:
- 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
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