Delivery efficiency rate and analysis for a logistics company

[Gotham]

Hi everyone,

I have been trying to create an algorithm to calculate the delivery efficiency rate (DER) of a logistics delivery company. The variables I have currently are late delivery risk( 1 or 0), distance between departure (customer city) and destination city(order city) , time taken for shipment, time taken for pre-shipment, weight of the parcel, the delivery fee revenue and delivery fee expense.

Currently, I am using one that involves a point system but I can't figure out which variable has the most effect on the delivery efficiency rate because I was already allocating a specific 'point' to each variable I used in the algorithm. I was wondering if anyone could lend me a hand in creating a new algorithm with the available variables that can help me to figure out how efficient the delivery process is. I could also use your valuable help in figuring out which variable has the most effect on the delivery efficiency rate.

Thank you!

 

[igold]

There are so many variables that you have not provided or perhaps not even thinking about. First would be, what defines an Efficient Delivery Route. Is it the route that completes the quickest, the route that is the least costly, the route that delivers the most packages in a given time frame, etc. Also, how many trucks, how many men per truck, time of day (rush hour), and the variables go on and on. I am not sure this is a task for Excel without integrating some sort of Map and real time traffic data.

Just my two cents.

 

[Gotham]

There are so many variables that you have not provided or perhaps not even thinking about. First would be, what defines an Efficient Delivery Route. Is it the route that completes the quickest, the route that is the least costly, the route that delivers the most packages in a given time frame, etc. Also, how many trucks, how many men per truck, time of day (rush hour), and the variables go on and on. I am not sure this is a task for Excel without integrating some sort of Map and real time traffic data.

Just my two cents.

Hi, I think I didn’t make my post clear enough. I’m actually a university student and I’m required to analyse a real business problem using the available data using excel. In other words, I do not have access to any of the variables you have mentioned even though there are unarguably relevant in this case.

 

[igold]

Ok, so usually I flat out do not do homework. That said and since you are being upfront about it, I still would need to know what is the criteria that you are supposed to use to determine a DER. Can you explain more. I don't know that I can do the problem but I would bang ideas off you and see what we come up with.

 

[igold]

Additionally, if you could either post your data on a share site or use the XL2BB add-in that would be great. I am definitely not going to re-type your data to a new worksheet.

 

[Gotham]

is there a share site you are familiar with?

 

[igold]

is there a share site you are familiar with?

Perhaps DropBox.

 

[Gotham]

Never mind, I managed to install XL2BB. It generates a pile of code when I copy it as a mini sheet. Just wanted to confirm that's what you are expecting?

 

[igold]

Yes, if you click on the Preview button on the upper right of the reply box you will see that the code creates a Worksheet...

 

[Gotham]

