+1 650 491 3131

Transport Routing Automation

4 Hours

Before Automation

13 Minutes

After VBA Automation

87 Hours

Saved per month

  • Industry:
  • Banking, Financial Services

Company Profile: It is the world's largest custodian bank and asset servicing company, headquartered in New York City.

  • Department:
  •  Employee Transportation Arrangement (Operations)
  • Tool(s) Used:
  • Microsoft Excel VB Programming (Macro)

Need for Automation:

The operation team invests a huge amount of time manually every month (around 240 minutes approx.) in generating the trip sheets for both Pickup and Drops. It also factors in the human errors. Hence, looking for a solution, which could really save the man-hour and help us optimize the vehicle allocation to occupancy ratio.

Solution Abstract:

The entire process is divided into three phases; Clubbing, Vendor development and Trip Sheet Generation.

Cost-Benefit Analysis (Post Macro Implementation)

For Pickup :

Activity # Description Frequency Time Consumed - Manually Time Consumed - After Automation Total Time Savings Per Activity Total Time Saved Per Month
1 Phase I All Business days of the company (Approx 22-23 days) 30 Min 3 Min 27 Min 621 Min (10 Hrs)
2 Phase II All Business days of the company (Approx 22-23 days) 120 Min 5 Min 115 Min 2649 Min (44 Hrs)
3 Phase III All Business days of the company (Approx 22-23 days) 90 Min 5 Min 85 Min 1955 Min (32.5 Hrs)
Total 240 Min 13 Min 227 Min 87 Hrs

For Drop :

Activity # Description Frequency Time Consumed - Manually Time Consumed - After Automation Total Time Savings Per Activity Total Time Saved Per Month
1 Phase I All Business days of the company (Approx 22-23 days) 30 Min 3 Min 27 Min 594 Min (10 Hrs)
2 Phase II All Business days of the company (Approx 22-23 days) 40 Min/Shift 40*13=520 Min 15 * 13= 195 Minutes 325 Min 7150 Minutes (119 Hrs)
3 Phase III All Business days of the company (Approx 22-23 days) 90 Min 5 Min/shift (5 * 13 = 65) 25 Min 550 Minutes (9 Hrs)
Total 640 Min 263 Min 377 Min 8294 Min (138 Hrs)

Cost-Benefit Analysis (Post Macro Implementation)

For Pickup :

Frequency (Monthly)

All Business days of the company

Time consumed manually

30 Minutes

Time Consumed for automated activity

3 Minutes

Total time savings per activity

27 Minutes

Total time saved Per month

621 Mins (10 hrs)

Frequency (Monthly)

All Business days of the company

Time consumed manually

120 Minutes

Time Consumed for automated activity

5 Minutes

Total time savings per activity

115 Minutes

Total time saved Per month

2645 Mins (44 hrs)

Frequency (Monthly)

All Business days of the company

Time consumed manually

90 Minutes

Time Consumed for automated activity

5 Minutes

Total time savings per activity

85 Minutes

Total time saved Per month

1955 Mins (32.5 hrs)

For Drop :

Frequency (Monthly)

All Business days of the company

Time consumed manually

30 Minutes

Time Consumed for automated activity

3 Minutes

Total time savings per activity

27 Minutes

Total time saved Per month

594 Mins (10 hrs)

Frequency (Monthly)

All Business days of the company

Time consumed manually

40 Min/Shift 40*13=520 Min

Time Consumed for automated activity

15 * 13= 195 Minutes

Total time savings per activity

325 Minutes

Total time saved Per month

7150 Mins (119 hrs)

Frequency (Monthly)

All Business days of the company

Time consumed manually

90 Minutes

Time Consumed for automated activity

5 Min/shift (5 * 13 = 65)

Total time savings per activity

25 Minutes

Total time saved Per month

550 Mins (32.5 hrs)

Time Consumed Manually

640 Minutes

Time Consumed for automated activity

263 Min

Total Time Savings Per Activity

377 Minutes

Total time saved Per month

8294 Mins (138 hrs)

Join our 10-day online session and learn how to automate your Excel Reports and Processes using VBA Programming. No prior programming experience needed!.

Upcoming Schedule:

  • Date:
  • Sunday, July 11
  • Time:
  • 10:00 AM - 12:30 PM (ET)

Proficiency with Excel formulas is a prerequisite. Not sure about your skills? Schedule a pre-assessment at 650-491-3131 to evaluate your Excel proficiency.

Explore 90+ practical scenarios, delve into 7 case studies, and complete a live VBA project.

Ready to learn Excel VBA Macro?