Formula for finding variance

Copper Contributor

Hi Community members, 

 

I need help in finding correct formula for variance. I am an internal auditor in a company where I need to reconcile credit cards transaction for the whole business day. 

I have 4 cells in an excel sheet which looks like below:

1st cell: External service merchant sending daily transactions record

2nd cell: Transactions processed and recorded in the venue

3rd cell: Transactions taken through manual terminal

4th cell: Manual transactions manually recorded in venue's software

 

For example, say at a certain day, venue's cc payments received were 10,000$ out of which $9500 were through normal terminal, and 500$ were through manual terminal. Now when I will receive data for the day through external merchant, it will be 9500 and 500 to be recorded manually through our's end, so, the data in the cell should look like this: 

1st cell: 9500

2nd cell: 9500

3rd cell: 500

4th cell: 500

 

Now, can anyone pls help me suggesting the formula where, if there is any variance (say any previous refunds recorded current day which then exceeds current day manual transactions) a formula that gives me correct variance when put the data?

 

Much appreciated.

1 Reply

@Shayanbana 

To calculate variance between the recorded transactions and the external merchant's data in Excel, you can use a straightforward formula. The variance in this context would typically mean the absolute difference between the recorded amount and the expected amount from the external service provider.

Let us denote the cells as follows:

  • Cell A1: External service merchant's daily transactions record
  • Cell B1: Transactions processed and recorded in the venue
  • Cell C1: Transactions taken through manual terminal
  • Cell D1: Manual transactions manually recorded in venue's software

Based on your example:

  • A1 (External service merchant's record) = 9500
  • B1 (Transactions recorded in venue) = 9500
  • C1 (Manual terminal transactions) = 500
  • D1 (Manual transactions recorded manually) = 500

Now, to calculate the variance, you can use the following formula:

=ABS(A1 - (B1 + C1 + D1))

Here is how it breaks down:

ABS formula

  • A1: External service merchant's record, which is the expected total.
  • B1 + C1 + D1: This represents the total recorded transactions in the venue, including both transactions processed normally and manually.

The formula subtracts the total recorded transactions (B1 + C1 + D1) from the expected total (A1), and then takes the absolute value (ABS) to ensure the variance is positive regardless of whether the recorded total is higher or lower than the expected total.

Example Calculation

Using your example values:

  • A1 = 9500 (External service merchant's record)
  • B1 = 9500 (Transactions recorded in venue)
  • C1 = 500 (Manual terminal transactions)
  • D1 = 500 (Manual transactions recorded manually)

The formula would be:

=ABS(9500 - (9500 + 500 + 500))

=ABS(9500 - 10500)

=ABS(-1000)

=1000

So, the variance in this case would be $1000.

Handling Negative Variance

If the recorded transactions (B1 + C1 + D1) exceed the external merchant's record (A1), the formula will still return a positive value because of the ABS function. This ensures that you always get the absolute difference, regardless of whether it's a surplus or a shortfall.

Implementation

To implement this in your Excel sheet:

  1. Enter the values in cells A1, B1, C1, and D1 as described.
  2. In another cell (e.g., E1), enter the formula =ABS(A1 - (B1 + C1 + D1)).
  3. Excel will automatically calculate the variance for you based on the values in A1, B1, C1, and D1.

This approach provides a clear and simple way to track and reconcile the discrepancies between your recorded transactions and the external merchant's data. The text and steps were edited with the help of AI.

 

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.