Forum Discussion
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.
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.
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:
- 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:
- Enter the values in cells A1, B1, C1, and D1 as described.
- In another cell (e.g., E1), enter the formula =ABS(A1 - (B1 + C1 + D1)).
- 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.
