Results 1 to 4 of 4

Thread: How would I go about determining all potential costs from each scenario to maybe inde

  1. #1
    Neophyte spencerwi's Avatar
    Join Date
    Sep 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016

    How would I go about determining all potential costs from each scenario to maybe inde



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,604
    Articles
    0
    Excel Version
    365
    cross posted without links:
    https://www.mrexcel.com/forum/excel-...ere-match.html
    https://www.ozgrid.com/forum/forum/h...invoice-amount
    https://stackoverflow.com/questions/...-scenarios-vba
    https://www.reddit.com/r/excel/comme...for_a_billing/

    spencerwi/spencermwi
    , for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

  3. #3
    Neophyte spencerwi's Avatar
    Join Date
    Sep 2019
    Posts
    2
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by p45cal View Post
    cross posted without links:
    https://www.mrexcel.com/forum/excel-...ere-match.html
    https://www.ozgrid.com/forum/forum/h...invoice-amount
    https://stackoverflow.com/questions/...-scenarios-vba
    https://www.reddit.com/r/excel/comme...for_a_billing/

    spencerwi/spencermwi
    , for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184
    I read it and I agree with the logic. My apologies. I will post links going forward

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,675
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Please add links to ALL your cross posts. there are still some missing

    (Note: this requirement is not optional. No help to be offered until the link is provided.)
    Thank you Ken for this secure forum.

Posting Permissions

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