Transport Routing Automation

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?

See how an HR team of British multinational bank saved 40 man-hours monthly automating manual mapping & attribution simulator using Excel VBA

Fill-in the details.

BY INDUSTRY

BY FUNCTION

Transport Routing Automation