+ Reply to Thread
Results 1 to 21 of 21

Formula Help - Basic Math Question [year-over-year operating ratios]

  1. #1
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Formula Help - Basic Math Question [year-over-year operating ratios]

    I have a math equation that I have a question on. My Excel formula works properly but my match isn’t quite right.

    I am working a P&L statement for a shipping company. We figure our O/R on a variety of things but in this case, I want to know if we had the same revenue per shipment this year as we did last year, would our O/R be better or worse based on the number of shipments we had.

    Here’s the formula I’m using and my figures for those cells.
    C68/(C10-((C14-D14)*C13))

    C14 is revenue per bill for current year: $78.94
    D14 is revenue per bill for previous year: $82.33
    C13 is total bill count: 3,723
    C10 is total revenue: $293,908
    C68 is total terminal cost: $259,947.00

    1. C14-D14 gives us the difference in the revenue per bill: -3.39
    2. Multiply that times the number of shipments, C13: -12,620.97
    3. Subtract Total Revenue in C10 from that and that’s where this thing gets weird to calculate. It’s a negative number.
    4. Lastly, that answer gets divided into C68, which is the total terminal cost or C68.

    When there is an increase in revenue per shipment, the formula works fine but since there was a decrease, it’s throwing the math off.

    Total Revenue: $293,908.00
    Revenue per shipment was down -$3.39
    Multiplied by 3,723 shipments
    Equals -$12,620.97 so without the additional revenue per shipment revenue would have only been
    $293,908 minus -$12,620.97 = $306,528.97 (That’s $293,908 minus a negative number, which equals a positive result in Excel)

    So if our costs remain the same as shown
    $259,947.00/$306,528.97 = The O/R changes to 84.81 versus 88.45

    This doesn’t make sense to me because if there was a drop in revenue, the operating ratio could not have been better.

    Any ideas how to solve this?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question

    First it seems a bit inside out to be doing your calculations based on revenue per shipment then scaling it up to total revenue. You have to calculate revenue per shipment first before you even start what you described above, then you are calculating back to total revenue again. This will cause you to lose precision, if you have done it correctly.

    The second problem is that you didn't say whether C13 was for current year or previous year, but regardless, you can't use the same number for both years. I am assuming you did not have exactly the same number of shipments in both years.

    The third problem and biggest problem is you should not subtract the difference in revenue per shipment, you should add it. That is why your result is the opposite of what you expect.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332
    We could probably debate what this formula tells us, that’s for sure. I can’t disagree there. Either we operated better or we didn’t. However, we base many things on shipment count. With that being said, we are looking at whether not we operated better from a ratio standpoint because of our increase in revenue or did we truly operate better because of efficiency. So, to figure this, we are basically saying, if we didn’t have that price increase this year, would we have operated better or worse with that same revenue per bill from last year but with the cost of this year.

    This shipment count you asked about is this year.

    If I change the formula to add the difference, my result is a negative percentage. The number is correct but it’s a negative.

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    If I change the formula to add the difference, my result is a negative percentage. The number is correct but it’s a negative.
    Your revenue went down and costs were held fixed, so O/R went down, so the percentage change should be a negative.

  5. #5
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by 6StringJazzer View Post
    Your revenue went down and costs were held fixed, so O/R went down, so the percentage change should be a negative.
    No, it's still should be a positive number. It's impossible for an O/R to be a negative number. It can go down, yes but it cannot be below zero.

    If a company's revenue is $100 and they spend $75 to make that $100. The O/R is 75% or .75 - This is no different.

    =C68/(C10-((C14-D14)*C13)) is 84.81
    =C68/(C10-((C14+D14)*C13)) is -84.81

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by Gtrtim112 View Post
    No, it's still should be a positive number. It's impossible for an O/R to be a negative number.
    That is correct but the positive/negative we are talking about is not the O/R, it is the change in O/R, which can be negative.

    You started with these two key points:
    [1]
    $293,908 minus -$12,620.97 = $306,528.97 (That’s $293,908 minus a negative number, which equals a positive result in Excel)
    Here is the error. It should not be minus a negative number, it should be plus a negative number. (If the difference were positive, then it would be plus a positive number.)

    [2]
    This doesn’t make sense to me because if there was a drop in revenue, the operating ratio could not have been better.
    It doesn't make sense because in fact the O/R was worse, not better.

  7. #7
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by 6StringJazzer View Post
    Here is the error. It should not be minus a negative number, it should be plus a negative number. (If the difference were positive, then it would be plus a positive number.)
    Ok... On the other two items, I understand what you are saying. This one has me a little stumped. I completely understand your explanation. However, when I change the formula to add instead of subtract, how come my answer doesn’t come out differently aside from the negative sign in front?

  8. #8
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    =C68/(C10-((C14-D14)*C13)) is 84.81
    =C68/(C10-((C14+D14)*C13)) is -84.81

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    I built a spreadsheet based on your description. Your description did not clearly say which figures were for which year, except for C14 and D14, so I think I was in error in talking about adding vs. subtracting. Nevertheless, I see an error in your description.

    The yellow cells are the figures you mentioned in your first post, where you said what cells they are in. I put them in the same cells.

    The blue cells are figures you mentioned and showed how you calculate them but did not indicate a cell.

    The red cell is the previous year's O/R. Nowhere in your posts above did you show how you got an O/R of 88.45 for the previous year. In fact this number is 81.45.

    So from last year to current year, your O/R goes from 81.45 to 84.80, getting worse.

    See attached. If you have further questions it will be essential to attach your own file.
    Attached Files Attached Files

  10. #10
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Thanks so much for all the help. I appreciate it. I will download and look at your attached file when I can get to a PC. I will also forward my file so you can see the whole thing.

    Have a good evening!

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Sorry this is dragging out, hope it is helping. I'll be watching for further posts.

  12. #12
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    I tried out your formula but it still didn't work for me. I'm not sure how you figured the current O/R without having the data to do so. I am attaching a more full version so you can see what I'm talking about. The number in question is on the PD 1 VC and PD 2 VC tabs. I have done two examples. PD 1 appears to be incorrect and PD 2 appears to be correct. The formula is the same for both of them. Because PD 1 is getting a negative number within the formula, it is not producing the proper result.

    See if this helps... thanks for the patience!
    Attached Files Attached Files

  13. #13
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by Gtrtim112 View Post
    I'm not sure how you figured the current O/R without having the data to do so.
    I don't understand this statement. In your first post you calculated prior and current O/R yourself. I just used the same data and the same math that you did.

    I'll take a look at your file.

  14. #14
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by Gtrtim112 View Post
    PD 1 appears to be incorrect and PD 2 appears to be correct. The formula is the same for both of them. Because PD 1 is getting a negative number within the formula, it is not producing the proper result.
    Why do you think the result is incorrect? It looks OK to me. In both cases the O/R went down from 2017 to 2018, and so the change is negative.

    I do find it unusual that you are showing the change as a percentage of a percentage; you are subtracting two percentages and dividing the result by the 2017 O/R. Normally when comparing percentages you would just do the subtraction and stop.

    Please Login or Register  to view this content.
    I would expect to see the calculation as a straight subtraction with a result of 88.45% - 95.85% = 7.41%

  15. #15
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332
    Quote Originally Posted by 6StringJazzer View Post
    I don't understand this statement. In your first post you calculated prior and current O/R yourself. I just used the same data and the same math that you did.

    I'll take a look at your file.
    In my first post, I didn’t calculate the prior year O/R. I simply stated what it was.

  16. #16
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332
    Quote Originally Posted by 6StringJazzer View Post
    Why do you think the result is incorrect? It looks OK to me. In both cases the O/R went down from 2017 to 2018, and so the change is negative.

    I do find it unusual that you are showing the change as a percentage of a percentage; you are subtracting two percentages and dividing the result by the 2017 O/R. Normally when comparing percentages you would just do the subtraction and stop.

    Please Login or Register  to view this content.
    I would expect to see the calculation as a straight subtraction with a result of 88.45% - 95.85% = 7.41%
    I’m thinking you may not understand O/R. To go down in percentage is positive. The lower, the better. There’s no way for the PD 1 to have been better when there was a decrease in revenue per shipment. The O/R should be been a higher percentage than the previous year.

  17. #17
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by Gtrtim112 View Post
    I’m thinking you may not understand O/R. To go down in percentage is positive. The lower, the better. There’s no way for the PD 1 to have been better when there was a decrease in revenue per shipment. The O/R should be been a higher percentage than the previous year.
    You are correctly using Total Revenue and Terminal Total Cost to calculate O/R. Yes, your revenue per shipment went down, but you had a large increase in volume of over 14%. Therefore your total revenue went up significantly, almost 10%. At the same time, your total costs went up by only a tiny amount, only about 1%. Therefore your O/R went down from 2017 to 2018. Revenue per shipment doesn't matter. Negative change in the number, positive performance indicator.

    Notwithstanding, that still does not address the reason that your calculation is taking a percentage of a percentage in E72.

  18. #18
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Column E has no bearing on what we are trying to figure out. Forget that it even exists. It’s not part of the equation. We use those percentages for something else.

    You are correct... we had more volume and it is definitely a positive indicator. Because of this volume, we were able to operate at a lower operating ratio. However, that’s not what I’m trying to figure.

    We are asking ourselves this: If we made the same amount of money per shipment as last year but with this year’s number of shipments and this year’s cost, how would our Operating Ratio have looked? Would we still have operated good without that increase in revenue per shipment?

    The formula works fine as long as their was increase in revenue per shipment year over year but when there’s a decrease in revenue per shipment year over year, the result is skewed.

  19. #19
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    I keep chasing your question, which has evolved over the course of the thread. Your question in this latest post is different than the questions you asked (and I tried to answer) previously.
    Quote Originally Posted by Gtrtim112 View Post
    We are asking ourselves this: If we made the same amount of money per shipment as last year but with this year’s number of shipments and this year’s cost, how would our Operating Ratio have looked?
    You may have been thinking this question all along but this is not the question you started out asking.

    The answer to the question above is:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    C68 is "this year’s cost". C13 is "this year’s number of shipments". D14 is "same amount of money per shipment as last year".

    I am still not sure what you think is wrong with your solution. This number (84.81%) is lower than the actual O/R for this year (88.45%). So your O/R for this year would have been better if your cost and volume were the same as this year but you made the same revenue per shipment as last year. Your revenue per shipment was much higher last year, so of course your O/R would be higher.

    This is equivalent to the formula you have in C74, although is simplified. I am not going to show a formal proof here but will if you ask.

    So you already have the correct formula and it gives the correct answer to your latest question.

    To recap:
    Post #1
    This doesn’t make sense to me because if there was a drop in revenue, the operating ratio could not have been better.
    It was better because there was not a drop in revenue, there was a drop in revenue per shipment. The overall revenue went up while costs rose only a tiny bit.

    Post #16
    I’m thinking you may not understand O/R. To go down in percentage is positive. The lower, the better. There’s no way for the PD 1 to have been better when there was a decrease in revenue per shipment. The O/R should be been a higher percentage than the previous year.
    I'm thinking I understand O/R quite well.

  20. #20
    Forum Contributor Gtrtim112's Avatar
    Join Date
    04-12-2013
    Location
    Alabama, USA
    MS-Off Ver
    Excel 2016
    Posts
    332

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    My apologies for not being clear on the first question. That, unfortunately, is my one of my daily struggles. Eventually, it comes out but sometimes it takes a few rounds of going back and forth. Eventually, I will illustrate it correctly. Of course, written form is not always the best either. Had we been standing in front of one another, this probably would have been solved in the first hour

    So... funny thing is... I posed this question to someone else a week ago... “What if the formula is correct?” I then walked through the reason it was right to begin with but I kept getting tripped up on my own analogy, which is what brought me here. In your explanation of why the formula was correct to begin with, it makes sense to me. Again, I’m not mathematically inclined. I can build a spreadsheet and I can insert a formula but that doesn’t always mean I completely understand the formula’s calculations. I just know how to do what someone has asked me to make the spreadsheet do, if that makes sense.

    With all that being said... I believe you have solved this. There was no incorrect answer to begin with. Although, I prefer your simplified formula compared to mine

    Thank you for sticking with me on this one!

  21. #21
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Formula Help - Basic Math Question [year-over-year operating ratios]

    Quote Originally Posted by Gtrtim112 View Post
    Had we been standing in front of one another, this probably would have been solved in the first hour
    I am guessing 15 minutes, tops. I spent an hour last weekend helping my son with his calculus homework when he was home for spring break. We covered 8-10 problems. Then the other night I tried to help him over Facebook Messenger and it took like 1/2 hour for one problem.

    With all that being said... I believe you have solved this. There was no incorrect answer to begin with.
    I know I am not in your particular business but the same principles apply to my business (although we typically use margin rather than O/R, which is kind of O/R upside down and inside out). I do wonder how some of these calculations are being used but you are not asking me to be a business consultant. I'm very glad we reached the end of the tunnel on the Excel question!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatically update year interval cycles from year to year
    By trumptight in forum Word Programming / VBA / Macros
    Replies: 8
    Last Post: 08-21-2014, 10:38 PM
  2. Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  3. Basic Year to Date Formula
    By Airport in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-25-2013, 02:53 PM
  4. [SOLVED] Keeping a date as current year, even though formula determines previous year
    By dropanddrive03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2013, 08:31 PM
  5. Replies: 0
    Last Post: 11-23-2012, 01:27 AM
  6. Replies: 0
    Last Post: 09-12-2009, 11:07 AM
  7. Replies: 3
    Last Post: 03-12-2009, 09:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1