前言
應收賬款的核銷是件費時費力的事，即使是使用財務軟件也必須十分小心，一不留神就會張冠李戴。本文將針對物業(yè)行業(yè)周期性收費的特征，介紹如何利用Excel的Power Query功能來實現(xiàn)應收賬款的自動清賬。
思路
根據收費性質的不同，設置不同的清賬策略
- 水電物業(yè)費（先開票后收費） 具有周期性，先出賬單后收費，出具賬單后根據賬單金額開票，并將開票金額及所屬會計期間錄入Excel表格，收到客戶的水電物業(yè)費時，判斷費用的所屬會計期間，將負數金額計入該期間，若該客戶在該期間的水電物業(yè)費匯總金額為零則標記自動清賬。
- 其他款項（先收費后開票） 收到款項時即開具發(fā)票，一般情況下當月清賬，但遇到月底關賬，需要跨越開票，這種情況下，采用的方法是將該客戶同一收費項目下收到的所有款項和所有開票金額匯總，若合計數為零則全部標記自動清賬，若不為零則需要配合手動清賬。
數據錄入
每一條記錄需要包含的字段有商鋪名稱、收費項目、會計期間、賬單期間、金額、入賬日期、憑證類型。
- 會計期間 是指應收賬款所屬期間（一般為開票月份），會計期間有可能晚于賬單期間（先出賬單下月開票）。
- 金額 正數代表開票，負數代表收到款項（可以是銀收、現(xiàn)收，也可以是押金抵扣等）
-
憑證類型 根據需要可以設置期初、銀收、現(xiàn)收、轉賬等
image.png
添加一列以年份月份表示的會計期間
=IF(ISBLANK([@會計期間]),"",TEXT([@會計期間],"yyyymm"))
創(chuàng)建輔助表
-
收費項目分類表
按照收費性質將收費項目分為兩類，編號為1的收費項目屬于先收費后開票，編號為2的收費項目屬于先開票后收費。
image.png - 輔助表1（已清賬項目_不含水電物業(yè)）
-
點擊“自表格/區(qū)域”
image.png -
篩選出先收費后開票的項目
image.png -
按收費項目和商鋪名稱分類匯總
image.png -
匯總金額四舍五入去掉尾差
image.png -
篩選出匯總金額為0記錄
image.png -
刪除總金額列后點擊關閉并上載
image.png -
上載后生成輔助表1
image.png
- 輔助表2（已清賬項目_水電物業(yè)費）
-
篩選出先開票后收費項目
image.png -
按照收費項目、商鋪名稱和會計期間（年月）分類匯總
image.png -
匯總金額四舍五入去掉尾差
image.png -
篩選出匯總金額為0記錄
image.png -
刪除總金額列后點擊關閉并上載
image.png -
上載后生成輔助表2
image.png
添加清賬標志
- 自動清賬
=CHOOSE(VLOOKUP([@收費項目],收費項目分類,2,0), //用choose函數選擇不同處理方法
COUNTIFS(已清賬項目_不含水電物業(yè)[收費項目],[@收費項目],已清賬項目_不含水電物業(yè)[商鋪名稱],[@商鋪名稱]), //商鋪名稱和收費項目出現(xiàn)在已清賬項目清單中則標記1
COUNTIFS(已清賬項目_水電物業(yè)費[收費項目],[@收費項目],已清賬項目_水電物業(yè)費[商鋪名稱],[@商鋪名稱],
已清賬項目_水電物業(yè)費[會計期間（年月）],[@會計期間（年月）]))//商鋪名稱、收費項目和會計期間均出現(xiàn)在已清賬項目清單中則標記1
- 清賬標志
=OR([@手動清賬],[@自動清賬])
-
根據清賬標志設置條件格式
紅色代表未清賬，綠色代表已清賬
image.png -
3249條記錄經過自動清賬后還剩550條數據
image.png
數據刷新
錄入數據后，只有點擊“數據”->“刷新”，Excel才會去已清賬項目清單中查找匹配項標記自動清賬
