This article will share with you several functions that are necessary for financial reconciliation. I believe that after reading this tutorial, your data verification work will be many times easier in the future!
Carrying out complicated reconciliation work is often a headache for financial personnel, not only because of the large amount of data, but also during the actual reconciliation process, various problems may arise. All kinds of situations can be said to be reconciliations, but the methods of handling them may be very different. Therefore, today we have sorted out some of the more commonly encountered problems for you. They can all be completed instantly using EXCEL. Let’s do it together. Take a look at the tormenting questions.
1. The simplest reconciliation problem
Data description: The left side is the system order data , the right side is manual data (generally provided by suppliers or manually entered and registered by clerks). The system data is complete. Now you need to check which orders are missing manual data.
Use the VLOOKUP function to find the manual data corresponding to the order number. Substitute the formula into the formula according to the format of VLOOKUP (search value, search range, search content, precise search). The search value is the system order number (A3 ), the search range is manual data (E:F), the order number is in the second column of the manual data, and the fourth parameter is 0 when searching accurately, there is a formula:=VLOOKUP(A3,E:F,2 ,0)
Some #N/A will appear in the data obtained using this formula, indicating that the corresponding data is not found, that is, it exists in the system data. Content that does not exist in manual data needs to be filtered out to find out the reasons.
This is the most commonly used method to check data. Sometimes we not only need to check whether the data exists, but also whether there is a difference in the order amount. At this time, it is inconvenient to use VLOOKUP. You need to use Another function SUMIF.
The idea is to use the SUMIF function to sum the order amount of the manual data according to the system order number, and then subtract it from the system order amount. According to whether the result is 0 and where the difference is, enter the formula in cell D3:
=SUMIF (E:E,A3,F:F)-B3, double-click to fill in the formula, the specific effect is as shown in the figure:
The format of the SUMIF function is: SUMIF (condition area, condition, summation area). In this example, the condition area is the manual order number (column E), the condition is the system order number (A3), and the summation area is the manual order amount. (Column F).
If the difference is 0, it means that the system data matches the manual data. There are two cases of data with a non-zero difference. One is that there is no corresponding manual data, and the other is that the manual data exists but the amount is inconsistent. , this is easy to see when combined with the previous VLOOKUP results.
For example, there is no #N/A error in cell C9 in the picture above, but the value in cell D9 is not zero, indicating that the order data was entered incorrectly.
For more standardized data, it is also very convenient to check. It can usually be solved by using VLOOKUP and SUMIF functions. However, in actual work, you will encounter some less standardized data, so continue to look at it.
2. Slightly troublesome reconciliation issues
On the right side is the system data, only retained There are four columns, which may actually be many columns. Useless columns can be eliminated during verification. On the left is manually registered data, with only three columns.
There is nothing much to say about the system data. Some systems are relatively complete and the exported data is relatively standardized. If we want to find fault with the system data in this example, we can only say that the registration in this expense type is too simple and is basically useless. information.
Looking at the manual data again, the problem is more obvious. There are two problems:
First, the date format is not standardized. Using a decimal point as the year, month, and day separator in the date is probably a lot of decimals. It’s my partner’s habit, but Excel will not treat this format as a date;
Secondly, the date column registration is incomplete, maybe for laziness, there are many empty cells, it is estimated that the empty cells are the same as the above cells The dates in the format are consistent. This is also the entry habit of many friends.
To get such data, you must first process column A. The processing method is: select the data area, press F5 or Ctrl G to open the position, locate the null value and confirm, enter =, and press the direction key ↑ , press the Ctrl key and press Enter to complete the filling; then select the data area, copy and paste it as a value, click Column, directly select the date format in the third step, and you are done. See the animation demonstration for the specific operation.
After the data processing is standardized, it is time to check the differences. In this example, it is necessary to determine which data have different amounts on the same date. This includes two conditions: date and amount. Therefore, consider using the SUMIFS function, the basic structure is SUMIFS (sum range, condition range 1, condition 1, condition range 2, condition 2), or use system data as the basis to check manual data. Enter the formula in cell I3 as: = SUMIFS(B:B,A:A,E3,B:B,H3)-H3, double-click to fill.
#If the difference is zero, it means that the data is completely consistent. If it is not zero, you need to filter it out to find the reason for the difference.
Because there is not much data, it can be seen that two transactions of 8,000 appeared on the same date. When we use SUMIFS to sum, the two transactions will be summarized. In fact, there is no real difference. . For such cases where the dates are the same and the amounts are the same but the specific uses are different, it is troublesome to directly use formulas to judge when checking. You can consider using auxiliary columns to make repeated judgments:
in Use the formula =COUNTIFS($A$2:A3,A3,$B$2:B3,B3) after manual data, which means to count those with the same date and amount. When selecting the range, be sure to check the starting position of the range. Add $ to lock, so that the range of the formula will increase when it is pulled down, and when repeated data appears, the result will also increase.
Similarly, the system data is also processed in this way. The formula is: =COUNTIFS($E$2:E3,E3,$H$2:H3,H3)
After completing the two auxiliary columns, the formula for checking the amount becomes three conditions:
=SUMIFS(B:B,A:A,E3,B:B ,H3,D:D,I3)-H3, double-click the fill to see the result. A negative number means that the item is not entered in the manual data.
Today I used two examples to analyze common ideas for data verification. When doing more complex verification work, you only need to master VLOOKUP, SUMIF, SUMIFS, COUNTIF and COUNTIFS If you are good at using auxiliary columns among these functions, you can basically find the differences quickly.
Related learning recommendations:excel tutorial
The above is the detailed content of Excel function learning: several functions you must know for financial reconciliation (share). For more information, please follow other related articles on the PHP Chinese website!