Determining all potential costs from each scenario to index the invoice amount

  • 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.

  • Cross-posting Without Telling Us


    Your post does not comply with our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.


    Post a link to any other forums where you have asked the same question.


    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.


    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!


    Read https://www.excelguru.ca/content.php?184 to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

  • Hello,


    You should take advantage of our Forum great feature : Attaching a sample file ...


    It would not only clarify your objective ... but also allow potential contributors to test solutions ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Thanks for your sample file.


    Just had a look at the Before / After Sheets ...


    Your formula in Column B looks fine ... so what is the problem you need to solve ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • The problem I have is that there are more scenarios that each have many rules. I’m trying to figure out an efficient way to go about determining if the invoices will match one of the many scenarios that aren’t included by creating a much larger index. I don’t know how to go about doing it when there are 100+ more scenarios that can happen. The index table I made is beyond simplified.

  • I may even have to see if combinations of different costs are possible. I’m not at that point yet, but using VBA to loop through and see if adding different costs together in the index can lead to the invoice amount seems like it would make the code run for days. However, if there is no simple solution to combining costs in the index, figuring out how to create all potential costs to index off of, or any other method besides indexing that may be recommended, is difficult for me to wrap my head around. Any help is beyond appreciated.

  • Thanks for your clarifications


    So, there is a need to simulate all possible combinations of your five different assumptions ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    Are they lower limits and upper limits for each one of your Five criteria ( Columns I to M ) ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)