採購如何利用Excel做好貨款對帳結算管理?

圖1

我們在小企業中發現，由於沒有合適的ERP系統軟體，或者即使有，但是缺乏系統的培訓，很多員工無法適應軟體的使用。畢竟ERP系統是比較「死板」的，而很多小企業，一方面人員配備不夠齊全且流動性較為頻繁，另一方面員工的工作經驗也不夠豐富，在很多情況下完全無法適應ERP軟體。

有些人寧願使用更加靈活的office辦公軟體，但是很多辦公軟體由於是前人設計留下的。隨著企業的不斷發展，有些模板顯地越來越陳舊，也影響了工作效率，為此筆者根據實踐結合小企業的特點，設計了一套關於採購結算付款方面的模板，讓大家擴展自己的思維，設計出一套屬於自己的模板。

一、採購崗位和財務崗位對於貨款管理關注點不太一樣，我們先看一下採購付款單位財務流程：

圖2：流程圖

二、應付貨款臺帳

圖3：工作簿

以office 2016為例，我們設計了圖3這個工作簿，整個工作簿包含了四張工作表，即：《匯總表》、《明細表》、《採購臺帳》和《基礎信息表》。

（一）基礎信息表

圖4：基礎信息表

我們先設計基礎信息表，如圖4所示，該表需要採購員做好日常維護，一般情況下，供應商很少會經常發生變化，所以平時不會花佔用太多時間去維護基礎信息，這裡我們主要用到的數據是「帳期」一列的數據。當然如果有些朋友想做地更細點，也可以在表格後面加入其它信息，比如對帳聯繫人、電話、地址等等。

（二）採購臺帳

圖5：採購臺帳

採購臺帳是需要採購員經常維護的一個工作表，採購入庫的數據信息，如果有進銷存系統的，可以從系統裡導出數據，粘貼在此處。

我們著重說一下裡面最後三列內容，因為考慮到由於種種原因，比如入庫單價填寫錯誤，或者部分退貨原因，導致結算數量和入庫數量不一致，或者結算金額和訂單金額不一致，筆者設計了這三列。同時，發票號碼一旦填入，也就說明了和對方對帳完畢，該行的數據有結算或者部分結算。朋友們也可以在這個表後面再加上一列「未結算數量」，會更細緻一點。

（三）明細表

圖6

明細表是關於收到發票和申請付款的明細，由於採購人員申請付款後，接下來付款事項已經移交給了財務部門，個人覺得不必要對此再進行整個跟蹤管理，只需要有用到時再和財務人員溝通，比如供應商打電話諮詢貨款未到，而採購已經早已提交了付款申請流程，那麼這個時候採購就應該和財務人員及時溝通，查明是哪一個環節出現問題。

因此，在設計模板時，筆者沒有加入付款金額，只是加了一列「申請付款時間」，這列有數據，就說明已經提交申請。

如圖所示，報表分為手工填列區域和公式區域，下面我們著重舉例說明一下公式區域：

1.E列帳期（天數），主要的數據來源就是基礎信息表中C列的數據，利用VLOOKUP公式，進行查詢到的，由於明細表第一列「供應商簡稱」不固定，因此我們設計這個公式來獲取數據。

以E4為例，公式為=VLOOKUP(A:A,基礎信息表！A:C,3,0)，我們以圖形的形式解釋一下：

圖7：VLOOKUP公式

2.F列票到付款日期，公式比較簡單，就是發票日期+帳期（天數），在excel裡，日期也是可以加減的。

3.G列超期天數，是用了一個if函數，我們先看一下if函數的用法：

=IF(測試條件，結果1，結果2)，意思是說如果符合測試條件，那麼就輸出結果1，否則就輸出結果2，簡單吧！

我們用G4單元格為例：=IF(H4="",TODAY()-F4,0)，意思是說，如果H4單元格為空值（空值我們用「」表示），那麼就輸出TODAY()-F4，否則就輸出0。

那麼有人要問了TODAY()-F4又是什麼？

比如今天是2019年6月15日，你在excel隨便找個單元格輸入=TODAY()，你發現了什麼？對是一串數字43631，那麼你選中單元格，點擊右鍵「設置單元格格式」，設置成日期格式，點確定，是不是出現了「2019-06-15」？

明白了嗎？=TODAY()表示現在的時間，那麼減去F4,就是表示，從現在開始到付款日期還有多少天，如果是大於0，表示已經超期了。

（四）匯總表

圖8：匯總表

匯總表的數據來源是就是《明細表》和《採購臺帳》，見圖8，公式主要是SUMIFS公式：

1.SUMIFS函數是條件求和公式，滿足所列示的所有條件才會被匯總求和。

語法：SUMIFS(求和區域， 列1, 條件1,列2, 條件2, ……)

2.B列公式，B4=SUMIFS(採購臺帳！I:I,採購臺帳！A:A,匯總表！A4),意思是說，匯總採購臺帳第I列數據，但是要滿足以下條件：採購臺帳A列的供應商簡稱要和匯總表中A4是一樣的。這個公式只需要滿足一個條件即可。

3.C列公式，C4=SUMIFS(採購臺帳！K:K,採購臺帳！A:A,匯總表！A4),意思是說，採購臺帳中A列的數據和匯總表A4數據一樣的，全部匯總求和。

4.E列公式，E4=SUMIFS(明細表！D:D,明細表！A:A,匯總表！A4);

F列公式，F4=SUMIFS(明細表!D:D,明細表!A:A,匯總表!A4,明細表!H:H,"*")，這兩個公式不再一一說明，可以參考上述公式自己想一下，很簡單，有必要提示一下，*屬於通配符，「*」意思說有數據，非空值。

5.審核驗證公式

所有表格設置完，記得要設置審核驗證公式，以免數據匯總錯誤，我們都還不知道呢。審核公式就是我們將匯總表裡的合計數字，和相應的數據來源表裡的合計數字相減，看是否有差異，有差異說明上述公式取數有問題，找到原因修正，保證數據的有效性。

朋友們，有空可以自己動手嘗試設計一下，可以設計出屬於自己的一套模板。