+ Reply to Thread
Results 1 to 20 of 20

Billing Variance Analysis

  1. #1
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Billing Variance Analysis

    Hi,

    I had posted a much simplified version of this under the "General" section of the forums here yesterday, however, my task has gotten way more complicated. Essentially, I was given tens of thousands of bills charged to customers. I am trying to determine if the bills are potentially accurate. The only data I have is the actual bill itself. Here's where things get complicated. There are four scenarios, each with their own nuances:

    Scenario 1 Nuances:
    1. Base Fee of $10 * the amount of items bought * the number of deliveries (maximum amount of items bought would be 10 and the maximum number of deliveries would be 7)
    2. If the amount of items bought exceeds 1, the total bill is discounted by 20%
    3. If the boxes are recycled, the total bill is further discounted by 15%
    4. If the good is over 15 pounds, an additional charge of $10 will be assessed per each item over 15 pounds (maximum amount of items would be 10)
    5. If there is no adequate port for delivery, an additional $50 per hour will be charged per delivery (maximum hours would be 3)

    Scenario 2 Nuances:
    1. Business that share a port of delivery for the second service will be charged $11 per item
    2. If the business does not share a port of delivery, the fee is $13 per item. Each additionally delivery for a business that does not share a port is $15 per delivery, in addition to each item costing $13 (maximum deliveries would be 7 and maximum amount of items would be 8).

    You see where I am going with all of this. The next two scenarios are just as lengthy. Sadly, no information besides the total bill is given. We don't know which scenario the bill belongs to, the number of items, etc., just a single column of the invoice. How would I go about determining all potential costs from each scenario to maybe index the invoice and see if there is a match? Any help on this would be amazing, whether it be in VBA or not.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Billing Variance Analysis

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Billing Variance Analysis

    Attached is an example as requested. Thank you so much for helping :D
    Attached Files Attached Files

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Billing Variance Analysis

    I'm struggling to understand the requirement and indeed how you use this in practice. You seem to start with a single column of invoice values and end up with a table of costs that contain all your Invoice values plus others along with some analyis by weight and other factors. But without knowing what factors were used to calculate each invoice I struggle to see how you obtain the table of costs and analysis.

    Hi,

    It's not at all clear (at least to me) what it is you're trying to achieve. In any case this is clearly a much simplified example and probably doesn't represent the size of your actual data. We often find that when we give an answer to what is a trivial non representative example when the solution is used with the real world data it doesn't work because of factors like string length and other stuff which is important but hasn't been mentioned.

    Remember that when you ask a question in a forum those of us who might be able to help know absolutely nothing about your system and work process or the terminology you use. Unless you have uploaded a workbook we know nothing about how it is laid out and little about what you want done with it or how the results should be presented.

    Whilst these things are second nature to you since you live with the workbook all the working day we start with zero knowledge so you need to take us by the hand and explain in quite precise detail your aims and goals as well as stuff like how your data might look different in other situations.
    Don't assume we are able to "figure it out". We're usually quite good but not prescient.

    Put yourself in our position and think about whether what you've posted makes sense and is unambiguous to someone who's never seen it before.
    You are asking us for help so help us to be able to help you by providing all the information we need, even if that information seems obvious or intuitive to you.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Billing Variance Analysis

    Understood. Essentially, I am given a list of invoices to clients. I need to see if their bills fit into any of the possible cost scenarios. For example, You could have two items bought, one that is less than 15 pounds, one that is greater than 15 pounds, they end up recycling, and they do not have an adequate delivery station which leads to an hour long delivery. Under the assumptions in scenario 1, we get the following cost:

    Cost = 80%*(85%*((2*$10)+(1*$10)+(1*$50)))

    To explain the above equation:
    - 2 items were bought and they are $10 each (2*$10)
    - 1 of the items were over 15 pounds so an additional $10 fee is added on to that item (1*$10)
    - there was not an adequate delivery station and the cost per hour is $50 when there isn't an adequate station (1*$50)
    - The goods were recycled (85%)
    - There was more than 1 item bought (80%)

    I did the math wrong in the workbook I had attached which led to even more confusion and I apologize. My goal is to see if the invoices (column A), match any potential combination of sub-scenarios under scenario 1, scenario 2, etc... I know I haven't listed out all of the scenarios, but I am trying to figure out how to go about finding if an invoice amount in column A could result from any possible scenario. If not, I want to flag them. The example I gave above is clearly one of many potential costs that could be output from Scenario 1. Let me know if I need to clarify further.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Billing Variance Analysis

    I understand the arithmetic but still don't see how it relates to the specific invoices in your list.

    Take the $93.50. If you don't know how many items this comprised or the weight of each item how do you start to decide whether it should appear in column J or K.
    In addition how do you know if it's recycled or whether there's an delivery port available.

    For each of the three Invoices in column A that exist in column H would you explain exactly how you determine the entries in J:M. We need to understand the calculations with reference to specific data cells and the rules you apply to them. The diffulty I have is that you seemingly create all the stuff on the 'After' sheet from a single column of values on the 'Before' Sheet.

    How incidentally do you get the column H values. Or are you actually saying that Index Column H is something you start with? i.e. it should have been on the 'Before' sheet. Even so I still need to understand the specific calcs/rules mentioned above.

  7. #7
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Billing Variance Analysis

    The issue is, we don't know the amount of items, the weight, etc... We are only given the invoice amount. All we are given is the column of values on the "Before" sheet. Sadly, no other data is provided. Clearly, we will get a plethora of false-positives, but that is okay at this point. I need to see if the invoice total that we are given is possible. The index I made was just an example of how I am going about trying to determine if the total is even possible among the many scenarios.

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Billing Variance Analysis

    But surely given there are 5 rules for setting an invoice value, it's extremely likely that one combination of basic cost, quantity, weight, recycling & delivery port might suggest the invoice value is possible, but a different combination might just as easily say it isn't. In other words it seems highly likely that in most cases there may not be a definitive answer.

    The only thing that seems certain is that an invoice <=$10 must be single item under 15 lb which HAS an adequate delivery station. Once you start getting above $10 and particularly in the $100 range then it seems increasingly likely that any combination might be valid.

    ..or maybe I'm missing something vital here. Perhaps SOLVER might be brought into play in some way.

    Just out of interest what business requirement is this aiming to solve? Is it some sort of audit check for valdity and trying to use statistics to calculate the likelihood of the invoice being a valid or fraudulent one?
    If so you may find, given a big enough population of data that you can use Benfords Law (sometimes referred to as the first digit law) to determine the chance of an invoice being outside the normal expected value. It doesn't use the sort of rules you're trying to use, it's much simpler in fact, but it may have a part to play. It has been used to detect fraud in the banking industry for example.

  9. #9
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Billing Variance Analysis

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

    Cross posted on at least 5 other forums under the username "spencerwi" or a variant thereof

  10. #10
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206
    The issue is, there are much much more than 5 rules. I haven’t listed them all here. I’m trying to figure out how I’d go about doing 20+ rules since that adds much more complexity. Also, at this point, I only need to see if the invoice total can match a potential cost. There may be a lot of flaws to doing that, but it is for auditing purposes as you noted and that’s what I’m forced into doing.

    I’m going to look into that law as you mentioned. Any other advice is highly appreciated as well.

  11. #11

  12. #12
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Billing Variance Analysis

    Please add links to ALL your cross posts
    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Billing Variance Analysis

    EdwardSnowden... thanks for the cross links. There are quite a lot of them!! A friendly word of advice. You are welcome to cross-post, as long as you declare it. before helping on a problem, I'd certainly check one declared cross-post to see if you had had an answer, but it's unlikely that I'd check two and ABSOLUTELY would not check 4 other fora, to see if you had had a nice answer.

    I would ignore your question and move on.

    Your choice and your risk.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  14. #14
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Billing Variance Analysis

    Quote Originally Posted by Glenn Kennedy View Post
    EdwardSnowden... thanks for the cross links.
    And still one missing ( that I know of)

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Billing Variance Analysis

    At risk of offending moderators -- if all cross posts are not yet linked, I will add this (because it really doesn't solve the problem). The problem seems very similar to a subset sum problem https://en.wikipedia.org/wiki/Subset_sum_problem Assuming your problem really fits the NP-complete properties of a subset sum problem, then the approach you are taking may be the most reliable method to solve the problem. Generate a list of all possible combination of charges within the pricing structure, then use a lookup/countifs to see if the charges in your invoice list are in the list of possible charges.

    Two problems I see. 1) Spreadsheets are not a very good programming language for generating permutations/combinations. I started exploring the possibility here: https://www.excelforum.com/tips-and-...thout-vba.html but I find it is generally not very easy to work with. Other programming languages seem to be better for generating permutations/combinations because of the loop structures other programming languages have available to them.

    2) 5 options is fairly manageable, but these problems expand dramatically as the number of options expands. Going from 5 to 20 (and who knows how many possible variations there are within those 20) is going to become quite large -- at least initially creating the possible charge list. If you can get the possible charge list created, then you should never need to create it again -- just reference it in your lookup/countif that will test if a given invoice charge is present in the list.

    The Wikipedia list mentions different dynamic programming options for solving the subset sum problem. If you have the programming expertise to understand and implement one of those, then it might be worth some effort to explore those possibilities -- especially if the pricing structure can change from scenario to scenario, since that is the computationally difficult part of the problem.

    As involved as this possibly becomes -- is there a specific part of the problem that you need help with? Narrowing the question down to what you really cannot do yourself will allow us to focus on what you don't understand about the problem.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  16. #16
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206
    Quote Originally Posted by Pepe Le Mokko View Post
    And still one missing ( that I know of)
    I literally cannot find the other one I posted and those are the ones I remember. If you found it, can’t you post it? I’m not trying to be shady, and I clearly attempted by posting the ones I remembered, but this seems a bit much at this point. I’m not going to do that again and the point is lesson learned, no?...

  17. #17
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Billing Variance Analysis

    I guess the issue was figuring out the best method. I appreciate all the advice and will look into all these recommendations once I return to a computer. Thank you everyone and if you have anything else to recommend, I greatly appreciate it.

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Billing Variance Analysis

    I know I can't help on this issue, as I am worse than hopeless with VBA....

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Billing Variance Analysis

    Edward, a word of advice here regarding getting help on multiple forums, like you are doing,.

    Although we (and other forum mods/admins) cannot prevent you from x-posting (well, we can apply a ban if the situation becomes persistent), it is unlikely that this behavior will go unnoticed for any length of time - most seniors here, are members on other forums.

    The outcome is that members (here and on other forums) will become less and less inclined to offer help when there is a possibility that you have already received an answer on another forum, and they would simply be wasting their time.

    So option 1 would be to stick with 1 forum until you get an answer, option 2 would be to share a link to other forums (and ON other forums) that you have asked the same thing. I would also keep those other links to a minimum.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  20. #20
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Billing Variance Analysis

    Quote Originally Posted by EdwardSnowden View Post
    I literally cannot find the other one I posted and those are the ones I remember. If you found it, can’t you post it? I’m not trying to be shady, and I clearly attempted by posting the ones I remembered, but this seems a bit much at this point. I’m not going to do that again and the point is lesson learned, no?...
    So you don't mind setting lots of people to work and you don't even remember where? I'll sure they'll appreciate
    Try https://www.excelguru.ca/forums/showthread.php?10248

+ 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. [SOLVED] Variance Analysis
    By EdwardSnowden in forum Excel General
    Replies: 4
    Last Post: 09-25-2019, 01:06 PM
  2. VBA for computing variance analysis
    By megaheinz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-09-2015, 10:35 AM
  3. VBA for Variance Analysis
    By megaheinz in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-09-2015, 05:56 AM
  4. Very difficult variance analysis for
    By amartino44 in forum Excel General
    Replies: 0
    Last Post: 12-05-2013, 06:04 PM
  5. Variance analysis
    By rhyan66 in forum Excel General
    Replies: 0
    Last Post: 04-18-2011, 11:14 AM
  6. Variance Calculation and Analysis
    By Mayank Trivedi in forum Excel General
    Replies: 3
    Last Post: 05-03-2010, 01:15 PM
  7. [SOLVED] using excell for analysis of variance (anova)
    By onrevsop in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 09-22-2005, 06:05 PM

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