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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
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,112,771
Messages
5,542,433
Members
410,551
Latest member
Ali3ta1r
Top