Forum Discussion

Shayanbana's avatar
Shayanbana
Copper Contributor
Jul 01, 2024

Formula for finding variance

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.  ...
Formulas and Functions
NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Jul 11, 2024

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.

Resources