Variance Analysis

Hi,

I have been given a set of services and charges to determine if there is a variance in the prices that were set and what was charged. Some nuances exist though. For example:

Service 1:
1. Base cost: $100
- nuance 1: each additional add on is $11.52
- nuance 2: if 5 or more add ons are purchased, the overall cost is reduced by 10%
- nuance 3: delivery is an extra $15.60
- nuance 4: if the box is returned for recycling, a discount of 15% is given

This is just a random example. There are 10s of thousands of bills and I am trying to determine if the final bill can be derived from these sets of rules before sending any back to accounting as being questionable. I am not given the amount of add ons purchased and I am not given if they were delivered, however, I am given if the service is "Service 1" and if the box was returned for recycling. Any help is beyond appreciated.

I'd recommend that you upload sample workbook with some sample bills and what the expected outcome should be for few items.

From your description, there isn't direct method to do variance analysis. You'll need to construct a model to calculate potential outcome possible from parameters provided and see if any match that of bill(s) provided. i.e. create lookup table of possible price list based on parameters.

Also, order of nuance/parameter application is important here.

So from your list of parameters, I've created price table for Add-on count up to 10.

Then, you can use something like below.
In D2:

Formula:

Please Login or Register  to view this content.

See attached.

EDIT: if you have service other than 1. That can be added as additional condition check.