TMS Monthly Stats Report

From UG

(Difference between revisions)

Revision as of 16:22, 30 November 2011

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

SOW 1

Create a module/methodology to produce this type of analytical reporting.

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

Core requirements:

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

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.

See #Figure_4

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.

See #Figure_5 for IE, but to see actual report for Jul, look at Mantis 2915 & Mantis: 3003

Also, the cleaned up results are the Global Month Tab - See #Figure_5a

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.

FTL Carriers

Landstar
Lawrence Transportation
Daybreak Express
Albert Farms
Dick Harris & Son

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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

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.

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

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

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.

FTL Standard

33 pallets or 33,000 lbs

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

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.

LTL Carriers

Fed-Ex National
YRC

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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

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.

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
- This column is added by MO and the #LTL_Savings_Formula is applied to achieve the results
Savings
- This column is added by MO and the #LTL_Savings_Formula is applied to achieve the results

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

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 by applying a target cost.

The columns are the same as the #LTL_Columns

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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
Target Cost Per LB
- This column is added by the MO and the #LTL_Savings_Formula is applied to achieve the results
Savings
- This column is added by the MO and the #LTL_Savings_Formula is applied to achieve the results

Northbound Savings Formula

Use #Separate_LTL_Savings

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.

Tri-State LTL Carriers

Jewels Transportation
Express Air

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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

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.

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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
- 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
- This column is added by MO and the #Tri-State_Savings_Formula are applied to achieve the results

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

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.

Small Package Carriers

Fed-Ex Small Package

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)
Carrier
Activity Type (this is added to the report manually and is used for the Activity Type Tab's Table/Pie Chart 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

Solution

SOW 2

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

Core requirements:

Create macros for chart tabs. There are several chart tabs that are needed and below are them with their definitions.

Charts

Consignee Location Chart

This report contains the historical data for the number of loads that were delivered to these specific consignee's within the Arden supply chain. And this tab works in conjunction with the results of the #Consignee_Location tab.

See #Figure_11

Total Weight

This report contains the historical data for all of the truck activity types. Reporting on the truck activity type and the amount of weight moved per carrier.

See #Figure_12

Load Count Moves

This report contains the historical data for the number of instances all loads were moved within the Arden supply chain. This tab works in conjunction with the results of the #Load_Counts tab.

See #Figure_13

Activity Type

An "Activity Type" is the type of truck move; within the Arden supply chain, there 4 types of trucking moves:

LTL
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. It includes the sum of the total weight for each of the activity type and has the total weight broken down by percentage. This table is used to update, fresh other tables within the entire report.

Denise's comments MO would like this to be added as a report to both the internal & client app's report menu

See: #Figure_6

Load Counts

This chart is used as a measuring tool in displaying the results of how many times a number of loads occurred within that month. It includes the total number of instances that had 1 load, 2 loads and so on. It includes the total number of instances, with the total load count and it's proportional value in a percentage. These figures & values are used to update, fresh other tables within the entire report.

Denise's comments MO would like this as a KPI

See #Figure_7

Consignee Location

This report displays the number of loads that were delivered to specific consignee's within the Arden supply chain. There are 6 names listed, the number of loads that were delivered to them and these values in a percentage. These figures & values are used to update, fresh other tables within the entire report.

The Consignee names are:

Evergreen
CEI
Roanoke
Kolmar
Sonoco
Others - this would be ALL other moves to consignee's not listed above

Denise's comments MO would like this as a KPI

See #Figure_8

Origin to Destination Total

This pie chart displays all suppliers per state, in load count order from largest to smallest and their values in percentages. These figures & values are used to update, fresh other tables within the entire report.

See #Figure_9

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

This chart displays the results of the dollar savings amount per activity type for all of the trucking moves that occurred within the month. It includes the sum of the total sales and total savings for each of the activity types including a percentage. These figures & values are used to update, fresh other tables within the entire report.

See: #Figure_10

Impact on DB performance and speed

TBD

SOW 3

Just a stub!!! If needed...

History

Links to Archived / Old specs

Mantis

See #SOW 2 FOR IE

User Guide

SOW covered

#SOW 1

Go into TMS to run report

TMS Reports Menu

Refresh Data Cache for current results

Run Report

Report Results

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

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.

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

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

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.

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.

FTL Savings Tab now Added

@@ Line 23: / 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.
-See [[#Go_into_TMS_to_run_report]], [[]]
+See [[#Go_into_TMS_to_run_report]], [[#Refresh_Data_Cache_for_current_results]] and [[#Run_Report]]
 ==== Columns set in the TMS ====