How would I go about determining all potential costs from each scenario to maybe index the invoice and see if there is a match?

Spencerwi

New Member
Joined
Sep 27, 2019
Messages
1
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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,315
Office Version
  1. 365
Platform
  1. Windows
Cross posted on multiple sites

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Please supply links to ALL sites where you have asked this question.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,358
Messages
5,547,464
Members
410,795
Latest member
santa12345
Top