Trucking Company Pay

  • Hi there


    Here is the Scenario, A friend of mine who works for a Trucking company asked me this Question; This is How their company calculates His Pay for the Trips that he Makes.
    @ first 100 Meter + Loading A Value of 1 Which will be Multiplied by the weight of the Load “L” and then a Flat Fee of “X’, And Off course The weight is not of any Issue for us for time being.


    @ first 100 Meter + Loading A Value of 1 * X
    @ Next 500 Meter a value of Max. 500-100=400 => 400/100= 4, then A Value of 4 * Y, Then if Total Traveling is 350 Meter, There we will have 1*X*L + 2.5*Y*L
    And again L is not important and for time Being Could left Out.
    @ Next 10 Kilometer A value of [Number of Kilometer - .5]*Z Then Max would be 9.5 * Z
    @ Traveling of Over 10 and Less Than 30 Kilometer would be [Number of Kilometer – 10] * W Then a Traveling of 27 would be 17 * W
    @ Over 30 Kilometer a Value of [Number of Kilometer – 30] * V
    And Finally all these would be added up accordingly.


    So far so good, and There is no Problem and Easily I can Use Excel To Calculate Every thing for me Just by Entering the Total travel amount and the Weigh of the Load, Using If, And Vlookup.
    But Here it gets Complicated, They have these Following additional Conditions;


    1: If the Traveling Road is a High way or Major Paved Roads Then all the above Values would be multiply by .77
    2: If the Road is a Semi Paved Road then all the Above Values would be multiplies by .9
    3; If the Road is a Dirt Road then all above would be Multiply by 1


    For Example if the Traveling is 17 Kilometer and 2 kilometer is Dirt at the Beginning, and 2 Kilometer is the semi Paved at the end and the Rest are Fine Paved Road.


    1*X*1+ 4*Y*1 + 1.5*Z*1 + 8*Z*(.77) + 5*W*(.77) + 2*W(.9)


    Any Help on how to do this would be Highly appreciated.


    Regards
    jhonExcel

  • Re: Trucking Company Pay


    Well, first of all, and correct me if I'm wrong, it sounds like you're mistaken about some things. If it's a semi-paved road, you multiply the value by .9? You're saying he's paid LESS to go down a semi-paved road? Shouldn't it be he's paid 90 percent EXTRA (1.9)? And you say a Dirt Road should be multiplied by 1 (times itself)? I would think you meant he's paid DOUBLE (times 2); after all, it's obviously more difficult. Maybe, I'm mistaken, as this amount may have already been included in the above amount, and so this could be an additional amount you mention.


    In any case, what I'd do is have some cells relating to the various conditions. Say in one area you calculate the normal trip amount. This amount is shown in, say, cell A10. Have another cell next to that (say in cell B10) with a Data Validation drop-down box with either "Dirt" or "No Dirt" showing. Then, have a cell next to that (say in cell C10) for where you'd put in the Dirt Road Distance. Then, next to that, in cell D10, put in an IF formula--something like this:
    =IF(B10="Dirt",2*C10,0)
    Then, do similar things with the other areas, and have them all added together.


  • Re: Trucking Company Pay


    Hi bythecringe2


    Thanks for the reply.


    But that is what I want, for the first 100 m there is a fixed amount of Pay that Includes the Loading, as my attachment (Which is just the Start of my work on this issue) You can see that the Breaking any Distance to the Required amount is no Problem and the application works just fine For Example:
    If you Enter a Travel distance of 37 km, We do get Values of .1, .4, 9.5, 20, 7
    And That is just Fine, and Then Using 1 Vlookup once for the Unit Cost of each Category Then we are Fine. But my Problem is for the Second Criteria


    Please read the Original Posting More Carefully, I am sure this requires
    A Matrix Multiplication, But I just do not How To develop those required Matrixes.


    regards
    jhonexcel

  • Re: Trucking Company Pay


    I'm sorry, but as I see it, you have a very complicated calculation spelt out in your first post. The first part of which you say you can work out easily. Then you provide a workbook that has three prominent constants, the one for Paved being 15. And collections of formulas with no explanations, labels, headings, nothing.


    The very first formula I look at is in cell C15,
    =IF(OR($B15>0.1,$B15<0.5),0.1,B15)
    and as I have pointed out, the value 15, from your constant, cell F13, via cell B15, will never come though that formula, because it will always equate to 0.1.


    I cannot help you if you cannot see that this must be fixed. If you need help to fix it, fair enough. But it would help a lot if you labelled what each formula is supposed to do, using comments, maybe.

  • Re: Trucking Company Pay


    Thank you very much for the reply.


    I try to Explain the whole thing again,
    The Goal is to calculate the Pay For a Driver for Carrying a Load "L" a Distance "DD" The Whole System of Pay Goes Basically around the Distance
    and the weight, But the weight Does not Change at all through Calculation therefore for the ease of Calculation we will leave it Out for time Being.


    But the Distance;
    They Pay For the Loading and the first 100 meter Of Traveling a Fixed amount of X which will be Multiplied by The Load "L" to come Up with the Pay amount.
    Therefore for the first 100 meter would be 1 * X * L .
    “and here is the .1 (100 m) that I tried to obtain in my application.”
    Now second for the next 400 mm They Pay an amount of Y
    Again If Total Traveling is 350 m Then 350-100(first payment)=250
    250/100=2.5 there would be an additional pay of 2.5 * Y * L
    Now the .5 Km > Traveling < 10 Km The Pay would be No. Of Kilometer
    Multiply by Z , Again if Total Traveling is 8 km, Then we would have;
    1*X*L + 4*Y*L + 7.5*Z*L
    And so on for the Rest.
    Usually They Pay to drivers Like this A Load of “L” Was Carried for Total distance of
    Example “37 Km” a Value in Cell of F9 in My application, And The First 8 Km is a Dirt Road The Cell F12, and The last Part of 9 Km is Semi Paved Road “Cell J12, And The Rest “37-10-9” ”cell N12” would be in High Ways.
    Now if you enter a value of for example 77 in Cell F12 (Just a Test for Cell F12) You would get the Following Answers in Range “D14:D18” , “ 1 , 4 , 9.5 , 20 , 47” Which are just What we are Looking for. And Going Back to my Example a Value 8 in F12 Produces “1 , 4 , 7.5” Which again are just Fine.
    But From know on it Gets Difficult, The reason of that we Require Three Level is
    Because of The Different Ratio of Road conditions (1, .9, .77)
    Now going Back to my Example 37 Kilometer Total Traveling it Should produce following equations; (I disregard the Value of “L”)


    1*X*(1) + 4*Y*(1) + 7.5*Z*(1) + 2*Z*(.77) + 18*W*(.77) + 2*W*(.9) + 7*V*(.9)


    As You can see The Max value for ;
    X is 1, Which is actually .1 Km
    Y is 4, Which is actually .4 Km
    Z is 9.5 , Which stands for 9.5 Km
    W is 20 , Which stands for 20 Km
    V would be any Number Stands for No. Of Km


    And if we add the Numbers we would get (.1+.4+7.5+2+18+2+7) = 37


    If you are not still clear please let me know regards


    jhonexcel

  • Re: Trucking Company Pay


    Dear ByTheCringe2


    It seems like my previous attempts werenot clear enough.
    Well I am attching a file for better explanation.




    Here is the Real Challenge I need To Brake Up any Given Number Like “D” to Following Fashion;
    In order to Fill 15 Boxes The sum of First Three Boxes are to be .1, If I reach .1 on the First Box the Other 2 will be 0
    The Next 3 Boxes will have a Max of .5, again if the First or the Second or the Sum of firs and second Becomes to .5 Other Box or Boxes will be 0
    The Next 3 Boxes will have a Max of 9.5, and again, if the First or the Second or the Sum of first and second Becomes to 9.5 the Other Box or Boxes will be 0
    The Next 3 Boxes will have a Max of 30, and a Min of 10, and, again, if the First or the Second or the Sum of first and second 30 the Other Box or Boxes will be 0
    And so on


    Please look at the attachment for better Explanation.


    Regards
    jhonexcel

  • Re: Trucking Company Pay


    OK, I can see you have put a lot of effort into this, so let's see if I can help. Firstly, I can see what I think are errors. Have a look at these suggestions: S/BE means should be.


    First part is OK.


    Remainder:
    In cells C42:G44, $A$21 S/BE $A$40
    In cells C48:G50, $B$21 S/BE $B$40


    Last:
    In cells C62:G64, $A$21 S/BE $A$60
    In cells C68:G70, $B$21 S/BE $B$60


    One more thing, Should cell E37 be 5? I think it should be 4.5 (9.5 - 5).


    If you agree, could you make those changes and re-attach?


    Then we'll see how it looks.

  • Re: Trucking Company Pay


    That looks good. Assuming everything you have put in is right, the total of the First, Remainder and Last seems to be 29.7. Is there anything left to do?


    Do please explain how you get 5 in E37 though. And perhaps figures like that can be derived from the previous workings with a formula?

  • Re: Trucking Company Pay


    Dear ByTheCringe2


    Thank s for the reply.


    I do not know how did you get 29.7 from.


    The sum of first and Last are 37 as you can see in range, M13:O13


    These are the Given in any Situation Total Distance of C1
    The First Part of Traveling C2, The Last Part of Traveling C3
    (Off course on some Scenarios, we may have no first or Last and all the Road is Paved, Or Dirt, and so on, but These are the Variables) And The Reminder of C4=C1-(C2+C3)


    and these are the Facts About the Traveling Rules;
    The First .1 Km has a Unit Cost of X, which can be Broken into 3 Pieces of
    First, Last And the reminder, In Real Situation Off Course all it will be Applied to the First because of its Short distance( Actually this is the Loading Fee),
    And the Same f or the Second segment of Traveling from .1 km to .5 km (This part usually Goes to the First Part of Road Condition, with Unit Cost of Y.
    Going Back to the Example of the Attachment We have a Total Traveling of 37 km
    Which First 5 km is let’s say is Dirt (By Using the Validation Combo Box in A21, A40, A60 we can Give any condition to them) Therefore 5 km will be broken to .1 (range 0 To .1), .4 (for the range .1 To .5), 4.5 For range (.5 km To 10 km) Since in the Range (.5 to 9.5), The First Traveling had filed a 4.5 of it therefore a 5 km of it is left


    From the Attachment


    Given Data First Reminder Last Total
    37 0.1 0 0 0.1
    5 0.4 0 0 0.4
    4 4.5 5 0 9.5
    28 0 20 0 20
    0 3 4 7

    5 28 4 37


    Rules;


    Sum of the First Row (First, Reminder, Last) has a Max Value of .1 km
    Sum of the Second Row (First, Reminder, Last) has a Max Value of .4 km
    Sum of the Third Row (First, Reminder, Last) has a Max Value of 9.5 km
    Sum of the Fourth Row (First, Reminder, Last) has a Max Value of 20 km
    Sum of the Fifth Row (First, Reminder, Last) has a Max Value of Any


    And also These are True for any Given Example


    Sum of the First Column (First) is Equal C2 (in our Example 5 km)
    Sum of the Second Column (Reminder) is Equal C4 (C4 = C1- (C2+C3)) in our Example 28 km
    Sum of the Third Column (Last) is equal to C3


    Regards
    jhonexcel

  • Re: Trucking Company Pay


    Quote from jhonexcel

    I do not know how did you get 29.7 from.


    The sum of first and Last are 37 as you can see in range, M13:O13


    You are talking distances. I was talking prices, and I got 29.7 by summing cells H32, H51, and H71. Except that I got it wrong, it should be 33.3...


    So if that is right, are we done? I haven't gone through all the rest of your post in case this straightens things out anyway.

  • Re: Trucking Company Pay


    Hello ByTheCringe2


    How are we doing on this issue?
    Are you still working on it, Or Did you forget about?
    Any help or Reccomandation is Preciated.


    regards
    jhonexcel

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!