dataset_project sample.xlsx
	A	B	C		E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Late_delivery_risk	Shipment time	shipping date (DateOrders)		Order country	Weight of Parcel (kg)	Distance between customer and order city	Speed of delivery (km/hr)	Speed of delivery(including processing time	Delivery Fee (Revenue)	Earnings per shipment/kg	Earning per shipment/kg weightage	Late Delivery weightage	Speed of delivery weightage	Delivery efficiency rate
2	0	4	04/01/2015 12:21		Philippines	4.82	2335.71	24.33		$ 47.64	-93.85	-187.70	1	72.99079	-13700.5
3	1	4	06/01/2015 1:03		Philippines	52.98	2355.68	24.54		$ 509.10	0.17	0.34	4	73.61492	101.1545		Shipment expense	500
4	1	2	04/01/2015 2:06		Australia	60.97	5218.09	108.71		$ 891.22	6.42	12.83	4	326.1308	16741.2
5	1	4	07/01/2015 3:09		Australia	81.25	5214.45	54.32		$ 1,140.99	7.89	15.78	4	162.9516	10284.34
6	0	4	05/01/2015 4:12		Australia	12.79	6102.60	63.57		$ 167.55	-25.99	-51.99	1	190.7062	-9914.04
7	1	4	07/01/2015 4:54		Australia	103.06	5179.88	53.96		$ 1,447.99	9.20	18.40	4	161.8713	11911.69
8	0	4	05/01/2015 5:57		Vietnam	58.71	2137.37	22.26		$ 473.40	-0.45	-0.91	1	66.79268	-60.5241
9	1	4	06/01/2015 6:18		Vietnam	26.85	2138.78	22.28		$ 190.34	-11.53	-23.07	4	66.8369	-6166.62
10	1	1	03/01/2015 7:00		Australia	48.64	6300.51	262.52		$ 645.29	2.99	5.97	4	787.5644	18819.94
11	1	2	07/01/2015 8:24		Thailand	21.65	1402.97	29.23		$ 180.48	-14.76	-29.52	4	87.68531	-10352.8
12	1	1	03/01/2015 11:33		Australia	5.2	6177.51	257.40		$ 74.12	-81.90	-163.80	4	772.1887	-505938
13	1	2	05/01/2015 12:57		Philippines	6.8	2527.86	52.66		$ 72.83	-62.82	-125.64	4	157.9912	-79398.9
14	0	2	03/01/2015 9:01		Vietnam	81.23	1009.96	21.04		$ 658.51	1.95	3.90	1	63.12226	246.3501
15	0	4	06/01/2015 11:49		Vietnam	27.77	1028.35	10.71		$ 228.10	-9.79	-19.58	1	32.13597	-629.296
16	0	4	04/02/2015 12:49		Australia	65.98	6089.99	63.44		$ 944.61	6.74	13.48	1	190.3121	2564.858
17	1	4	06/02/2015 1:31		Australia	61.5	6104.53	63.59		$ 888.55	6.32	12.64	4	190.7666	9641.931
18	1	1	03/02/2015 4:41		Australia	29.2	6183.55	257.65		$ 402.37	-3.34	-6.69	4	772.944	-20674.7
19	1	4	07/02/2015 5:23		Australia	5.5	6170.83	64.28		$ 80.35	-76.30	-152.60	4	192.8383	-117709
20	0	4	04/02/2015 7:50		Australia	33.95	6540.11	68.13		$ 474.54	-0.75	-1.50	1	204.3783	-306.537
21	1	4	06/02/2015 8:32		Australia	75.54	6384.50	66.51		$ 1,046.51	7.23	14.47	4	199.5157	11547.51
22	0	2	03/02/2015 7:45		Vietnam	26.9	1029.21	21.44		$ 207.18	-10.89	-21.77	1	64.32553	-1400.43
23	1	2	06/02/2015 8:48		Philippines	101.14	2279.73	47.49		$ 959.62	4.54	9.09	4	142.4834	5180.005
24	1	2	05/02/2015 11:57		Australia	26.12	5961.59	124.20		$ 375.23	-4.78	-9.55	4	372.5992	-14238.7
25	1	4	06/03/2015 2:11		Philippines	33.21	2607.13	27.16		$ 317.80	-5.49	-10.97	4	81.47288	-3575.88
26	0	4	03/03/2015 2:53		Philippines	16.73	2601.97	27.10		$ 165.36	-20.00	-40.00	1	81.31142	-3252.85
27	1	4	06/03/2015 3:56		Philippines	56.84	2591.67	27.00		$ 545.04	0.79	1.58	4	80.98973	513.4099
28	1	4	07/03/2015 4:17		Philippines	51.26	2595.32	27.03		$ 491.15	-0.17	-0.35	4	81.10379	-112.02
29	1	1	03/03/2015 6:02		Philippines	28.74	2216.76	92.36		$ 272.08	-7.93	-15.86	4	277.0948	-17579.8
30	1	2	05/03/2015 8:51		Australia	27.77	6102.62	127.14		$ 403.79	-3.46	-6.93	4	381.4139	-10571.4
31	0	4	04/03/2015 10:15		Australia	23	5957.78	62.06		$ 339.46	-6.98	-13.96	1	186.1805	-2599.08
32	1	4	06/03/2015 12:42		Australia	60.36	6117.35	63.72		$ 853.41	5.86	11.71	4	191.167	8954.32
33	0	4	03/03/2015 1:24		Philippines	33.27	2455.42	25.58		$ 319.57	-5.42	-10.85	1	76.73198	-832.266
34	1	2	04/03/2015 3:30		Philippines	72.31	2454.46	51.13		$ 686.74	2.58	5.16	4	153.4037	3169.31
35	1	2	04/03/2015 5:15		Australia	72.9	6130.30	127.71		$ 1,038.75	7.39	14.78	4	383.1436	22652.25
36	0	4	05/03/2015 7:21		Thailand	56.25	1485.34	15.47		$ 477.58	-0.40	-0.80	1	46.41685	-37.0015
37	1	2	07/03/2015 8:03		Australia	82.31	5627.58	117.24		$ 1,171.25	8.16	16.31	4	351.724	22946.88
38	1	4	06/03/2015 9:27		Philippines	86.03	2456.92	25.59		$ 818.71	3.70	7.41	4	76.77863	2275.496
39	1	4	07/03/2015 9:48		Philippines	7.09	2395.72	24.96		$ 70.08	-60.64	-121.28	4	74.86617	-36317.6
40	1	4	06/03/2015 11:12		Australia	43.24	6309.47	65.72		$ 628.11	2.96	5.93	4	197.171	4673.371
41	1	2	04/04/2015 12:13		Australia	5.5	6095.81	127.00		$ 80.35	-76.30	-152.60	4	380.9882	-232555
42	1	4	07/04/2015 1:16		Thailand	36.11	1861.82	19.39		$ 314.74	-5.13	-10.26	4	58.18179	-2387.98
43	1	2	04/04/2015 1:58		Australia	56.25	6345.65	132.20		$ 782.85	5.03	10.06	4	396.603	15954.37
44	1	4	07/04/2015 3:01		Australia	5.2	6177.83	64.35		$ 74.12	-81.90	-163.80	4	193.0572	-126491
45	0	4	04/04/2015 3:43		Australia	47.23	6112.95	63.68		$ 664.15	3.48	6.95	1	191.0298	1327.866
46	0	4	05/04/2015 9:20		Thailand	34.51	1427.86	14.87		$ 304.71	-5.66	-11.32	1	44.62071	-505.012
47	1	4	06/04/2015 9:41		Australia	29.07	6161.98	64.19		$ 406.02	-3.23	-6.47	4	192.5619	-4980.25
48	1	4	06/04/2015 11:26		Australia	85.56	5628.74	58.63		$ 1,210.58	8.31	16.61	4	175.898	11686.73
49	1	4	07/04/2015 11:47		Australia	4.82	5628.06	58.63		$ 67.89	-89.65	-179.30	4	175.8769	-126138
50	0	4	03/04/2015 12:08		Australia	9.23	5631.96	58.67		$ 123.95	-40.74	-81.48	1	175.9988	-14341.1
51	1	4	07/04/2015 3:17		Australia	32.59	6096.68	63.51		$ 471.68	-0.87	-1.74	4	190.5212	-1324.47
52	0	4	04/04/2015 5:44		Australia	58.31	5423.81	56.50		$ 839.35	5.82	11.64	1	169.494	1972.828
53	1	1	03/05/2015 2:17		Vietnam	91.93	2130.31	88.76		$ 740.77	2.62	5.24	4	266.2885	5579.401
54	1	4	07/05/2015 6:29		Indonesia	48.8	1640.87	17.09		$ 300.10	-4.10	-8.19	4	51.27713	-1680.38
55	0	4	05/05/2015 11:02		Australia	13.21	6102.56	63.57		$ 191.21	-23.38	-46.75	1	190.7049	-8915.63
56	1	2	06/05/2015 4:38		Thailand	21.65	1563.06	32.56		$ 180.48	-14.76	-29.52	4	97.69109	-11534.1
57	0	4	04/05/2015 10:57		Vietnam	47.34	985.65	10.27		$ 353.05	-3.10	-6.21	1	30.8015	-191.224
58	0	4	04/06/2015 12:39		Vietnam	81.04	2243.15	23.37		$ 630.26	1.61	3.21	1	70.09854	225.3464
59	1	4	07/06/2015 3:28		Australia	10.7	5995.51	62.45		$ 154.47	-32.29	-64.59	4	187.3597	-48402.5
60	1	4	06/06/2015 4:52		Philippines	4.82	2370.41	24.69		$ 47.64	-93.85	-187.70	4	74.0754	-55616.2
61	0	4	03/06/2015 5:34		Philippines	88.46	2379.45	24.79		$ 841.60	3.86	7.72	1	74.35768	574.2841
62	1	2	04/06/2015 11:10		Philippines	97.6	2518.37	52.47		$ 917.05	4.27	8.55	4	157.3978	5380.554
63	1	2	06/06/2015 11:52		Australia	33.94	4683.99	97.58		$ 462.70	-1.10	-2.20	4	292.7495	-2573.85
64	0	4	03/06/2015 12:34		Australia	31.32	4697.03	48.93		$ 449.35	-1.62	-3.23	1	146.7823	-474.746
65	0	4	03/06/2015 2:19		Philippines	36.44	2360.58	24.59		$ 351.97	-4.06	-8.12	1	73.76805	-599.335
66	1	4	07/06/2015 3:43		Indonesia	5.5	1188.19	12.38		$ 37.42	-84.11	-168.21	4	37.13101	-24983.4
67	1	4	06/06/2015 6:52		Australia	108.45	6302.01	65.65		$ 1,572.81	9.89	19.78	4	196.9378	15585.2
68	0	4	04/06/2015 7:56		Australia	41.95	5421.69	56.48		$ 601.85	2.43	4.86	1	169.4278	822.7041
69	0	4	03/07/2015 2:01		Thailand	112.79	1401.44	14.60		$ 977.84	4.24	8.47	1	43.7951	371.0799
70	1	2	05/07/2015 4:28		Thailand	6.12	1989.18	41.44		$ 58.82	-72.09	-144.18	4	124.3239	-71698.3
71	0	4	04/07/2015 7:37		Vietnam	28.77	2374.91	24.74		$ 198.36	-10.48	-20.97	1	74.21601	-1556.24
72	1	2	06/07/2015 11:50		Philippines	58.14	2419.40	50.40		$ 558.36	1.00	2.01	4	151.2122	1214.275
73	1	1	03/07/2015 3:41		Philippines	74.24	2508.50	104.52		$ 707.40	2.79	5.59	4	313.5626	7007.853
74	1	2	05/07/2015 8:14		Australia	48.94	5522.00	115.04		$ 662.74	3.33	6.65	4	345.1249	9181.139
75	1	4	07/08/2015 12:24		Philippines	21.65	2414.08	25.15		$ 202.00	-13.76	-27.53	4	75.44004	-8307.12
76	0	4	04/08/2015 1:06		Philippines	26.91	2605.40	27.14		$ 254.72	-9.11	-18.23	1	81.41876	-1484.24
77	1	4	06/08/2015 1:48		Philippines	65.75	2454.65	25.57		$ 627.81	1.94	3.89	4	76.70792	1192.887
78	0	1	03/08/2015 3:12		Philippines	10.7	2461.87	102.58		$ 108.74	-36.57	-73.13	1	307.734	-22505.4
79	1	2	05/08/2015 4:57		Australia	67.45	5552.70	115.68		$ 974.50	7.03	14.07	4	347.0435	19531.17
80	0	4	03/08/2015 6:00		Australia	45.37	5549.14	57.80		$ 644.12	3.18	6.35	1	173.4106	1101.694
81	0	4	05/08/2015 6:42		Australia	86.24	5994.33	62.44		$ 1,234.24	8.51	17.03	1	187.3227	3189.7
82	1	4	06/08/2015 7:03		Philippines	79.07	1764.88	18.38		$ 748.24	3.14	6.28	4	55.15236	1385.205
83	0	4	04/08/2015 8:06		Thailand	43.3	1477.52	15.39		$ 360.96	-3.21	-6.42	1	46.17235	-296.527
84	0	4	05/08/2015 1:43		Australia	7.09	6260.22	65.21		$ 99.03	-56.55	-113.11	1	195.6318	-22127.6
85	1	2	07/08/2015 4:10		Australia	1.3	3980.59	82.93		$ 24.29	-365.93	-731.86	4	248.787	-728311
Sheet1

Cell Formulas
Range	Formula
K2:K85	K2	=(J2-$R$3)/F2
L2:L85	L2	=K2*2
M2:M85	M2	=IF(A2=0,1,4)
N2:N85	N2	=H2*3
O2:O85	O2	=L2*M2*N2