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!

 

[Gotham]

wow, that's cool.

Anyway, this is a sample of my workbook and I have included the current algorithm I am using.

Maybe I should give some explanation to help you understand better.

Each row represents one shipment.

Late delivery risk = 1 (means there is a late delivery risk for that particular shipment) or 0 (means there is no late delivery risk)

My algorithm consists of three parts :

Late delivery risk weightage (4 points if the late delivery risk is 0 and 1 point if late delivery risk is 1)
Speed of delivery weightage (3 points)
Earnings per package (I am assuming $500 is the shipping expense for every shipment for the purpose of the project)

The challenge I am facing is - I am not sure which is the most influential factor among all the weightages in my algorithm. If I could find out which it is, I could allocate the points based on the importance. Perhaps, would a regression analysis help?

I am also receptive to new algo ideas!

 

[igold]

Two things:

1) Although you explained what you were doing, I am not clear as to what the goals are. Is there a goal that is more important than others or is it a combination of factors. You talk about Late Delivery Risk, how is that computed with the given data. What makes a delivery late, and how with the given data do you differentiate which delivery will be on time and which will be late.

2) I am guessing you are somewhere down under. Here in Florida, USA, it is dinner time ( 8:26PM), and I have not started cooking yet. That is late for most Americans, but my other half and I prefer to eat late. That said, I have got to stop here. If you could post as much info as possible, I will jump on first thing tomorrow and see what is what.

 

[Gotham]

1. The points system I use is progressive. Higher the DER, the more efficient the shipment (based on my algo). The main problem is the late delivery issue faced by the company. That is the reason I used the lowest point for late delivery (1 point if there is a late delivery risk represented by number 0 in column A) because you can still earn a good earning per shipment even though you have a late delivery risk. In other words, your speed of delivery risk can be very high, you might be earning a lot of profit from that shipment, but if there is a late delivery risk, I don't think there is efficiency. So, I would consider late delivery risk as the most important factor. However, I don't think this is always the case for every shipment. So, I would like to construct an algo that can allocate points in a case-to-case basis rather having a fixed raw number to each factor. By the way, I am not sure how the company computed the late delivery risk (1 or 0).

I am not sure if I made it clear enough. Feel free to ask me if you are confused.

2. I stay at Singapore (8:42am). To be honest, I didn't expect anyone to be really committed to my problem. You are very kind. Thank you so much for helping out. Have a great dinner!