Current accounting is the accounting of any collection rights or payment obligations incurred by an enterprise in connection with business activities, including receivables and payables, advance receipts and advances, other receivables and payables, etc.
When your boss asks you to check if your current accounts are correct, where do you start? At the beginning of 2022, the little secretary presents you with customer reconciliation skills, which are divided into three parts:
1. Self-check after the balance is settled
Focus on reviewing the size and direction of the current account balance to see if it is normal. If there is an abnormality, turn to the detailed account, the detailed account should check whether the debit and credit party has an abnormal amount and summary, and the corresponding bookkeeping and original documents should also be confirmed clearly to prevent the wrong amount. If the self-examination is correct, it is necessary to record the date, amount, invoice number and other information of the voucher to facilitate reconciliation with the other unit.
2. Check the balance with the other company
If the balances of both parties are consistent, prove that both sides are recorded correctly.
If there is no consistency, then combine the digital tool to calculate the difference between the two, check the nodes, determine the time point at which the error occurred, and analyze its direction and size:
a. Determine whether there is a business occurrence of the same amount as the difference? Are there any outstanding items and should a "balance reconciliation" be prepared to reconcile?
b. Is there a difference of half/double the amount of the business occurred?
If the analysis still can't be analyzed, continue to the next step: the amount of occurrence check.
3. There are two common methods for checking the amount of occurrence
- Reconcile debit (or credit) occurrences separately, which does not require chronological checking;
- If you want to narrow the scope of the detailed reconciliation, you can check the debit (or credit) amount in the unit current account to find the corresponding business. If such operations do not have an impact on the balance, the problem may exist in operations that do not have a loan counterpart.
Today we're going to use an example of how to apply digital tools to customer bill reconciliation
Based on the accounts receivable voucher data, we count the transactions with a company in 2020, or the customer accounts receivable transaction data with unliquidated items in 2020, and use Python to make customer statements.
1
Data preparation
Export all accounts receivable data from the accounting system and load the data into Python:
import pandas as pd
import openpyxl
from openpyxl.styles import Font,Border,Side,Alignment,PatternFill,Color
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.utils import get_column_letter
pd.options.display.float_format = '{:,.2f}'.format
file = '财会实验数据.xlsx'
df = pd.read_excel(file, sheet_name='客户往来账')
df.fillna(0,inplace=True)
df 
2
data processing
If we want to send a statement to a customer, we need to meet two conditions first, if the customer balance is 0, and there is no business dealing with the customer in 2020, we do not need to reconcile with him, so this part of the data is excluded.
a. Filter customers whose balance is not 0 first
v = pd.pivot_table(df, index='客户编号',values='本币金额',aggfunc='sum')
EL1 = v[(v['本币金额']>0.0001)|(v['本币金额']<-0.0001)].reset_index()
# 根据非0客户列表，筛选应收账数据
df1 = df[df['客户编号'].isin(EL1['客户编号'])] # DataFrame.isin()方法的主要任务是选择特定列中具有特定(或多个)值的行。条件前加~表示isin函数的逆函数
df1 
b. Filter the data of customers with business transactions in 2020
df2=df[df['年']==2020]
df2 c. Combine the results of the two screenings, and we're sending the raw data of the customer statement
df3=pd.merge(df1,df2,how='outer')
df3 3
Data grouping
We make statements by customer, one table per customer, do a function, and write a Sheet for each customer's data after grouping, and of course, we can also write a workbook/workbook for each customer. The tool we use here to write Excel is openpyxl.
wb = openpyxl.Workbook()
def writesheet(x):
ws = wb.create_sheet(title=str(x.name))
for r in dataframe_to_rows(x, index=False,header=True):
ws.append(r)
df3.groupby('客户编号',as_index=False).apply(writesheet)
wb.save('客户对账单.xlsx') You can see that the system has generated the form, but the necessary instructions and formatting are missing.
4
Data output
Do some styling on the generated statement, so let's rework a function:
wb = openpyxl.Workbook()
def writesheet(x):
ws = wb.create_sheet(title=str(x.name))
ws.append([str(x.name)+'对账单']) #插入一行单元格数据作为标题
ws.append(['日期','摘要','凭证编号','金额','备注']) #插入表头
data = x[['过帐日期','摘要','凭证编号','本币金额']]
for r in dataframe_to_rows(data, index=False,header=False): #按行插入表格
ws.append(r)
ws.append(['合计','——','——',data['本币金额'].sum()])# 插入合计数
ws.append(['请核对，如无误请在七天内签名后回传我司，谢谢！']) #再加一些文字说明
ws.append(['某某公司财务部制作'])
# 调整表格样式
ws.merge_cells('A1:E1') #第一行合并单元格
for c in ws['A']: #第一列居左
c.alignment = Alignment(horizontal='left')
ws['A1'].alignment = Alignment(horizontal='center',vertical='center') #第一行居中
ws.column_dimensions['A'].width = 20 #调整列宽
ws.column_dimensions['B'].width = 30
ws.column_dimensions['D'].width = 18
ws.row_dimensions[1].height = 25 #调整行高
for c in ws['D']: #调整数据格式
c.number_format = '##,##0.00'
for c in ws['A']: #调整日期格式
c.number_format = 'yyyy-mm-dd'
for row in ws[1:2]+ws[ws.max_row-2:ws.max_row]: #字体加粗
for c in row:
c.font = Font(bold=True)
df3.groupby('客户编号',as_index=False).apply(writesheet)
wb.save('客户对账单.xlsx') In the case, we combined with Python to complete the reconciliation of customer bills, friends of the financial code, will everyone meet?
© The copyright of the picture belongs to the digitization of the financial code, if there is any objection to the copyright, please contact the background to negotiate the processing.
This article is published by the digital collation of the financial code, if you need to reprint please be sure to indicate the above information.